EF include a linked table with 0 results

Question

I have stores with products, I want to get all stores even if the store doesn't have active products

here are the entity classes

class Store
{
    [Key]
    public Guid UId { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public DateTime Created { get; set; }
    public DateTime Updated { get; set; }
    public bool Inactive { get; set; }

    public virtual ICollection<Product> Products { get; set; }

}

class Product
{
    [Key]
    public Guid Id { get; set; }

    public Guid StoreUid { get; set; }
    public virtual Store Store{ get; set; }

    public string Name { get; set; }
    public string Description { get; set; }
    public DateTime Created { get; set; }
    public DateTime Updated { get; set; }
    public bool Inactive { get; set; }

}

the statement I'm using now is:

        using (var context = new DbContext())
        {
            context.Stores.
                .AsNoTracking()
                    .Where(store => !store.Inactive)
                    .Include(x => x.Products)
                    .ToList();
        }

I tired

   using (var context = new DbContext())
        {
            context.Stores.
                .AsNoTracking()
                    .Where(store => !store.Inactive && store.Products.Any(p=>!p.Inactive))
                    .Include(x => x.Products)
                    .ToList();
        }

but I didn't get the store without any linked product to it.

the think is that I want to be able to get the Store.Products as Null or empty collection I want to avoid as much for each statement and try to do it SQLish.. because in SQL is much easier to do this but I need the Include for nesting

Apologies for not knowing _code-first_, but is anything special required to indicate a 1:many relationship for store-products? What does the generated schema in the DB look like? Check the relationships and foreign keys. — , Dec 14 '22 at 22:25
I know you probably added it for debugging but the `.Include(x => x.Products)` probably isn't needed due to the previous `store.Products.Any(p=>!p.Inactive)` bit. — , Dec 14 '22 at 22:27
You need a filtered Include: `Include(x => x.Products.Where(p => !p.Inactive))` and remove the `store.Products.Any...` condition from the query. See [this](https://stackoverflow.com/questions/43618096/filtering-on-include-in-ef-core/61147681#61147681) for how these conditions play together. — Gert Arnold, Dec 15 '22 at 07:45
I tired using IncludeFilter from https://stackoverflow.com/questions/39636952/how-to-filter-include-entities-in-entity-framework - but I didn't get tote that are active populated — Izikon, Dec 15 '22 at 08:21
So it's EF6. Then, sadly, you need a laborious [work-around](https://stackoverflow.com/a/16801205/861716). — Gert Arnold, Dec 15 '22 at 08:59
If I understand you correctly, you want to retrieve the store when (1) it is not inactive and (2.1) it has at least one active product or (2.2) it doesn't have any (inactive) products. You need to specify both 2.1 and 2.2 condition, they can't be easily combined into a single condition. — grek40, Dec 15 '22 at 12:21

vernou · Answer 1 · 2022-12-15T10:52:17.590

0

EF Core support filtered in include.

To retrieve all active stores and include active products, you can :

context.Stores.AsNoTracking()
    .Where(store => !store.Inactive)
    .Include(x => x.Products.Where(p=>!p.Inactive))
    .ToList();

Also, EF Core support Global Query Filters. Very useful to manage soft delete.

edited Dec 15 '22 at 10:52

answered Dec 15 '22 at 00:17

vernou

6,818
5
30
58

I tried it. it returning all Stores that have products. the one without any was not returned. also reversing the condition will bring the products that are inactive , and there can be alot – Izikon Dec 15 '22 at 07:36
You want retrieve all stores, but include only active products? – vernou Dec 15 '22 at 10:48

EF include a linked table with 0 results

1 Answers1