[Solved] problems combining join with groupBy in LINQ
Looking to automatically optimize YOUR SQL query? Start for free.

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)

Category table

And CategoryPaths, in which we contain information about the directory structure.

Category Paths

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:

Requested 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

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `categories` ADD INDEX `categories_idx_id` (`Id`);
ALTER TABLE `categorypaths` ADD INDEX `categorypaths_idx_categoryid` (`CategoryId`);
The optimized query:
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

Related Articles



* original question posted on StackOverflow here.