.Contains() on a simple List<T> within a LINQ query Where clause results in Microsoft.Data.SqlClient.SqlException: 'Incorrect syntax near '$'.'

[MikeYeager]

Include your code

The following code run against any table and any Guid column will cause the exception. We use Guids for Ids in our database, but this exception seems to occur with int PKs also.

 var ids = new List<Guid> { Guid.Parse("BE2A84A6-80EB-4846-B465-C226E28A676E") };

 var result = context.MyTable
     .Where(_ => Ids.Contains(_.Id))
     .ToList();

Include verbose output

I used the following logging options. Let me know if you want something different.

  optionsBuilder.LogTo(Console.WriteLine, new[] { DbLoggerCategory.Database.Command.Name }, LogLevel.Trace);

...
dbug: 7/20/2023 12:08:06.763 RelationalEventId.CommandCreating[20103] (Microsoft.EntityFrameworkCore.Database.Command)
      Creating DbCommand for 'ExecuteReader'.
dbug: 7/20/2023 12:08:06.763 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command)
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 7/20/2023 12:08:06.777 RelationalEventId.CommandInitialized[20106] (Microsoft.EntityFrameworkCore.Database.Command)
      Initialized DbCommand for 'ExecuteReader' (13ms).
dbug: 7/20/2023 12:08:06.779 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[@__8__locals1_userToken_UserId_0='?' (DbType = Guid), @__ids_1='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SELECT [e].[PK_EmployeeTfsTokens], [e].[FK_Employee], [e].[FK_TfsServer], [e].[Token], [e].[TokenExpiration]
      FROM [EmployeeTfsTokens] AS [e]
      WHERE [e].[FK_Employee] = @__8__locals1_userToken_UserId_0 AND [e].[FK_TfsServer] IN (
          SELECT [i].[value]
          FROM OPENJSON(@__ids_1) WITH ([value] uniqueidentifier '$') AS [i]
      )
fail: 7/20/2023 12:08:06.843 RelationalEventId.CommandError[20102] (Microsoft.EntityFrameworkCore.Database.Command)
      Failed executing DbCommand (60ms) [Parameters=[@__8__locals1_userToken_UserId_0='?' (DbType = Guid), @__ids_1='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SELECT [e].[PK_EmployeeTfsTokens], [e].[FK_Employee], [e].[FK_TfsServer], [e].[Token], [e].[TokenExpiration]
      FROM [EmployeeTfsTokens] AS [e]
      WHERE [e].[FK_Employee] = @__8__locals1_userToken_UserId_0 AND [e].[FK_TfsServer] IN (
          SELECT [i].[value]
          FROM OPENJSON(@__ids_1) WITH ([value] uniqueidentifier '$') AS [i]
      )

Include stack traces

   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 Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ClockWork.Services.Implementation.ClockWorkService.GetProjects(GetProjectsRequest request) in C:\TFS\Repos\ClockWork.Services\ClockWork.Services.Implementation\ClockWorkService.cs:line 565

Include provider and version information

EF Core version: 8.0.0-preview.6.23329.4
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: NET 7.0.9
Operating system: Windows 11 Pro 10.022621 Build 22621
IDE: Visual Studio 2022 17.6.5

[roji]

This doesn't repro for me with preview6. Can you please tweak the code below to make it fail, or provide a similar minimal, runnable code sample?

await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

var ids = new List<Guid> { Guid.Parse("BE2A84A6-80EB-4846-B465-C226E28A676E") };

_ = context.MyTable
    .Where(m => ids.Contains(m.Id))
    .ToList();

public class BlogContext : DbContext
{
    public DbSet<MyTable> MyTable { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class MyTable
{
    public Guid Id { get; set; }
    public string? Name { get; set; }
}

[roji]

One more thing - which version of SQL Server are you running against?

[MikeYeager]

I’m running against Azure SQL. Setting up an Azure SQL DB with this minimal table that we can both connect to...

[ErikEJ]

@MikeYeager Just:

SELECT @@VERSION

[MikeYeager]

Microsoft SQL Azure (RTM) - 12.0.2000.8 Jun 1 2023 13:36:49 Copyright (C) 2022 Microsoft Corporation

[MikeYeager]

I think we found the culprit. The exception happened against our database, but not the new one I created to test with. Our database has been around a long time and was originally on-premises. Even though it's now running in Azure, its Compatibility Level was set to SQL Server 2008 (100). Increasing the Compatibility Level to SQL Server 2014 (120) or higher fixes the problem. This has the same effect as
optionsBuilder.UseSqlServer(connectionString, o => o.UseCompatibilityLevel(120));
Not particularly easy to troubleshoot, but solved :-). Thank you.

I wonder if it would make sense to build in a Compatibility level check and a targeted exception message?

[ErikEJ]

Yeah:

SELECT compatibility_level FROM sys.databases WHERE name = 'WideWorldImporters';

[ErikEJ]

I think it must be 130 though

[roji]

OK, that all makes sense now.

I wonder if it would make sense to build in a Compatibility level check and a targeted exception message?

Unfortunately, by design EF doesn't know the actual version/compat level of your database at the point where you execute a query (if it did, we wouldn't require you to manually specify the compat level with UseCompatibilityLevel). I know this is problematic for discoverability; we should at least add a breaking change note (opened dotnet/EntityFramework.Docs#4423).

[vadimkhm]

Hi all, I will raise an issue related to the following query.
Our database Compatibility level is set to 150.

We have this "fancy" query, which normally translates to SQL in EF 7:

var statuses = new[] { StatusEnum.Activated, StatusEnum.PendingApproval, StatusEnum.Initiated };
var hasVendor = await repo.AnyAsync(mvsc => mvsc.MemberId == memberId && mvsc.IsActive && 	
                                            statuses.Contains(mvsc.Flows.OrderByDescending(mvscf => mvscf.CreatedDate)
                                                                        .Select(mvscf => mvscf.Status)																		 
                                                                        .First()));

After updating to EF 8, we received the following exception:

The LINQ expression '__statuses_1
    .Contains(MaterializeCollectionNavigation(
        Navigation: MemberVendorServiceCategory.Flows,
        subquery: DbSet<MemberVendorServiceCategoryFlow>()
            .Where(i => EF.Property<int?>(StructuralTypeShaperExpression: 
                BE.Domain.VendorModels.MemberVendorServiceCategory
                ValueBufferExpression: 
                    ProjectionBindingExpression: EmptyProjectionMember
                IsNullable: False
            , "Id") != null && object.Equals(
                objA: (object)EF.Property<int?>(StructuralTypeShaperExpression: 
                    BE.Domain.VendorModels.MemberVendorServiceCategory
                    ValueBufferExpression: 
                        ProjectionBindingExpression: EmptyProjectionMember
                    IsNullable: False
                , "Id"), 
                objB: (object)EF.Property<int?>(i, "MemberVendorServiceCategoryId"))))
        .AsQueryable()
        .OrderByDescending(mvscf => mvscf.CreatedDate)
        .Select(mvscf => mvscf.Status)
        .First())' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

I hope you will find out what is going on!
Thanks,
Vadim

[roji]

@vadimkhm that looks like #32217. Can you please try the latest daily build and report whether the query works?

[vadimkhm]

@roji thanks for pointing out the #32217, this looks very connected.
Unfortunately, we have a lot of dependencies and it is tough to test it using the EF 9 version.
We will stay on EF7 until the EF 8.0.2 is released.