LINQ to SQL – IsNull and Ordering

January 30, 2010

tags: , ,
4 comments

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’:

cattable

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:

catrows

 

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:

res1

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:

res2

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.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

4 comments

  1. GigiJanuary 30, 2010 ב 13:09

    you could also use the ?? operator which would translate to the COALESCE T-SQL function (which works pretty much the same as ISNULL)

    Reply
  2. Amir ZukerJanuary 30, 2010 ב 18:06

    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.

    Reply
  3. LocarnoApril 22, 2010 ב 18:32

    What happens if you just “orderby” in the LINQ without caring about nulls?

    Reply
  4. Amir ZukerApril 22, 2010 ב 22:11

    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.

    Reply