CODEWITHSUNDEEP
c#entity-framework
Sam
Sam

Reputation: 15771

Entity Framework Select Query

I have the following classes:

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

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

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?

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

Upvotes: 2

Views: 160

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109109

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

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.

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.

Upvotes: 1

Related Questions