Hello, I have run into an issue after upgrading from EFCore 6 to EFCore 7. Previously, the below query was working though the select portion was running client-side, as the string,Join method was unable to be translated.

After upgrading to EFCore 7, it seems that it has been implemented using STRING_AGG. However, we are still using SQL Server 2016 which does not support it (Trying to get us to upgrade but I don't know how soon that may happen).

I'm wondering if there is some way to target EFCore 7 for a specific version of SQL Server (i.e. 2016) and have it fall back to client-side evaluation? I have not seen any method of doing this though so I wanted to see if there are any suggestions or fixes.

I'm hoping to avoid having to modify all of the code where we may be doing this to force client-side evaluation.

This also was not part of the breaking changes when upgrading to EFCore7, so it isn't obvious until runtime as well.

Thank you for any help!

from appraisals in _assessorContext.LienDateAppraisal
            join status in _assessorContext.LienDateAppraisalStatus.Where(x => x.Name == StatusSubmitted) on
                appraisals.Status equals status.Id
            join locks in _assessorContext.LienDateAppraisalLocks on appraisals.Assessment equals locks.Assessment
                into appraisalLocks
            from locks in appraisalLocks.DefaultIfEmpty()
            select new { appraisals, locks } into appraisalLocks
            group appraisalLocks by new { appraisalLocks.appraisals.Assessment } into appraisalLocks
            select new
            {
                appraisalLocks.Key.Assessment,
                Triggers = string.Join(", ", appraisalLocks.Select(x => x.appraisals.Trigger)),
                Locks = appraisalLocks.Select(x => x.locks).First() ?? new LienDateAppraisalLocks(),
                Count = appraisalLocks.Count(),
                Date = appraisalLocks.Min(x => x.appraisals.StatusChangeTDS)
            }

STRING_AGG' is not a recognized built-in function name.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.

EF Core version: 7.0.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0
Operating system: Windows Server 2016
IDE: Visual Studio 2022 17.4.326.54890