How to use materialized collection in IQueryable query?

Question

I have a problem with query which I want to produce.

var ratings = repository.GetAll<Rating>()
                .AsNoTracking()
                .Select(e => new RatingGridDto()
                {
                    Id = e.Id,
                    Sites = e.Locations.Select(x => x.Name).ToList(),
                });

// this filter will come from frontent
var mockedFilter = new List<string>()
            {
                "LosAngeles","Vienna","Praha"
            };


// ratings = IQueryable of course
    
// case 1
ratings = ratings.Where(e => mockedFilter.AsEnumerable().All(f => e.Sites.Contains(f)));

// case 2
ratings = ratings.Where(e => mockedFilter.All(f => e.Sites.Contains(f)));
    
// case 3
ratings = ratings .Where(e => mockedFilter.All(x => e.Sites.Any(y => y == x)));

// case 4
foreach (var filter in mockedFilter)
{
    ratings = ratings .Where(e => e.Sites.Contains(filter));
}

Only method from case 4 works, rest of them are throwing error that EF is not able to translate it to SQL ...

Anyone know why this problem occures and why when we will use foreach all works?

What's the question? What are you trying to do? Describe the actual problem, not how you think it can be solved. There's no EF Core code in the question, unless GetAll just returns a DbSet. Are you trying to return ratings for specific locations? Or load only specific locations for all ratings? The first one can be done by filtering on locations, eg `e.Locations.Any(l=>filters.Contains(l))`. The second requires a [filtered include](https://learn.microsoft.com/en-us/ef/core/querying/related-data/eager#filtered-include) — Panagiotis Kanavos, May 12 '23 at 11:27
Wrapping a high-level DbContext beneath a low-level "repository" is bad practice too. It offers no benefits, introduces bugs and in this case prevents you from writing a good query. Since you want to filter locations, why not start from the *location* entity? Or use query syntax, eg `from rating in ratings from location in rating.locations where filter.Contains(location.Name) select rating;` — Panagiotis Kanavos, May 12 '23 at 11:57
@Panagiotis Kanavos Question is how to get only this ratings, which sites contains all which is in filter, so: if rating site contains all which is in filter , give me that rating. ``` return db.Set(); ``` it's literally starting from ratings .. "Wrapping a high-level DbContext beneath a low-level "repository" is bad practice too." - could You pleasse describe it more preciously? — ruddnisrus, May 12 '23 at 13:01
[Repositories and Unit-of-Work Don't Mix](https://robconery.com/databases/repositories-on-top-unitofwork-are-not-a-good-idea/), [No need for repositories and unit of work with Entity Framework Core](https://gunnarpeipman.com/ef-core-repository-unit-of-work/), and [Repository is the new Singleton](https://ayende.com/blog/3955/repository-is-the-new-singleton) — Panagiotis Kanavos, May 12 '23 at 13:17

Svyatoslav Danyliv · Answer 1 · 2023-05-12T13:18:03.710

1

I would suggest the following approach. Filter before projection:

var ratingsFull = repository.GetAll<Rating>().AsQueryable();

ratingsFull = ratingsFull.Where(r => r.Locations
     .Count(l => mockedFilter.Contains(l.Name)) == mockedFilter.Count);

var rates = ratingsFull  
    .Select(e => new RatingGridDto()
    {
        Id = e.Id,
        Sites = e.Locations.Select(x => x.Name).ToList(),
    });

edited May 12 '23 at 13:18

answered May 12 '23 at 11:55

Svyatoslav Danyliv

21,911
3
16
32

I want only this ratings which locations contains all, which is in mockedFilter, here I will get only if I got one of them – ruddnisrus May 12 '23 at 13:04
You know how to resolve also below similar example? // orders is IQueryable and PurchaseOrderNumbers is list of strings, purchaseOrderFilter is just string orders= orders.Where(e => e.PurchaseOrderNumbers.Any(x => x.StartsWith(purchaseOrderFilter))); – ruddnisrus May 12 '23 at 15:25
Yes, i know. But it needs dynamic Expression Tree generation. – Svyatoslav Danyliv May 13 '23 at 05:25
Svyatoslav Danyliv I posted new question for that and I was trying to implement it this way as You suggested but nothing works... could You please check it? https://stackoverflow.com/questions/76249997/own-implementation-of-iqueryable-contains-method-does-not-work – ruddnisrus May 14 '23 at 22:55

How to use materialized collection in IQueryable query?

1 Answers1

Linked