search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.81k stars 3.2k forks source link

System.Linq IQueryable CreateQuery creates incorrect SQL #24330

Closed cirrusone closed 2 years ago

cirrusone commented 3 years ago

Description

Assuming database nullable column [Rank] [int] NULL, with data 1,2,3,NULL,NULL the following SQL query should return 1 record of value 3 (ie the nulls are not included).

select [Rank]
from [tblSomeData]
where Not([Rank] >= 1 and [Rank] <= 2)
-- Returns 1 record, value 3

In .NET4.7.2, building an expression to represent this query using System.Linq.Expressions and converting to a sql query using IQueryProvider IQueryable CreateQuery(Expression expression) 

Expression exBelow = Expression.GreaterThanOrEqual(propertyExp, Expression.Convert(values[0], propertyExp.Type));
Expression exAbove = Expression.LessThanOrEqual(propertyExp, Expression.Convert(values[1], propertyExp.Type));
Expression between = Expression.And(exBelow, exAbove);
Expression notBetween = Expression.Not(between);
...
var whereCallExpression = Expression.Call(
  typeof(Queryable),
  "Where",
  new[] { queryableData.ElementType },
  queryableData.Expression,
  Expression.Lambda<Func<SomeData, bool>>(notBetween, parameterExpression));

var filterQuery = queryableData.Provider.CreateQuery<SomeData>(whereCallExpression);

works as expected and gives the following sql query and correctly outputs the expected data.

OUTPUT in .NET4.7.2

Expression: `Not(((item.Rank >= Convert(1)) And (item.Rank <= Convert(2))))`

SQL Query:
SELECT [Extent1].[Rank] AS [Rank]
FROM [dbo].[tblSomeData] AS [Extent1]
WHERE  NOT (([Extent1].[Rank] >= 1) AND ([Extent1].[Rank] <= 2))

However on netcore2.2, netcore3.1 and .NET5.0 it gives different expression, query and the results are incorrect and include NULL. The query includes some unwanted casts to BIT which also converts the NULL values.

OUTPUT in .NET5.0:

Expression: Not(((item.Rank >= Convert(1, Nullable`1)) And (item.Rank <= Convert(2, Nullable`1))))

SQL Query: SELECT [t].[Rank]
FROM [tblSomeData] AS [t]
WHERE (CASE
    WHEN [t].[Rank] >= 1 THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END & CASE
    WHEN [t].[Rank] <= 2 THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END) <> CAST(1 AS bit)

As the expression is also different I'm assuming this is a problem with System.Linq rather than EntityFrameworkCore? This was initially discovered migrating an ASP.NET MVC project from .NET4.7.2 to .NET5.0. Since it can be awkward to generate testable project I've simplified the problem into a console app which is easier to test (see below). Only requirement is SQL Server/Express required.

Configuration

Complete testable example .NET4.7.2

using Dapper;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Core.Objects;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;
using System.Text;

namespace ConsoleNET472Test
{
    class Program
    {
        // .NET 4.7.2
        // Dapper 2.0.78 (nuget)
        // EntityFramework 6.4.4 (nuget)
        // SQL Server Installed
        // Server Name=DESKTOP-FJTG46H

        static void Main(string[] args)
        {
            // Setup sample database using dapper
            string connectionString = $"Data Source=DESKTOP-FJTG46H;Integrated Security=True;Connection Timeout=60";

            // Benchmark: Database returns 1 items, values: 3
            CreateDatabaseAndBenchmark(connectionString);

            // json filter {"field":"Rank","type":"integer","operator":"not_between","value":[1,2]}
            string field = "Rank";
            string value = "1, 2";

            // Construct Expression
            Type fieldType = typeof(int);
            ParameterExpression parameterExpression = Expression.Parameter(typeof(SomeData), "item");
            Expression propertyExp = Expression.Property(parameterExpression, field);

            TypeConverter tc = TypeDescriptor.GetConverter(fieldType);
            List<ConstantExpression> values = value.Split(new[] { ",", "[", "]", "\r\n" }, StringSplitOptions.RemoveEmptyEntries)
                    .Where(p => !string.IsNullOrWhiteSpace(p))
                    .Select(p => tc.ConvertFromString(p.Trim())).Select(p => Expression.Constant(p, fieldType)).ToList();

            Expression exBelow = Expression.GreaterThanOrEqual(propertyExp, Expression.Convert(values[0], propertyExp.Type));
            Expression exAbove = Expression.LessThanOrEqual(propertyExp, Expression.Convert(values[1], propertyExp.Type));

            Expression between = Expression.And(exBelow, exAbove);
            Expression notBetween = Expression.Not(between);
            Debug.WriteLine($"Expression: {notBetween.ToString()}");

            using (var context = new SomeDataEntity())
            {
                var queryableData = context.SomeData.AsQueryable();
                string parsedQuery = notBetween.ToString();

                var whereCallExpression = Expression.Call(
                typeof(Queryable),
                "Where",
                new[] { queryableData.ElementType },
                queryableData.Expression,
                Expression.Lambda<Func<SomeData, bool>>(notBetween, parameterExpression));

                var filterQuery = queryableData.Provider.CreateQuery<SomeData>(whereCallExpression);
                var results = filterQuery.ToList();

                Debug.WriteLine($"Results  Database returns {results.Count} items, values: {String.Join(",", results.Select(x => x.Rank.ToString()).ToArray())}");
                Debug.WriteLine($"Results SQL Query: {filterQuery.ToTraceString()}");

            }

            // OUTPUT:
            // Benchmark: Database returns 1 items, values: 3
            // Expression: Not(((item.Rank >= Convert(1)) And (item.Rank <= Convert(2))))
            // Results  Database returns 1 items, values: 3
            // Results SQL Query: SELECT
            //     [Extent1].[Pkey] AS [Pkey],
            //     [Extent1].[Rank] AS [Rank]
            //     FROM [dbo].[tblSomeData] AS [Extent1]
            //     WHERE  NOT (([Extent1].[Rank] >= 1) AND ([Extent1].[Rank] <= 2))

        }

        static void CreateDatabaseAndBenchmark(string connectionString)
        {
            // Drop Database
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                string sqlQuery = $@"
                        if exists (select * from sys.databases where name = 'dbFilterTest')
                        begin

                        drop database dbFilterTest

                        end

                ";
                connection.Execute(sqlQuery, null, null, 600);
            }

            // Create Database
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                string sqlQuery = $@"
                        if not exists (select * from sys.databases where name = 'dbFilterTest')
                        begin

                        create database dbFilterTest

                        end

                ";
                connection.Execute(sqlQuery, null, null, 600);
            }

            // Create Table and Insert data
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                string sqlQuery = $@"
                        if not exists (select 1 from dbFilterTest.information_schema.tables t where t.table_schema = 'dbo' and t.table_name = 'tblSomeData')
                        begin

                        create table [dbFilterTest].[dbo].[tblSomeData] ([Pkey] [int] NOT NULL PRIMARY KEY, [Rank] [int] NULL) ON [PRIMARY]

                        insert into [dbFilterTest].[dbo].[tblSomeData]([Pkey], [Rank])
                        values
                        (1, 1),
                        (2, 2),
                        (3, 3),
                        (4, null),
                        (5, null)

                        end

                ";
                connection.Execute(sqlQuery, null, null, 600);
            }

            // Benchmark 'select [Rank] from tblSomeData where [Rank] not between 1 and 2'
            List<SomeData> dataList = new List<SomeData>();
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                string sqlQuery = "select [Rank] from [dbFilterTest].[dbo].[tblSomeData] where [Rank] not between 1 and 2";
                dataList = connection.Query<SomeData>(sqlQuery, null, null, false, 600).ToList();
            }

            // Benchmark: Database returns 1 items, values: 3
            Debug.WriteLine($"Benchmark: Database returns {dataList.Count} items, values: {String.Join(",", dataList.Select(x => x.Rank.ToString()).ToArray())}");
        }
    }

    public class SomeData
    {
        [Key]
        public int Pkey { get; set; }
        public int? Rank { get; set; }
    }

    public class SomeDataEntity : DbContext
    {
        public SomeDataEntity() : base($"Data Source=DESKTOP-FJTG46H;Database=dbFilterTest;Integrated Security=True;Connection Timeout=60")
        {

        }
        public DbSet<SomeData> SomeData { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<SomeData>().ToTable("tblSomeData").HasKey(c => new { c.Pkey });
        }
    }

    public static class SQLExtensions
    {
        public static string ToTraceString<T>(this IQueryable<T> query)
        {
            ObjectQuery<T> objectQuery = GetQueryFromQueryable(query);

            var traceString = new StringBuilder();

            traceString.AppendLine(objectQuery.ToTraceString());
            traceString.AppendLine();

            foreach (var parameter in objectQuery.Parameters)
            {
                traceString.AppendLine(parameter.Name + " [" + parameter.ParameterType.FullName + "] = " + parameter.Value);
            }

            return traceString.ToString();
        }

        private static System.Data.Entity.Core.Objects.ObjectQuery<T> GetQueryFromQueryable<T>(IQueryable<T> query)
        {
            var internalQueryField = query.GetType().GetFields(System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).Where(f => f.Name.Equals("_internalQuery")).FirstOrDefault();
            var internalQuery = internalQueryField.GetValue(query);
            var objectQueryField = internalQuery.GetType().GetFields(System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).Where(f => f.Name.Equals("_objectQuery")).FirstOrDefault();
            return objectQueryField.GetValue(internalQuery) as System.Data.Entity.Core.Objects.ObjectQuery<T>;
        }
    }
}

Complete testable example .NET5.0

using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query.Internal;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

using Microsoft.EntityFrameworkCore.Storage;

namespace ConsoleNET5Test
{
    class Program
    {
        // .NET 5.0
        // Dapper 2.0.78
        // Microsoft.EntityFrameworkCore 5.0.3
        // Microsoft.EntityFrameworkCore.SqlServer 5.0.3
        // SQL Server Installed
        // Server Name=DESKTOP-FJTG46H

        static void Main(string[] args)
        {
            // Setup sample database using dapper
            string connectionString = $"Data Source=DESKTOP-FJTG46H;Integrated Security=True;Connection Timeout=60";

            // Benchmark: Database returns 1 items, values: 3
            CreateDatabaseAndBenchmark(connectionString);

            // Textual filter {"field":"Rank","type":"integer","operator":"not_between","value":[1,2]}
            string field = "Rank";
            string value = "1, 2";

            // Construct Expression
            Type fieldType = typeof(int);
            ParameterExpression parameterExpression = Expression.Parameter(typeof(SomeData), "item");
            Expression propertyExp = Expression.Property(parameterExpression, field);

            TypeConverter tc = TypeDescriptor.GetConverter(fieldType);
            List<ConstantExpression> values = value.Split(new[] { ",", "[", "]", "\r\n" }, StringSplitOptions.RemoveEmptyEntries)
                    .Where(p => !string.IsNullOrWhiteSpace(p))
                    .Select(p => tc.ConvertFromString(p.Trim())).Select(p => Expression.Constant(p, fieldType)).ToList();

            Expression exBelow = Expression.GreaterThanOrEqual(propertyExp, Expression.Convert(values[0], propertyExp.Type));
            Expression exAbove = Expression.LessThanOrEqual(propertyExp, Expression.Convert(values[1], propertyExp.Type));

            Expression between = Expression.And(exBelow, exAbove);
            Expression notBetween = Expression.Not(between);
            Debug.WriteLine($"Expression: {notBetween.ToString()}");

            using (var context = new SomeDataEntity())
            {
                var queryableData = context.SomeData.AsQueryable();
                string parsedQuery = notBetween.ToString();

                var whereCallExpression = Expression.Call(
                typeof(Queryable),
                "Where",
                new[] { queryableData.ElementType },
                queryableData.Expression,
                Expression.Lambda<Func<SomeData, bool>>(notBetween, parameterExpression));

                var filterQuery = queryableData.Provider.CreateQuery<SomeData>(whereCallExpression);
                var results = filterQuery.ToList();

                Debug.WriteLine($"Results  Database returns {results.Count} items, values: {String.Join(",", results.Select(x => x.Rank.ToString()).ToArray())}");
                Debug.WriteLine($"Results SQL Query: {filterQuery.ToQueryString()}");

                // ERROR returns 3 items instead of 1, null values being returned due to funky casting??

                //Benchmark: Database returns 1 items, values: 3
                //Results Database returns 3 items, values: 3,,
                //Results SQL Query: SELECT[t].[Pkey], [t].[Rank]
                //FROM[tblSomeData] AS[t]
                //WHERE(CASE
                //    WHEN[t].[Rank] >= 1 THEN CAST(1 AS bit)
                //    ELSE CAST(0 AS bit)
                //END & CASE
                //    WHEN[t].[Rank] <= 2 THEN CAST(1 AS bit)
                //    ELSE CAST(0 AS bit)
                //END) <> CAST(1 AS bit)

            }
        }

        static void CreateDatabaseAndBenchmark(string connectionString)
        {
            // Drop Database
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                string sqlQuery = $@"
                        if exists (select * from sys.databases where name = 'dbFilterTest')
                        begin

                        drop database dbFilterTest

                        end

                ";
                connection.Execute(sqlQuery, null, null, 600);
            }

            // Create Database
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                string sqlQuery = $@"
                        if not exists (select * from sys.databases where name = 'dbFilterTest')
                        begin

                        create database dbFilterTest

                        end

                ";
                connection.Execute(sqlQuery, null, null, 600);
            }

            // Create Table and Insert data
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                string sqlQuery = $@"
                        if not exists (select 1 from dbFilterTest.information_schema.tables t where t.table_schema = 'dbo' and t.table_name = 'tblSomeData')
                        begin

                        create table [dbFilterTest].[dbo].[tblSomeData] ([Pkey] [int] NOT NULL PRIMARY KEY, [Rank] [int] NULL) ON [PRIMARY]

                        insert into [dbFilterTest].[dbo].[tblSomeData]([Pkey], [Rank])
                        values
                        (1, 1),
                        (2, 2),
                        (3, 3),
                        (4, null),
                        (5, null)

                        end

                ";
                connection.Execute(sqlQuery, null, null, 600);
            }

            // Benchmark 'select [Rank] from tblSomeData where [Rank] not between 1 and 2'
            List<SomeData> dataList = new List<SomeData>();
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                string sqlQuery = "select [Rank] from [dbFilterTest].[dbo].[tblSomeData] where [Rank] not between 1 and 2";
                dataList = connection.Query<SomeData>(sqlQuery, null, null, false, 600).ToList();
            }

            // Benchmark: Database returns 1 items, values: 3
            Debug.WriteLine($"Benchmark: Database returns {dataList.Count} items, values: {String.Join(",", dataList.Select(x => x.Rank.ToString()).ToArray())}");
        }
    }

    public class SomeData
    {
        [Key]
        public int Pkey { get; set; }
        public int? Rank { get; set; }
    }

    public class SomeDataEntity : DbContext
    {
        public SomeDataEntity() : base()
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer($"Data Source=DESKTOP-FJTG46H;Database=dbFilterTest;Integrated Security=True;Connection Timeout=60");
            }
        }

        public DbSet<SomeData> SomeData { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<SomeData>().ToTable("tblSomeData").HasKey(c => new { c.Pkey });
        }
    }
}
dotnet-issue-labeler[bot] commented 3 years ago

I couldn't figure out the best area label to add to this issue. If you have write-permissions please help me learn by adding exactly one area label.

ghost commented 3 years ago

Tagging subscribers to this area: @eiriktsarpalis See info in area-owners.md if you want to be subscribed.

Issue Details
### Description Assuming database nullable column `[Rank] [int] NULL`, with data `1,2,3,NULL,NULL` the following SQL query should return 1 record of value 3 (ie the nulls are not included). ``` select [Rank] from [tblSomeData] where Not([Rank] >= 1 and [Rank] <= 2) -- Returns 1 record, value 3 ``` In .NET4.7.2, building an expression to represent this query using System.Linq.Expressions and converting to a sql query using `IQueryProvider IQueryable CreateQuery(Expression expression)` ``` Expression exBelow = Expression.GreaterThanOrEqual(propertyExp, Expression.Convert(values[0], propertyExp.Type)); Expression exAbove = Expression.LessThanOrEqual(propertyExp, Expression.Convert(values[1], propertyExp.Type)); Expression between = Expression.And(exBelow, exAbove); Expression notBetween = Expression.Not(between); ... var whereCallExpression = Expression.Call( typeof(Queryable), "Where", new[] { queryableData.ElementType }, queryableData.Expression, Expression.Lambda>(notBetween, parameterExpression)); var filterQuery = queryableData.Provider.CreateQuery(whereCallExpression); ``` works as expected and gives the following sql query and correctly outputs the expected data. OUTPUT in .NET4.7.2 ``` Expression: `Not(((item.Rank >= Convert(1)) And (item.Rank <= Convert(2))))` SQL Query: SELECT [Extent1].[Rank] AS [Rank] FROM [dbo].[tblSomeData] AS [Extent1] WHERE NOT (([Extent1].[Rank] >= 1) AND ([Extent1].[Rank] <= 2)) ``` However on netcore2.2, netcore3.1 and .NET5.0 it gives different expression, query and the results are incorrect and include NULL. The query includes some unwanted casts to BIT which also converts the NULL values. OUTPUT in .NET5.0: ``` Expression: Not(((item.Rank >= Convert(1, Nullable`1)) And (item.Rank <= Convert(2, Nullable`1)))) SQL Query: SELECT [t].[Rank] FROM [tblSomeData] AS [t] WHERE (CASE WHEN [t].[Rank] >= 1 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END & CASE WHEN [t].[Rank] <= 2 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END) <> CAST(1 AS bit) ``` As the expression is also different I'm assuming this is a problem with System.Linq rather than EntityFrameworkCore? This was initially discovered migrating an ASP.NET MVC project from .NET4.7.2 to .NET5.0. Since it can be awkward to generate testable project I've simplified the problem into a console app which is easier to test (see below). Only requirement is SQL Server/Express required. ### Configuration * Works as expected on .NET4.7.2 and EntityFramework * Does not work on netcore2.2, netcore3.1 and .NET5.0 and EntityFrameworkCore * Windows 10 19042.804 * x86 and x64 ### Complete testable example .NET4.7.2 ``` using Dapper; using System; using System.Collections.Generic; using System.ComponentModel; using System.ComponentModel.DataAnnotations; using System.Data; using System.Data.Entity; using System.Data.Entity.Core.Objects; using System.Data.SqlClient; using System.Diagnostics; using System.Linq; using System.Linq.Expressions; using System.Text; namespace ConsoleNET472Test { class Program { // .NET 4.7.2 // Dapper 2.0.78 (nuget) // EntityFramework 6.4.4 (nuget) // SQL Server Installed // Server Name=DESKTOP-FJTG46H static void Main(string[] args) { // Setup sample database using dapper string connectionString = $"Data Source=DESKTOP-FJTG46H;Integrated Security=True;Connection Timeout=60"; // Benchmark: Database returns 1 items, values: 3 CreateDatabaseAndBenchmark(connectionString); // json filter {"field":"Rank","type":"integer","operator":"not_between","value":[1,2]} string field = "Rank"; string value = "1, 2"; // Construct Expression Type fieldType = typeof(int); ParameterExpression parameterExpression = Expression.Parameter(typeof(SomeData), "item"); Expression propertyExp = Expression.Property(parameterExpression, field); TypeConverter tc = TypeDescriptor.GetConverter(fieldType); List values = value.Split(new[] { ",", "[", "]", "\r\n" }, StringSplitOptions.RemoveEmptyEntries) .Where(p => !string.IsNullOrWhiteSpace(p)) .Select(p => tc.ConvertFromString(p.Trim())).Select(p => Expression.Constant(p, fieldType)).ToList(); Expression exBelow = Expression.GreaterThanOrEqual(propertyExp, Expression.Convert(values[0], propertyExp.Type)); Expression exAbove = Expression.LessThanOrEqual(propertyExp, Expression.Convert(values[1], propertyExp.Type)); Expression between = Expression.And(exBelow, exAbove); Expression notBetween = Expression.Not(between); Debug.WriteLine($"Expression: {notBetween.ToString()}"); using (var context = new SomeDataEntity()) { var queryableData = context.SomeData.AsQueryable(); string parsedQuery = notBetween.ToString(); var whereCallExpression = Expression.Call( typeof(Queryable), "Where", new[] { queryableData.ElementType }, queryableData.Expression, Expression.Lambda>(notBetween, parameterExpression)); var filterQuery = queryableData.Provider.CreateQuery(whereCallExpression); var results = filterQuery.ToList(); Debug.WriteLine($"Results Database returns {results.Count} items, values: {String.Join(",", results.Select(x => x.Rank.ToString()).ToArray())}"); Debug.WriteLine($"Results SQL Query: {filterQuery.ToTraceString()}"); } // OUTPUT: // Benchmark: Database returns 1 items, values: 3 // Expression: Not(((item.Rank >= Convert(1)) And (item.Rank <= Convert(2)))) // Results Database returns 1 items, values: 3 // Results SQL Query: SELECT // [Extent1].[Pkey] AS [Pkey], // [Extent1].[Rank] AS [Rank] // FROM [dbo].[tblSomeData] AS [Extent1] // WHERE NOT (([Extent1].[Rank] >= 1) AND ([Extent1].[Rank] <= 2)) } static void CreateDatabaseAndBenchmark(string connectionString) { // Drop Database using (IDbConnection connection = new SqlConnection(connectionString)) { string sqlQuery = $@" if exists (select * from sys.databases where name = 'dbFilterTest') begin drop database dbFilterTest end "; connection.Execute(sqlQuery, null, null, 600); } // Create Database using (IDbConnection connection = new SqlConnection(connectionString)) { string sqlQuery = $@" if not exists (select * from sys.databases where name = 'dbFilterTest') begin create database dbFilterTest end "; connection.Execute(sqlQuery, null, null, 600); } // Create Table and Insert data using (IDbConnection connection = new SqlConnection(connectionString)) { string sqlQuery = $@" if not exists (select 1 from dbFilterTest.information_schema.tables t where t.table_schema = 'dbo' and t.table_name = 'tblSomeData') begin create table [dbFilterTest].[dbo].[tblSomeData] ([Pkey] [int] NOT NULL PRIMARY KEY, [Rank] [int] NULL) ON [PRIMARY] insert into [dbFilterTest].[dbo].[tblSomeData]([Pkey], [Rank]) values (1, 1), (2, 2), (3, 3), (4, null), (5, null) end "; connection.Execute(sqlQuery, null, null, 600); } // Benchmark 'select [Rank] from tblSomeData where [Rank] not between 1 and 2' List dataList = new List(); using (IDbConnection connection = new SqlConnection(connectionString)) { string sqlQuery = "select [Rank] from [dbFilterTest].[dbo].[tblSomeData] where [Rank] not between 1 and 2"; dataList = connection.Query(sqlQuery, null, null, false, 600).ToList(); } // Benchmark: Database returns 1 items, values: 3 Debug.WriteLine($"Benchmark: Database returns {dataList.Count} items, values: {String.Join(",", dataList.Select(x => x.Rank.ToString()).ToArray())}"); } } public class SomeData { [Key] public int Pkey { get; set; } public int? Rank { get; set; } } public class SomeDataEntity : DbContext { public SomeDataEntity() : base($"Data Source=DESKTOP-FJTG46H;Database=dbFilterTest;Integrated Security=True;Connection Timeout=60") { } public DbSet SomeData { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Entity().ToTable("tblSomeData").HasKey(c => new { c.Pkey }); } } public static class SQLExtensions { public static string ToTraceString(this IQueryable query) { ObjectQuery objectQuery = GetQueryFromQueryable(query); var traceString = new StringBuilder(); traceString.AppendLine(objectQuery.ToTraceString()); traceString.AppendLine(); foreach (var parameter in objectQuery.Parameters) { traceString.AppendLine(parameter.Name + " [" + parameter.ParameterType.FullName + "] = " + parameter.Value); } return traceString.ToString(); } private static System.Data.Entity.Core.Objects.ObjectQuery GetQueryFromQueryable(IQueryable query) { var internalQueryField = query.GetType().GetFields(System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).Where(f => f.Name.Equals("_internalQuery")).FirstOrDefault(); var internalQuery = internalQueryField.GetValue(query); var objectQueryField = internalQuery.GetType().GetFields(System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).Where(f => f.Name.Equals("_objectQuery")).FirstOrDefault(); return objectQueryField.GetValue(internalQuery) as System.Data.Entity.Core.Objects.ObjectQuery; } } } ``` ### Complete testable example .NET5.0 ``` using Dapper; using Microsoft.Data.SqlClient; using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Query.Internal; using System; using System.Collections.Generic; using System.ComponentModel; using System.ComponentModel.DataAnnotations; using System.Data; using System.Diagnostics; using System.Linq; using System.Linq.Expressions; using System.Reflection; using Microsoft.EntityFrameworkCore.Storage; namespace ConsoleNET5Test { class Program { // .NET 5.0 // Dapper 2.0.78 // Microsoft.EntityFrameworkCore 5.0.3 // Microsoft.EntityFrameworkCore.SqlServer 5.0.3 // SQL Server Installed // Server Name=DESKTOP-FJTG46H static void Main(string[] args) { // Setup sample database using dapper string connectionString = $"Data Source=DESKTOP-FJTG46H;Integrated Security=True;Connection Timeout=60"; // Benchmark: Database returns 1 items, values: 3 CreateDatabaseAndBenchmark(connectionString); // Textual filter {"field":"Rank","type":"integer","operator":"not_between","value":[1,2]} string field = "Rank"; string value = "1, 2"; // Construct Expression Type fieldType = typeof(int); ParameterExpression parameterExpression = Expression.Parameter(typeof(SomeData), "item"); Expression propertyExp = Expression.Property(parameterExpression, field); TypeConverter tc = TypeDescriptor.GetConverter(fieldType); List values = value.Split(new[] { ",", "[", "]", "\r\n" }, StringSplitOptions.RemoveEmptyEntries) .Where(p => !string.IsNullOrWhiteSpace(p)) .Select(p => tc.ConvertFromString(p.Trim())).Select(p => Expression.Constant(p, fieldType)).ToList(); Expression exBelow = Expression.GreaterThanOrEqual(propertyExp, Expression.Convert(values[0], propertyExp.Type)); Expression exAbove = Expression.LessThanOrEqual(propertyExp, Expression.Convert(values[1], propertyExp.Type)); Expression between = Expression.And(exBelow, exAbove); Expression notBetween = Expression.Not(between); Debug.WriteLine($"Expression: {notBetween.ToString()}"); using (var context = new SomeDataEntity()) { var queryableData = context.SomeData.AsQueryable(); string parsedQuery = notBetween.ToString(); var whereCallExpression = Expression.Call( typeof(Queryable), "Where", new[] { queryableData.ElementType }, queryableData.Expression, Expression.Lambda>(notBetween, parameterExpression)); var filterQuery = queryableData.Provider.CreateQuery(whereCallExpression); var results = filterQuery.ToList(); Debug.WriteLine($"Results Database returns {results.Count} items, values: {String.Join(",", results.Select(x => x.Rank.ToString()).ToArray())}"); Debug.WriteLine($"Results SQL Query: {filterQuery.ToQueryString()}"); // ERROR returns 3 items instead of 1, null values being returned due to funky casting?? //Benchmark: Database returns 1 items, values: 3 //Results Database returns 3 items, values: 3,, //Results SQL Query: SELECT[t].[Pkey], [t].[Rank] //FROM[tblSomeData] AS[t] //WHERE(CASE // WHEN[t].[Rank] >= 1 THEN CAST(1 AS bit) // ELSE CAST(0 AS bit) //END & CASE // WHEN[t].[Rank] <= 2 THEN CAST(1 AS bit) // ELSE CAST(0 AS bit) //END) <> CAST(1 AS bit) } } static void CreateDatabaseAndBenchmark(string connectionString) { // Drop Database using (IDbConnection connection = new SqlConnection(connectionString)) { string sqlQuery = $@" if exists (select * from sys.databases where name = 'dbFilterTest') begin drop database dbFilterTest end "; connection.Execute(sqlQuery, null, null, 600); } // Create Database using (IDbConnection connection = new SqlConnection(connectionString)) { string sqlQuery = $@" if not exists (select * from sys.databases where name = 'dbFilterTest') begin create database dbFilterTest end "; connection.Execute(sqlQuery, null, null, 600); } // Create Table and Insert data using (IDbConnection connection = new SqlConnection(connectionString)) { string sqlQuery = $@" if not exists (select 1 from dbFilterTest.information_schema.tables t where t.table_schema = 'dbo' and t.table_name = 'tblSomeData') begin create table [dbFilterTest].[dbo].[tblSomeData] ([Pkey] [int] NOT NULL PRIMARY KEY, [Rank] [int] NULL) ON [PRIMARY] insert into [dbFilterTest].[dbo].[tblSomeData]([Pkey], [Rank]) values (1, 1), (2, 2), (3, 3), (4, null), (5, null) end "; connection.Execute(sqlQuery, null, null, 600); } // Benchmark 'select [Rank] from tblSomeData where [Rank] not between 1 and 2' List dataList = new List(); using (IDbConnection connection = new SqlConnection(connectionString)) { string sqlQuery = "select [Rank] from [dbFilterTest].[dbo].[tblSomeData] where [Rank] not between 1 and 2"; dataList = connection.Query(sqlQuery, null, null, false, 600).ToList(); } // Benchmark: Database returns 1 items, values: 3 Debug.WriteLine($"Benchmark: Database returns {dataList.Count} items, values: {String.Join(",", dataList.Select(x => x.Rank.ToString()).ToArray())}"); } } public class SomeData { [Key] public int Pkey { get; set; } public int? Rank { get; set; } } public class SomeDataEntity : DbContext { public SomeDataEntity() : base() { } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { if (!optionsBuilder.IsConfigured) { optionsBuilder.UseSqlServer($"Data Source=DESKTOP-FJTG46H;Database=dbFilterTest;Integrated Security=True;Connection Timeout=60"); } } public DbSet SomeData { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Entity().ToTable("tblSomeData").HasKey(c => new { c.Pkey }); } } } ```
Author: cirrusone
Assignees: -
Labels: `area-System.Linq`, `untriaged`
Milestone: -
Joe4evr commented 3 years ago

You're probably gonna have to take this up with Dapper or EF, because as far as I'm aware, there's absolutely no code in CoreFX that performs Expression->SQL translation like that; it all gets delegated to whatever implements the Query Provider.

Unless you have proof that the expression trees themselves are significantly changed and that's what causes EF/Dapper to interpret them differently.

cirrusone commented 3 years ago

Dapper is only used to setup the test conditions in this example, it's only EF and EFCore being used where the bug occurs,

The expressions are different between .NET framework and .NET5.0

Expression .NET 4.7.2: Not(((item.Rank >= Convert(1)) And (item.Rank <= Convert(2))))

Expression .NET5.0 : Not(((item.Rank >= Convert(1, Nullable`1)) And (item.Rank <= Convert(2, Nullable`1))))

eiriktsarpalis commented 3 years ago

In that case, it would probably make sense to transfer the issue to the EF repo. That's not discounting that it might be a System.Linq issue, but the EF team should need to take a look first.

smitpatel commented 3 years ago

Expression.And(exBelow, exAbove)

Expression.And generates a bitwise & operator in the tree. What you need is Expression.AndAlso which generates a boolean && operation. EF Core differentiates both operator separately hence you get a different query.

cirrusone commented 3 years ago

Ah, that's the issue. Using Expression.AndAlso completely solved my problem and it now works as expected. Sorry for wasting your time and thanks for the help.