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+ "<>" );
}