如何将带有左联接的SQL转换为EF核心3 LINQ

Question

我需要转换这个SQL：

SELECT
  sb.Id AS id,
  sb.name as name,
  sb.age AS age,
  sb.BirthDay as BirthDay,
  su.UnitId AS unitId,
  sb.WorkedInDodo AS workedInDodo,
  sb.RelativesWorkedInDodo AS relativesWorkedInDodo,
  sb.PhoneNumber AS phoneNumber,
  sb.Email AS email,
  sb.DeliveryCheck AS deliveryCheck,
  sb.RestaurantCheck AS restaurantCheck,
  sb.StandardsRatingCheck as standardsRatingCheck,
  sb.SocialNetwork AS socialNetwork,
  sb.SocialNetworkId AS socialNetworkId,
  sb.socialNetworkScreenName AS socialNetworkScreenName,
  sb.SourceOfInformationAboutSecretBuyers AS SourceOfInformationAboutSecretBuyers,
  sb.suitable AS suitable,
  sb.SocialNetworkMessagingEnable as socialNetworkMessagingEnable,
  sb.IsInGroup as isInGroup,
  sb.IsKeyWord as IsKeyWord,
  sb.IsBanned as IsBanned,
  sb.IsFraud as IsFraud,
  sb.LastUnitsUpdateUtcDate as lastUnitsUpdateUtcDate,
  sb.Comments as comments,
  sb.MessagingApproval as messagingApproval,
  sb.ProcessDataApproval as processDataApproval,
  sb.CreatedDateTimeUtc AS createdDateTimeUtc,
  sb.ModifiedDateTineUTC AS modifiedDateTime,
  sb.Country AS country,
  sb.PhoneNumberEditedUtc AS PhoneNumberEditedUtc
  FROM (select * from
    (SELECT  s.*,
    IF(s.LastSearchMessageDateUtc is null, 0, 1) as searched,
    IF(cc.Id is null, 0, 1) as onCheck
    FROM secretbuyers s
    JOIN secretbuyerunits sbu ON sbu.SecretBuyerId = s.Id
    LEFT JOIN outgoingMessageQueue mq ON mq.Type = 1 AND mq.VkUserId = s.SocialNetworkId and mq.State in (1,2)
    LEFT JOIN checkcandidates cc ON cc.SecretBuyerId = s.id AND cc.State IN (1,4) AND cc.Date >= @p_checkBeginDateTime AND cc.Date <= @p_checkEndDateTime
    WHERE sbu.UnitId = @p_unitId
    AND (s.LastSearchMessageDateUtc is null OR s.LastSearchMessageDateUtc < @p_lastSearchMessageDateUtcLimit)
    AND ((@p_deliveryCheck = true AND s.DeliveryCheck = true) OR (@p_restaurantCheck = true AND s.RestaurantCheck = true) OR (@p_standardsRatingCheck = true AND s.StandardsRatingCheck = true))
    AND s.Suitable = true
    AND s.IsRemove = false
    AND mq.Id is null
    AND s.IsBanned = false
    AND s.IsFraud = false
    AND s.IsAutoSearchable = true
    group by s.Id
    Order By onCheck ASC, searched asc, s.LastSearchMessageDateUtc asc) as temp
    LIMIT @p_count) AS sb
  JOIN secretbuyerunits su ON su.SecretBuyerId = sb.Id;

到EF Core3.0 linq。主要问题是左联接。一开始我试着这样做：

private async Task<IEnumerable<MysteryShopper>> FindMysteryShoppers(
        SearchMysteryShoppersParameters searchParameters, CancellationToken ct)
    {
        var lastSearchEdge = _nowProvider.UtcNow().Date.AddDays(-3);

        bool shouldHaveDeliveryCheckMark = ShouldHaveDeliveryCheckMark(searchParameters.SearchType);
        bool shouldHaveRestaurantCheckMark = ShouldHaveRestaurantCheckMark(searchParameters.SearchType);
        bool shouldHaveStandardsCheckMark = ShouldHaveStandardsCheckMark(searchParameters.SearchType);

        var lastCheckupEdgeDate = searchParameters.CheckupDates.Max().AddDays(-30);

        return await _ratingsContext.SecretBuyers.AsNoTracking().Where(ms => !ms.IsBanned &&
                !ms.IsFraud
                && ms.IsAutoSearchable
                && ms.Suitable
                && !ms.IsRemove
                && ms.SocialNetworkMessagingEnable
                && (ms.LastSearchMessageDateUtc == null
                    || ms.LastSearchMessageDateUtc < lastSearchEdge)
            )
            .Where(ms => !shouldHaveDeliveryCheckMark && ms.DeliveryCheck)
            .Where(ms => !shouldHaveRestaurantCheckMark && ms.RestaurantCheck)
            .Where(ms => !shouldHaveStandardsCheckMark && ms.StandardsRatingCheck)
            .Where(ms => ms.MysteryShopperUnits
                .Any(u => searchParameters.UnitIds.Contains(u.UnitId))
            )
            .GroupJoin(_ratingsContext.Checkups.AsNoTracking().Where(cc => !_cancelledStates.Contains(cc.State)), ms => ms.Id,
                cc => cc.SecretBuyerId, (ms, cc) => new
                {
                    MysteryShopper = ms,
                    LastCheckupDate = cc
                        .Max(c => c.Date)
                }
            )
            .GroupJoin(_ratingsContext.Messages.AsNoTracking().Where(m =>
                    m.Type == OutgoingMessageType.CandidateSearch
                    && (m.State == OutgoingMessageState.Sending || m.State == OutgoingMessageState.Waiting)),
                ms => ms.MysteryShopper.SocialNetworkId,
                m => m.VkUserId,
                (ms, m) =>
                    new {ms.MysteryShopper, ms.LastCheckupDate, HasPendingMessages = m.Any()})
            .Where(ms => !ms.HasPendingMessages)
            .Where(ms => ms.LastCheckupDate < lastCheckupEdgeDate)
            .OrderBy(ms => ms.LastCheckupDate != null)
            .ThenBy(ms => ms.MysteryShopper.LastSearchMessageDateUtc != null)
            .ThenBy(ms => ms.MysteryShopper.LastSearchMessageDateUtc)
            .Select(ms => ms.MysteryShopper)
            .ToArrayAsync(ct);
    }

在运行此查询后，我得到了错误：

...由'NavigationExpandingExpressionVisitor‘失败。这可能表示EF中存在缺陷或限制。有关更多详细信息，请参见https://go.microsoft.com/fwlink/?linkid=2101433。

在此之后，我从这个question中发现，您不能再对客户端上的查询进行分组计算。

经过更多的搜索，我发现了另一个solution。所以我重写了我的方法如下：

        private async Task<IEnumerable<MysteryShopper>> FindMysteryShoppers(
        SearchMysteryShoppersParameters searchParameters, CancellationToken ct)
    {
        var lastSearchEdge = _nowProvider.UtcNow().Date.AddDays(-3);

        bool shouldHaveDeliveryCheckMark = ShouldHaveDeliveryCheckMark(searchParameters.SearchType);
        bool shouldHaveRestaurantCheckMark = ShouldHaveRestaurantCheckMark(searchParameters.SearchType);
        bool shouldHaveStandardsCheckMark = ShouldHaveStandardsCheckMark(searchParameters.SearchType);

        var lastCheckupEdgeDate = searchParameters.CheckupDates.Max().AddDays(-30);

        var mysteryShoppersLeftJoinedWithCheckups = from ms in _ratingsContext.Set<MysteryShopper>()
            where ms.MysteryShopperUnits
                .Any(u => searchParameters.UnitIds.Contains(u.UnitId)
                          && !shouldHaveStandardsCheckMark && ms.StandardsRatingCheck
                          && !shouldHaveRestaurantCheckMark && ms.RestaurantCheck
                          && !shouldHaveDeliveryCheckMark && ms.DeliveryCheck
                          && !ms.IsBanned
                          && !ms.IsFraud
                          && ms.IsAutoSearchable
                          && ms.Suitable
                          && !ms.IsRemove
                          && ms.SocialNetworkMessagingEnable
                          && (ms.LastSearchMessageDateUtc == null || ms.LastSearchMessageDateUtc < lastSearchEdge))
            join cc in _ratingsContext.Set<Checkup>()
                on ms.Id equals cc.SecretBuyerId into checkups
            from cc in checkups.DefaultIfEmpty()
            where !_cancelledStates.Contains(cc.State)
            select new {MysteryShopper = ms, LastCheckupDate = checkups.Max(c => c.Date)};


        var mysteryShoppersLeftJoinedWithCheckupsAndMessages =
            from mysteryShopperWithCheckup in mysteryShoppersLeftJoinedWithCheckups
            join m in _ratingsContext.Set<Message>()
                on mysteryShopperWithCheckup.MysteryShopper.SocialNetworkId equals m.VkUserId into messages
            from m in messages.DefaultIfEmpty()
            where m.Type == OutgoingMessageType.CandidateSearch && (m.State == OutgoingMessageState.Sending ||
                                                                    m.State == OutgoingMessageState.Waiting)
            select new
            {
                mysteryShopperWithCheckup.MysteryShopper,
                mysteryShopperWithCheckup.LastCheckupDate,
                HasPendingMessages = messages.Any()
            };

        var orderedMysteryShoppersWithoutPendingMessage =
                from mysteryShopperWithCheckupAndMessage in mysteryShoppersLeftJoinedWithCheckupsAndMessages
                where !mysteryShopperWithCheckupAndMessage.HasPendingMessages &&
                      mysteryShopperWithCheckupAndMessage.LastCheckupDate < lastCheckupEdgeDate
                orderby mysteryShopperWithCheckupAndMessage.LastCheckupDate != null,
                    mysteryShopperWithCheckupAndMessage.MysteryShopper.LastSearchMessageDateUtc != null,
                    mysteryShopperWithCheckupAndMessage.MysteryShopper.LastSearchMessageDateUtc
                select mysteryShopperWithCheckupAndMessage.MysteryShopper;

        return await orderedMysteryShoppersWithoutPendingMessage.ToArrayAsync(ct);
    }

但现在我又犯了一个错误：

不能翻译。要么用可以翻译的表单重写查询，要么通过插入对AsEnumerable()、AsAsyncEnumerable()、ToList()或ToListAsync()的调用，显式地切换到客户端计算。

经过更多的测试后，我确信checkups.Max(c => c.Date)}和HasPendingMessages = messages.Any()是此错误的来源。如何修复此查询？您可以提出完全不同的解决方案或方法。其主要思想是将逻辑从sql带到c#。

PS:很抱歉提了很长的问题。

Answer 1

添加了导航属性和nugget Z.EntityFramework.Plus (用于IncludeFilter)，并重写了如下查询：

            var query = _ratingsContext.SecretBuyers
            .AsNoTracking()
            .IncludeFilter(ms => ms.Checkups.Where(cc => !_cancelledStates.Contains(cc.State)))
            .IncludeFilter(ms => ms.Messages.Where(m => m.Type == OutgoingMessageType.CandidateSearch &&
                                                        (m.State == OutgoingMessageState.Sending ||
                                                         m.State == OutgoingMessageState.Waiting)))
            .Where(ms => !ms.IsBanned
                         && !ms.IsFraud
                         && ms.IsAutoSearchable
                         && ms.Suitable
                         && !ms.IsRemove
                         && ms.SocialNetworkMessagingEnable
                         && (ms.LastSearchMessageDateUtc == null || ms.LastSearchMessageDateUtc < lastSearchEdge)
            )
            .Where(ms => !shouldHaveDeliveryCheckMark || ms.DeliveryCheck)
            .Where(ms => !shouldHaveRestaurantCheckMark || ms.RestaurantCheck)
            .Where(ms => !shouldHaveStandardsCheckMark || ms.StandardsRatingCheck)
            .Where(ms => ms.MysteryShopperUnits
                .Any(u => searchParameters.UnitIds.Contains(u.UnitId))
            )
            .Where(ms => !ms.Messages.Any())
            .Select(x => new {MysteryShopper = x, LastCheckupDate = x.Checkups.Max(c => c.Date)})
            .Where(ms => ms.LastCheckupDate < lastCheckupEdgeDate)
            .OrderBy(x => x.LastCheckupDate != null)
            .ThenBy(x => x.MysteryShopper.LastSearchMessageDateUtc != null)
            .ThenBy(x => x.MysteryShopper.LastSearchMessageDateUtc)
            .Select(x => x.MysteryShopper)
            .Take(searchParameters.MessagesPerUnit);