none
DefaultIfEmpty Returns Empty Date.

    Question

  • I'm trying to return a DateTime value from a SQL table.  If the query finds no rows I want to be able to return today's date.  I found an example in my Google search that uses FirstOrDefault. But I want the default date to be today if no record exists.  I have the code below and when I step through the code and the query has zero records the date variable is set to January 1, 0001. Can someone tell me how I am misusing DefaultIfEmpty?

    IQueryable<DateTime> oScorecardResultsQuery = from ScorecardResults in moDataClasses1DataContext.tblScorecardResults
    where ScorecardResults.StoreID == iStoreID
    && ScorecardResults.ScorecardID == iScorecardID
    orderby ScorecardResults.LogDate descending
    select ScorecardResults.LogDate;
    
    DateTime dDefaultDateTime = DateTime.Today.Date;
    oScorecardResultsQuery.DefaultIfEmpty(dDefaultDateTime);
    DateTime dScorecardDate = oScorecardResultsQuery.FirstOrDefault();
    
    


    MCSD .NET developer in Dallas, Texas

    Wednesday, December 11, 2013 7:30 PM

Answers

  • Fred

    Thanks for the code, but it doesn't work for me.  Yes, I'm using LinqToSQL.  I still get 1/1/0001 using your AsEnumerable.  Apparently DefaultIfEmpty doesn't work on LinqToSQL; at least for me, today, in this dimension.  I used this code as a workaround:

                if (dScorecardDate.Year < 2000)
                {
                    dScorecardDate = dDefaultDateTime;
                }


    MCSD .NET developer in Dallas, Texas

    Thursday, December 12, 2013 1:26 PM

All replies

  • That looks fine. Are you sure your query returns no rows?

    EG:

          using (var db = new AdventureWorks2012Entities())
          {
            var q = from r in db.BusinessEntities
                    where false
                    select r.ModifiedDate;
    
            var date = q.DefaultIfEmpty(DateTime.Now).First();
            Console.WriteLine(date);
          }

    works as expected.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, December 11, 2013 8:04 PM
  • David

    Thanks for taking the time to reply.  I tried pasting your code into my code and I ran it.  When it calls the next to last code line I get the error: "Unsupported overload used for query operator 'DefaultIfEmpty'."  So far I haven't been able to re-write your code so that it will run.  Any ideas?

    Steve


    MCSD .NET developer in Dallas, Texas


    • Edited by DallasSteve Wednesday, December 11, 2013 8:22 PM
    Wednesday, December 11, 2013 8:22 PM
  • David

    In answer to your question, I think my query returns no rows.  I stop the code in debug and when I enter "?oScorecardResultsQuery.Count()" in the Immediate Window it replies with 0.


    MCSD .NET developer in Dallas, Texas

    Wednesday, December 11, 2013 8:36 PM
  • Hello DallasSteve,

    It seems that you are using Linq2Sql, you may need to change the query to below, adding the AsEnumerable().

    var result = from od in db.OrderDetails
    
                                 where od.OrderID == 10
    
                                 select od.DataTime;
    
    
                    //int count = result.Count();
    
    
                    var date = result.AsEnumerable().DefaultIfEmpty(DateTime.Now).First();
    
                    Console.WriteLine(date);
    

    The count is 0, the date will get the local system datetime.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 12, 2013 8:24 AM
  • Fred

    Thanks for the code, but it doesn't work for me.  Yes, I'm using LinqToSQL.  I still get 1/1/0001 using your AsEnumerable.  Apparently DefaultIfEmpty doesn't work on LinqToSQL; at least for me, today, in this dimension.  I used this code as a workaround:

                if (dScorecardDate.Year < 2000)
                {
                    dScorecardDate = dDefaultDateTime;
                }


    MCSD .NET developer in Dallas, Texas

    Thursday, December 12, 2013 1:26 PM
  • IQueryable<DateTime> oScorecardResultsQuery =

    from ScorecardResults in moDataClasses1DataContext.tblScorecardResults where ScorecardResults.StoreID == iStoreID && ScorecardResults.ScorecardID == iScorecardID group ScorecardResults by ScorecardResults.StoreID into grouped select grouped.Any() ? grouped.Min(x => x.LogDate) : DateTime.Now;

    Thursday, December 19, 2013 3:06 PM