How to perform Linq select new with datetime in SQL 2008

Posted by kd7iwp on Stack Overflow See other posts from Stack Overflow or by kd7iwp
Published on 2010-05-12T18:34:23Z Indexed on 2010/05/12 19:04 UTC
Read the original article Hit count: 318

Filed under:
|
|

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:

                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");

© Stack Overflow or respective owner

Related posts about linq-to-sql

Related posts about c#