title: Get default value type (DateTime) in Linq to Sql query with empty results
body: <p>I am having a problem returning a default <code>DateTime</code> value from a complex <code>Linq-to-Sql</code> query.</p>
  
  <p>Hopefully the following simplified example shows the problem (although I haven't run this exact code): </p>
  
  <pre><code>users.Select(u =&gt;
    new MyDomainObject(
       u.Id,
       u.Transactions
          .Where(t =&gt; false) // empty results set
          .Select(t =&gt; t.TransactionTime) // TransactionTime is DATETIME NOT NULL
          .OrderByDescending(x =&gt; x)
          .FirstOrDefault() // I want DateTime.MinValue (or SqlDateTime.MinValue)
    )
  ); 
  </code></pre>
  
  <p>So I want the last timestamp, or some <code>MinValue</code> timestamp if there are no results.</p>
  
  <p>Enumerating the above query gives the error</p>
  
  <pre><code>The null value cannot be assigned to a member with type System.DateTime
  </code></pre>
  
  <hr>
  
  <p><strong>UPDATE</strong> </p>
  
  <p>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.</p>
  
  <p>The following example recreates the exact error:</p>
  
  <p><img src="https://i.stack.imgur.com/qG4fh.jpg" alt="enter image description here"></p>
  
  <p>So I have a car, which I can take to the mechanic, which sometimes (but not always) is serviced by the mechanic.</p>
  
  <p>The requirement is to find <strong>via a query to the car table</strong> 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:</p>
  
  <pre><code>Car
  ------------- 
  Id: 1         
  
  
  MechanicVisit          
  ------------- 
  Id: 1 
  CarId: 1 
  ServiceRecordId: NULL
  VisitDate: 1 Jan 2011    
  
  
  ServiceRecord
  ------------- 
  &lt;empty&gt;
  </code></pre>
  
  <p>So a simple example that shows the error is a query to get the list of last service times:</p>
  
  <pre><code> var test = _dataContext.GetTable&lt;Car&gt;
                   .Select(c =&gt; 
                       c.MechanicVisits
                        .Select(m =&gt; m.ServiceRecord)
                        .Select(s =&gt; s.ServiceDate)
                        .OrderByDescending(d =&gt; d)
                        .FirstOrDefault()
                    ).ToList();
  </code></pre>
  
  <p>This gives the previously described error of trying to assign null to a non-nullable type, where what I need to do is return <code>DateTime.MinValue</code> or <code>SqlDateTime.MinValue</code> when the date is null (so I can do the actual query which is number of mechanic visits since the last service)</p>
  
  <hr>
  
  <p><strong>SOLUTION</strong></p>
  
  <p>I used a variation of what Jon Skeet suggested, using a cast to <code>DateTime?</code> and null coalescing operator:</p>
  
  <pre><code> var test = _dataContext.GetTable&lt;Car&gt;
                       .Select(c =&gt; 
                           c.MechanicVisits
                            .Select(m =&gt; m.ServiceRecord)
                            .Select(s =&gt; (DateTime?)s.ServiceDate)
                            .OrderByDescending(d =&gt; d)
                            .FirstOrDefault() ?? new DateTime(1900, 1, 1)
                        ).ToList();
  </code></pre>
  
  <p>Note the use of the paramatered constructor for the "default" date - <code>DateTime.MinValue</code> can't be used here as it throws an out of range exception when converted to SQL, and <code>SqlDateTime.MinValue</code> can't be used as it is non-nullable (so the coalesce operator becomes invalid).</p>
  
  <p>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.</p>