Why can't I discard the DateTime value as a string in a LinQ query?

Question

I am trying to accept a DateTime value, and if it is not null, return a short time string. My query looks like this: (TimeIn is not NULLABLE, while TimeOut is NULLABLE)

var times = from t in db.TimePostings where t.MemberID == member.MemberID select new { Date = t.TimeIn.ToShortDateString(), TimeIn = t.TimeIn.ToShortTimeString(), TimeOut = t.TimeOut.HasValue ? t.TimeOut.Value.ToShortTimeString() : "-------" }; gvTimePostings.DataSource = times; gvTimePostings.DataBind(); 

but this fails when I try to bind data with an error:

Failed to translate the expression "Table (TimePosting). Where (t => (t.MemberID == Invoke (value (System.Func 1[System.String])))).Select(t => new <>f__AnonymousType8 4 ( Date = t.TimeIn.ToShortDateString (), TimeIn = t.TimeIn.ToShortTimeString (), TimeOut = IIF (t.TimeOut.HasValue, (t.TimeOut ?? Call (value (System.Func`1 [System.DateTime] ))). ToShortTimeString (), "-------"), Hours = "")) "in SQL and cannot be considered as a local expression.

I also get a similar error if I try to use:

 TimeOut = t.TimeOut.HasValue ? Convert.ToDateTime(t.TimeOut).ToShortTimeString() : "-------" 

however, if I changed the TimeOut property to:

 TimeOut = t.TimeOut.HasValue ? t.TimeOut.ToString() : "-------", 

It works fine, but does not format the time as I want it (shortTimeString).

what's up with that?

Answer 1

As others have said, the problem is trying to convert ToShortDateString , etc. in SQL. Fortunately, this is easy to fix: retrieve data using SQL, and then format it in .NET:

 var timesFromDb = from t in db.TimePostings where t.MemberID == member.MemberID select new { t.TimeIn, t.TimeOut }; var times = from t in timesFromDb.AsEnumerable() select new { Date = t.TimeIn.ToShortDateString(), TimeIn = t.TimeIn.ToShortTimeString(), TimeOut = t.TimeOut.HasValue ? t.TimeOut.Value.ToShortTimeString() : "-------" }; 

Calling AsEnumerable() here basically means: "stop processing the query using SQL, the rest is in LINQ to Objects."

Answer 2

ToShortTimeString() has no translation in SQL. Because of this, the conversion of a statement to a single SQL statement fails and an exception is thrown.

If you break the instruction for two calls (one to extract data and the other to create a projection), everything will work fine:

 // must call ToList to force execution of the query before projecting var results = from t in db.TimePostings where t.MemberID == member.MemberID select new { t.TimeIn, t.TimeOut }; var times = from t in results.AsEnumerable() select new { Date = t.TimeIn.ToShortDateString(), TimeIn = t.TimeIn.ToShortTimeString(), TimeOut = t.TimeOut.HasValue ? t.TimeOut.Value.ToShortTimeString() : "-------" }; 

Answer 3

You tried:

 TimeOut = t.TimeOut.HasValue ? t.TimeOut.ToString("d") : "-------", 

This usually gives a short DateTime format. Whether this will work or not will depend on whether it can be translated into SQL or not.

If this does not work, you will have to split the request into two parts. The first receives the data, the second - the format. You will need to convert the first query to a list ( .ToList() ) to force SQL to be evaluated.

Answer 4

It just is not supported by this particular linq provider.

Your linq query is converted to an expression tree. Linq SQL Provider can convert this expression tree to SQL. It is clear that he does not have the ability to translate every .NET function.

Your solution is to explicitly run SQL by calling ToArray or ToList , and then allow LinqToObjects to handle the rest.

  var times = from t in db.TimePostings where t.MemberID == member.MemberID select new { TimeIn = t.TimeIn, TimeOut = t.TimeOut }; var timesFormated = times.ToArray() // Runs the query - any further processing will be run in memory by the local .NET code .Select(t => new { Date = t.TimeIn.ToShortDateString(), TimeIn = t.TimeIn.ToShortTimeString(), TimeOut = t.TimeOut.HasValue ? t.TimeOut.Value.ToShortTimeString() : "-------", Hours = "" } ); 

Answer 5

Your query converts LINQ to SQL, which runs against your database, and obviously there is no way to translate t.TimeOut.Value.ToShortTimeString() into SQL.

Possible solutions:

• First retrieve the data from the database (by calling .ToList() or .ToArray() in the LINQ query), which will convert your IQueryable<> to IEnumerable<> , and then apply your transform for each row selected.
• Use a view that takes the source table and performs the conversion using the CONVERT () function on SQL Server and uses it as the source for your Linq-to-SQL class. This will be executed, but requires some server-side changes.

Answer 6

I had the same problem in a project in vb.net. The solution I found is based on the use of:

if(table.field.hasvalue, table.field.value.ToShortDateString, string.format("NULL"))

In this case, if the selected field (table.field) has a value, it is converted to a date string, otherwise if the field does not matter, the input field is filled with the string "NULL"