CODEWITHSUNDEEP
entity-frameworkasp.net-coreentity-framework-core
Perrier
Perrier

Reputation: 2837

EF Core could not be translated and will be evaluated locally

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?

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

Upvotes: 5

Views: 24949

Answers (2)

Brad
Brad

Reputation: 4553

If you add the navigation property as Ivan correctly suggests:

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

Then you can simply create a query like this:

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

Upvotes: 0

Ivan Stoev
Ivan Stoev

Reputation: 205729

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

Upvotes: 5

Related Questions