CODEWITHSUNDEEP
linq
Sreedhar
Sreedhar

Reputation: 30045

Linq Error: InvalidOperationException: Could not translate expression

Get value out of DateTime column if null to return String.Empty else DateTime.ToShortDateString

What am I doing wrong => query produced below:

var queryable = from p in Products
    select new {
               selldate = p.SellEndDate == null
                           ? string.Empty
                           : p.SellEndDate.Value.ToShortDateString()  };

Error: InvalidOperationException: Could not translate expression 'Table(Product).Select(p => new <>f__AnonymousType01(selldate = IIF((p.SellEndDate = null), Invoke(value(System.Func1[System.String])), p.SellEndDate.Value.ToShortDateString())))' into SQL and could not treat it as a local expression.

Upvotes: 2

Views: 1267

Answers (3)

Russell Harkins
Russell Harkins

Reputation: 11

If the date time field allows nulls:

from order in repository.Order
     select order.OrdShipDate == null ? "" : order.OrdShipDate.GetValueOrDefault(DateTime.Now).Month.ToString() + "/" + order.OrdShipDate.GetValueOrDefault(DateTime.Now).Day.ToString() + "/" + order.OrdShipDate.GetValueOrDefault(DateTime.Now).Year.ToString();

If the date time field doesn't allow nulls:

from order in repository.Order
     select order.OrdShipDate.Month.ToString() + "/" + order.OrdShipDate.Day.ToString() + "/" + order.OrdShipDate.Year.ToString();

Upvotes: 1

shahkalpesh
shahkalpesh

Reputation: 33484

ToShortDateString doesn't seem to have equivalent SQL translation.
Use ToString instead.

Upvotes: 1

Matt Hamilton
Matt Hamilton

Reputation: 204259

Basically what's happening here is that LINQ to SQL is taking your entire query and trying to convert it into something that SQL Server can understand. The problem, though, is that SQL Server has no concept of DateTime.ToShortDateString, so the conversion to SQL fails.

You'll have to change your query so that it just selects SellEndDate (which will get it as a Nullable<DateTime>) and then when you use the results of that query you can do the conversion to string. For example:

var list = (from p in Products
           select p.SellEndDate).ToList();

// calling ToList() above means we have the entire resultset in memory and
// no longer have to pass the query back to SQL Server

var stuff = from p in list select new
{ 
    selldate = p.SellEndDate == null ?
                   string.Empty :
                   p.SellEndDate.Value.ToShortDateString()
};

Upvotes: 3

Related Questions