SELECT NEW with a potentially null field using LINQ and Entity Framework

Question

I want to quickly select some rows, format it nicely for a dropdownlist/selectlist or something like that - but I have a field in the db, that is nullable (DateOfBirth).

var athletes = (from at in _db.Athletes
           select new{
                  Name = at.Name + " " + at.DateOfBirth, 
                  Id = at.AthleteId 
                  }).ToList();

Is there a way to handle nullable types inside the LINQ in a case like this?

Edit:

I was not paying attention to the fact, that since this is using entity framework, methods that work with standard LINQ cannot be used unless they have a SQL translation.

DateOfBirth is a Nullable < DateTime >
Source is Entity Framework 4

... " + (at.DateOfBirth as string ?? string.empty ), – asawyer Feb 08 '11 at 15:11 — asawyer, Feb 08 '11 at 15:11
Is that linq to SQL, Entity Framework, or just plain LINQ? – Brian Mains Feb 08 '11 at 16:07 — Brian Mains, Feb 08 '11 at 16:07

score 7 · Accepted Answer · edited May 23 '17 at 12:15

7

You can use the null coalesce operator, see Equivalent of SQL ISNULL in LINQ?.

Something like:

var athletes = (from at in _db.Athletes
           select new{
                  Name = at.Name + " " + (at.DateOfBirth ?? ""), 
                  Id = at.AthleteId 
                  }).ToList();

edited May 23 '17 at 12:15

Community

1
1

answered Feb 08 '11 at 15:11

Shan Plourde

8,528
2
29
42

score 2 · Answer 2 · answered Feb 08 '11 at 15:12

2

Often a nullable can be handled using variable ?? default

var res = nullableVariable ?? defaultValue;

But be carefull with datetime, linq will try to implement this in SQL and DateTime.MinValue from C# is not within the valid range for SQL and will give you an error message.

answered Feb 08 '11 at 15:12

David Mårtensson

7,550
4
31
47

1

This is true for the SQL Server datetime and smalldatetime types, but SQL Server 2008 datetime2 and date types have the same precision support as the C# DateTime type, so this type out of range error won't occur with those types. – Shan Plourde Feb 08 '11 at 15:45
True, but very many databases uses DateTime fields and changing the database is rarely an option and for this case most likely not worthwhile ;) – David Mårtensson Feb 08 '11 at 16:16
Yes, these types are definitely good to use for new development. The development effort to refactor your database model and stored procedures, etc. to use these data types in SQL Server, just for the sake of it, is a wasteful activity. Anyhow, we don't know what the date datatype is in use, the questioner never indicated...so this is a very tangentially related discussion – Shan Plourde Feb 08 '11 at 17:02

score 2 · Answer 3 · answered Feb 08 '11 at 15:12

Since you are just doing string appending try this.

var athletes = (from at in _db.Athletes
           select new{
                  Name = at.Name + " " + (at.DateOfBirth ?? string.Empty), 
                  Id = at.AthleteId 
                  }).ToList();

score 0 · Answer 4 · answered Sep 24 '14 at 16:32

In VB.NET

Dim athletes = (From at In _db.Athletes
       Select New With{
              .Name = at.Name + " " + If(at.Field(Of Object)("DateOfBirth") = Nothing, string.Empty, at.Field(Of Object)("DateOfBirth")), 
              .Id = at.AthleteId 
              }).ToList()

SELECT NEW with a potentially null field using LINQ and Entity Framework

4 Answers4