Cross apply-like query produces invalid SQL

[Akridian]

Bug

I was trying to implement the folowing query in our service:

DECLARE @twoMonthsAgo DATE = '20201201',
		@housingSGHCId BIGINT = (SELECT TOP (1) Id FROM no.sr$ServiceGroupsHC WHERE Code = 'housing'),
		@now DATE = SYSDATETIMEOFFSET(),
		@ircPapTypeId INT = (SELECT TOP (1) Id FROM hm.FacetItems@get('PersonalAccountProviderType') WHERE code = 'ForRIC'),
		@exposePdStatusId INT = (SELECT TOP (1) Id FROM hm.FacetItems@get('paymentDocumentStatus') WHERE Code = 'expose')

SELECT o.Id, q.ProviderOrganizationId
FROM no.[cmn$Object] o
	JOIN no.[cmn$ObjectType] ot ON ot.Id = COALESCE(o.GisTypeId, o.TypeId)
	JOIN no.[cmn$Structure] st ON st.Id = o.StructureId
	CROSS APPLY (
		SELECT TOP (1) pdi.ProviderOrganizationId
		FROM no.[sr$PersonalAccountProvider] pap
			JOIN fin.PaymentDocument pd ON pd.PersonalAccountProviderId = pap.Id
			JOIN fin.PaymentDocumentIncome pdi ON pdi.PaymentDocumentId = pd.Id
			JOIN rate.Service rs ON pdi.RateServiceId = rs.Id
			JOIN no.[sr$Service] s ON rs.ServiceId = s.Id
			JOIN no.[sr$ServiceGroupsHC] sghc ON sghc.Id = s.GroupId
			LEFT JOIN no.[sr$ServiceGroupsHC] psghc ON psghc.Id = sghc.ParentId
		WHERE pap.Accommodation_ObjectId = o.Id
			AND pap.ClosedDate IS NULL
			AND pap.TypeId = @ircPapTypeId
			AND pd.StatusId = @exposePdStatusId
			AND pd.Period >= @twoMonthsAgo
			AND pdi.IsUsedForGis = 1
			AND (sghc.Id = @housingSGHCId OR psghc.Id = @housingSGHCId OR psghc.ParentId = @housingSGHCId)
		GROUP BY pdi.ProviderOrganizationId
		ORDER BY COUNT(*) DESC
	) q
WHERE o.IsDeleted = 0
	AND COALESCE(o.DateStart, '19000101') <= @now
	AND COALESCE(O.DateEnd, '99991231') > @now
	AND ot.IsLiving = 1
	AND st.Latitude IS NOT NULL
	AND st.Longitude IS NOT NULL

Here is the code:

var now = _dateTimeService.ClientNow.Date; //current date in client timezone

var houseQuery =
     from o in db.cmn_Object
     join ot in db.cmn_ObjectType on o.GisTypeId ?? o.TypeId equals ot.Id
     join s in db.cmn_Structure on o.StructureId equals s.Id
     where !o.IsDeleted
         && (o.DateStart == null || o.DateStart <= now)
         && (o.DateEnd == null || o.DateEnd > now)
         && ot.IsLiving
         && s.Latitude.HasValue
         && s.Longitude.HasValue
     select new
     {
         o.Id,
         AddressId = s.AdrId,
         Type = ot.Name,
         Latitude = s.Latitude.Value,
         Longitude = s.Longitude.Value
     };

var ircPapTypeId = db.FacetItemsGet("PersonalAccountProviderType").Where(i => i.Code == "ForRIC").Select(i => i.Id).FirstOrDefault();
var exposePdStatusId = db.FacetItemsGet("paymentDocumentStatus").Where(i => i.Code == "expose").Select(i => i.Id).FirstOrDefault();

var twoMonthsAgo = new DateTime(now.Year, now.Month, 1).AddMonths(-2);
var housingSGHCId = db.sr_ServiceGroupsHC.Where(i => i.Code == "housing").Select(i => i.Id).FirstOrDefault();

var ircHouseAndOrgs =
    (
        from h in houseQuery
        from q in (
            from pap in db.sr_PersonalAccountProvider
            join pd in db.fin_PaymentDocument on pap.Id equals pd.PersonalAccountProviderId
            join pdi in db.fin_PaymentDocumentIncome on pd.Id equals pdi.PaymentDocumentId
            join rs in db.rate_Service on pdi.RateServiceId equals rs.Id
            join s in db.sr_Service on rs.ServiceId equals s.Id
            join sghc in db.sr_ServiceGroupsHC on s.GroupId equals sghc.Id
            join psghc in db.sr_ServiceGroupsHC on sghc.ParentId equals psghc.Id into psghcGrp
            from psghc in psghcGrp.DefaultIfEmpty()
            where pap.Accommodation_ObjectId == h.Id
                && pap.TypeId == ircPapTypeId
                && pap.ClosedDate == null
                && pd.StatusId == exposePdStatusId
                && pd.Period >= twoMonthsAgo
                && pdi.IsUsedForGis
                && (sghc.Id == housingSGHCId || psghc.Id == housingSGHCId || psghc.ParentId == housingSGHCId)
            group 1 by new { pdi.ProviderOrganizationId } into grp
            orderby grp.Count() descending
            select grp.Key.ProviderOrganizationId
        ).Take(1)
        select new
        {
            ObjectId = h.Id,
            OrganizationId = q
        }
    ).ToDictionary(i => i.ObjectId, i => i.OrganizationId);

And this query produces invalid SQL:

SELECT [c].[Id] AS [ObjectId], [t0].[ProviderOrganizationId] AS [OrganizationId]
FROM [no].[cmn$Object] AS [c]
INNER JOIN [no].[cmn$ObjectType] AS [c0] ON COALESCE([c].[GisTypeId], [c].[TypeId]) = [c0].[Id]
INNER JOIN [no].[cmn$Structure] AS [c1] ON [c].[StructureId] = [c1].[Id]
INNER JOIN (
    SELECT [t].[ProviderOrganizationId], [t].[Accommodation_ObjectId]
    FROM (
        SELECT [p0].[ProviderOrganizationId], [s].[Accommodation_ObjectId], ROW_NUMBER() OVER(PARTITION BY [s].[Accommodation_ObjectId] ORDER BY COUNT(*) DESC) AS [row]
        FROM [no].[sr$PersonalAccountProvider] AS [s]
        INNER JOIN [fin].[PaymentDocument] AS [p] ON [s].[Id] = [p].[PersonalAccountProviderId]
        INNER JOIN [fin].[PaymentDocumentIncome] AS [p0] ON [p].[Id] = [p0].[PaymentDocumentId]
        INNER JOIN [rate].[Service] AS [s0] ON [p0].[RateServiceId] = [s0].[Id]
        INNER JOIN [no].[sr$Service] AS [s1] ON [s0].[ServiceId] = [s1].[Id]
        INNER JOIN [no].[sr$ServiceGroupsHC] AS [s2] ON [s1].[GroupId] = [s2].[Id]
        LEFT JOIN [no].[sr$ServiceGroupsHC] AS [s3] ON [s2].[ParentId] = [s3].[Id]
        WHERE ((((([s].[TypeId] = @__ircPapTypeId_1) AND [s].[ClosedDate] IS NULL) AND ([p].[StatusId] = @__exposePdStatusId_2)) AND ([p].[Period] >= @__twoMonthsAgo_3)) AND ([p0].[IsUsedForGis] = CAST(1 AS bit))) AND ((([s2].[Id] = @__housingSGHCId_4) OR ([s3].[Id] = @__housingSGHCId_4)) OR ([s3].[ParentId] = @__housingSGHCId_5))
        GROUP BY [p0].[ProviderOrganizationId]
    ) AS [t]
    WHERE [t].[row] <= 1
) AS [t0] ON [c].[Id] = [t0].[Accommodation_ObjectId]
WHERE ((((([c].[IsDeleted] <> CAST(1 AS bit)) AND ([c].[DateStart] IS NULL OR ([c].[DateStart] <= @__now_0))) AND ([c].[DateEnd] IS NULL OR ([c].[DateEnd] > @__now_0))) AND ([c0].[IsLiving] = CAST(1 AS bit))) AND [c1].[Latitude] IS NOT NULL) AND [c1].[Longitude] IS NOT NULL

Which failed with exception:

Microsoft.Data.SqlClient.SqlException (0x80131904): Column 'no.sr$PersonalAccountProvider.Accommodation_ObjectId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()

Join with ROW_NUMBER hint does not work with GROUP BY. Expected CROSS APPLY query. Possibly related to this issue.

Workaround

As workaround I tried to use subquery:

var ircHouseAndOrgs =
    (
        from d in (
            from h in houseQuery
            select new
            {
                ObjectId = h.Id,
                OrganizationId = (
                    from pap in db.sr_PersonalAccountProvider
                    join pd in db.fin_PaymentDocument on pap.Id equals pd.PersonalAccountProviderId
                    join pdi in db.fin_PaymentDocumentIncome on pd.Id equals pdi.PaymentDocumentId
                    join rs in db.rate_Service on pdi.RateServiceId equals rs.Id
                    join s in db.sr_Service on rs.ServiceId equals s.Id
                    join sghc in db.sr_ServiceGroupsHC on s.GroupId equals sghc.Id
                    join psghc in db.sr_ServiceGroupsHC on sghc.ParentId equals psghc.Id into psghcGrp
                    from psghc in psghcGrp.DefaultIfEmpty()
                    where pap.Accommodation_ObjectId == h.Id
                        && pap.TypeId == ircPapTypeId
                        && pap.ClosedDate == null
                        && pd.StatusId == exposePdStatusId
                        && pd.Period >= twoMonthsAgo
                        && pdi.IsUsedForGis
                        && (sghc.Id == housingSGHCId || psghc.Id == housingSGHCId || psghc.ParentId == housingSGHCId)
                    group 1 by new { pdi.ProviderOrganizationId } into grp
                    orderby grp.Count() descending
                    select (int?)grp.Key.ProviderOrganizationId
                ).FirstOrDefault()
            }
        )
        where d.OrganizationId != null
        select new
        {
            d.ObjectId,
            OrganizationId = d.OrganizationId.Value
        }
    ).ToDictionary(i => i.ObjectId, i => i.OrganizationId);

This produce valid SQL:

SELECT [c].[Id] AS [ObjectId], (
    SELECT TOP(1) [p0].[ProviderOrganizationId]
    FROM [no].[sr$PersonalAccountProvider] AS [s]
    INNER JOIN [fin].[PaymentDocument] AS [p] ON [s].[Id] = [p].[PersonalAccountProviderId]
    INNER JOIN [fin].[PaymentDocumentIncome] AS [p0] ON [p].[Id] = [p0].[PaymentDocumentId]
    INNER JOIN [rate].[Service] AS [s0] ON [p0].[RateServiceId] = [s0].[Id]
    INNER JOIN [no].[sr$Service] AS [s1] ON [s0].[ServiceId] = [s1].[Id]
    INNER JOIN [no].[sr$ServiceGroupsHC] AS [s2] ON [s1].[GroupId] = [s2].[Id]
    LEFT JOIN [no].[sr$ServiceGroupsHC] AS [s3] ON [s2].[ParentId] = [s3].[Id]
    WHERE (((((([s].[Accommodation_ObjectId] = [c].[Id]) AND ([s].[TypeId] = @__ircPapTypeId_1)) AND [s].[ClosedDate] IS NULL) AND ([p].[StatusId] = @__exposePdStatusId_2)) AND ([p].[Period] >= @__twoMonthsAgo_3)) AND ([p0].[IsUsedForGis] = CAST(1 AS bit))) AND ((([s2].[Id] = @__housingSGHCId_4) OR ([s3].[Id] = @__housingSGHCId_4)) OR ([s3].[ParentId] = @__housingSGHCId_5))
    GROUP BY [p0].[ProviderOrganizationId]
    ORDER BY COUNT(*) DESC) AS [OrganizationId]
FROM [no].[cmn$Object] AS [c]
INNER JOIN [no].[cmn$ObjectType] AS [c0] ON COALESCE([c].[GisTypeId], [c].[TypeId]) = [c0].[Id]
INNER JOIN [no].[cmn$Structure] AS [c1] ON [c].[StructureId] = [c1].[Id]
WHERE (((((([c].[IsDeleted] <> CAST(1 AS bit)) AND ([c].[DateStart] IS NULL OR ([c].[DateStart] <= @__now_0))) AND ([c].[DateEnd] IS NULL OR ([c].[DateEnd] > @__now_0))) AND ([c0].[IsLiving] = CAST(1 AS bit))) AND [c1].[Latitude] IS NOT NULL) AND [c1].[Longitude] IS NOT NULL) AND (
    SELECT TOP(1) [p2].[ProviderOrganizationId]
    FROM [no].[sr$PersonalAccountProvider] AS [s4]
    INNER JOIN [fin].[PaymentDocument] AS [p1] ON [s4].[Id] = [p1].[PersonalAccountProviderId]
    INNER JOIN [fin].[PaymentDocumentIncome] AS [p2] ON [p1].[Id] = [p2].[PaymentDocumentId]
    INNER JOIN [rate].[Service] AS [s5] ON [p2].[RateServiceId] = [s5].[Id]
    INNER JOIN [no].[sr$Service] AS [s6] ON [s5].[ServiceId] = [s6].[Id]
    INNER JOIN [no].[sr$ServiceGroupsHC] AS [s7] ON [s6].[GroupId] = [s7].[Id]
    LEFT JOIN [no].[sr$ServiceGroupsHC] AS [s8] ON [s7].[ParentId] = [s8].[Id]
    WHERE (((((([s4].[Accommodation_ObjectId] = [c].[Id]) AND ([s4].[TypeId] = @__ircPapTypeId_1)) AND [s4].[ClosedDate] IS NULL) AND ([p1].[StatusId] = @__exposePdStatusId_2)) AND ([p1].[Period] >= @__twoMonthsAgo_3)) AND ([p2].[IsUsedForGis] = CAST(1 AS bit))) AND ((([s7].[Id] = @__housingSGHCId_4) OR ([s8].[Id] = @__housingSGHCId_4)) OR ([s8].[ParentId] = @__housingSGHCId_5))
    GROUP BY [p2].[ProviderOrganizationId]
    ORDER BY COUNT(*) DESC) IS NOT NULL

But subquery is presented in query twice and it has very high calculation cost.

Expected reusage of scalar value like:

SELECT q.ObjectId, q.OrganizationId
FROM (
	SELECT [c].[Id] AS [ObjectId], (
			SELECT TOP(1) [p0].[ProviderOrganizationId]
			FROM [no].[sr$PersonalAccountProvider] AS [s]
			INNER JOIN [fin].[PaymentDocument] AS [p] ON [s].[Id] = [p].[PersonalAccountProviderId]
			INNER JOIN [fin].[PaymentDocumentIncome] AS [p0] ON [p].[Id] = [p0].[PaymentDocumentId]
			INNER JOIN [rate].[Service] AS [s0] ON [p0].[RateServiceId] = [s0].[Id]
			INNER JOIN [no].[sr$Service] AS [s1] ON [s0].[ServiceId] = [s1].[Id]
			INNER JOIN [no].[sr$ServiceGroupsHC] AS [s2] ON [s1].[GroupId] = [s2].[Id]
			LEFT JOIN [no].[sr$ServiceGroupsHC] AS [s3] ON [s2].[ParentId] = [s3].[Id]
			WHERE (((((([s].[Accommodation_ObjectId] = [c].[Id]) AND ([s].[TypeId] = @ircPapTypeId)) AND [s].[ClosedDate] IS NULL) AND ([p].[StatusId] = @exposePdStatusId)) AND ([p].[Period] >= @twoMonthsAgo)) AND ([p0].[IsUsedForGis] = CAST(1 AS bit))) AND ((([s2].[Id] = @housingSGHCId) OR ([s3].[Id] = @housingSGHCId)) OR ([s3].[ParentId] = @housingSGHCId))
			GROUP BY [p0].[ProviderOrganizationId]
			ORDER BY COUNT(*) DESC
		) AS [OrganizationId]
	FROM [no].[cmn$Object] AS [c]
	INNER JOIN [no].[cmn$ObjectType] AS [c0] ON COALESCE([c].[GisTypeId], [c].[TypeId]) = [c0].[Id]
	INNER JOIN [no].[cmn$Structure] AS [c1] ON [c].[StructureId] = [c1].[Id]
	WHERE (((((([c].[IsDeleted] <> CAST(1 AS bit)) AND ([c].[DateStart] IS NULL OR ([c].[DateStart] <= @now))) AND ([c].[DateEnd] IS NULL OR ([c].[DateEnd] > @now))) AND ([c0].[IsLiving] = CAST(1 AS bit))) AND [c1].[Latitude] IS NOT NULL) AND [c1].[Longitude] IS NOT NULL)
) q
WHERE q.OrganizationId IS NOT NULL

I end up with next code:

var ircHouseAndOrgs =
    (
        from h in houseQuery
        select new
        {
            ObjectId = h.Id,
            OrganizationId = (
                from pap in db.sr_PersonalAccountProvider
                join pd in db.fin_PaymentDocument on pap.Id equals pd.PersonalAccountProviderId
                join pdi in db.fin_PaymentDocumentIncome on pd.Id equals pdi.PaymentDocumentId
                join rs in db.rate_Service on pdi.RateServiceId equals rs.Id
                join s in db.sr_Service on rs.ServiceId equals s.Id
                join sghc in db.sr_ServiceGroupsHC on s.GroupId equals sghc.Id
                join psghc in db.sr_ServiceGroupsHC on sghc.ParentId equals psghc.Id into psghcGrp
                from psghc in psghcGrp.DefaultIfEmpty()
                where pap.Accommodation_ObjectId == h.Id
                    && pap.TypeId == ircPapTypeId
                    && pap.ClosedDate == null
                    && pd.StatusId == exposePdStatusId
                    && pd.Period >= twoMonthsAgo
                    && pdi.IsUsedForGis
                    && (sghc.Id == housingSGHCId || psghc.Id == housingSGHCId || psghc.ParentId == housingSGHCId)
                group 1 by new { pdi.ProviderOrganizationId } into grp
                orderby grp.Count() descending
                select (int?)grp.Key.ProviderOrganizationId
            ).FirstOrDefault()
        }
    ).ToList().Where(i => i.OrganizationId.HasValue).ToDictionary(i => i.ObjectId, i => i.OrganizationId.Value);

Which uses following SQL:

SELECT [c].[Id] AS [ObjectId], (
    SELECT TOP(1) [p0].[ProviderOrganizationId]
    FROM [no].[sr$PersonalAccountProvider] AS [s]
    INNER JOIN [fin].[PaymentDocument] AS [p] ON [s].[Id] = [p].[PersonalAccountProviderId]
    INNER JOIN [fin].[PaymentDocumentIncome] AS [p0] ON [p].[Id] = [p0].[PaymentDocumentId]
    INNER JOIN [rate].[Service] AS [s0] ON [p0].[RateServiceId] = [s0].[Id]
    INNER JOIN [no].[sr$Service] AS [s1] ON [s0].[ServiceId] = [s1].[Id]
    INNER JOIN [no].[sr$ServiceGroupsHC] AS [s2] ON [s1].[GroupId] = [s2].[Id]
    LEFT JOIN [no].[sr$ServiceGroupsHC] AS [s3] ON [s2].[ParentId] = [s3].[Id]
    WHERE (((((([s].[Accommodation_ObjectId] = [c].[Id]) AND ([s].[TypeId] = @__ircPapTypeId_1)) AND [s].[ClosedDate] IS NULL) AND ([p].[StatusId] = @__exposePdStatusId_2)) AND ([p].[Period] >= @__twoMonthsAgo_3)) AND ([p0].[IsUsedForGis] = CAST(1 AS bit))) AND ((([s2].[Id] = @__housingSGHCId_4) OR ([s3].[Id] = @__housingSGHCId_4)) OR ([s3].[ParentId] = @__housingSGHCId_5))
    GROUP BY [p0].[ProviderOrganizationId]
    ORDER BY COUNT(*) DESC) AS [OrganizationId]
FROM [no].[cmn$Object] AS [c]
INNER JOIN [no].[cmn$ObjectType] AS [c0] ON COALESCE([c].[GisTypeId], [c].[TypeId]) = [c0].[Id]
INNER JOIN [no].[cmn$Structure] AS [c1] ON [c].[StructureId] = [c1].[Id]
WHERE ((((([c].[IsDeleted] <> CAST(1 AS bit)) AND ([c].[DateStart] IS NULL OR ([c].[DateStart] <= @__now_0))) AND ([c].[DateEnd] IS NULL OR ([c].[DateEnd] > @__now_0))) AND ([c0].[IsLiving] = CAST(1 AS bit))) AND [c1].[Latitude] IS NOT NULL) AND [c1].[Longitude] IS NOT NULL

And filters data on client side. That leads to useless DB-Service traffic.

Provider and version information

EF Core version: 3.1.4
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1
Operating system: Windows 10
IDE: Microsoft Visual Studio Enterprise 2019 16.8.4

[anranruye]

If you:

1. have an already written sql query
2. get into trouble to map the sql query to a linq query
3. require high performance
4. don't need to switch between different database providers

Using raw sql command is always the best fallback.

For this issue, I guess the ef core team will re-design the area around GroupBy(ef core 5 even doesn't support 'GroupBy') and will spend much time. So I strongly recommend to use raw sql instead at this moment.

If you don't want to hardcode your sql text in your C# code or you need to switch between different database providers, you can create a database view or stored procedure.

To map raw sql to any .NET type rather than predefined entity types, see #22742
#1862 (comment)

[Akridian]

Thank you for suggestion, @anranruye. But that is not my case.

We have too much entities in our database (1000+) and trying to avoid creating new ones without real need (such as temp tables, update queries and so on). Project startup time grows non-linearly with the growth of context entities count.

And of course it's realy hard to support raw SQL in project.

I want to believe that EF Core 6.0 will bring back complex queries I was able to create by means of EF6.

[roji]

ef core 5 even doesn't support 'GroupBy'

That's inaccurate - EF Core 5 supports many GroupBy patterns, but some patterns are indeed unsupported. Improving this is part of the EF Core 6 plan (we aim to get as close to query feature parity with the non-core EF). Some more information on what's supported can be found in the complex query operators docs.

@smitpatel will probably comment on the specific issue.

[anranruye]

@Akridian Unfortunately, ef core 6 will be released almost end of the year.

How about using another orm for those linq queries which are not supported by ef core?(However, I can't give advise for the choice of the orm tool)

[Akridian]

@anranruye don't worry.

My current workaround is not the best one but takes appropriate time/resources for our tasks for now.

If data size grows we will consider usage of stored procedure.

[smitpatel]

I believe that the core issue for invalid SQL is fixed in EF Core 5.0. We stopped adding additional columns used for correlation in Select for Group by because it is invalid. Though it may run into issues of finding a way to correlated outer & inner. Since you want CROSS APPLY in your query, consider using SelectMany operator rather than just Select to be very specific and EF 5.0 may be able to handle that. We will need your model classes to write a working query with desired SQL.

[Akridian]

@smitpatel I use such syntax because it is sql-like and provided as example in doc.
I tried SelectMany solution from this comment but it failed to compile. I think it requires EF Core 5.0.
Here is our model classes.zip. I believe I cut all required classes.

[smitpatel]

Duplicate of #22892