LINQ to Entities does not recognize the method 'System.TimeSpan Subtract(System.DateTime)

Question

The following throws an error:

public FieldViewer GetFieldViewByFieldIDIPAndUserByDate( int fieldID, string ip, string userID, DateTime date )
{
    return this.context.FieldViewers.Where( x =>
        x.Field.FieldID == fieldID &&
        x.Viewer.IPAddress == ip &&
        x.Viewer.User.Id == userID &&

        date.Subtract( x.Viewer.CreatedAt ).TotalMinutes >= 10 ).FirstOrDefault();
}

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

How do I go about solving this as I need to subtract per query instead.

https://msdn.microsoft.com/en-us/library/dd395530(v=vs.110).aspx possibly? example usage http://stackoverflow.com/questions/17486218/how-to-subtract-entityfunctions-truncatetime-from-custom-time — Ric, Jul 22 '15 at 12:30
possible duplicate of [LINQ to Entities for subtracting 2 dates](http://stackoverflow.com/questions/570858/linq-to-entities-for-subtracting-2-dates) — default, Jul 22 '15 at 12:32

score 2 · Answer 1 · answered Jul 22 '15 at 12:31

One way to fix this is to perform that part of the filtering outside of the LINQ to Entities provider, using the LINQ to Objects provider. To do that, append a call to AsEnumerable() before that operation:

public FieldViewer GetFieldViewByFieldIDIPAndUserByDate( int fieldID, string ip, string userID, DateTime date )
{
    return this.context.FieldViewers.Where( x =>
            x.Field.FieldID == fieldID &&
            x.Viewer.IPAddress == ip &&
            x.Viewer.User.Id == userID)
       .AsEnumerable()
       .Where(x => date.Subtract( x.Viewer.CreatedAt ).TotalMinutes >= 10)
       .FirstOrDefault();  
}

Another way is to use one of the specialized LINQ to Entities operations, like DiffMinutes.

score 2 · Answer 2 · answered Jul 22 '15 at 12:40

EntityFunctions.DiffMinutes for your case will be inefficient. You should do this way

public FieldViewer GetFieldViewByFieldIDIPAndUserByDate( int fieldID, string ip, string userID, DateTime date )
{
    var tenMinThreshold = date - TimeSpan.FromMinutes(10);
    return this.context.FieldViewers.Where( x =>
        x.Field.FieldID == fieldID &&
        x.Viewer.IPAddress == ip &&
        x.Viewer.User.Id == userID &&
        x.Viewer.CreatedAt <= tenMinThreshold).FirstOrDefault();
}

score 1 · Answer 3 · edited May 23 '17 at 12:06

1

Try this to find the difference in minutes:

EntityFunctions.DiffMinutes(date, x.Viewer.CreatedAt) >= 10

Sources:

How to subtract EntityFunctions.TruncateTime from custom time

EntityFunctions.DiffMinutes

As this api is now obsolete (thanks for pointing this out @Jimmyt1988), instead use: DbFunctions.DiffMinutes

edited May 23 '17 at 12:06

Community

1
1

answered Jul 22 '15 at 12:34

Ric

12,855
3
30
36

`EntityFunctions is obsolete, you can use DbFunctions`. Thanks for this it works great and does it before the db call! – Jimmyt1988 Jul 22 '15 at 12:37

LINQ to Entities does not recognize the method 'System.TimeSpan Subtract(System.DateTime)

3 Answers3

Linked

Related