Linq中使用 外连接和右连接

use Test
Create table Student(
ID int identity(1,1) primary key,
[Name] nvarchar(50) not null
)

Create Table Book(
ID int identity(1,1) primary key,
[Name] nvarchar(50)not null,
StudentID int not null
)

insert into Student values('张三')
insert into Student values('李四')
insert into Student values('王五')
select * from student

--张三借的书
insert into Book values('红楼',1)
insert into Book values('大话红楼',1)

--李四借的书
insert into Book values('三国',2)

--王五没借书

--一本错误的记录
insert into Book values('错误时怎样练成的',111)

--左连接
select s.name,b.name from student as s
left join Book as b on s.id=b.studentid

--右连接
select s.name,b.name from student as s
right join Book as b on s.id=b.studentid

要用Linq实现左连接，写法如下

DataClasses1DataContext db = new DataClasses1DataContext();
            var leftJoinSql = from student in db.Student
                              join book in db.Book on student.ID equals book.StudentID into temp
                              from tt in temp.DefaultIfEmpty()
                              select new
                              {
                                   sname= student.Name,
                                   bname = tt==null?"":tt.Name//这里主要第二个集合有可能为空。需要判断
                              };

用Linq实现右连接，写法如下

DataClasses1DataContext db=new DataClasses1DataContext();
            var rightJoinSql = from book in db.Book
                               join stu in db.Student on book.StudentID equals stu.ID into joinTemp
                               from tmp in joinTemp.DefaultIfEmpty()
                               select new { 
                               sname=tmp==null?"":tmp.Name,
                               bname=book.Name

                               };

附加:

DataTable dtTable = new DataTable();
            DataTable dtTable2 = new DataTable();
            DataRow row;
            DataRow row2;
            dtTable.Columns.Add("ID", typeof(string));
            dtTable.Columns.Add("AGE", typeof(string));
            dtTable.Columns.Add("City", typeof(string));

            dtTable2.Columns.Add("ID", typeof(string));
            dtTable2.Columns.Add("AGE", typeof(string));
            dtTable2.Columns.Add("City", typeof(string));

            row = dtTable.NewRow();
            row[0] = "001";
            row[1] = "2012-01-01";
            row[2] = "BJ";
            dtTable.Rows.Add(row);
            row = dtTable.NewRow();
            row[0] = "001";
            row[1] = "2012-01-01";
            row[2] = "TJ";
            dtTable.Rows.Add(row);
            row = dtTable.NewRow();
            row[0] = "001";
            row[1] = "2012-01-01";
            row[2] = "HB";
            dtTable.Rows.Add(row);

            row = dtTable.NewRow();
            row[0] = "002";
            row[1] = "2012-01-01";
            row[2] = "SZ";
            dtTable.Rows.Add(row);
            row = dtTable.NewRow();
            row[0] = "002";
            row[1] = "2012-01-01";
            row[2] = "HG";
            dtTable.Rows.Add(row);
            row = dtTable.NewRow();
            row[0] = "003";
            row[1] = "2012-01-01";
            row[2] = "SZ";
            dtTable.Rows.Add(row);
            row = dtTable.NewRow();
            row[0] = "004";
            row[1] = "2012-01-01";
            row[2] = "SZ";
            dtTable.Rows.Add(row);



            row2 = dtTable2.NewRow();
            row2[0] = "001";
            row2[1] = "2011-01-01";
            row2[2] = "GZ";
            dtTable2.Rows.Add(row2);
            row2 = dtTable2.NewRow();
            row2[0] = "001";
            row2[1] = "2011-01-01";
            row2[2] = "TJ";
            dtTable2.Rows.Add(row2);
            row2 = dtTable2.NewRow();
            row2[0] = "002";
            row2[1] = "2012-01-01";
            row2[2] = "SZ";
            dtTable2.Rows.Add(row2);

            List<stu> st = new List<stu>();
            stu a1 = new stu();
            

            var query = from s in dtTable.Select()
                        join c in dtTable2.Select() on s.Field<string>("ID") equals c.Field<string>("ID") into gc
                        from gci in gc.DefaultIfEmpty()
                       // where s.Field<int>("AGE") > ( gci == null ? 0 : gci.Field<int>("AGE"))
                       // where gci != null
                        where Convert.ToDateTime(s.Field<string>("AGE")) > (gci == null ? Convert.ToDateTime(null) : Convert.ToDateTime(gci.Field<string>("AGE")))
                       
                        select new
                        {
                            //A1=new{
                            //      ID1= s.Field<string>("ID"),
                            //      AGE1 = s.Field<string>("AGE"),
                            //      City1 = s.Field<string>("City")
                            //       }
                            //,
                            //B1=new{
                            //   ID2 = gci == null ? "" : gci.Field<string>("ID"),
                            //   AGE2 = gci == null ? "" : gci.Field<string>("AGE"),
                            //   City2 = gci == null ? "" : gci.Field<string>("City")
                            //   },
                            s,
                            gci,
                      

                            
                        
                        };

           // foreach (var item in query.Select(p => p.B1).Distinct())
           // foreach (var item in query.Select(p => new { p.s ,p.gci}).Distinct())
            foreach (var item in query.Select(p => p.gci).Distinct())
            {
                if (item == null) continue;
                Console.WriteLine(item.Field<string>("ID") + "<>" + item.Field<string>("AGE") + "<>" + item.Field<string>("City") + "<>");
                //  Console.WriteLine(item.ID1 + "<>" + item.AGE1+"<>"+item.City1);

               // Console.WriteLine(item.gci.Field<string>("ID") + "<>" + item.gci.Field<string>("AGE") + "<>" + item.gci.Field<string>("City") + "<>");
                Console.WriteLine("********************************");
                // Console.WriteLine(item.ID2 + "<>" + item.AGE2 + "<>" + item.City2);

            }
            Console.WriteLine("///");

            var vv = dtTable.Select().Select(p => p.Field<string>("ID")).Except(dtTable2.Select().Select(p => p.Field<string>("ID")));

            foreach (var item in vv.Distinct())
            {

                Console.WriteLine(item+ "<>" );
            }