CODEWITHSUNDEEP
c#linq
Oleh
Oleh

Reputation: 447

The specified type member 'Offset' is not supported in LINQ to Entities

I got the exception when the next code run:

 var fbPost = db.FacebookStatusUpdates
         .Where(f => 
               f.FacebookUpdateTime - f.ClientTime.Offset <= 
               DateTimeOffset.Now.UtcDateTime - ConvertTimeSpan(f.Offset) &&
               f.Status == FacebookNotificationStatus.Active &&
               f.Alarm.User.FbStatus == true).ToList();

Exception message

The specified type member 'Offset' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

My model:

 public class FacebookStatusUpdate
 {
    public long Id { get; set; }
    public DateTime FacebookUpdateTime { get; set; }
    public string PostId { get; set; }
    public DateTime? FacebookPostTime { get; set; }
    public DateTimeOffset ClientTime { get; set; }
    public int Offset { get; set; }

    public virtual FacebookNotificationStatus Status { get; set; }
    public virtual Alarm Alarm { get; set; }
}

Can somebody help me?

Upvotes: 1

Views: 1731

Answers (5)

user1389591
user1389591

Reputation:

The following code can replace a.MyDate.Offset in a Linq to Entities query:

SqlFunctions.DatePart("TZoffset", a.MyDate)

Please remember that this work with SqlServer.

Upvotes: 1

Alejandro del R&#237;o
Alejandro del R&#237;o

Reputation: 4046

You can split your query in two parts.

var fbPost_DBQuery = db.FacebookStatusUpdates
     .Where(f => f.Status == FacebookNotificationStatus.Active &&
                 f.Alarm.User.FbStatus == true).ToList();

fbPost_MemoryQuery = fbPost_DBQuery
     .Where(f => f.FacebookUpdateTime - f.ClientTime.Offset <= 
           DateTimeOffset.Now.UtcDateTime - ConvertTimeSpan(f.Offset))
     .ToList();

fbPost_DBQuery is going to query more information from the database and then filtered in fbPost_MemoryQuery. But it will work.

Upvotes: 0

CodeCaster
CodeCaster

Reputation: 151588

The problem is that you can't directly access the DateTimeOffset.Offset property (f.ClientTime.Offset), just as you can't access a DateTime.Year property directly in Linq to entities - it can't directly be translated into a store expression.

I don't know what your f.FacebookUpdateTime - f.ClientTime.Offset <= DateTimeOffset.Now.UtcDateTime - ConvertTimeSpan(f.Offset) is supposed to do, but you can take a look at the static SqlFunctions class for some common date manipulation functions like SqlFunctions.DateDiff() that you probably can replace that entire expression with.

Also, are you using EF6? Otherwise you'll have to cast your enum to int, too.

Upvotes: 0

James
James

Reputation: 82096

When you use LINQ to Entities you need to remember your query will be translated into TSQL - if Offset is a custom type then EF has no idea on how to translate it to SQL.

You can of course make this work if you can convert your query to LINQ to Objects i.e.

 var fbPost = db.FacebookStatusUpdates
     .Where(f => f.Status == FacebookNotificationStatus.Active &&
                 f.Alarm.User.FbStatus == true)
     .AsEnumerable() // convert from L2E to L2O
     .Where(f => f.FacebookUpdateTime - f.ClientTime.Offset <= 
           DateTimeOffset.Now.UtcDateTime - ConvertTimeSpan(f.Offset))
     .ToList();

After the call to AsEnumerable() the rest of your query is LINQ to Objects which understands Offset/ConvertToTimespan code. Just to clarify, AsEnumerable() won't materialize your query right way - you still get lazy loading. However, any further querying after AsEnumerable() is done client-side, there is no more interaction with the DB.

When using L2E it's best to stick with the supported primitive types.

Upvotes: 6

Kjartan
Kjartan

Reputation: 19071

I suspect that this has to do with the fact that you can't use standard C# methods and properties directly in Linq to entities, since the data from db.FacebookStatusUpdates might not be loaded initially. To put it another way: That property can't be translated to SQL.

If my understanding is correct, this might provide a solution for you: 

var fbPost = db.FacebookStatusUpdates
              .ToList() // <-- Forces evaluation/data retrieval!
              .Where(f => 
                   f.FacebookUpdateTime - f.ClientTime.Offset <= 
                   DateTimeOffset.Now.UtcDateTime - ConvertTimeSpan(f.Offset) &&
                   f.Status == FacebookNotificationStatus.Active &&
                   f.Alarm.User.FbStatus == true).ToList();

This will load the data into a list before attempting to call ClientTime.Offset, therefore allowing you to make that call.

The cost of this however, is that all the data is loaded before before the Where()-filtering is applied. I guess you have to decide whether this is acceptable or not based on your context and other requirements, or if you will have to look for some other better solution.

Upvotes: 1

Related Questions