EF Core beta3 : wrong sql generated for empty string — oracle-tech

    Forum Stats

  • 3,715,999 Users
  • 2,242,927 Discussions
  • 7,845,731 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

EF Core beta3 : wrong sql generated for empty string

user1075044
user1075044 Member Posts: 2
edited May 2019 in ODP.NET

Hi,

There is a bug in ef core beta3, for linq queries filtering null or empty string :

using (var db = new BloggingContext()){    var blogs = db.Blogs.Where(b => !String.IsNullOrEmpty(b.Url));    Console.WriteLine("Url = " + blogs.FirstOrDefault().Url);}

SQL :

SELECT "b"."BlogId", "b"."Url"FROM "Blogs" "b"WHERE "b"."Url" IS NOT NULL AND (("b"."Url" <> N'') OR "b"."Url" IS NULL)FETCH FIRST 1 ROWS ONLY

but

"b"."Url" <> N''

Is always false ! Because for Oracle empty string is NULL.

With a simpler query :

using (var db = new BloggingContext()){    var blogs = db.Blogs.Where(b => b.Url != "");    Console.WriteLine("Url = " + blogs.FirstOrDefault().Url);}

SQL :

SELECT "b"."BlogId", "b"."Url"FROM "Blogs" "b"WHERE ("b"."Url" <> N'') OR "b"."Url" IS NULLFETCH FIRST 1 ROWS ONLY

For me there is a logical bug in this query, I want a not empty Url, so it should be

"b"."Url" IS NOT NULL

The workaround I used is to only test against null and not empty string, but this is not portable for other database :

using (var db = new BloggingContext()){    var blogs = db.Blogs.Where(b => b.Url != null);    Console.WriteLine("Url = " + blogs.FirstOrDefault().Url);}

SELECT "b"."BlogId", "b"."Url"      FROM "Blogs" "b"      WHERE "b"."Url" IS NOT NULL      FETCH FIRST 1 ROWS ONLY

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,753 Employee
    edited May 2019

    I filed an ER 29798071 for my dev team to discuss whether to implement your suggested change. I'm not hopeful though as Oracle does not implement LINQ and does not control significant aspects how it generates SQL.

  • user1075044
    user1075044 Member Posts: 2
    edited May 2019

    Ok, Thanks !

    For information the Devart  driver generate this SQL for isNullOrEmpty :

    using (var db = new BloggingContext()){    var blogs = db.Blogs.Where(b => !String.IsNullOrEmpty(b.Url));    Console.WriteLine("Url = " + blogs.FirstOrDefault().Url);}

    SELECT "b"."BlogId", "b"."Url"  FROM "Blogs" "b"WHERE ("b"."Url" IS NOT NULL)  AND ("b"."Url" IS NOT NULL)FETCH FIRST 1 rows ONLY
Sign In or Register to comment.