EF Core无法翻译，将在本地进行评估

Question

EF Core无法翻译，将在本地进行评估

asp.net-core entity-framework entity-framework-core

题

我在EF Core 1.1.2中有一个查询，在客户端进行评估，并想知道是否有更好的方法将其转换为sql？

查询：

from l in _ctx.Locations
  join i in _ctx.Inventories on l.Id equals i.LocationId
  join it in _ctx.Items on i.ItemId equals it.Id
  where l.ProjectId == projectid
  group i by new {l.Id, l.LHA} into il
  select new InventoryLocations() {
      Id= il.Key.Id,
      LHA = il.Key.LHA,
      FlaggedItems = il.Any(x=>x.Item != null && x.Item.Flagged)
  }

如果没有，我还有其他选择吗？

据我所知，仍然无法映射视图。
FromSQL（）方法只能返回上下文中已知的类型，例如，我不能将一个模型标记为[NotMapped]。
回到ef6不是一个选择，因为.net核心是目标框架。

楷模：

public class Location
{
    public Guid Id { get; set; }

    [ForeignKey("Project")]
    public Guid ProjectId { get; set; }
    public Project Project {get; set; }
    public string Name { get; set; }
    public string LHA { get; set; }

    [ForeignKey("ScanUser")]
    public Guid? ScanUserId { get; set; }
    public User ScanUser { get; set; }
    [ForeignKey("CheckUser")]
    public Guid? CheckUserId { get; set; }
    public User CheckUser { get; set; }

    [ForeignKey("GroupLeader")]
    public Guid? GroupLeaderId { get; set; }
    public User GroupLeader { get; set; }
    public int State { get; set; }
}

public class Inventory
{
    public Guid Id { get; set; }

    [ForeignKey("Project")]
    public Guid ProjectId { get; set; }
    public Project Project {get; set; }
    public string EANCode { get; set; }
    [ForeignKey("Location")]
    public Guid LocationId { get; set; }
    public Location Location { get; set; }
    public Double ScanQty { get; set; }
    [ForeignKey("ScanUser")]
    public Guid? ScanUserId { get; set; }
    public User ScanUser { get; set; }
    public DateTime? ScanDate { get; set; }
    [ForeignKey("Item")]
    public Guid? ItemId { get; set; }
    public Item Item { get; set; }

    [ForeignKey("InventoryTask")]
    public Guid? InventoryTaskId { get; set; }
    public InventoryTask InventoryTask { get; set; }

    [ForeignKey("CheckUser")]
    public Guid? CheckUserId { get; set; }
    public User CheckUser { get; set; }
    public DateTime? CheckDate { get; set; }
    public Double PrevQty { get; set; }
}

public class Item
{
    public Guid Id { get; set; }
    [ForeignKey("Project")]
    public Guid ProjectId { get; set; }
    public Project Project {get; set; }
    public string ItemNo { get; set; }
    public string EANCode { get; set; }
    public string Name { get; set; }
    public Double Price { get; set; }
    public bool Deleted { get; set; }
    public DateTime ChangeTime { get; set; }

    public Double BaseQty { get; set; }
    public bool Flagged { get; set; }
}

Perrier

热门答案

如果您添加导航属性，正如Ivan正确建议：

public class Location
{
    // ...
    public ICollection<Inventory> Inventories { get; set; }
}

然后你可以简单地创建一个这样的查询：

var locations = _ctx.Locations
   .Include(x => x.Inventories)
       .ThenInclude(x => x.Item)
   .Where(x => x.ProjectId == projectId)
   .Select(loc => new InventoryLocations
   {
        Id = loc.Id,
        LHA = loc.LHA,
        FlaggedItems = loc.Inventories.Any(inv => inv.LocationId == loc.Id && inv.Item?.Flagged)
   });

Brad

在 Stack Overflow 上查看更多

Ivan Stoev · Accepted Answer · 7/22/2017 12:00:47 AM

当前（并且在传入的EF Core v.2.0中也看起来如此） GroupBy查询在本地处理，因此关键是尽可能避免它们。

并且您的查询似乎符合条件 - 无需首先将数据集与连接相乘，然后将其分组。

我注意到你在实体中只使用了引用导航属性和FK，基本上就像数据库表记录和SQL。但EF允许您定义相应的集合导航属性，允许您从逻辑根开始查询，从而消除了连接和分组的需要。

如果您定义从Location到Inventory导航属性

public class Location
{
    // ...
    public ICollection<Inventory> Inventories { get; set; }
}

那么等价的查询可以简单地说：

from loc in _ctx.Locations
where loc.ProjectId == projectid
select new InventoryLocations()
{
    Id = loc.Id,
    LHA = loc.LHA,
    FlaggedItems = loc.Inventories.Any(inv => inv.Item != null && inv.Item.Flagged)
}

这将完全转换为SQL。

如果由于某种原因您无法创建上述集合导航属性，您仍然可以从位置开始并手动将它们与库存相关联：

from loc in _ctx.Locations
where loc.ProjectId == projectid
select new InventoryLocations()
{
    Id = loc.Id,
    LHA = loc.LHA,
    FlaggedItems = _ctx.Inventories.Any(inv => loc.Id == inv.LocationId && inv.Item != null && inv.Item.Flagged)
}

EF Core无法翻译，将在本地进行评估

题

Fastest Entity Framework Extensions

一般承认的答案

热门答案

Performance

Expression Evaluator

Related