The user can search for companies by city, brand, service type and rating. I have developed such a query, but I am getting an error in the rating section. The error message is as follows:
The LINQ expression 'DbSet .Join( outer: DbSet, inner: f => f.Id, outerKeySelector: c => c.FirmId, innerKeySelector: (f, c) => new TransparentIdentifier<Firm, Comment>( outer = f, inner = c )) .GroupBy( source: ti => ti.Outer, keySelector: ti => new { firm = ti.Outer, rating = ti.Inner.Rate })' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
I encountered this error after adding the rating part. How can I fix this? I don't think it will be effective to pull the data in this way. How should I go about developing this place? I also share the codes I used for the query with you.
public async Task<IEnumerable<Firm>> GetFirmsForCustomerSearch(int cityId, int brandId, int serviceTypeId, int rate)
{
var query = from firms in AracsalContext.Firm select firms;
if (brandId > 0)
query = from firms in query
join firmBrands in AracsalContext.Firmbrand on new { f1 = firms.Id, f2 = brandId } equals new { f1 = firmBrands.FirmId, f2 = firmBrands.BrandId }
select firms;
if (serviceTypeId > 0)
query = from firms in query
join firmServices in AracsalContext.Firmservice on new { f1 = firms.Id, f2 = serviceTypeId } equals new { f1 = firmServices.FirmId, f2 = firmServices.ServiceId }
select firms;
if (cityId > 0)
query = from firms in query
where firms.CityId == cityId
select firms;
if (rate > 0)
{
query = from firms in query
join comments in AracsalContext.Comment on firms.Id equals comments.FirmId
group new
{
firm = firms,
rating = comments.Rate
} by firms into g
where g.Average(r => r.rating) > rate
select g.Key;
}
var result = await query.ToListAsync();
return result;
}
Thank you so much. Ramazan
CodePudding user response:
Grouping in SQL has limitations - you can return only grouping key and aggregation result. For returning whole record, you have to join to original query gain.
if (rate > 0)
{
filteredByRate =
from firms in query
join comments in AracsalContext.Comment on firms.Id equals comments.FirmId
group new
{
rating = comments.Rate
} by new { firms.Id } into g
where g.Average(r => r.rating) > rate
select g.Key;
query =
from films in query
join f in filteredByRate on films.Id equals f.Id
select films;
}
CodePudding user response:
You need to group by some Id like firms.Id
query = from firms in query
join comments in AracsalContext.Comment on firms.Id equals comments.FirmId
group new
{
firm = firms,
rating = comments.Rate
} by firms.Id into g
where g.Average(r => r.rating) > rate
select g.Key;