I thought I would share a nice small tweak I made to a LINQ to SQL model.
Assume I have the following table structure for ‘Categories’:
I faced with the need to select categories while ordering it by the combination of Description1 and Description2 (Note: nullable fields).
I entered some dummy data to the table to illustrate the point:
The goal is to retrieve the order as follows: 1, 3, 5, 2, 4
After explaining that, lets forward to the LINQ statements then.
Take 1 – Simple Aggregation
var query = from c in Categories
orderby c.Description1 + c.Description2
select c;
query.Dump();
Result:
That is not the desired result and that’s ok, it was expected.
Lets look at the generated SQL statement:
SELECT [t0].[Id], [t0].[Name], [t0].[Description1], [t0].[Description2]
FROM [Categories] AS [t0]
ORDER BY [t0].[Description1] + [t0].[Description2]
The way SQL aggregation works is in case it encounters NULL in one of the aggregated fields, the entire aggregated expression is NULL.
That is why we get this order.
To make things short, we need this kind of SQL statement:
select * from categories
order by IsNull(description1, ”) + IsNull(description2, ”)
That will give us the right result, which is:
So let’s get that result already..
Take 2 – Null Consideration
var query = from c in Categories
orderby (c.Description1 == null ? string.Empty : c.Description1) + (c.Description2 == null ? string.Empty : c.Description2)
select c;
query.Dump();
Good news – that yields the results I intended for.
However, the generated SQL isn’t quite what I aimed for –
— Region Parameters
DECLARE @p0 NVarChar(1) SET @p0 = ”
DECLARE @p1 NVarChar(1) SET @p1 = ”
— EndRegion
SELECT [t0].[Id], [t0].[Name], [t0].[Description1], [t0].[Description2]
FROM [Categories] AS [t0]
ORDER BY (
(CASE
WHEN [t0].[Description1] IS NULL THEN CONVERT(NVarChar(500),@p0)
ELSE [t0].[Description1]
END)) + (
(CASE
WHEN [t0].[Description2] IS NULL THEN CONVERT(NVarChar(500),@p1)
ELSE [t0].[Description2]
END))
You can see that LINQ to SQL translated it into the piece of order-by statement which is acceptable from the LINQ query I wrote, it did get me the results I needed too.
Still, I was looking for using the built-in SQL IsNull function as seen in take 1 as my final goal.
Take 3 – Using IsNull Function
In order to use the “IsNull” function, I need to define it.
I added the method to the generated Data Context class through the partial class paradigm –
public partial class LocalTestDataContext
{
[Function(Name = “IsNull”, IsComposable = true)]
[return: Parameter(DbType = “NVarChar(MAX)”)]
public string IsNull(
[Parameter(Name = “field”, DbType = “NVarChar(MAX)”)] string field,
[Parameter(Name = “output”, DbType = “NVarChar(MAX)”)] string output)
{
return ((string)(this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())),
field, output).ReturnValue));
}
}
Then I can go on and use that function as part of my LINQ queries –
var ctx = new LocalTest.LocalTestDataContext();
var query = from c in ctx.Categories
orderby ctx.IsNull(c.Description1, “”) + ctx.IsNull(c.Description2, “”)
select c;
query.Dump();
..And that’s the way the cookie crumbles, got my ordered results using the built-in IsNull function.
you could also use the ?? operator which would translate to the COALESCE T-SQL function (which works pretty much the same as ISNULL)
Thanks for the note Gigi, this could be another nice way to tackle this thing.
Do keep in mind that COALESCE is considered to be slower than IsNull though.
What happens if you just “orderby” in the LINQ without caring about nulls?
Locarno,
This was the initial take that I tried (Take 1).
I used the simple order by technique provided by LINQ on both the fields.
You can see that I didn’t get the desired result, although it was expected since I wanted to order by according to both fields together which may be null.