1

I observe a strange behavior when querying for null field an Entity Framework entity with the Devart DotConnect for PostgreSQL connector.

For example: If I made the 2 following tests:

var test1 = context.blocs.Where(x => x.id_bloc == null);

var test2 = context.blocs.Where(x => x.id_bloc == 100);

When checking for EF auto-generated SQL query I obtain the following results:

Result for test1:

{SELECT 
 CAST(NULL AS int) AS "C1",
 CAST(NULL AS varchar) AS "C2",
 CAST(NULL AS varchar) AS "C3",
 CAST(NULL AS varchar) AS "C4"
FROM  ( SELECT 1 AS X) AS "SingleRowTable1"
WHERE true = false}

Result for test2:

{SELECT 
"Extent1".id_bloc,
"Extent1".numero,
"Extent1".nom,
"Extent1".titre
FROM "role".bloc AS "Extent1"
WHERE "Extent1".id_bloc = 100}

The result when querying for null field is very strange... I was expecting a result similar to the test 2 result but with an "Extent1".id_bloc IS NULL WHERE clause...

Is it a bug? How can I effectively query for null field?

Etienne Desgagné
  • 3,102
  • 1
  • 29
  • 36
  • Did you try `Where(x => !x.id_bloc.HasValue)`? (But honestly I would expect that it will create the same SQL.) – Slauma Oct 30 '12 at 21:31
  • 1
    I don't know about this query provider, but did it map the database field `id_bloc` to a nullable type while in the database it isn't? It may decide to create a cheap phoney query when the predicates evaluate to `false` for sure. What happens if you type `Where(x => true == false)` yourself? – Gert Arnold Oct 30 '12 at 23:35
  • You are right, a dummy query was generated because I was trying to evaluate if a not nullable property was null... Thanks – Etienne Desgagné Oct 31 '12 at 13:44

1 Answers1

1

Is the id_bloc property defined with the Entity Key attribute? If yes, the result SQL is generated correctly.

Entity Key cannot be nullable that's why Entity Framework avoids unnecessary request to the table: generates fake SQL which gives the same empty result set but does not eat server resources.

You can check the behaviuor with SQL Server (via System.Data.SqlClient) - it must be the same.

Devart
  • 119,203
  • 23
  • 166
  • 186
  • My problem was effectively related to a not nullable property in my model... Everything work like expected when the nullable option is setted correctly. Thanks – Etienne Desgagné Oct 31 '12 at 13:41