实体框架选择查询

问题描述

我有以下课程:

public class Seller : Entity
{
    public int SellerId { get; set; }
    public string Name { get; set; }

    public ICollection<InventoryItem> InventoryItems { get; set; }
}

public class InventoryItem : Entity
{
    public int InventoryId { get; set; }
    public int SellerId { get; set; }
    public string SellerSku { get; set; }

    public ICollection<SiteInventoryItem> SiteInventoryItems { get; set; }
}

public class SiteInventoryItem : Entity
{
    public int Id { get; set; }
    public int InventoryId { get; set; }
    public SiteType Site { get; set; }
}

因此，卖方具有 InventoryItem 的集合，而反过来又具有 SiteInventoryItem 的集合.

So a Seller has a collection of InventoryItem which in turn have a collection of SiteInventoryItem.

如何获取具有列表 InventoryItems 的 Seller ，其中该列表具有 SiteInventoryItems 的列表，其中 SiteType == SiteType.SiteName 和 Seller.SellerId == 14 ?

How do I get a Seller with a list of InventoryItems that have a list of SiteInventoryItems where the SiteType == SiteType.SiteName and the Seller.SellerId == 14?

SiteType 是一个 Enum ，而数据库类型是 int .

The SiteType is an Enum and the db type is int.

我尝试了以下方法，并且可以编译并运行，但是结果不是预期的:

I have tried the following and it compiles and runs, but the result is not what is expected:

var seller = repo.Query(
                x => x.InventoryItems,
                x => x.InventoryItems.Select(y => y.SiteInventoryItems)
            ).Select(x => new
            {
                Seller = x,
                InventoryItems = x.InventoryItems,
                SiteInventoryItems = x.InventoryItems.Select(y => new
                {
                    InventoryItem = y,
                    SiteInventoryItems = y.SiteInventoryItems.Where(z => z.Site == SiteType.Amazon)
                })
            }).Where(x => x.Seller.SellerId == 14).First();

推荐答案

var seller = repo.Query()
        .Select(x => new
        {
            Seller = x,
            InventoryItems = x.InventoryItems.Select(y => new
            {
                InventoryItem = y,
                SiteInventoryItems = y.SiteInventoryItems.Where(z => z.Site == SiteType.Amazon)
            }).Where(y => y.SiteInventoryItems.Any())
        }).Where(x => x.Seller.Id == 14).First();

我对结果类型进行了一些重组，因为我认为这样更有意义.现在，您将看到结果仅返回具有 InventoryItems 和 SiteInventoryItems 的 Seller .

I've restructured the result type a bit, because I think it makes more sense this way. Now you see that the result only returns Sellers that have InventoryItems that have SiteInventoryItems.

请注意，我还删除了 repo.Query()的参数.我假设它们充当 Include .但我也希望 repo.Query()返回一个 IQueryable ，以便将 Where 子句转换为生成的SQL.如果是这样的话， Include 是没有用的，因为您已经在查询包含的实体，因为它们是匿名类型的一部分.

Note that I also removed the arguments for repo.Query(). I assume that they serve as Includes. But I also hope that repo.Query() returns an IQueryable, so that the Where clause will be translated into the generated SQL. If so, the Includes are useless, because you're already querying the included entities because they are part of the anonymous type.

这篇关于实体框架选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！