How to perform Linq select new with datetime in SQL 2008

Question

In our C# code I recently changed a line from inside a linq-to-sql select new query as follows:

OrderDate = (p.OrderDate.HasValue ? 
    p.OrderDate.Value.Year.ToString() + "-" + 
    p.OrderDate.Value.Month.ToString() + "-" + 
    p.OrderDate.Value.Day.ToString() : "")

To:

OrderDate = (p.OrderDate.HasValue ? 
    p.OrderDate.Value.ToString("yyyy-mm-dd") : "")

The change makes the line smaller and cleaner. It also works fine with our SQL 2008 database in our development environment. However, when the code deployed to our production environment which uses SQL 2005 I received an exception stating: Nullable Type must have a value. For further analysis I copied (p.OrderDate.HasValue ? p.OrderDate.Value.ToString("yyyy-mm-dd") : "") into a string (outside of a Linq statement) and had no problems at all, so it only causes an in issue inside my Linq. Is this problem just something to do with SQL 2005 using different date formats than from SQL 2008?

Here's more of the Linq:

var FilteredOrders = [linq-to-sql query].AsEnumerable().ToList<Order>();

                dt = FilteredOrders.Where(x => x != null).Select(p =>
                new
                {
                    Order = p.OrderId,
                    link = "/order/" + p.OrderId.ToString(),
                    StudentId = (p.PersonId.HasValue ? p.PersonId.Value : 0),
                    FirstName = p.IdentifierAccount.Person.FirstName,
                    LastName = p.IdentifierAccount.Person.LastName,
                    DeliverBy = p.DeliverBy,
                    OrderDate = p.OrderDate.HasValue ? 
                        p.OrderDate.Value.Date.ToString("yyyy-mm-dd") : 
                        ""
                }).ToDataTable();

This is selecting from a List of Order objects. The FilteredOrders list is from another linq-to-sql query and I call .AsEnumerable on it before giving it to this particular select new query.

Doing this in regular code works fine:

if (o.OrderDate.HasValue)
    tempString += " " + o.OrderDate.Value.Date.ToString("yyyy-mm-dd");

Here is the stack trace from the error. This is part of a large system at a school for retrieving orders for transcripts from the DB to show on screen.

Line 46:   
Line 47:            dt = FilteredOrders.Where(x => x != null).Select(p =>  
Line 48:            new  
Line 49:                     { 
Line 50:                         Order = p.OrderId,


Stack Trace:

[InvalidOperationException: Nullable object must have a value.]    System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource) +51    System.Nullable`1.get_Value() +1373881 Aqueduct.Platform.Web.packages.finance_carttranscriptorder_default.<PopulateSearchResultsGrid>b__1(CartTranscriptOrder p) in D:\Repositories\aqueduct\Aqueduct.Platform.Web\trunk\packages\finance\carttranscriptorder\default.aspx.cs:48 System.Linq.WhereSelectListIterator`2.MoveNext()
+107    System.Linq.Buffer`1..ctor(IEnumerable`1 source) +434    System.Linq.<GetEnumerator>d__0.MoveNext()
+108    Aqueduct.Core.Data.ObjectShredder`1.Shred(IEnumerable`1 source, DataTable table, Nullable`1 options) in D:\Repositories\aqueduct\Aqueduct.Core\trunk\Data\LinqExtensions.cs:116 Aqueduct.Core.Data.LinqExtensions.ToDataTable(IEnumerable`1 source) in D:\Repositories\aqueduct\Aqueduct.Core\trunk\Data\LinqExtensions.cs:49 Aqueduct.Platform.Web.packages.finance_carttranscriptorder_default.PopulateSearchResultsGrid(List`1 FilteredOrders) in D:\Repositories\aqueduct\Aqueduct.Platform.Web\trunk\packages\finance\carttranscriptorder\default.aspx.cs:47 Aqueduct.Platform.Web.packages.finance_carttranscriptorder_default.RunFilter() in D:\Repositories\aqueduct\Aqueduct.Platform.Web\trunk\packages\finance\carttranscriptorder\default.aspx.cs:101 Aqueduct.Platform.Web.packages.finance_carttranscriptorder_default.Page_Load(Object sender, EventArgs e) in D:\Repositories\aqueduct\Aqueduct.Platform.Web\trunk\packages\finance\carttranscriptorder\default.aspx.cs:22 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
+14    System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35    System.Web.UI.Control.OnLoad(EventArgs e) +99    System.Web.UI.Control.LoadRecursive()
+50    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627

Answer 1

I suspect it's trying to convert the code into SQL. If this is just the projection side of things, I suggest you do a simple projection to the bits of data you need within the LINQ-to-SQL bit, then use AsEnumerable to force the rest of the query to execute in .NET itself. At that point you can do things like this with a lot more freedom. So in this case you'd have something like:

var query = from ...
            where ...
            select new { p.OrderData, p.SomeOtherFields };

var transformed = query.AsEnumerable()
                       .Select(p => new {
   OrderDate = (p.OrderDate.HasValue ? p.OrderDate.Value.ToString("yyyy-mm-dd") 
                                     : ""),
   ... });

Answer 2

So, just to confirm, FilteredResults is something like this:

FilteredResults = [query].AsEnumerable()

Correct?

If so, then the only option that I can see is if OrderID is null. Is it a nullable type? All of your other (conceivably) nullable properties all appear to have a HasValue check around them or are not used at all (like DeliverBy), so I can't see any other options.

The only thing that I could suggest would be to remove all of the anonymous type's properties and add them back one-by-one until you encounter the error.

As an aside, there's no need to go after the Date property of OrderDate.Value, since the ToString() function of DateTime is perfectly fine formatting dates without a time component.

Answer 3

When you do this in Linq to SQL

OrderDate = (p.OrderDate.HasValue ? 
    p.OrderDate.Value.ToString("yyyy-mm-dd") : "")

It actually evaluates both sides of the case but only returns to you the "true" case. So just do something like this and it will avoid the nullable, but still give you the correct result.

OrderDate = (p.OrderDate.HasValue ? 
    p.OrderDate.GetValueOrDefault(DateTime.Now).ToString("yyyy-mm-dd") : "")