How to perform Linq select new with datetime in SQL 2008
- by kd7iwp
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");
