Ryan Versaw's Blog

Check Your Entity Framework Contains() Queries (and Everything Else)

| Comments

I was analyzing the queries generated by a few of our page loads and came across a rather strange query:

SELECT CAST(NULL AS varchar(1))       AS [C1],
       CAST(NULL AS uniqueidentifier) AS [C2],
       CAST(NULL AS uniqueidentifier) AS [C3],
       CAST(NULL AS int)              AS [C4],
       CAST(NULL AS varchar(1))       AS [C5],
       CAST(NULL AS varchar(1))       AS [C6],
       CAST(NULL AS varchar(1))       AS [C7],
       CAST(NULL AS varchar(1))       AS [C8],
       CAST(NULL AS bit)              AS [C9],
       CAST(NULL AS varchar(1))       AS [C10],
       CAST(NULL AS varchar(1))       AS [C11],
       CAST(NULL AS varchar(1))       AS [C12],
       CAST(NULL AS varchar(1))       AS [C13],
       CAST(NULL AS varchar(1))       AS [C14],
       CAST(NULL AS varchar(1))       AS [C15],
       CAST(NULL AS varchar(1))       AS [C16],
       CAST(NULL AS varchar(1))       AS [C17]
FROM   (SELECT 1 AS X) AS [SingleRowTable1]
WHERE  1 = 0

(Note, this query has not been modified in any way, this is exactly as I found it)

When I investigated this, I found the following code generated this awesome SQL:

GetContext().CreateObjectSet<ProductFamily>().Where(pf => ids.Contains(pf.Id))

If you’re unfamiliar with Entity Framework, this query should pull any ProductFamily records whose Id is within the ids collection. The problem I experienced came up when the ids collection was an empty set. I would have expected EF to short-circuit this query so it didn’t even hit the database, but this is another case where having a good profiler is often a necessity when using an ORM.

I just came across another query that generated similar SQL:

        public virtual T GetById(object id)
        {
            return Items.SingleOrDefault(i => i.Id == id);
        }

This is on our base repository along with numerous other methods for ease of reuse, which is why an object comes in – we can support Guids, ints, or whatever other PKs are in use. This new issue came up when we were using a nullable Guid elsewhere. We weren’t checking to make sure it had a value, and instead passed the nullable Guid in directly. Once again, EF detected that the value was null and that no records will match it, but decided to execute a query anyway.

Comments