Exception in SQL server query in EF Core 6, looks related to HasQueryFilter(), worked in EF Core 5

[smegleyshire]

I've been trying out EF Core 6 against our existing SQL server DB and have come across a strange exception when executing a simple query that includes a navigation between 2 tables in a 1:0/1 relationship. Our tables have a "deleted" (integer) column used for soft delete. I've mapped that to a bool IsDeleted property in my entity classes using the built-in HasConversion() and set up a query filter using HasQueryFilter(s => !s.IsDeleted).

I've put together a single file console app that demonstrates the problem below - zipped project also attached.

EFTest.zip

using Microsoft.EntityFrameworkCore;

namespace EFTest
{
    internal class Program
    {
        private static void Main()
        {
            using (var context = new TestDbContext())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                // Create active location
                string createLocationCommand = $@"
INSERT INTO [dbo].[pp_location] (location_id, address, deleted)
VALUES ('1C038679-A316-4C4C-AD53-58463A46EBF6', 'Active address', 0)
";
                context.Database.ExecuteSqlRaw(createLocationCommand);

                // Create deleted location
                string createDeletedLocationCommand = $@"
INSERT INTO [dbo].[pp_location] (location_id, address, deleted)
VALUES ('8377FF01-E49E-492A-AB28-690534575FC6', 'Deleted address', 1)
";
                context.Database.ExecuteSqlRaw(createDeletedLocationCommand);

                // Create active supplier 1 - linked to active location
                string createSupplierCommand1 = $@"
INSERT INTO [dbo].[pp_supplier] (supplier_id, name, location_id, deleted)
VALUES ('03E159BC-839C-4C3B-9C78-8891539AE542', 'Active supplier 1', '1C038679-A316-4C4C-AD53-58463A46EBF6', 0)
";
                context.Database.ExecuteSqlRaw(createSupplierCommand1);

                // Create active supplier 2 - linked to deleted location
                string createSupplierCommand2 = $@"
INSERT INTO [dbo].[pp_supplier] (supplier_id, name, location_id, deleted)
VALUES ('307B9F86-00B9-4561-968F-F05661E9B636', 'Active supplier 2', '8377FF01-E49E-492A-AB28-690534575FC6', 0)
";
                context.Database.ExecuteSqlRaw(createSupplierCommand2);

                // Create active supplier 3 - not linked to a location
                string createSupplierCommand3 = $@"
INSERT INTO [dbo].[pp_supplier] (supplier_id, name, location_id, deleted)
VALUES ('99C798F9-C61F-4AB5-95DC-5E62220E1BFA', 'Active supplier 3', NULL, 0)
";
                context.Database.ExecuteSqlRaw(createSupplierCommand3);

                // Create deleted supplier - not linked to a location
                string createDeletedSupplierCommand = $@"
INSERT INTO [dbo].[pp_supplier] (supplier_id, name, location_id, deleted)
VALUES ('0ECEF718-5A9B-4BDA-B7B4-B87B6EB57EE0', 'Deleted supplier', NULL, 1)
";
                context.Database.ExecuteSqlRaw(createDeletedSupplierCommand);

                var suppliers = context.Suppliers.Include(s => s.Location).OrderBy(s => s.Name).ToList();

                Console.WriteLine();
                if (suppliers.Count > 0)
                {
                    foreach (Supplier s in suppliers)
                        Console.WriteLine("Supplier: " + s.Name + " -> " + "Location: " + ((s.Location != null) ? s.Location.Address : "<none or deleted>"));
                }
                else
                    Console.WriteLine("No records found");
                Console.WriteLine();
            }
        }
    }

    public class TestDbContext : DbContext
    {
        public DbSet<Supplier> Suppliers => Set<Supplier>();
        public DbSet<Location> Locations => Set<Location>();

        protected override void OnConfiguring(DbContextOptionsBuilder options)
            => options.UseSqlServer("Data Source=(localdb)\\ProjectModels;Database=EFTest;Integrated Security=true")
               .LogTo(Console.WriteLine);

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Supplier>().ToTable("pp_supplier");
            modelBuilder.Entity<Supplier>().Property(s => s.SupplierId).HasColumnName("supplier_id");
            modelBuilder.Entity<Supplier>().Property(s => s.Name).HasColumnName("name").IsUnicode(false).HasMaxLength(50);
            modelBuilder.Entity<Supplier>().Property<Guid?>("LocationId").HasColumnName("location_id");
            modelBuilder.Entity<Supplier>().HasKey(s => s.SupplierId);
            modelBuilder.Entity<Supplier>().Property(s => s.IsDeleted).HasColumnName("deleted").HasConversion<int>();
            modelBuilder.Entity<Supplier>().HasQueryFilter(s => !s.IsDeleted);

            modelBuilder.Entity<Location>().ToTable("pp_location");
            modelBuilder.Entity<Location>().Property(l => l.LocationId).HasColumnName("location_id");
            modelBuilder.Entity<Location>().Property(l => l.Address).HasColumnName("address").IsUnicode(false).HasMaxLength(100);
            modelBuilder.Entity<Location>().HasKey(l => l.LocationId);
            modelBuilder.Entity<Location>().Property(l => l.IsDeleted).HasColumnName("deleted").HasConversion<int>();
            modelBuilder.Entity<Location>().HasQueryFilter(l => !l.IsDeleted);
        }
    }

    public class Supplier
    {
        public Guid SupplierId { get; set; }
        public string Name { get; set; } = null!;
        public Location? Location { get; set; }
        public bool IsDeleted { get; set; }
    }

    public class Location
    {
        public Guid LocationId { get; set; }
        public string Address { get; set; } = null!;
        public bool IsDeleted { get; set; }
    }
}

When I run this against EFCore 6.0.0-rc.2.21480.5, I get a crash: System.ArgumentOutOfRangeException: 'Index was out of range. Must be non-negative and less than the size of the collection. Arg_ParamName_Name'. From the exception, ParamName is "index" and the following is the stack trace:

   at System.ThrowHelper.ThrowArgumentOutOfRange_IndexException()
   at System.Collections.Generic.List`1.get_Item(Int32 index)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.GetProjection(ProjectionBindingExpression projectionBindingExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.GetProjectionIndex(ProjectionBindingExpression projectionBindingExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
   at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block)
   at System.Linq.Expressions.ExpressionVisitor.VisitBlock(BlockExpression node)
   at System.Linq.Expressions.BlockExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.ProcessShaper(Expression shaperExpression, RelationalCommandCache& relationalCommandCache, LambdaExpression& relatedDataLoaders, Int32& collectionId)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.VisitShapedQuery(ShapedQueryExpression shapedQueryExpression)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at EFTest.Program.Main() in C:\Temp\EFTest\Program.cs:line 56

If I use Nuget to switch to using EFCore 5.0.11, the code executes OK without exception.

A couple of things I've found:

• Remove the Include() from the query and it runs OK, query filter prevents deleted supplier from being returned, but of course we aren't joining to Location any more.
• Keep the Include() in the query but change both the .HasQueryFilter(x => !x.IsDeleted) lines to .HasQueryFilter(x => x.IsDeleted == false). This seems to fix the problem but I don't understand why? (especially when x => !x.IsDeleted works fine with EF Core 5).

EF Core version: 6.0.0-rc.2.21480.5
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 10 Pro 20H2
IDE: Visual Studio 2022 Version 17.0.0 Preview 6.0

[smitpatel]

The root cause is the conversion applied to IsDeleted column which is being used in query filter.

[adamhathcock]

I've also ran into this exact exception with a query like:

 var apiKey = await _readonlyDisputedDbContext.Keys
                .Include(x => x.User)
                .Where(x => x.Key == key
                            && x.Enabled
                            && x.Usage == Usage.Public)
                .FirstOrDefaultAsync();

removing the include fixed it. Just adding this if it helps but hopefully the fix is coming soon :)

[mikewodarczyk]

Workaround:

The problem seems to come from the combination of the include statement AND a where clause that filters on a HasConversion property. If you can remove the Where clause from the SQL/server side, or the part of the where clause needing the HasConversion property, but instead apply the where clause after the ToList, then you will get the expected output. For me the cost of the extra rows pulled was minimal, but if your Where clause filters a lot of rows, that wont work for you.

Hopefully the real fix will be released soon!

[Doomer3D]

Is any due date to release those fixes? We using Oracle Boolean Converters, therefore all requests with boolean checks in combination with other fields throws exceptions.

Thanks!

[ajcvickers]

@Doomer3D 6.0.1 is scheduled for release in the coming days.

[ajcvickers]

FYI for those impacted by this issue: EF Core 6.0.1 is now available from NuGet.

[Doomer3D]

Problem is fixed, thanks. Update #23884 also, please.