CODEWITHSUNDEEP
c#linqentity-framework
Jimmyt1988
Jimmyt1988

Reputation: 21146

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

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.

Upvotes: 3

Views: 3001

Answers (3)

Stanislav Berkov
Stanislav Berkov

Reputation: 6287

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();
}

Upvotes: 2

Ric
Ric

Reputation: 13248

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

Upvotes: 1

Theodoros Chatzigiannakis
Theodoros Chatzigiannakis

Reputation: 29213

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.

Upvotes: 2

Related Questions