Get default value type (DateTime) in Linq to Sql query with empty results

Question

I am having a problem returning a default DateTime value from a complex Linq-to-Sql query.

Hopefully the following simplified example shows the problem (although I haven't run this exact code):

users.Select(u =>
  new MyDomainObject(
     u.Id,
     u.Transactions
        .Where(t => false) // empty results set
        .Select(t => t.TransactionTime) // TransactionTime is DATETIME NOT NULL
        .OrderByDescending(x => x)
        .FirstOrDefault() // I want DateTime.MinValue (or SqlDateTime.MinValue)
  )
); 

So I want the last timestamp, or some MinValue timestamp if there are no results.

Enumerating the above query gives the error

The null value cannot be assigned to a member with type System.DateTime

---

UPDATE

Ok I'm not sure that my above example was sufficient to illustrate the problem. I believe the error may be something to do with how I'm trying to do a subquery on a third linked table.

The following example recreates the exact error:

So I have a car, which I can take to the mechanic, which sometimes (but not always) is serviced by the mechanic.

The requirement is to find via a query to the car table how many mechanic visits each car has had since it was last serviced. The issue is when the car has never been serviced, so the data looks like:

Car
------------- 
Id: 1         


MechanicVisit          
------------- 
Id: 1 
CarId: 1 
ServiceRecordId: NULL
VisitDate: 1 Jan 2011    


ServiceRecord
------------- 
<empty>

So a simple example that shows the error is a query to get the list of last service times:

 var test = _dataContext.GetTable<Car>
                 .Select(c => 
                     c.MechanicVisits
                      .Select(m => m.ServiceRecord)
                      .Select(s => s.ServiceDate)
                      .OrderByDescending(d => d)
                      .FirstOrDefault()
                  ).ToList();

This gives the previously described error of trying to assign null to a non-nullable type, where what I need to do is return DateTime.MinValue or SqlDateTime.MinValue when the date is null (so I can do the actual query which is number of mechanic visits since the last service)

---

SOLUTION

I used a variation of what Jon Skeet suggested, using a cast to DateTime? and null coalescing operator:

 var test = _dataContext.GetTable<Car>
                     .Select(c => 
                         c.MechanicVisits
                          .Select(m => m.ServiceRecord)
                          .Select(s => (DateTime?)s.ServiceDate)
                          .OrderByDescending(d => d)
                          .FirstOrDefault() ?? new DateTime(1900, 1, 1)
                      ).ToList();

Note the use of the paramatered constructor for the "default" date - DateTime.MinValue can't be used here as it throws an out of range exception when converted to SQL, and SqlDateTime.MinValue can't be used as it is non-nullable (so the coalesce operator becomes invalid).

I still don't really understand why the original error was occuring and this solution does feel a bit hacky, but I've been unable to find any neater way of fixing it.

Answer 1

I'd be very tempted to actually use a nullable DateTime for this. For example, from your "Car" sample:

var test = _dataContext.GetTable<Car>
                       .Select(c => 
                           c.MechanicVisits
                            .Select(m => m.ServiceRecord)
                            .Select(s => (DateTime?) s.ServiceDate)
                            .OrderByDescending(d => d)
                            .FirstOrDefault()
                       ).ToList();

That way I suspect you'll end up with it working and giving you null DateTime? values. You could always transform that later if you wanted:

var test = _dataContext.GetTable<Car>
                       .Select(c => 
                           c.MechanicVisits
                            .Select(m => m.ServiceRecord)
                            .Select(s => (DateTime?) s.ServiceDate)
                            .OrderByDescending(d => d)
                            .FirstOrDefault()
                       ).AsEnumerable()
                        .Select(dt => dt ?? DateTime.MinValue)
                        .ToList();

---

Original answer (doesn't work)

Hmm. I won't claim to fully understand the reasons for this, but here's a potential workaround:

users.Select(u =>
  new MyDomainObject(
     u.Id,
     u.Transactions
        .Where(t => false)
        .Select(t => t.TransactionTime)
        .OrderByDescending(x => x)
        .DefaultIfEmpty(DateTime.MinValue)
        .First()
  )
); 

In other words, if the result set is empty, use the specified default - and then take the first result of the now-definitely-not-empty sequence.

Answer 2

The error implies that t.TransactionTime is nullable (i.e. DateTime?). Even if the field can't be null in the database if the property is nullable and the query returns no rows FirstOrDefault() will return null as this is the default value of t.TransactionTime

EDIT

further to your comments, that is very strange: the below code outputs True

List<DateTime> dates = new List<DateTime>();
DateTime date = dates.FirstOrDefault();
Console.WriteLine(date == DateTime.MinValue);

And i would expect your code to do the same.

Answer 3

users.Select(u =>
  new MyDomainObject(
     u.Id,
     u.Transactions
        .Where(t => false) // empty results set
        .Select(t => t.TransactionTime)
        .Any() ?
     u.Transactions
        .Where(t => false) // empty results set
        .Select(t => t.TransactionTime) // TransactionTime is DATETIME NOT NULL
        .OrderByDescending(x => x)
        .FirstOrDefault() : // I want DateTime.MinValue (or SqlDateTime.MinValue)
     DateTime.MinValue
  )
);

This will supply DateTime.MinValue if there are no transactions available for that object.

Answer 4

This will supply DateTime.MinValue if there are no transactions available for that object, example:

users.Select(u =>
  new MyDomainObject(
     u.Id,
     u.Transactions
        .Where(t => false) // empty results set
        .Select(t => t.TransactionTime)
        .Any() ?
     u.Transactions
        .Where(t => false) // empty results set
        .Select(t => t.TransactionTime) // TransactionTime is DATETIME NOT NULL
        .OrderByDescending(x => x)
        .FirstOrDefault() : // I want DateTime.MinValue (or SqlDateTime.MinValue)
     DateTime.MinValue
  )
);