C#•2y ago

✅ LINQ / EFCore

        var centers = await _dbContext.Centers
            .Include(x => x.Categories)
            .Where(x => x.CityId == request.CityId && (request.CategoryIds.Count == 0 ||
                                                       x.Categories.All(y => request.CategoryIds.Contains(y.Id))))
            .Include(x => x.Reviews)
            .DoThis(x.Rating = x.Reviews.Average(y=>y.Rating))
            .Skip(request.Page * request.Count)
            .Take(request.Count)
            .ToListAsync(cancellationToken: cancellationToken);

        var centers = await _dbContext.Centers
            .Include(x => x.Categories)
            .Where(x => x.CityId == request.CityId && (request.CategoryIds.Count == 0 ||
                                                       x.Categories.All(y => request.CategoryIds.Contains(y.Id))))
            .Include(x => x.Reviews)
            .DoThis(x.Rating = x.Reviews.Average(y=>y.Rating))
            .Skip(request.Page * request.Count)
            .Take(request.Count)
            .ToListAsync(cancellationToken: cancellationToken);

is this possible? I'm not sure how DoThis function is called

8 Replies

barcodeOP•2y ago

I have a non mapped rating in my model that i want to fill with a rating from reviews table

Jimmacle•2y ago

i don't know what DoThis is but if it's a custom extension method then that's not possible to do that way EF core queries are translated to an equivalent SQL query and sent to the server, so you're limited in the kind of code you can use you could project it to a model that includes the average rating with .Select

barcodeOP•2y ago

     var centers = await _dbContext.Centers
            .Include(x => x.Categories)
            .Where(x => x.CityId == request.CityId && (request.CategoryIds.Count == 0 ||
                                                       x.Categories.All(y => request.CategoryIds.Contains(y.Id))))
            .Skip(request.Page * request.Count)
            .Take(request.Count)
            .ToListAsync(cancellationToken: cancellationToken);

        foreach (var center in centers)
        {
            center.Rating = await _dbContext.Reviews
                .Where(x => x.CenterId == center.Id)
                .DefaultIfEmpty()
                .AverageAsync(x => x == null ? 0 : x.Rating, cancellationToken: cancellationToken);
        }

     var centers = await _dbContext.Centers
            .Include(x => x.Categories)
            .Where(x => x.CityId == request.CityId && (request.CategoryIds.Count == 0 ||
                                                       x.Categories.All(y => request.CategoryIds.Contains(y.Id))))
            .Skip(request.Page * request.Count)
            .Take(request.Count)
            .ToListAsync(cancellationToken: cancellationToken);

        foreach (var center in centers)
        {
            center.Rating = await _dbContext.Reviews
                .Where(x => x.CenterId == center.Id)
                .DefaultIfEmpty()
                .AverageAsync(x => x == null ? 0 : x.Rating, cancellationToken: cancellationToken);
        }

i ended up on this I load needed centers first then calculate their average ratins wanted to do it in single query but i don't know how

Jimmacle•2y ago

that looks like it would be pretty inefficient to execute, you're making N +1 queries something like

.Select(x => new ModelWithRating
  {
    ...,
    Rating = x.Reviews.Average(y => y.Rating)
  })
.ToListAsync();

.Select(x => new ModelWithRating
  {
    ...,
    Rating = x.Reviews.Average(y => y.Rating)
  })
.ToListAsync();

barcodeOP•2y ago

I already include the Rating in my model i just don't map it, I will remove it and do i that way, thank you

Jimmacle•2y ago

you could do a computed column on the server side too depending on your use case idk about the performance of that

barcodeOP•2y ago

just an average rating so idk if there is a need i will do it with select any way to just include it in existing object instead of creating a new one in select?

Jimmacle•2y ago

you could try mutating x and returning it but i don't know if that would be translatable to SQL

Gaming

Programming

✅ LINQ / EFCore