Managed Code within LINQ Pitfall

I thought I’ll post this in case anyone else encounters this problem. The purpose of the method is to get back a set of ClientContacts filtering based on the ClientID and select only those active if the onlyActive parameter is true, else ignore the IsActive filter and return all the records.

Here’s my LINQ query with a simple if-else shortcut within it:

public IQueryable<ClientContact> GetClientContactByClient(int clientID, bool onlyActive)
{
    return from clientContact in this.db.ClientContacts
             where clientContact.Client_AutoID == clientID &&
                      onlyActive ? clientContact.IsActive : true
             select clientContact;
}

Notice line 5? Here’s where I got this code wrong. The above code generates the following SQL statement:

SELECT *
FROM [dbo].[ClientContacts] AS [t0]
WHERE (
    (CASE
        WHEN [t0].[Client_AutoID] = @p0 THEN CONVERT(Int,[t0].[IsActive])
        ELSE @p1
     END)) = 1

If you actually work it out, this SQL statement will always return all records.

Here’s the change I made on the code to fix it.

public IQueryable<ClientContact> GetClientContactByClient(int clientID, bool onlyActive)
{
    return from clientContact in this.db.ClientContacts
             where clientContact.Client_AutoID == clientID &&
                      (onlyActive ? clientContact.IsActive : true)
             select clientContact;
}

Notice the brackets I added? This resulted in the generation of the following SQL statement:

SELECT *
FROM [dbo].[ClientContacts] AS [t0]
WHERE ([t0].[Client_AutoID] = @p0) AND ((
    (CASE
        WHEN @p1 = 1 THEN CONVERT(Int,[t0].[IsActive])
        ELSE @p2
     END)) = 1)

Again, if you work it out, this is the correct SQL statement to achieve what I want. I didn’t know a set of brackets make such a huge difference in the SQL code generation. Do take note of this when you’re finding out why your LINQ query doesn’t work as you might have thought.

Related posts:

1. ManWrap Library – Managed Code in Unmanaged C++!?!
2. Random Insert/Select with T-SQL
3. Code You Should Not Be Writing – Part 2
4. Recursive Lambda with Fixed Point Generator
5. QuickSort in Functional C#