LINQ不支持指定的类型成员"偏移"[英] The specified type member 'Offset' is not supported in LINQ to Entities

本文是小编为大家收集整理的关于LINQ不支持指定的类型成员"偏移"的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我在下一个代码运行时得到了例外:

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

异常消息

LINQ在实体中不支持指定的类型成员"偏移".仅有的 初始化器,实体成员和实体导航属性是 支持.

我的模型:

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

有人可以帮我吗?

推荐答案

当您将LINQ使用到实体时,您需要记住您的查询将被翻译成TSQL-如果Offset是自定义类型,则EF不知道如何将其转换为SQL.

.

,如果您可以将查询转换为linq,即.

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

呼叫AsEnumerable()查询的其余部分是对Offset/ConvertToTimespan代码的对象的linq.只是为了澄清,AsEnumerable()不会正确地查询 您的查询方式 - 您仍然会得到懒惰的加载.但是,AsEnumerable()之后的任何进一步的查询都完成了客户端,与DB不再相互作用.

使用l2e时,最好坚持使用支持原始类型.

其他推荐答案

我怀疑这与您不能直接使用LINQ中的标准C#方法和属性到实体有关,因为最初可能不会加载来自db.FacebookStatusUpdates的数据.换句话说:该属性无法翻译成SQL.

如果我的理解是正确的,这可能为您提供解决方案:

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

这将在尝试调用ClientTime.Offset之前将数据加载到列表中,因此允许您进行呼叫.

然而,成本是在应用Where()滤光之前,所有数据都已加载.我想您必须根据您的上下文和其他要求决定是否可以接受,或者您是否必须寻找其他更好的解决方案.

其他推荐答案

以下代码可以在LINQ中替换为实体查询:

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

请记住,这与SQLServer一起工作.

本文地址:https://www.itbaoku.cn/post/1557090.html

问题描述

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?

推荐答案

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.

其他推荐答案

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.

其他推荐答案

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.