How to query an Entity Framework entity for null field value with DotConnect for PostgreSQL

Question

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?

Did you try Where(x => !x.id_bloc.HasValue)? (But honestly I would expect that it will create the same SQL.) — Slauma
– Slauma, Commented Oct 30, 2012 at 21:31
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
– Gert Arnold, Commented Oct 30, 2012 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é
– Etienne Desgagné, Commented Oct 31, 2012 at 13:44

Devart · Accepted Answer · 2012-10-31 09:58:49Z

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.

answered Oct 31, 2012 at 9:58

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Etienne Desgagné Over a year ago

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

Collectives™ on Stack Overflow

How to query an Entity Framework entity for null field value with DotConnect for PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related