Entity Framework Core Linq query to filter related entity

Question

I've been figuring out a long time on how to write a query on filtering related entity through Entity Framework Core while using Include, suppose I have following two class:

public class Order
{
  public int OrderId {get; set;}
  public String CreatedBy{get; set;}
  public virtual Collection<OrderDetail> OrderDetails { get; set; } = new Collection<OrderDetail>();
}

public class OrderDetail
{
   public Int64? OrderDetailID { get; set; }
   public Int64? OrderID { get; set; }
   public string ProductName { get; set; }
}

if I would like to find all orders created by "Jason" and which order detail has product name equals to "Apple", in sql it would be like: Hide Copy Code

SELECT *
FROM Orders O
INNER JOIN OrderDetail OD ON O.OrderId = OD.OrderId
WHERE O.CreationUser = 'Jason' and OD.ProductName = 'Apple'

However I am not able to figure out how to write that using EntityFramework, something like below would not work:

 await DbContext.Set<Order>()
    .Include(p => p.OrderDetails)
    .Where(o => o.CreationUser == "Jason")
    .Where(o => o.OrderDetails.Where(od => od.ProductName == "Apple"));

There are scenarios like above, I know how to filter property with base entity class like Order in above example but I don't know how to deal with related entity using Include/ThenInclude like filtering on OrderDetail.ProductName, I've been researching a lot but still no clue therefore at the end I have to use Store procedure instead, which is not recommended by most developers.

Maybe a linq sql could do that?

Please help me understand more about it! Thanks very much to everyone who can share your knowledge!

Answer 1

You can simply translate your SQL script to linq:

var orders = (from O in context.Order
              join OD in context.OrderDetail on O.OrderId equals OD.OrderId
              where O.CreatedBy == "Jason" && OD.ProductName == "Apple"
              select order).Distinct().ToList();

//or this solution
orders = context.Set<Order>().Include(p => p.OrderDetails)
   .Where(x => x.CreatedBy == "Jason" && x.OrderDetails.Any(y => y.ProductName == "Apple"))
   .ToList();    

Answer 2

@Slava answer looks correct. But I want to extend his answer. If you want to use like in your query, you can use EF.Functions.Likemethod. It is less expensive in terms of memory and handles complex expressions. You can use the same in your scenario also like the below code. On relational databases, this is usually directly translated to SQL.

var orders = (from O in context.Order
              join OD in context.OrderDetail on O.OrderId equals OD.OrderId
              where EF.Functions.Like(O.CreatedBy, "Jason") && EF.Functions.Like(OD.ProductName, "Apple")
              select order).Distinct().ToList();