Microsoft Study Bible

There is a need to display all the information of the members (MemberInfo) , Sorting  by SortNumber column of which the Member type is zero in Member Sort table .

 Table structure and the relevant test data scripts are as follows:

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

Using the traditional SQL mode query code

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)

Hope that the same support can be achieved in Linq to SQL .

Firstly, try out the failure code:

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;

The above code revised is as follows:

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;

The key statement is “from cx in x.where (d=>d.MemberType==0).DefaultEmpty();”,which means that it will return the data collection   of which  the MemberType==0 or the value of  MemberType does not exist in Connection Portfolio.

Share This