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.