Where expression on GroupJoin/SelectMany could not be translated · Issue #12901 · dotnet/efcore

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

任性的菠菜 · 2021尼日利亚足球世界排名：第32，积分1 ...· 3 月前 ·

文武双全的热带鱼 · 学生会主席及副主席职责-生物工程与技术学院· 4 月前 ·

俊秀的凳子 · Vue3 + TS ...· 1 年前 ·

坐怀不乱的绿茶 · Kafka的生产者和消费者代码解析 - ...· 1 年前 ·

月球上的牛肉面 · 有哪些好用的开源的表单系统？ - 知乎· 1 年前 ·

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement . We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

When executing following code I got warning message "The LINQ expression 'where (?[s]?.SecondDecimalPayload? > 100,0)' could not be translated and will be evaluated locally":

var query = context.FirstEntities
    .GroupJoin(
        context.FirstEntities
            .SelectMany(x => x.SecondEntities, (x, s) => new {FirstId = x.Id, SecondDecimalPayload = s.DecimalPayload}),
        x => x.Id, x => x.FirstId, (f, ss) => new {First = f, NewSeconds = ss})
    .SelectMany(x => x.NewSeconds.DefaultIfEmpty(), (x, s) => new
        FirstDecimalPayload = x.First.DecimalPayload,
        SecondDecimalPayload = s != null ? (decimal?) s.SecondDecimalPayload : null
    });
var list = query.Where(x => x.SecondDecimalPayload > 100.0m).ToList();

EFCore generates good SQL:

SELECT [t].[FirstId], [t].[SecondDecimalPayload], [x].[DecimalPayload] AS [FirstDecimalPayload]
FROM [FirstEntities] AS [x]
LEFT JOIN (
    SELECT [x0].[Id] AS [FirstId], [x.SecondEntities].[DecimalPayload] AS [SecondDecimalPayload]
    FROM [FirstEntities] AS [x0]
    INNER JOIN [SecondEntities] AS [x.SecondEntities] ON [x0].[Id] = [x.SecondEntities].[FirstEntityId]
) AS [t] ON [x].[Id] = [t].[FirstId]

but cannot add simple WHERE clause.

Steps to reproduce

Extract attached archive , compile and execute project.

Further technical details

EF Core version: 2.1.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.7.6

~~Where expression on GroupJoin could not be translated~~ Where expression on GroupJoin/SelectMany could not be translated

Aug 6, 2018

This is a duplicate of #8094 - we can't bind to properties hidden behind anonymous projection

Workaround can be to apply the filter earlier, like so:

                var query = ctx.FirstEntities
                    .GroupJoin(
                        ctx.FirstEntities.SelectMany(x => x.SecondEntities.Where(ss => ss.DecimalPayload > 100.0m), (x, s) => new { FirstId = x.Id, SecondDecimalPayload = s.DecimalPayload }),
                        x => x.Id,
                        x => x.FirstId,
                        (f, ss) => new { First = f, NewSeconds = ss })
                    .SelectMany(
                        x => x.NewSeconds.DefaultIfEmpty(),
                        (x, s) => new
                            FirstDecimalPayload = x.First.DecimalPayload,
                            SecondDecimalPayload = s != null ? (decimal?)s.SecondDecimalPayload : null
                        });
                var list = query.ToList();

which should produce the following sql:

SELECT [t].[FirstId], [t].[SecondDecimalPayload], [x].[DecimalPayload] AS [FirstDecimalPayload]
FROM [FirstEntities] AS [x]
LEFT JOIN (
    SELECT [x0].[Id] AS [FirstId], [x.SecondEntities].[DecimalPayload] AS [SecondDecimalPayload]
    FROM [FirstEntities] AS [x0]
    INNER JOIN [SecondEntities] AS [x.SecondEntities] ON [x0].[Id] = [x.SecondEntities].[FirstEntityId]
    WHERE [x.SecondEntities].[DecimalPayload] > 100.0
) AS [t] ON [x].[Id] = [t].[FirstId]

This is not a general purpose workaround, but should be ok for this case, because null values from the LOJ would have been filtered out by the final Where clause.