-1

I want to have my EfCore query translated into the following SQL query:

select
  c.blablabla
from
  codes c
  left join lookups l on c.codeId = l.entityid and l.languageCode = <variable - language code of current thread> and l.lookuptype = 'CODE'
where
  ..something..
order by
  l.displayname

Note: tables 'codes' and 'lookups' are not connected! 'lookups' contains a lot of different lookup data in different languages!

I am stuck into limitations of EfCore (like 'NavigationExpandingExpressionVisitor' failed). I don't want to make in-memory filtering, it looks silly to me... Am I missing something obvious?

In perspective, I'd like to make universal method to help sort by displayname (or other lookup name) for different kind of entities - not only codes.

Alexander
  • 1,152
  • 1
  • 16
  • 18
  • *"Am I missing something obvious?"* You haven't shown any attempted LINQ query in order to answer the question. – Ivan Stoev Dec 17 '20 at 16:42
  • @IvanStoev I've posted reply to my question - looks like it works: one field is joined and all the rest are moved to 'WHERE' conditions before making LEFT JOIN... – Alexander Dec 17 '20 at 17:07
  • 1
    Ok, but that's just a standard composite key LINQ left join pattern, not sure what was the issue with EF Core which needed SO question. In EF Core it can be simplified by using correlated `SelectMany` + `Where` + `DefaultIfEmpty` , thus eliminating `GroupJoin` (yet another standard LINQ left join pattern), but the result will be the same. – Ivan Stoev Dec 17 '20 at 17:21

1 Answers1

0

Seems like I figured it out. If there's a better approach - please let me know:

    protected override IQueryable<FixCode> SortByDisplayName(IQueryable<FixCode> queryable, string languageCode = null)
    {
        return queryable
            .GroupJoin(
                DbContext.FixCodeValues.Where(x =>
                    x.DomainId == CentralToolConsts.Domains.CENTRAL_TOOLS
                    && x.CodeName == CentralToolsFieldTypes.CODE_ORIGIN
                    && (x.LanguageCode == languageCode || x.LanguageCode == CentralToolsDbLanguageCodes.English)),
                //TODO: this will be a 'selector' parameter
                code => code.CodeOriginId,
                codeOrigin => codeOrigin.StringValue,
                (c, co) => new
                {
                    Code = c,
                    CodeOrigin = co
                }
            )
            .SelectMany(
                x => x.CodeOrigin.DefaultIfEmpty(),
                (x, codeOrigin) => new { Code = x.Code, CodeOrigin = codeOrigin }
            )
            .OrderBy(x => x.CodeOrigin.ShortName)
            .Select(x => x.Code);
    }
Alexander
  • 1,152
  • 1
  • 16
  • 18