.NET 8 & SQL Server 2016 - Contains() throws error

Question

I decided to switch my project from .NET6 to .NE 8. My project database is SQL Server 2016.

At first everything was going well, but I ran into a bug:

var seferIds = tList.expedition.Select(r => r.Key).ToArray();
var sql = (from h in context.Line
           join v in context.Route on h.id equals v.lineId
           join s in context.Expedition on v.id equals s.routeId
           where seferIds.Contains(s.id)
           select new { h, v, s })
          .ToDictionary(r => r.s.id);

In this way, I cannot use Contains() in Linq queries I created with my database tables (AsQueryable).

I did some research and if I define it as

where seferIds.AsQueryable().Contains(s.id)

the error disappears. At the same time, I need to do an empty check in the form of seferIds.Any() in my code.

I looked to see if I would get the same error using SQL Server 2019 as the database and where seferIds.Contains(s.id) works there without any problems and there is no error.

As for my question, is this problem that .net8 is no longer compatible with the old database version? Is there another situation that I missed.

I would like to know if there is a different solution for this error.

Answer 1

Before updating your project to a newer version, be sure to read Breaking changes, your problem is also in this list:

Breaking changes in EF Core 8 (EF8)

The new EF Core 8.0 translation uses the SQL Server OPENJSON function to instead transfer the values as a JSON array. This solves the performance issues inherent in the previous technique; however, the OPENJSON function is unavailable in SQL Server 2014 and below.

solution:

The OPENJSON function was introduced in SQL Server 2016 (13.x); To do this, simply call the new UseCompatibilityLevel method when configuring your context options The 120 argument is the desired SQL Server compatibility level; 120 corresponds to SQL Server 2014 (12.x). When this is done, EF will generate the previous translation.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseSqlServer(@"<CONNECTION STRING>", o => o.UseCompatibilityLevel(120));