How to concat strings in LINQ while properly dealing with NULL values

Question

I'd like an elegant way to concatenate several columns together using LINQ, but using the + operator or concat() when any of the columns are NULL results in NULL for the value after concatenation.

Is there anything similar to concat() that handles NULL differently, or am I thinking about this in the incorrect way?

Any help is appreciated!

Here is the code I am 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();

Wow, this is insane. You should totally add a calculating column, like @taylorn suggested. — Dan Abramov
– Dan Abramov, Commented Apr 7, 2011 at 20:07

Jon Skeet · Accepted Answer · 2011-04-07 19:51:55Z

14

One option is to use the null coalescing 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();

Hopefully the generated SQL will use an appropriate translation.

answered Apr 7, 2011 at 19:51

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Larry · Accepted Answer · 2011-04-07 19:52:01Z

3

You can use the ?? operator this way :

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

answered Apr 7, 2011 at 19:52

Larry

18.1k9 gold badges83 silver badges108 bronze badges

Comments

Kevin LaBranche · Accepted Answer · 2011-04-07 19:53:09Z

2

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

answered Apr 7, 2011 at 19:53

Kevin LaBranche

21.1k5 gold badges55 silver badges77 bronze badges

Comments

taylonr · Accepted Answer · 2011-04-07 19:58:10Z

2

Can you add a new column to your database? Something like "Keywords" or "FullText"

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

Make sure to mark it as persisted, so it doesn't have to calculate each time.

Then you just need to pull down that one field from your table.

edited Apr 7, 2011 at 19:58

answered Apr 7, 2011 at 19:52

taylonr

10.8k7 gold badges40 silver badges66 bronze badges

Comments

Pete M · Accepted Answer · 2011-04-07 19:52:37Z

there's probably a cleaner way, but first thing that comes to mind would be a quick null to zero length string conversion:

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();

This is incorrect. The plus operator has higher priority than the ?? operator. You should add parenthesis around (object.VchWorkNote1 ?? "")

Collectives™ on Stack Overflow

How to concat strings in LINQ while properly dealing with NULL values

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related