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 –

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.