Strange SQL generated from LINQ when checking BIT

问题:

I have the following LINQtoSQL statement

from t1 in __table1
join t2 in __table2 on t1.Id equals t2.OtherTableId
where t2.BranchId == branchId
      && !t1.IsPersonal
select t1.Id

And this generates the following SQL

SELECT DISTINCT [t0].[Id]
FROM [__table1] AS [t0]
INNER JOIN [__table2] AS [t1] ON [t0].[Id] = [t1].[OtherTableId]
WHERE ([t1].[BranchId] = @p0) AND (NOT ([t0].[IsPersonal] = 1))

Now the issue that I have is this:

(NOT ([t0].[IsPersonal] = 1))

How can I write the LINQ to just say

[t0].[IsPersonal] = 0

NOTE: IsPersonal is not nullable.

回答1:

Right so I think that I have figured it out. The following line

t1.IsPersonal == false

gets optimised as

!t1.IsPersonal

Which is, in turn, literally translated into

(NOT ([t0].[IsPersonal] = 1))

Seems that the optimiser is to "blame"

回答2:

Edit: I may have outsmarted the optimizer but unfortunately when using Linq2Sql the filtered indexes aren't used when the 'filter criteria' is a parameter - which is what this does. So in the end I gave up and switched to a stored procedure. Alternatives were just too icky.

Note: the generated SQL does work with filtered indexes without an index hint, but since I was running in in SSMS the query plan cache doesn't apply.

---

Aha! Finally managed to outsmart the optimizer.

WHERE object.Equals(t.Voided, 0) or

WHERE object.Equals(t.Voided, "false")

Which generates

WHERE ([t0].[Voided] = @p0)

@p0 is sent as a string or number which SQL Server casts to a boolean for you.

This seems to work with a filtered index (and force hint), which is the reason I needed to get around the optimizer in the first place.

Note: For some reason sometimes "0" gives a boolean parse error so 0 or "false" is probably better. Could depend on some subtleties of your query.

I prefer 0 because "false" ends up being a varchar(8000) which is a little overkill!