UseCSharpNullComparisonBehavior may need to improve

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
Zerda
Posts: 13
Joined: Wed 22 Jan 2014 00:16

UseCSharpNullComparisonBehavior may need to improve

Post by Zerda » Mon 05 Feb 2018 08:10

I have built an example with these components.
- dotConnect for Oracle 9.5.429
- EF Core 2.0.1
- .Net Core 2.0.0

Code: Select all

class Program
{
    static void Main(string[] args)
    {
        var config = OracleEntityProviderConfig.Instance;
        config.QueryOptions.UseCSharpNullComparisonBehavior = true;

        var context = new MyDbContext();
        context.Database.EnsureCreated();
       
        int? length = 1;
        var result = context.Products.Where(p => p.Length == length).ToList();

        length = null;
        result = context.Products.Where(p => p.Length == length).ToList();
    }
}

class MyDbContext : DbContext
{    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseOracle(@"REDACTED");
    }

    public DbSet<Product> Products { get; set; }
}

class Product
{
    [Key]
    public int Id { get; set; }

    public string Name { get; set; }

    public int? Length { get; set; }
}
Those two queries issued same sql to database like this. It's really hard to understand and maybe running slow in database.

Code: Select all

SELECT "p"."Id", "p"."Length", "p"."Name"
FROM "Products" "p"
WHERE (((("p"."Length" = :p__length_0) AND NOT ("p"."Length" IS NULL OR :p__length_0 IS NULL)) OR ("p"."Length" IS NULL AND :p__length_0 IS NULL)) AND NOT ("p"."Length" IS NULL OR :p__length_0 IS NULL)) 
  OR ("p"."Length" IS NULL AND :p__length_0 IS NULL)
I have also tested with SqlServer provider v2.0.1. I have to say it's much more easier to read.

Code: Select all

// when length value is not null
SELECT [p].[Id], [p].[Length], [p].[Name]
FROM [Products] AS [p]
WHERE [p].[Length] = @__length_0

// when length value is null
SELECT [p].[Id], [p].[Length], [p].[Name]
FROM [Products] AS [p]
WHERE [p].[Length] IS NULL
It seems the where clause have translated to something like following internally (https://stackoverflow.com/a/2541042/692076).

Code: Select all

length == null ? p.Length == null : p.Length == length
I am wondering if it can be done in dotConnect for Oracle, thank you.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: UseCSharpNullComparisonBehavior may need to improve

Post by Shalex » Thu 08 Feb 2018 10:54

Thank you for the detailed description of the issue. We will investigate the question and notify you about the result.

Zerda
Posts: 13
Joined: Wed 22 Jan 2014 00:16

Re: UseCSharpNullComparisonBehavior may need to improve

Post by Zerda » Thu 10 May 2018 06:20

Any progress on this?

It will make null comparison much more effective and easier to write code and read. Thanks.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: UseCSharpNullComparisonBehavior may need to improve

Post by Shalex » Mon 14 May 2018 18:40

The investigation is in progress. As soon as we have any results, we will contact you.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: UseCSharpNullComparisonBehavior may need to improve

Post by Shalex » Sat 23 Jun 2018 17:19

* The behavior is changed: the default value of config.QueryOptions.UseCSharpNullComparisonBehavior is true now in EF Core.
* The bug with SQL generation for comparing column with parameter in EF Core 2 is fixed.

We will notify you when the new build is available for download.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: UseCSharpNullComparisonBehavior may need to improve

Post by Shalex » Mon 25 Jun 2018 18:45


Zerda
Posts: 13
Joined: Wed 22 Jan 2014 00:16

Re: UseCSharpNullComparisonBehavior may need to improve

Post by Zerda » Fri 20 Jul 2018 02:47

I have been tested with 9.6.558, It's working as expected.

Thank you for the excellent work, This is very helpful.

Post Reply