Strange SQL generated from LINQ when checking BIT column

Question

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.

Before worrying too much, have you tested the TSQL with tools like SSMS query plan etc to see whether it actually matters? — Marc Gravell
– Marc Gravell, Commented Apr 15, 2014 at 9:23
@MarcGravell it may not matter in the actual execution as the optimiser will probably handle it but I do not like relying in the optimiser. I would also like to understand why it is doing what it is doing. — David Pilkington
– David Pilkington, Commented Apr 15, 2014 at 9:25
@MarcGravell It does matter unfortunately. With the generated NOT ([t0].[IsPersonal] = 1) it's a clustered index scan, with ([t0].[IsPersonal] = 0) it's a non clustered index seek. — GSerg
– GSerg, Commented May 25, 2017 at 9:15

Simon_Weaver · Accepted Answer · 2017-09-16 08:42:08Z

3

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!

edited Sep 16, 2017 at 8:42

answered Sep 15, 2017 at 9:12

Simon_Weaver

147k92 gold badges689 silver badges724 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AndyG Over a year ago

Note that object.Equals(t.Voided, 0) cannot be substituted for !t.Voided in regular code; this is a hack specific for EntityFramework

David Pilkington · Accepted Answer · 2014-04-18 11:47:06Z

2

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"

answered Apr 18, 2014 at 11:47

David Pilkington

13.6k3 gold badges44 silver badges75 bronze badges

Collectives™ on Stack Overflow

Strange SQL generated from LINQ when checking BIT column

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related