LINQ Hidden Gem

This tip wont be useful to most, but in case you find your self wanting to have a “default” in your query check this out.

So lets suppose you have a table for languages ( I have denormailzed for an easier example, Code should be in a separate table):
Language:

ID Code Description
1 EN English Description
2 SP Spanish Description

Now assume you want to create a method to return the description based on code.  If the code doesn’t exist return English.  Well you would need to either A do two queries, the first to query for the code, and the second to get the default if your results were empty:

  IEnumerable<Language> result = (
     from l in Languages
       where x.Code== Code
      select x
    ).ToList();

   Language languageDescription = result.SingleOrDefault();
    if(string.IsNullOrEmpty(languageDescription )){
     //Get default!
    }

Well there is a better way.. and i can’t find any documentation on it. Here it is it involves using an order by:

    IEnumerable<Language> result = (
          from l in Languages
            where x.Code== Code  || x.Code=="EN"
          order by x.Code=="EN"
         select x
      ).ToList();
  Language languageDescription = result.FirstOrDefault();

It works! So this basically says put EN last. Anything that doesn’t match comes before “EN” so we can now just do a FirstOrDefault. Not the difference this time we want FirstOrDefault because we could have more than 1 entry.

So how does it work?  Well this is what SQL gets generated:

  SELECT
     [Project1].[ID] AS [ID],
     [Project1].[Code] AS [Code],
     [Project1].[Description] AS [Description]
  FROM ( SELECT
     CASE WHEN ("EN" = [Extent1].[Code]) THEN cast(1 as bit) WHEN ("EN" <> [Extent1].[Code]) THEN cast(0 as bit) END AS [C1],
     [Extent1].[ID] AS [ID],
     [Extent1].[Code] AS [Code],
     [Extent1].[Description] AS [Description]
     FROM [dbo].[Langages] AS [Extent1]
  )  AS [Project1]
  ORDER BY [Project1].[C1] ASC

It uses a bit that is 1 if equal and 0 if not. It then orders by that bit thus the reason why “EN” will always be last.

Advertisements
Tagged with: , , ,
Posted in C#, LinkedIn, Microsoft, Programming

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: