How to execute a LINQ to Entities query, pulling values from an existing resultset variable instead of against the DB

dynamic-linq entity-framework-6 jqgrid orm stored-procedures


To provide some context, I'm using jqGrid to conduct a server side search on a datetime field, truncating the time portion server side, and then fetching results via a stored procedure. This is being done using System.Dynamic.Linq, which I've modified slightly as per this question here. I'm using EF6 Code First approach. The relevant portion of the code looks something like this:

//For matching date instead of datetime values
if (wc.Clause.Contains("Date"))
    wc.Clause = wc.Clause.Replace("DeliveryDate", "DbFunctions.TruncateTime(DeliveryDate)");
results = dataFileDB.Database.SqlQuery<Document>("exec [dbo].[sp_getDocumentDetails]").AsQueryable().Where(wc.Clause, wc.FormatObjects);

The problem I'm having is that because the query is fetched using a stored procedure, I get an exception on the last line saying

System.NotSupportedException: This function can only be invoked from LINQ to Entities. at System.Data.Entity.DbFunctions.TruncateTime(Nullable`1 dateValue)

In other places where I've used a lambda query to fetch directly from the entity itself and apply the where clause filter, the filtering is done correctly.

What I was trying to do (and I'm not sure if this is possible but...) was run an entity query against the results variable and apply the where clause to that variable but I've had no luck. Is this the right way to go or is there another approach I could take?

Thanks in advance!

5/23/2017 11:50:47 AM

Accepted Answer

Got it... I was overcomplicating things too much.

I just converted the DateTime field to a Date field when returning it via the stored procedure. Doing so no longer required me to use the DbFunctions.TruncateTime method, thereby resolving my issue.

Hope this helps someone else!

4/23/2015 3:28:10 PM

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow