One of the best things about LINQ to SQL is that is really does have solid support for stored procedures. However, it's not quite as friendly as dynamic LINQ to SQL with scalar values because you can't take advantage of anonymous types. Instead you must return an actual known type. Furthermore, this can't be a primitive so you can't return string or bool directly. You also can't use a class that doesn't have a default (parameterless) constructor so that also rules out nullable types like Nullable<bool>.
Consider a trival stored procedure that looks like this:
1: CREATE PROCEDURE [dbo].[GetFirstName]
2: @ID int
3: AS
4: BEGIN
5: SELECT FirstName
6: FROM Contacts
7: WHERE ContactID = @id
8: END
We just want to return a single scalar string here. So we have to create a dummy class that looks like this:
1: public class DummyClass
2: { 3: public string FirstName { get; set; } 4: }
Then our DataContext method can look like this:
1: [Function(Name = "dbo.GetFirstName")]
2: public ISingleResult<DummyClass> GetFirstName(int id)
3: { 4: IExecuteResult result = this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(), id);
5: return (ISingleResult<DummyClass>)result.ReturnValue;
6: }
And add to that, we'll need an extra consuming method that defensively checks for an empty resultset:
1: public static string GetFirstName(int id)
2: { 3: using (MyDataContext dataContext = new MyDataContext())
4: { 5: var result = dataContext.GetFirstName(id).FirstOrDefault();
6: if (result != null)
7: { 8: return result.FirstName;
9: }
10: else
11: { 12: return null;
13: }
14: }
15: }
So all this works just fine but it seems like a lot of work to go through just to get a simple scalar value from a stored procedure. Not to mention that I don't want to have to go through this every single time I need a scalar value from a stored procedure in my app. Visual Studio will essentially generate the DummyClass for me but what if I'm hand-crafting my LINQ to SQL and am not currently using Visual Studio code generation? I don't want to have to go through that every time. So what can we do to make this slightly more developer-friendly?
First off, I want to avoid code duplication if at all possible. Not only if I return multiple scalars at different places in my app but also I'd prefer to avoid the duplication if these scalars are different data types (e.g., sometimes strings, sometimes Boolean, etc.). What we can do is to create a simple generic class called Scalar that looks like this:
1: public class Scalar<T>
2: { 3: public T Value { get; set; } 4: }
This means I can re-write my DataContext method like this:
1: [Function(Name = "dbo.GetFirstName")]
2: public ISingleResult<Scalar<string>> GetFirstName(int id)
3: { 4: IExecuteResult result = this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(), id);
5: return (ISingleResult<Scalar<string>>)result.ReturnValue;
6: }
So since I'm using generics here, I never have to write another dummy class again - regardless of whether I've got a string or Boolean or whatever. I can always just re-use Scalar<T>.
Additionally, I had to write a consuming method that checked to see if DummyClass was null and if not return its value; otherwise return null. Now that I know I'm always going to use the Scalar class for all my scalar queries, I can move this into a re-usable extension method like this:
1: public static T GetScalar<T>(this Scalar<T> scalar)
2: { 3: if (scalar != null)
4: { 5: return scalar.Value;
6: }
7: else
8: { 9: return default(T);
10: }
11: }
So basically, all I've had to do is create that trivial Scalar<T> class and that 1 extension method which consisted of 1 IF statement and now anytime I need a scalar value from a stored procedure in my app, it becomes as trivial as writing my data context method with the re-usable Scalar<T> and this:
1: public static string GetFirstName(int id)
2: { 3: using (MyDataContext dataContext = new MyDataContext())
4: { 5: return dataContext.GetFirstName(id).FirstOrDefault().GetScalar();
6: }
7: }
Now, the one catch - the one thing to notice here is that the property name in Scalar<T> is "Value". That means that I'd have to change my stored procedure to alias my scalar data like this:
1: SELECT FirstName as Value
Although this is a downside, I believe it's well worth it in order to realize all the other benefits of quick implementation and avoiding duplicate and unnecessary code.