Entity Framework Core - dynamic filtering

Question

I'm having an issue retrieving correct result from database using Entity Framework Core.

Here is my Article table:

I would like to create dynamic filter using IQueryable that will return the following results:

Select a.Id, a.Name, a.BrandId, a.GenderId
from dbo.Articles a
where GenderId in (1)
  and BrandId in (1, 2, 3)

which returns:

This is my controller action:

public async Task<IActionResult> Clothes()
{
    var model = new ArticleFilterViewModel();
    model.Genders.AddRange(new int[1] { 1 });
    model.Brands.AddRange(new int[3] { 1, 2, 3 });
    var result = await articleSerivce.GetFilteredUsers(model);
    return View(result);
}

Here is my repository method for fetching filtered articles:

public ICollection<Article> GetFilteredUsers(ArticleFilter filter)
{
    var articles = GetAll();
    articles = FilteredByBrand(articles, filter.Brands);
    articles = FilteredByGender(articles, filter.Genders);
    var result = articles.ToList();
    return result;
}

IQueryable<Article> FilteredByBrand(IQueryable<Article> articles,  List<int> items)
{
    return articles.WhereIf(items.IsNotNullOrEmpty(),  x => items.Contains(x.BrandId));
}

IQueryable<Article> FilteredByGender(IQueryable<Article> articles, List<int> items)
{
     return articles.WhereIf(items.IsNotNullOrEmpty(), x => items.Contains(x.GenderId));
}

After this code is executed I get the following:

I get 3 articles as a result instead of 2 (Article Id's: 2,3,2).

Any idea what I'm doing wrong?

Looks ok to me. So you are getting objects with duplicate PK (2,3,2)? Sounds weird. — Ivan Stoev
– Ivan Stoev, Commented Aug 23, 2018 at 9:41
You can provide a public downloadable link in your question (Dropbox, OneDrive, GitHub etc.) — Ivan Stoev
– Ivan Stoev, Commented Aug 23, 2018 at 11:43

Selmir · Accepted Answer · 2018-08-23 13:17:28Z

1

I dont know what your WhereIf() method do or what your really get from GetAll() but if I replace WhereIf() by Where() it works as expected.

private IQueryable<Article> FilteredByBrand(IQueryable<Article> articles, List<int> items)
{
    return items.IsNullOrEmpty() ? articles : articles.Where(x => items.Contains(x.BrandId));
}

private IQueryable<Article> FilteredByGender(IQueryable<Article> articles, List<int> items)
{
    return items.IsNullOrEmpty() ? articles : articles.Where(x => items.Contains(x.GenderId));
}

Returns

2 - Cool t-shirt - 2 - 1
3 - Yellow panties - 3 - 1

Here is my complete repro code.

edited Aug 23, 2018 at 13:17

answered Aug 23, 2018 at 12:44

Selmir

1,2261 gold badge12 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3569465 Over a year ago

Thanks. It seams that problem is in WhereIf function. ' public static IQueryable<TSource> WhereIf<TSource>(this IQueryable<TSource> source, bool condition, Func<TSource, bool> predicate) { if (condition) return source.Where(predicate).AsQueryable(); else return source; } '

Ivan Stoev Over a year ago

@user3569465 Then apparently it's wrong. I was expecting it to be

public static IQueryable<TSource> WhereIf<TSource>(this IQueryable<TSource> source, bool condition, Expression<Func<TSource, bool>> predicate) { return condition ? source.Where(predicate) : source; }

. Note the Expression<Func<…>> - never use Func<..> with IQueryable<T>.

Marcio Martins · Accepted Answer · 2019-02-08 21:40:02Z

1

With EF Core, try this.

Simple and performatic dynamic filter

public static List<Inventory> GetAll(string po, string cod)
{
    using (var context = new ApplicationDbContext())
    {
        var Items = context.Inventorys
            .Where(p => p.CodigoPO == po || po == string.Empty)
            .Where(p => p.CodigoProduto == cod || cod == string.Empty).ToList();
        return Items;
    }
}

edited Feb 8, 2019 at 21:40

answered Feb 7, 2019 at 19:15

Marcio Martins

113 bronze badges

Collectives™ on Stack Overflow

Entity Framework Core - dynamic filtering

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related