We’ve built a data access layer on top of LINQ2SQL for dynamically building the layer we call for data access. It’s convenient because we pass in a query object as a parameter that has a bunch of nullable variables in it. Here is kind of what it looks like:
public partial class CodeGenTestQuery : QueryBase
{
public int? Id { get; set; }
public List<int> Ids { get; set; }
public bool? IsStarred { get; set; }
...
If I pass an empty list of int’s into Ids, I have a line of code that build the query as follows:
if (query.Ids != null) baseQuery = baseQuery.Where(a => query.Ids.Contains(a.Id));
This works fine as long as a pass into ids either null, or a list that contains more than one object. However, if I pass in an empty list like this:
new CodeGenTestQuery { Ids = new List<int>() { }, MyAge = 2 }
Then, the result of the LINQ query is it creates a where clause that says something like this:
SELECT [t0].[Id],
[t0].[IsStarred]
FROM [dbo].[CodeGenTest] AS [t0]
WHERE ([t0].[MyAge] = 2) AND
(0 = 1)
Which means you will not get the MyAge == 2 record ever
So, I changed my c# as follows and now I get what I expect.
if (query.Ids != null) { if (query.Ids.Count > 0) { baseQuery = baseQuery.Where(a => query.Ids.Contains(a.Id)); } }
I can see this kind of thinking on the LINQ2SQL team, but it’s a little insidious if you ask me.
June 15th, 2009 at 3:01 pm
I think Linq2Sql handles it correctly. The only other way of handling it would be to not execute the query at all, since it will never return anything, but I don’t think we can expect that since
1. The absence of a query execution could indicate a problem
2. The query may be a subquery
3. It’s not up to the Linq2Sql provider to perform any query logic
SQL Server will see that the query can’t return any results, so there will be exactly zero I/O cost.
June 15th, 2009 at 3:19 pm
yes, I agree LINQ handles it correctly. It just tricked me some so I thought i’d post it in case someone else runs into the same issue. It’s certainly not obvious.
June 15th, 2009 at 5:12 pm
Whenever you see 1=0 in the TSQL LINQ to SQL generates it means it believes you are testing something that can’t be nullable with something that must be nullable…
[)amien
June 16th, 2009 at 1:45 pm
@peter: OK!
@Damien: I’m not sure I follow..