EverSQL Database Performance Knowledge Base
problems combining join with groupBy in LINQ
I have following SQL request:
select cp.CategoryId, group_concat(cp.PathId order by cp.Level) as Path,
group_concat(c.Seo order by cp.Level) as SeoPath, max(cp.Level) as Level,
max(c.SortOrder) as SortOrder from categorypaths cp
join categories c on cp.PathId = c.Id group by cp.CategoryId ORDER BY Level ASC
I can't translate a part of it. The part with group_contact exactly.
public async Task<IActionResult> GetPath()
{
using DataContext context = Context.CreateDbContext();
var result = from cp in context.CategoryPaths
join c in context.Categories
on cp.PathId equals c.Id
select new {cp.CategoryId, c.SortOrder };
return Ok(result);
}
I fail as soon I combine join with groupBy. If I print first groupBy, then I am out of scope and can't access previous date.
What may be a solution for that?
UPD:
I have these two tables Category, where we have the basic information about its status and its namings (name to be displayed and SEO title)
And CategoryPaths, in which we contain information about the directory structure.
CategoryID - the category that is described PathId is a category that is included in the path of the category selected by Id. Level - the nesting level of the Path Id in the description of the path to the category we need
In this screenshot, for example, you can see that the full path for a category with ID 6 is category 5 -> category 6
I need to create LINQ request to get this table:
For getting this table, I've used this SQL request:
select cp.CategoryId, group_concat(cp.PathId order by cp.Level) as Path,
group_concat(c.Seo order by cp.Level) as SeoPath, max(cp.Level) as Level,
max(c.SortOrder) as SortOrder from categorypaths cp
join categories c on cp.PathId = c.Id group
by cp.CategoryId ORDER BY Level ASC
UPD FOR @SELVIN I've tried to do that simple thing
public async Task<IActionResult> GetPath()
{
using DataContext context = Context.CreateDbContext();
var result = await context.CategoryPaths
.GroupBy(cp => cp.CategoryId)
.Select(table => new
{
table.Key,
Path = string.Join(", ", table.OrderBy(a=> a.Level).Select(e => e.PathId))
//anotherGroupedPropInSimilarWay
}).ToListAsync();
return Ok(result);
}
But as soon I run this, I get this:
InvalidOperationException: The LINQ expression 'GroupByShaperExpression: KeySelector: c.CategoryId, ElementSelector:EntityShaperExpression: EntityType: CategoryPath ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False .OrderBy(a => a.Level)' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
But I use .ToListAsync() already. Don't get it