JustinLee.sg

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:

[sourcecode language="csharp" highlight="5"]
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;
}
[/sourcecode]

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

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

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.

[sourcecode language="csharp" highlight="5"]
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;
}
[/sourcecode]

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

[sourcecode language="sql"]
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)
[/sourcecode]

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.