How to concatenate strings in LINQ with proper use of null values

Question

I would like an elegant way to combine multiple columns using LINQ, but using the + or concat () operator, when any of the columns is NULL, it is NULL for the value after concatenation.

Is there something similar to concat () that handles NULL differently or am I thinking about it the wrong way?

Any help is appreciated!

Here is the code I'm using:

List<CustomObject> objects = ( from obj in ObjectTable where obj.Id == Id select new CustomObject() { EnteredBy = obj.EnteredBy, EntryDate = obj.EntryDate, WorknoteText = obj.VchWorkNote1 + obj.VchWorkNote2 + obj.VchWorkNote3 + obj.VchWorkNote4 + obj.VchWorkNote5 + obj.VchWorkNote6 + obj.VchWorkNote7 + obj.VchWorkNote8 + obj.VchWorkNote9 + obj.VchWorkNote10 + obj.VchWorkNote11 + obj.VchWorkNote12 + obj.VchWorkNote13 + obj.VchWorkNote14 + obj.VchWorkNote15 + obj.VchWorkNote16 + obj.VchWorkNote17 + obj.VchWorkNote18 + obj.VchWorkNote19 + obj.VchWorkNote20 }).ToList(); 

Answer 1

One option is to use the zero coalescence operator:

 List<CustomObject> objects = (from o in ObjectTable where o.Id == Id select new CustomObject(){ EnteredBy = o.EnteredBy, EntryDate = o.EntryDate, WorknoteText = (o.VchWorkNote1 ?? "") + (o.VchWorkNote2 ?? "") + (o.VchWorkNote3 ?? "") + (o.VchWorkNote4 ?? "") + ... (o.VchWorkNote20 ?? "") }).ToList(); 

We hope that the generated SQL will use the appropriate translation.

Answer 2

Can you use ? operator as follows:

 ... (object.VchWorkNote5 ?? "") + (object.VchWorkNote6 ?? "") + (object.VchWorkNote7 ?? "") + ... 

Answer 3

Can you add a new column to your database? Something like Keywords or Full Text

Define it to have the calculation that the calculation is basically "ISNULL(<Field1>, '') + ISNULL(<Field2>, '')" etc.

Be sure to mark it as saved, so it does not need to be calculated every time.

Then you just need to pull this field from the table.

Answer 4

How about (object.VchWorkNote1 ?? "") + .....

Answer 5

there is probably a cleaner way, but the first thing that comes to mind is the quick conversion of a string with zero length:

 List<CustomObject> objects = (from object in ObjectTable where object.Id == Id select new CustomObject(){ EnteredBy = object.EnteredBy, EntryDate = object.EntryDate, WorknoteText = object.VchWorkNote1 ?? "" + object.VchWorkNote2 ?? "" + object.VchWorkNote3 ?? "" + object.VchWorkNote4 ?? "" + object.VchWorkNote5 ?? "" + object.VchWorkNote6 ?? "" + object.VchWorkNote7 ?? "" + object.VchWorkNote8 ?? "" + object.VchWorkNote9 ?? "" + object.VchWorkNote10 ?? "" + object.VchWorkNote11 ?? "" + object.VchWorkNote12 ?? "" + object.VchWorkNote13 ?? "" + object.VchWorkNote14 ?? "" + object.VchWorkNote15 ?? "" + object.VchWorkNote16 ?? "" + object.VchWorkNote17 ?? "" + object.VchWorkNote18 ?? "" + object.VchWorkNote19 ?? "" + object.VchWorkNote20 ?? "" }).ToList();