Working around null values in LINQ queries

I’m almost ashamed to admit I hadn’t commonly used the ‘null coalescing operator‘ ?? in C# until recently, and was commonly writing code like

var myVar = myNullableVar == null ? myNullableVar.Value : 0;

or variations on a theme using HasValue etc (still better than the long-hand if-else mind you)

Clearly this is more readable as

var myVar == myNullableVar ?? 0;

Often I find that things break down when you introduce Entity Framework, as there’s limitations on what it will understand (from the point of view of translating to the underlying data context).  Null values though are another place you can save a bit of repetitive code, as you’ll quite often have nullable dates, or other nullable types..

var output = (
    from tab in context.MyTable
    where tab.EffectiveDate == effectiveDate
    select new 
        Code = tab.Code,
        /* Old
        Value = tab.Value == null ? 0 : tab.Value.Value
        // New
        Value = tab ?? 0

This is a pretty simple example, but in conjunction with the SqlFunctions library, you can keep things nice and neat with type conversions in your code.

It’s only when you look a little further into the language that you see c#’s got quite a nice set of operators now 🙂


LINQ Group by MAX Date Query

I’ve found some weird, wonderful and ridiculously complicated LINQ queries for getting the row with MAX(DATE) based on a key.  Most unnecessarily use lambda expressions, and some just had several interim steps.  I knew there had to be a better way, and found an unassuming post at the bottom of a StackOverflow page.

Here’s my non-lambda’d, contrived example…  assuming you’ve got an EntityFramework model (i.e. context)

            //Get Client Order with (max) order date 
            var maxclientOrder = (from clientOrder in context.ClientOrders
                               where clientOrder.OrderDate ==
                               (from clientOrder2 in context.ClientOrders
                                where clientOrder2.ClientID == clientOrder.ClientID
                                    select clientOrder2.OrderDate).Max()
                                select clientOrder).ToList();

LINQ to SQL Connection Strings with Class Library and Web.Config

Most Microsoft technologies that you can operate with a GUI come with some tradeoffs.  Things have certainly improved over the years and now something like the LINQ to SQL designer is pretty trouble free – unless of course you have something like this fairly common scenario:

I had a class library (Data Access), and decided to add LINQ to SQL classes for a new database that was being introduced.
This class library is also ultimately being consumed by WCF web services.  I have dev, test, prod environments, so I use ASP.NET Web Deployment projects to change configuration per environment for things like appSettings and connectionStrings.

It therefore followed that I wanted to configure the LINQ DataContext connection properties in web.config.  Out of the box you’ll find your connection properties go into your Settings properties class, which gets a little bit in the way.

If you start playing around with the generated classes to change where you’re getting the connection info from then any changes in the designer will wipe them out, so a (relatively) pain free approach to setting your connection safely is the following:

Go to your LINQ to SQL designer and remove the Connection String, and set Application Settings to False

Create a new partial class to mirror your DataContext, and set the constructor to retrieve from your alternative source…

using System;
using System.Linq;
using System.Configuration;
using System.Data.Linq;

namespace CodeBureau.Services.DataAccess
    public partial class MyDataContext : DataContext
        public MyDataContext()
            : base(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString, 
mappingSource) { OnCreated(); } } }

This will leave all your generated code intact, but will sort out your configuration woes.