Skip to Main Content
Forums

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

EF Core beta3 : wrong sql generated for empty string

user1075044May 17 2019 — edited May 20 2019

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 NULL
FETCH 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

Comments

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
1 - 2

Post Details

Added on May 17 2019
#odp-net
2 comments
639 views