I want to execute this LINQ query, but since I've changed a hardcoded 5 to firNr (a local variable) in a where clause, I'm getting the following error:

System.AggregateException: "One or more errors occurred. (The LINQ expression 'DbSet<ArlArtikel>()
    .LeftJoin(
        inner: DbSet<LkoLagerkonto>(), 
        outerKeySelector: arl => arl.ArlArtikelnr, 
        innerKeySelector: lk => lk.LkoArtikelnr, 
        resultSelector: (arl, lk) => new { 
            <>h__TransparentIdentifier0 = new { 
                arl = arl, 
                lkList = lkList
             }, 
            lk = lk
         })
    .LeftJoin(
        inner: DbSet<VlkVaLagerkontodispowerte>(), 
        outerKeySelector: <>h__TransparentIdentifier1 => <>h__TransparentIdentifier1.lk.LkoLagerkontolfdnr, 
        innerKeySelector: dw => dw.VlkLagerkontolfdnr, 
        resultSelector: (<>h__TransparentIdentifier1, dw) => new { 
            <>h__TransparentIdentifier2 = new { 
                <>h__TransparentIdentifier1 = <>h__TransparentIdentifier1, 
                dwList = dwList
             }, 
            dw = dw
         })
    .GroupJoin(
        inner: DbSet<PtpPreistabpo>(), 
        outerKeySelector: <>h__TransparentIdentifier3 => <>h__TransparentIdentifier3.<>h__TransparentIdentifier2.<>h__TransparentIdentifier1.<>h__TransparentIdentifier0.arl.ArlArtikelnr, 
        innerKeySelector: pt => pt.PtpArtikelnr, 
        resultSelector: (<>h__TransparentIdentifier3, ptList) => new { 
            <>h__TransparentIdentifier3 = <>h__TransparentIdentifier3, 
            ptList = ptList
         })' 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.)"

This is my code:

long firNr = 5;

var articles = from arl in context.ArlArtikels
    join lk in context.LkoLagerkontos
        on arl.ArlArtikelnr equals lk.LkoArtikelnr
        into lkList
    from lk in lkList.DefaultIfEmpty()
    join dw in context.VlkVaLagerkontodispowertes
        on lk.LkoLagerkontolfdnr equals dw.VlkLagerkontolfdnr into dwList
    from dw in dwList.DefaultIfEmpty()
    join pt in context.PtpPreistabpos
        on arl.ArlArtikelnr equals pt.PtpArtikelnr into ptList
    from pt in ptList.Where(a => a.PtpArlFirmennr == firNr && a.PtpIstnetto == 0).DefaultIfEmpty() // <--
    where arl.ArlFirmennr == firNr
    group new { arl, lk, dw, pt } by new
        { arl.ArlArtikelnr, lk.LkoArtikelnr, arl.ArlArtikelbez, pt.PtpPreis }
    into articleGroup
    select new ErpArticle() 
    {
        Id = articleGroup.Key.ArlArtikelnr,
        Title = articleGroup.Key.ArlArtikelbez,
        Quantity = (int?)articleGroup.Sum(g => g.dw.VlkVerfLagermenge),
        Price = articleGroup.Key.PtpPreis
    };

var res = await articles.ToListAsync();

I've found out that it works if I declare the variable as const, but I want to be able to change it. Can anybody explain why I'm getting this error and how I can fix it, please?

--- EDIT ---

With the answer of @Svyatoslav Danyliv I were able to write the working code now:

var articles = from arl in context.ArlArtikels
    join lk in context.LkoLagerkontos
        on arl.ArlArtikelnr equals lk.LkoArtikelnr
        into lkList
    from lk in lkList.DefaultIfEmpty()
    join dw in context.VlkVaLagerkontodispowertes
        on lk.LkoLagerkontolfdnr equals dw.VlkLagerkontolfdnr into dwList
    from dw in dwList.DefaultIfEmpty()
        // changes
    join pt in context.PtpPreistabpos
        .Where(pt => pt.PtpArlFirmennr == firNr && pt.PtpIstnetto == 0)
        .DefaultIfEmpty()
        on arl.ArlArtikelnr equals pt.PtpArtikelnr into ptList
    from pt in ptList.DefaultIfEmpty()
    where arl.ArlFirmennr == firNr
    group new { arl, lk, dw, pt } by new { arl.ArlArtikelnr, lk.LkoArtikelnr, arl.ArlArtikelbez, pt.PtpPreis }
    into articleGroup
    select new ErpArticle() {
        Id = articleGroup.Key.ArlArtikelnr,
        Title = articleGroup.Key.ArlArtikelbez,
        Quantity = (int?)articleGroup.Sum(g => g.dw.VlkVerfLagermenge),
        Price = articleGroup.Key.PtpPreis
    };

CodePudding user response：

EF Core sill has limitstion in GroupJoin trnslating. It works only for simple LEFT JOIN, but it fails when query become complex. I have rewritten your problematic part using this technique:

var articles = from arl in context.ArlArtikels
    join lk in context.LkoLagerkontos
        on arl.ArlArtikelnr equals lk.LkoArtikelnr
        into lkList
    from lk in lkList.DefaultIfEmpty()
    join dw in context.VlkVaLagerkontodispowertes
        on lk.LkoLagerkontolfdnr equals dw.VlkLagerkontolfdnr into dwList
    from dw in dwList.DefaultIfEmpty()
    // changes
    from pt in context.PtpPreistabpos
        .Where(pt => arl.ArlArtikelnr == pt.PtpArtikelnr)
        .Where(pt => pt.PtpArlFirmennr == firNr && pt.PtpIstnetto == 0)
        .DefaultIfEmpty() // <--
    where arl.ArlFirmennr == firNr
    group new { arl, lk, dw, pt } by new
        { arl.ArlArtikelnr, lk.LkoArtikelnr, arl.ArlArtikelbez, pt.PtpPreis }
    into articleGroup
    select new ErpArticle() 
    {
        Id = articleGroup.Key.ArlArtikelnr,
        Title = articleGroup.Key.ArlArtikelbez,
        Quantity = (int?)articleGroup.Sum(g => g.dw.VlkVerfLagermenge),
        Price = articleGroup.Key.PtpPreis
    };