LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.Object)' method, and this method cannot be translated into a store expression.

Question

0.00/5 (No votes)

See more:

, +

how to get TimeSpan of Inserted date in database and DateTime.Now in Hours.

I have linq

var getFacility = (from item in objDB.LabDetails
                                   select new LabDetailsModel
                                   {
                                       DetailsID = item.DetailsID,
                                       CallerName = item.CallerName,
                                       FaciltityID = item.FaciltityID,
                                       FacilityName=(from fac in objDB.Facilities.Where(a=>a.FacilityID==item.FaciltityID) select fac.FacilityName).FirstOrDefault(),
                                       ReqBarCode = item.ReqBarCode,
                                       SpecimenType = item.SpecimenType,
                                       Contact = (from fac in objDB.Facilities.Where(a => a.FacilityID == item.FaciltityID) select fac.Contact).FirstOrDefault(),
                                       CourierName=(from fac in objDB.Facilities.Where(a=>a.FacilityID==item.FaciltityID) select fac.CourierName).FirstOrDefault(),
                                       RequiredTesting = item.RequiredTesting,
                                       RequiredTestingStat = item.RequiredTestingStat,
                                       ConfirmationCode = item.ConfirmationCode,
                                       Representative = item.Representative,
                                       DeliveryType=item.DeliveryType,
                                       DeliveryTypeName = item.DeliveryType == 1 ? "1 hour" : item.DeliveryType == 2 ? "2 hour" : item.DeliveryType == 3 ? "OverNight" : item.DeliveryType == 4 ? "Ground" : item.DeliveryType == 5?"2 Day":"None",
                                       Reference = item.Reference,
                                       Notes = item.Notes,
                                       OrderingPhysicianType = item.OrderingPhysicianType,
                                       PhysicianName = (from fac in objDB.Facilities.Where(a => a.FacilityID == item.FaciltityID) select fac.PhysicianName).FirstOrDefault(),
                                       CreatedDate = item.CreatedDate.Value,
                                       ReceiveDate=(from recv in objDB.ReceiveSpecimen.Where(a=>a.LabDetailsID==item.DetailsID) select recv.ReceiveDate).FirstOrDefault(),
                                       ReceivedBy = (from recv in objDB.ReceiveSpecimen.Where(a => a.LabDetailsID == item.DetailsID) select recv.ReceivedBy).FirstOrDefault(),
                                       ReceiveNote = (from recv in objDB.ReceiveSpecimen.Where(a => a.LabDetailsID == item.DetailsID) select recv.ReceiveNote).FirstOrDefault(),
                                       DeliveryLateTime = (DateTime.Now - Convert.ToDateTime(item.CreatedDate)).Hours,
                                   }).OrderByDescending(a=>a.DetailsID).ToList();
                return getFacility;

It shows the error "LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.Object)' method, and this method cannot be translated into a store expression." I think error is due to

Convert.ToDateTime(item.CreatedDate)

but unable to find the exact solution after many searches.

Thanks.

Posted 21-May-15 0:37am

itsathere

Updated 19-Apr-17 20:55pm

v2

Add a Solution

Comments

virusstorm 21-May-15 7:03am

First, you have a lot of tags on this, makes sure you are only adding relevant tags. Second, what is the type of item.CreatedDate?

itsathere 21-May-15 7:31am

type of item.CreatedDate is DateTime. Actually, without using TimeSpan it may not be possible to get the difference and in linq if i will implement it then coding will be complex that's y i use another static method to get exact difference.

Richard Deeming 21-May-15 8:39am

If the type is already DateTime, then you don't need to call Convert.ToDateTime at all.

itsathere 21-May-15 8:51am

I am using Model to insert,update data and model is like below code
public DateTime? CreatedDate { get; set; }

afterward i m using it.
before using Convert.ToDateTime it shows because of it's type DateTime?

Richard Deeming 21-May-15 8:53am

In that case, use either item.CreatedDate.Value or item.CreatedDate.GetValueOrDefault().

The former will throw an exception if the CreatedDate is null, which is the same behaviour as the Convert.ToDateTime method.

The latter will return DateTime.MinValue if the CreatedDate is null.

itsathere 21-May-15 9:40am

I have checked database CreatedDate is not null and also checked like item.CreatedDate.Value but still error is same

johannesnestler 21-May-15 7:33am

You have to understand how Linq-Providers (Linq to Entities in your case), and how Expression-trees work... anyway in your case just prepare the converted datetime object outside the Expression and use that (captured) variable inside the Linq-Expression.

itsathere 21-May-15 8:47am

elaborate please..

virusstorm 21-May-15 11:15am

You need to do something like this:

//Use if CreatedDate is not nullable
var timeAgo = TimeAgo(item.CreatedDate);

//Use if CreatedDate is nullable
var timeAgo = TimeAgo(item.CreatedDate.GetValueOrDefault());

Then in your linq statement, do this:

DeliveryLateTime = timeAgo,

As johannesnestler was saying, linq works by the compiler taking the statement, converting it into an expression tree and then evaluating it. As a result, there are limitations as to what you can do. Take a look at this link for more details.
https://msdn.microsoft.com/en-us/library/bb397951.aspx

Sascha Lefèvre 21-May-15 20:26pm

If you can't solve the problem with the comments already posted, please include the actual Linq query you're trying to execute here. (Please use the link "Improve question" below your question to insert it there.)

itsathere 22-May-15 1:16am

I have updated actual linq.
now, i want hour difference between two datetime using below code
DeliveryLateTime = (DateTime.Now - Convert.ToDateTime(item.CreatedDate)).Hours,

Sascha Lefèvre 22-May-15 7:33am

Have you already tried this?
DeliveryLateTime = (DateTime.Now - item.CreatedDate).Hours

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2015-06-10T09:45:00

On the first look...

Seems you have got only one option:

C#

SqlFunctions.DateDiff("h", item.CreatedDate, DateTime.Now)

For further details, please see: SqlFunctions.DateDiff Method[^]

Optionally, as Sascha Lefevre[^] suggested, you can try something like this:

C#

DeliveryLateTime = (DateTime.Now - item.CreatedDate).Hours,

**D V L** · Answer 2 · 2015-08-20T05:52:00

Solution 2

I also have same problem

so, i used "DbFunctions".

try this.

https://msdn.microsoft.com/en-US/library/system.data.entity.dbfunctions.diffhours(v=vs.113).aspx[^]

Posted 20-Aug-15 5:52am

D V L

Updated 20-Aug-15 5:53am

v2

Sinan Ergin · Answer 3 · 2016-09-07T00:44:00

Solution 3

Do you have return type ?

I just guess and I have solution.
Bro, you can see below,

C#

DeliveryLateTime = DateTime.Now.TimeOfDay.Subtract(item.CreatedDate) 
//If DeliveryLateTime is type as TimeSpan

//OR

DeliveryLateTime = DateTime.Now.TimeOfDay.Subtract(item.CreatedDate).Hours
//If DeliveryLateTime is type as int

Posted 7-Sep-16 0:44am

Sinan Ergin

Updated 7-Sep-16 0:45am