Entity Framework Core Linq query to filter related entity

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 CreatedByget; 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!

something like below would not work:

2 Answers
2

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


OrderDetails

Include

@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.

like

EF.Functions.Like

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


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.