实体框架复杂LINQ支持 [英] Entity Framework Complex LINQ Support

问题描述

我知道实体框架有一些LINQ支持问题（至少与它的前身LINQ to SQL相比）...通常我可以找到重组我的LINQ查询的创造性方式，以支持EF并且不会抛出

 无法创建类型为...的常量值
  

但这一次我有麻烦。像往常一样，问题与复杂的连接有关。这次需要建模我正在使用的一些遗留数据。

我有一个关于办公室的查询（一个简单的POCO）

  public IQueryable< Office>办公室
 {
获取
 {
 IQueryable< Office> query = 
从_context.Locations中的pn 
其中pn.Type ==Y
选择新的Office 
 {
 Id = pn.Id + 1000，
 Name = pn.Name，
}; 
 
 query = query.Union（
 from _ in _context.Resources 
其中r.ResourceType ==L
选择新的Office 
 {
 Id = r.ResourceId，
 Name = r.ResourceName，
}）; 
 
返回查询; 
} 
} 
  

然后我还有其他一些Office属性

  public IQueryable< ScheduleEntry> ScheduleEntries 
 {
 get 
 {
从_context.CalendarEntries中的pc返回
 
在此处加入o。pc.LocationId上的办公室
等于o.Id进入办公室
从办公室办公室.DefaultIfEmpty（）
让mainOffice = this.Offices.First（）
选择新的ScheduleEntry 
 {
 Id = pc .CalendarId，
 StartDateTime = pc.StartDateTime？ DateTime.MinValue，
 EndDateTime = pc.EndDateTime？ DateTime.MinValue，
 Office = pc.LocationId == 0？ main办公室，办公室，
}; 
} 
} 
  

请注意，告诉我使其成为可计数的失败目的...所以请不要建议。

等等....做 CalendarEntries.ToArray（） throws 无法创建类型为Office的常量值...

具体问题是让mainOffice = Offices.First（）。如果我删除该逻辑，查询工作正常。任何想法如何使这个工具与实体框架？

谢谢。

解决方案

好的，看起来像双重查询是绊倒了。只有选择是离开加入办公室或执行以下操作：

  Office mainOffice = Offices.First（）; 
从_context.CalendarEntries中的pc返回
在pc.LocationId的办公室中加入o在办公室中的办公室
中的o.Id.DefaultIfEmpty（）
选择新的ScheduleEntry 
 {
 Id = pc.CalendarId，
 StartDateTime = pc.StartDateTime？ DateTime.MinValue，
 EndDateTime = pc.EndDateTime？ DateTime.MinValue，
 Office = pc.LocationId == 0？ main办公室，办公室，
}; 
  

我假设你不想这样做，因为有关使IEnumerable的投诉，所以你将需要加入该局可查询的一些如何告诉EF如何将查询串在一起。让x = y或value = value寻找一个常数，我猜这是有道理的。

I know that Entity Framework has some LINQ support problems (at least in comparison to its predecessor LINQ to SQL)...and usually I'm able to find a creative way of restructuring my LINQ query so that it's supported by EF and doesn't throw a

"Unable to create a constant value of type ..."

But this time I'm having trouble. As usual, the problem is related to complex joins. This time it's required for modeling some legacy data I'm working with.

I have a query for Offices (a simple POCO)

public IQueryable<Office> Offices
{
    get
    {
        IQueryable<Office> query = 
            from pn in _context.Locations
            where pn.Type == "Y"
            select new Office
            {
                Id = pn.Id  + 1000,
                Name = pn.Name,
            };

        query = query.Union(
            from r in _context.Resources
            where r.ResourceType == "L"
            select new Office
            {
                Id = r.ResourceId,
                Name = r.ResourceName,
            });

        return query;
    }
}

Then I have something else that has an Office property on it.

public IQueryable<ScheduleEntry> ScheduleEntries
{
    get
    {
        return 
            from pc in _context.CalendarEntries
            join o in this.Offices on pc.LocationId 
                equals o.Id into offices
            from office in offices.DefaultIfEmpty()
            let mainOffice = this.Offices.First()
            select new ScheduleEntry
            {
                Id = pc.CalendarId,
                StartDateTime = pc.StartDateTime ?? DateTime.MinValue,
                EndDateTime = pc.EndDateTime ?? DateTime.MinValue,
                Office = pc.LocationId == 0 ? mainOffice : office,
            };
    }
}

Please note, telling me to make it an enumerable defeats the purpose...so please don't advise that.

And so....doing CalendarEntries.ToArray() throws "Unable to create a constant value of type Office"...

Specifically the problem is the let mainOffice = Offices.First(). If I remove that logic, the query works fine. Any idea how to make this work with Entity Framework?

Thanks.

解决方案

Yep ok, looks like the double queryable is tripping it up. Only option is to either left join to Offices or do the following:

       Office mainOffice = Offices.First();
       return from pc in _context.CalendarEntries
               join o in Offices on pc.LocationId equals o.Id into offices
               from office in offices.DefaultIfEmpty()
               select new ScheduleEntry
                          {
                              Id = pc.CalendarId,
                              StartDateTime = pc.StartDateTime ?? DateTime.MinValue,
                              EndDateTime = pc.EndDateTime ?? DateTime.MinValue,
                              Office = pc.LocationId == 0 ? mainOffice : office,
                          };

I'm assuming you didn't want to do that due to complaints about making it IEnumerable so you will need to join to the Offices queryable some how to tell EF how to string the queries together. Let x = y or value = value is looking for a constant which i guess makes sense.

这篇关于实体框架复杂LINQ支持的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！