EF Core 无法翻译，将在本地进行评估 [英] EF Core could not be translated and will be evaluated locally

问题描述

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

I have a query in EF Core 1.1.2 that is evaluated on client side and would like to know if there is a better way to translate it into 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)
  }

如果没有，我还有什么其他选择?

If not, what other options do I have?

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

模型:

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

推荐答案

目前(看起来也是在传入的 EF Core v.2.0)GroupBy 查询是在本地处理的，所以关键是尽可能避免它们.

Currently (and looks like also in the incoming EF Core v.2.0) the GroupBy queries are processed locally, so the key is to avoid them where possible.

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

And your query seems to be eligible for that - there is no need to first multiply the data set with joins and then group it back.

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

I've noticed you use only reference navigation properties and FKs in your entities, basically like database table record and SQL. But EF allows you to define also a corresponding collection navigation properties which allow you to start queries from the logical root, thus eliminating the need of joins and group by.

如果你定义了从 Location 到 Inventory

If you define navigation property from Location to Inventory

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

那么等效的查询可以是:

then the equivalent query could be simply:

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.

which will be fully translated to SQL.

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

If for some reason you can't create the above collection navigation property, still you can start with locations and manually correlate them with inventories:

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 无法翻译，将在本地进行评估的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！