EF Core could not be translated and will be evaluated locally

Question

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?

The query:

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?

• As I know there's still no way mapping views.
• FromSQL() method can return types already known in the context only and I can not mark one model as [NotMapped] for example.
• Moving back to ef6 is not an option because .net core is the target framework.

Models:

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

Solution

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.

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.

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

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