实体框架核心Linq查询以过滤相关实体 [英] Entity Framework Core Linq query to filter related entity

问题描述

我一直在弄清楚如何在使用Include的同时通过Entity Framework Core编写有关过滤相关实体的查询，假设我有以下两个类:

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

如果我想查找由"Jason"创建的所有订单，并且该订单的详细信息的产品名称等于"Apple" ，则在sql中将是这样:隐藏复制代码

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'

但是我无法弄清楚如何使用EntityFramework来编写该代码，如下所示将无法正常工作:

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

在上述情况下，我知道如何使用基础实体类(如上例中的Order)过滤属性，但是我不知道如何使用Include/ThenInclude处理相关实体，例如对OrderDetail.ProductName进行过滤， 我已经进行了很多研究，但仍然没有任何线索，因此最后我不得不使用存储过程代替，大​​多数开发人员不建议这样做.

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.

也许linq sql可以做到这一点?

Maybe a linq sql could do that?

请帮助我更多地了解它！非常感谢所有可以分享您的知识的人！

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

推荐答案

您只需将SQL脚本转换为linq:

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

这篇关于实体框架核心Linq查询以过滤相关实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！