SQL连接中加条件查询的LINQ表达式写法-sql where条件中加if判断

为了提高开发效率，公司的的开发大量采用LINQ To SQL进行开发

在这里把项目中遇上的问题和大家分享一下。(即LINQ to sql 连接加条件查询的问题）

问题描述：

表结构以及相关测试数据脚本如下：

create table MemberInfo
(
MemberID int,
MemberName nvarchar(50)
)
create table MemberSort
(
MemberId int,
SortNumber int,
MemberType int
)
insert into MemberInfo values (1,'A')
insert into MemberInfo values (2,'B')
insert into MemberInfo values (3,'C')
insert into MemberInfo values (4,'D')
insert into MemberInfo values (5,'E')

insert into MemberSort values (1,5,0)
insert into MemberSort values (2,4,0)
insert into MemberSort values (3,3,1)

--Drop table MemberInfo
--Drop table MemberSort

SELECT [t0].[MemberID], [t0].[MemberName]
FROM [dbo].[MemberInfo] AS [t0]
LEFT OUTER JOIN [dbo].[MemberSort] AS [t1] ON ([t1].[MemberType] =0) AND
([t0].[MemberID] = [t1].[MemberID])
ORDER BY
    (CASE
        WHEN [t1].[SortNumber] IS NOT NULL THEN [t1].[SortNumber]
        ELSE 9999
     END)

var data = from m in db.MemberInfo
    join s in db.MemberSort on m.MemberID equals s.MemberID &&
    s.MemberType == 0
    into x
    from cx in x.DefaultIfEmpty()
    orderby cx.SortNumber.HasValue ? cx.SortNumber.Value : 9999
    select m;

var data = from m in db.MemberInfo
    join s in db.MemberSort on m.MemberID equals s.MemberID
    into x
    from cx in x.Where(d => d.MemberType == 0).DefaultIfEmpty()
    orderby cx.SortNumber.HasValue ? cx.SortNumber.Value : 9999
    select m;

SQL连接中加条件查询的LINQ表达式写法

关于我们&条款