你的位置:首页 > 数据库

[数据库]容易忽略的括号


注意看以下LINQ的写法.

var modeltest1 = db.SysUsers.Where(m => m.State != 0

&& string.IsNullOrEmpty(User_Name) ? true : m.Name.Contains(User_Name));


 

var modeltest2 = db.SysUsers.Where(m => m.State != 0

&& (string.IsNullOrEmpty(User_Name) ? true : m.Name.Contains(User_Name)));


 

var modeltest3 = db.SysUsers.Where(m =>

string.IsNullOrEmpty(User_Name) ? true : m.Name.Contains(User_Name)

&& m.State != 0);


 

var modeltest4 = db.SysUsers.Where(m =>

(string.IsNullOrEmpty(User_Name) ? true : m.Name.Contains(User_Name))

&& m.State != 0); 


系统翻译为

Modeltest1

SELECT  *

FROM    [dbo].[SysUser] AS [Extent1]

WHERE   ( CASE WHEN ( ( NOT ( ( 0 = [Extent1].[State] )

                                  AND ( [Extent1].[State] IS NOT NULL )

                                 )

                           )

                      AND ( ( @p__linq__0 IS NULL )

                            OR ( ( CAST(LEN(@p__linq__0) AS INT) ) = 0 )

                          )

                    ) THEN CAST(1 AS BIT)

               WHEN ( [Extent1].[Name] LIKE @p__linq__1 ESCAPE N'~' )

               THEN CAST(1 AS BIT)

               WHEN ( NOT ( [Extent1].[Name] LIKE @p__linq__1 ESCAPE N'~' )

                    ) THEN CAST(0 AS BIT)

          END ) = 1


翻译结果,把第一个条件当作了条件运算符的一部分。 State不等于0且User_Name为空或NULL时,条件成立。

 

Modeltest2

 

SELECT  *

FROM    [dbo].[SysUser] AS [Extent1]

WHERE   ( 0 <> [Extent1].[State] )

        AND ( ( CASE WHEN ( ( @p__linq__0 IS NULL )

                            OR ( ( CAST(LEN(@p__linq__0) AS INT) ) = 0 )

                          ) THEN CAST(1 AS BIT)

                     WHEN ( [Extent1].[Name] LIKE @p__linq__1 ESCAPE N'~' )

                     THEN CAST(1 AS BIT)

                     WHEN ( NOT ( [Extent1].[Name] LIKE @p__linq__1 ESCAPE N'~' )

                          ) THEN CAST(0 AS BIT)

                END ) = 1 )


结果正常,过滤出State不为0的数据。

 

Modeltest3

 

SELECT  *

FROM    [dbo].[SysUser] AS [Extent1]

WHERE   ( CASE WHEN ( ( @p__linq__0 IS NULL )

                      OR ( ( CAST(LEN(@p__linq__0) AS INT) ) = 0 )

                    ) THEN CAST(1 AS BIT)

               WHEN ( ( [Extent1].[Name] LIKE @p__linq__1 ESCAPE N'~' )

                      AND ( NOT ( ( 0 = [Extent1].[State] )

                                  AND ( [Extent1].[State] IS NOT NULL )

                                )

                          )

                    ) THEN CAST(1 AS BIT)

               WHEN ( NOT ( ( [Extent1].[Name] LIKE @p__linq__1 ESCAPE N'~' )

                            AND ( 0 <> [Extent1].[State] )

                          )

                    ) THEN CAST(0 AS BIT)

          END ) = 1


翻译结果,根据条件运行符从右向左结合方向,把第二个条件当作子条件。当User_Name为空或NULL时,均条件成立。

modeltest4

SELECT  *

FROM    [dbo].[SysUser] AS [Extent1]

WHERE   ( ( CASE WHEN ( ( @p__linq__0 IS NULL )

                        OR ( ( CAST(LEN(@p__linq__0) AS INT) ) = 0 )

                      ) THEN CAST(1 AS BIT)

                 WHEN ( [Extent1].[Name] LIKE @p__linq__1 ESCAPE N'~' )

                 THEN CAST(1 AS BIT)

                 WHEN ( NOT ( [Extent1].[Name] LIKE @p__linq__1 ESCAPE N'~' )

                      ) THEN CAST(0 AS BIT)

            END ) = 1 )

        AND ( 0 <> [Extent1].[State] )


 

翻译结果同2

 

结论:括号是很重要的,不注意的话,结果会千差万别,至于State != 0

一会翻译为 0 <> [Extent1].[State];

一会翻译为 NOT ( ( 0 = [Extent1].[State] ) AND ( [Extent1].[State] IS NOT NULL ) )

还不清楚是有什么规律,不过看其结果,当其为独立条件时,是翻译为<>的。(至于还有没有例外情况,则未验证)。


 

呃,题外话,日志格式编排小技巧:

Word里编辑,再原格式粘贴,将色块用表格包装一下,则不会出现分散的情况!