LinqToDB Exception cannot be converted to SQL

Question

Hello I have an issue with this query

var queryGrouped = queryFiltered
                .GroupBy(c => new { c.Id, c.TableOneId, c.TableOneName, c.TableTwoId, c.TableTwoName, c.TableTwoCode, c.TableThreeId, c.TableThreeName, c.Description, c.EffectiveDate, c.CreatedBy, c.ServiceGroupName })
                .DisableGuard()
                .Select(cg => new Model
                {
                    Id = cg.Key.Id,
                    TableOneId = cg.Key.TableOneId,
                    TableOneName = cg.Key.TableOneName,
                    TableTwoId = cg.Key.TableTwoId,
                    TableTwoCode = cg.Key.TableTwoCode,
                    TableTwoName = cg.Key.TableTwoName,
                    TableThreeId = cg.Key.TableThreeId,
                    TableThreeName = cg.Key.TableThreeName,
                    Description = cg.Key.Description,
                    EffectiveDate = cg.Key.EffectiveDate,
                    EffectiveDateText = cg.Key.EffectiveDate != null ? cg.Key.EffectiveDate.Value.ToString("MM/dd/yyyy") : string.Empty,
                    ServiceGroupName = string.Join(", ", cg.Select(g => g.ServiceGroupName).Distinct()),
                    CreatedBy = cg.Key.CreatedBy
                }).OrderBy(x => x.ServiceGroupName).ToListAsync();

If i run this when try to order by the field ServiceGroup it returns this message

LinqToDB.Linq.LinqException: ''Join(", ", cg.Select(g => g.ServiceGroupName).Distinct())' cannot be converted to SQL.'

So I don't know how to order by this field ServiceGroupName, thanks for any answer.

There is no field called ServiceGroupName in your grouped selection. I don't see how that could work. — topsail, Jun 05 '22 at 22:10
Well, that should be a step forward. Are you expecting ServiceGroupName to be a collection of some kind (more like ServiceGroupNames). It looks like you are trying to turn a list into a string, although normally distinct is on the list, not on the string. I don't think the ordering is the problem, its that line with the join. — topsail, Jun 05 '22 at 22:36
ServiceGroupNames is a string separated by comma, and the ordering works fine if I ordered by another field, but I get the error just when I try to order by ServiceGroupNames. — Oscar Mauricio Benavidez Suare, Jun 05 '22 at 22:44
I see, well if its a string separated by commas (already) there is no need to use String.Join or any of that ... just leave it as is (as a string) `ServiceGroupName = cg.ServiceGroupName` — topsail, Jun 05 '22 at 22:53
Oh wait, well I see you want to remove duplicates ... inline so to speak. Well, if the sql is to complex the sql is too complex. Perhaps you can do the ordering on the list after it is returned? I don't know enough otherwise I'm afraid ... I can only watch for other answers with you. — topsail, Jun 05 '22 at 22:56

score 0 · Answer 1 · answered Jun 06 '22 at 11:22

I would suggest to make grouping on the client side. Note that I have removed ServiceGroupName from grouping key.

var data = await queryFiltered
    .Select(c => new { 
        c.Id, 
        c.TableOneId, 
        c.TableOneName, 
        c.TableTwoId, 
        c.TableTwoName, 
        c.TableTwoCode, 
        c.TableThreeId, 
        c.TableThreeName, 
        c.Description, 
        c.EffectiveDate, 
        c.CreatedBy, 
        c.ServiceGroupName 
    })
    .ToListAsync();

var queryGrouped = data
    .GroupBy(c => new { c.Id, c.TableOneId, c.TableOneName, c.TableTwoId, c.TableTwoName, c.TableTwoCode, c.TableThreeId, c.TableThreeName, c.Description, c.EffectiveDate, c.CreatedBy })
    .Select(cg => new Model
    {
        Id = cg.Key.Id,
        TableOneId = cg.Key.TableOneId,
        TableOneName = cg.Key.TableOneName,
        TableTwoId = cg.Key.TableTwoId,
        TableTwoCode = cg.Key.TableTwoCode,
        TableTwoName = cg.Key.TableTwoName,
        TableThreeId = cg.Key.TableThreeId,
        TableThreeName = cg.Key.TableThreeName,
        Description = cg.Key.Description,
        EffectiveDate = cg.Key.EffectiveDate,
        EffectiveDateText = cg.Key.EffectiveDate != null ? cg.Key.EffectiveDate.Value.ToString("MM/dd/yyyy") : string.Empty,
        ServiceGroupName = string.Join(", ", cg.Select(g => g.ServiceGroupName).Distinct()),
        CreatedBy = cg.Key.CreatedBy
    })
    .OrderBy(x => x.ServiceGroupName)
    .ToList();

LinqToDB Exception cannot be converted to SQL

1 Answers1