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].
• 因为.net核心是目标框架，所以不能选择返回ef6.

型号:

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.

我注意到您在实体中仅使用 reference 导航属性和FK，基本上像数据库表记录和SQL一样.但是EF允许您还定义相应的 collection 导航属性，该属性允许您从逻辑根目录开始查询，从而消除了联接和分组依据.

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

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.

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

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屋！