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!

something like below would not work: What specifically about it didn't work? — mjwills
– mjwills, Commented Jun 1, 2018 at 3:55

Slava Utesinov · Accepted Answer · 2018-06-02 17:39:02Z

6

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

edited Jun 2, 2018 at 17:39

answered Jun 1, 2018 at 5:45

Slava Utesinov

13.5k2 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Drex Over a year ago

Thank you very much for sharing your answer! Is there any way to use "Include" query for the same scenario? Or being said that linq-to-sql might be better than "Include" query and the only way to implement this approach?

Slava Utesinov Over a year ago

@KevinDing, if you want to fetch OrderDetails as part of final result, you can Include them, as shown at my answer, but it is not needed, if you only want to filter by them.

Drex Over a year ago

Thank you very much for providing the alternative answer!

Drex Over a year ago

Hi Slava, would you mind helping me take a look at another EF Core related question at stackoverflow.com/questions/50733417/…?

Martijn Pieters · Accepted Answer · 2018-09-10 11:18:28Z

0

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

edited Sep 10, 2018 at 11:18

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

answered Jun 1, 2018 at 7:26

Vivek Nuna

30.6k34 gold badges162 silver badges259 bronze badges

2 Comments

Drex Over a year ago

Thank you very much for providing the extension usage for "LIKE" command!

Gert Arnold Over a year ago

How can you hold that Like be "less expensive" than =? The way you use it is functionally equivalent to = because there are no wildcards. So there is no reason to use Like and it should not be used because, on the contrary, it's more expensive. Also, there's no reason to bring up Like here. It's not asked for and beside the point.

Collectives™ on Stack Overflow

Entity Framework Core Linq query to filter related entity

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related