EF Core include lists only if a property is true

Question

I have 2 models,

 public class Bin : INotifyPropertyChanged
    {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]   
    public int Id { get; set; }    

    public IList<Produit> Produits
    {
        get;
        set;
    }      
}

and

 public class Produit : INotifyPropertyChanged
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }


        public bool Actif
        {
            get
            {
                return _Actif;
            }
            set
            {
                if (_Actif != value)
                {
                    _Actif = value;
                    RaisePropertyChanged("Actif");
                }
            }
        }
     }

In my ViewModel I try to include Produit only if the property "Actif" is true:

Bin bins = new ObservableCollection<Bin>(await db.Bins.Include(b=>b.Produits).Where(b => b.Produits.Count() > 0).ToListAsync());

If I use:

 Bin bins = new ObservableCollection<Bin>(await db.Bins.Include(b => b.Produits.Where(p => p.Actif)).Where(b => b.Produits.Count() > 0).ToListAsync());

I received an error like:

System.ArgumentException: 'The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties.

How can I load only "Actif" products when I include "Produit" in my query?

This work perfectly:

bins = new ObservableCollection<Bin>(db.Bins.Where(b => b.Produits.Count() > 0).Select(p => new
                {
                    p,
                    Produit = p.Produits.Where(pp => pp.Actif)
                })
               .AsEnumerable()
               .Select(x => x.p)
               .ToList()
               );

The only problem is that it loads the entity anyway, but with a null attribute. But an IF corrects the problem.

You can always do two asynchronous queries and combine them together. — Fabio, Oct 04 '19 at 17:39
checkout this [answer](https://stackoverflow.com/a/16801205/9534819). — Matt.G, Oct 04 '19 at 17:55
fabio, yes but we can't do a "WHERE" into a include, I can't load entities with a include condition of loading. — steffy97, Oct 08 '19 at 12:54

Schmuhl · Answer 1 · 2019-10-04T18:21:34.290

0

Edit

I think this is what you want to do...

Bin bins = new ObservableCollection<Bin>(await db.Bins.Where(b => b.Produits.Count() > 0)
.Where(b => b.Produits.Any(p => p.Actif))
.Select(b => new
{
    b,
    Produits = b.Produits.Where(p => p.Actif)
})
.ToListAsync());

edited Oct 04 '19 at 18:21

answered Oct 04 '19 at 17:36

Schmuhl

11
4

The "Actif" property is in "Produits" class, I can't access to "b.Actif" because "produits" is a list. – steffy97 Oct 04 '19 at 17:41
Ok, i oversee that... Maybe this is what you are searching for... https://entityframework.net/include-with-where-clause – Schmuhl Oct 04 '19 at 18:07

score 0 · Answer 2 · answered Oct 07 '19 at 10:29

As you noticed, EF Core doesn't support to filter in the Include method.

Disclaimer: I'm the owner of the project Entity Framework Plus

The EF+ Query IncludeFilter (free and open source) allows easily filter included entities.

To use it, you simply need to replace all "Filter" by "IncludeFilter"

Example:

Bin bins = new ObservableCollection<Bin>(
    await db.Bins.IncludeFilter(b => b.Produits.Where(p => p.Actif))
        .Where(b => b.Produits.Count() > 0).ToListAsync()
    );

EF Core include lists only if a property is true

2 Answers2