Entity Framework Select Query

Question

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

You need a `Seller` that its `InventoryItems` collection contain at least one `InventoryItem` that is having at least one `SiteInventoryItem` with the appropriate value? — haim770, Aug 18 '15 at 17:58
@haim770 - I need a `Seller` with a collection of `InventoryItem` and then each of those have a collection of `SiteInventoryItem` with the constraints being `Seller.SellerId == 14` and `SiteInventoryItem.Site == 1` — Sam, Aug 18 '15 at 18:01
What does "no luck at all" mean? Does it compile? Does it run? Does it return results? — CodeCaster, Aug 18 '15 at 18:51
@CodeCaster - It compiles and runs, but the result is not what is expected. — Sam, Aug 18 '15 at 20:14

score 1 · Accepted Answer · edited Aug 19 '15 at 22:13

1

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.

edited Aug 19 '15 at 22:13

Sam

15,336
25
85
148

answered Aug 19 '15 at 15:28

Gert Arnold

105,341
31
202
291

This stills returns all of the `InventoryItem` regardless of if they have a `SiteInventoryItem` or not. – Sam Aug 19 '15 at 18:23
I see, the condition should be one level deeper (the naming is a bit elusive). – Gert Arnold Aug 19 '15 at 19:45
Still getting the same result. – Sam Aug 19 '15 at 20:25
Does not even compile now. – Sam Aug 19 '15 at 21:39
Well, I haven't got your code. Probably some parentheses somewhere. I have to do this by eyesight only. Feel free to modify my answer. – Gert Arnold Aug 19 '15 at 21:51
1

OK, I updated the answer, you were super close and it got me going in the right direction, Thanks for your efforts!! – Sam Aug 19 '15 at 22:13

Entity Framework Select Query

1 Answers1

Linked