EntityFrameworkCore PostgreSQL左加入不工作的地方

Question

我试图使用实体框架核心()执行左联接，但它在join中创建了一个无法工作的子查询。

我创建了一个样本工程来说明这个问题。

这是我的Linq查询，它从一个帐户中选择所有的流，并在该流中选择订阅者。

Linq查询语法

    from account in context.Accounts
    where account.Id == 6
    join steam in context.Streams on account.Id equals steam.AccountId
    join subscription in context.Subscriptions on account.Id equals subscription.TargetAccountId into
        groupJoin
    from subscription in groupJoin.Where(subscription => subscription.Date > steam.StreamStart && subscription.Date < steam.StreamEnd).DefaultIfEmpty()
    select new {account, steam, subscription};

Linq扩展方法语法

    context.Accounts.Where(account => account.Id == 6)
        .Join(context.Streams, outer => outer.Id, inner => inner.AccountId,
        (account, stream) => new { Account = account, Stream = stream })
        .GroupJoin(context.Subscriptions, outer => outer.Account.Id, inner => inner.TargetAccountId,
        (outer, subscriptions) => new
        { Account = outer.Account, Stream = outer.Stream, Subscriptions = subscriptions })
        .SelectMany(x =>
            x.Subscriptions.Where(subscription =>
                    subscription.Date > x.Stream.StreamStart && subscription.Date < x.Stream.StreamEnd)
                .DefaultIfEmpty(),
        (x, subscription) => new { x.Account, x.Stream, Subscription = subscription }).ToList();

这是生成的查询。

    SELECT account."Id", account."Name", stream."Id", stream."AccountId", stream."StreamEnd", stream."StreamStart", stream."Title", t."SourceAccountId", t."TargetAccountId", t."Date"
          FROM "Accounts" AS account
          INNER JOIN "Streams" AS stream ON account."Id" = stream."AccountId"
          LEFT JOIN (
              SELECT "inner"."SourceAccountId", "inner"."TargetAccountId", "inner"."Date"
              FROM "Subscriptions" AS "inner"
              WHERE ("inner"."Date" > stream."StreamStart") AND ("inner"."Date" < stream."StreamEnd")
          ) AS t ON account."Id" = t."TargetAccountId"
          WHERE account."Id" = 6

这将给出以下错误：

Npgsql.PostgresException (0x80004005)：42P01:对表“流”的FROM-子句条目的引用无效

我的Linq查询是错误的还是实体框架中的错误/限制？

Answer 1

绝对是-使用最新的EFCore2.2.6和SqlServer提供程序进行复制，所以它不是特定于Npgsql提供程序。

因为3.0将使用完全重写的查询翻译程序，而且在当前的预览中，许多东西不能正常工作，所以不能说它是否会被修复。

解决方法是将相关过滤器移出join之外(附加null检查以说明DefaultIfEmpty()引入的left outer join )：

var result = context.Accounts
    .Where(account => account.Id == 6)
    .Join(context.Streams, outer => outer.Id, inner => inner.AccountId,
        (account, stream) => new { Account = account, Stream = stream })
    .GroupJoin(context.Subscriptions, outer => outer.Account.Id, inner => inner.TargetAccountId,
        (outer, subscriptions) => new
        { Account = outer.Account, Stream = outer.Stream, Subscriptions = subscriptions })
    .SelectMany(x => x.Subscriptions.DefaultIfEmpty(),
        (x, subscription) => new { x.Account, x.Stream, Subscription = subscription })
    .Where(x => x.Subscription == null || (
        x.Subscription.Date > x.Stream.StreamStart &&
        x.Subscription.Date < x.Stream.StreamEnd))
    .ToList();