LINQ to SQL String Joining with a null value

Question

My query looks like this:

var products = from p in Products
               select new
               {
                  ProductId = p.ProductId,
                  Description = p.Quantity + " x " p.Price + ", " + p.ItemDescription
               };

The reason I am joinin the Description in the query, is I am doing this for multiple querys/objects, to create a history screen (kind of like an audit screen). The screen was taking awhile to load, so I am taking all of the query's and doing a

products.Concat(otherProducts);

The speed has greatly increased (2-3 minutes down to 2-3 seconds), however, if in the example, p.ItemDescription (which is a VARCHAR(50) in the database) is null, but Quantity and Price are not null, then the whole Description field becomes null.

Has anyone encountered this quirk? Anyone know of a way to have it show "4 x 5.99, " instead of just setting it to null?

Any help would be appreciated, I have been trying to work around this for a bit, and don't even know how to really search for this on google.

Answer 1

In sql terms concatenating null with any other value results in null.

So, you have two choices. First (and this is the way I would go), modify the ItemDescription field in your table to not be nullable. Set a default value of something like '' (blank). Problem goes away.

Second option, modify your linq code to coalesce the ItemDescription field to emit a blank if the underlying value is null. I'm not a linq guy, so i'm unsure of how to do this.

One of the problems with the second route is that it would have to run the coalesce function for each record in the products result set, which could have a negative performance hit.

Answer 2

This isn't a LINQ-to-SQL "quirk", this is standard SQL behavior.

If I have the expression:

columnA + columnB + columnC

And any of those columns are null, then the entire expression will be evaluated as null.

You want to coalesce the null values into empty strings. Try this:

select new
           {
              ProductId = p.ProductId,
              Description = p.Quantity + " x " + 
                            p.Price + ", " + 
                            (p.ItemDescription ?? "")
           }

Answer 3

It's not a quirk, this is default behavior on SQL server (you can use SET CONCAT_NULL_YIELDS_NULL ON|OFF on the connection to enable or disable it).

You should be able to use something like this to get around it:

var products = from p in Products
               select new
               {
                  ProductId = p.ProductId,
                  Description = p.Quantity + " x " p.Price + ", " + (p.ItemDescription ?? "")
               };

Answer 4

Try:

select new 
           { 
              ProductId = p.ProductId, 
              Description = string.Format(
                            "{0} x {1}, {2}", 
                            p.Quantity,  
                            p.Price,   
                            p.ItemDescription) 
           }