I need to convert this 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;
to EF Core 3.0 linq. The main problem is left joins. At first I've tried to do it like this:
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);
}
After running this query I got error:
... by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.
After that I discovered from this question that you can't make group by queries evaluated on client anymore.
After more searching I've discovered another solution. So i've re-written my method like this:
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);
}
But now i get another error:
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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().
After some more testing i know for sure that checkups.Max(c => c.Date)} and HasPendingMessages = messages.Any() is the source of this error. How to fix this query? You can suggest completely different solution or approach. The main idea is to bring logic from sql to c#.
PS: Sorry for long question.
