EF Core beta3 : wrong sql generated for empty string

user1075044

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

Alex Keh-Oracle

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

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