如何实现和sql一样的左连接效果
//创建一个UserInfo的数据表
DataTable dt = new DataTable("UserInfo");
//添加id和username列
dt.Columns.Add("id");
dt.Columns.Add("username");
//创建行
DataRow dr = dt.NewRow();
dr["id"] = 1;
dr["username"] = "张三";
dt.Rows.Add(dr);
DataRow dr1 = dt.NewRow();
dr1["id"] = 2;
dr1["username"] = "李四";
dt.Rows.Add(dr1);
dt.Rows.Add("3", "王五");
DataTable detailInfo = new DataTable("detailInfo");
detailInfo.Columns.Add("ID");
detailInfo.Columns.Add("phoneNumber");
detailInfo.Columns.Add("addRess");
detailInfo.Rows.Add("1", "135XXXX", "北京");
detailInfo.Rows.Add("2", "136XXXX", "上海");
detailInfo.Rows.Add("5", "186XXXX", "广州");
Console.WriteLine("2个表进行合并");
var query = from t1 in dt.AsEnumerable()
join t2 in detailInfo.AsEnumerable()
on t1.Field<string>("id") equals t2.Field<string>("ID")
select new
{
id = t1.Field<string>("id"),
username = t1.Field<string>("username"),
phoneNumber = t2.Field<string>("phoneNumber"),
addRess = t2.Field<string>("addRess")
};
可以看到query的记录是2条，此写法是找到id相同的记录
我想要的是和sql的left join一样的效果
主表应该是3条记录，右表示2条记录，第三条记录全部都是null
[最优解释]
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
//创建一个UserInfo的数据表
DataTable dt = new DataTable("UserInfo");
//添加id和username列
dt.Columns.Add("id");
dt.Columns.Add("username");
//创建行
DataRow dr = dt.NewRow();
dr["id"] = 1;
dr["username"] = "张三";
dt.Rows.Add(dr);
DataRow dr1 = dt.NewRow();
dr1["id"] = 2;
dr1["username"] = "李四";
dt.Rows.Add(dr1);
dt.Rows.Add("3", "王五");
DataTable detailInfo = new DataTable("detailInfo");
detailInfo.Columns.Add("ID");
detailInfo.Columns.Add("phoneNumber");
detailInfo.Columns.Add("addRess");
detailInfo.Rows.Add("1", "135XXXX", "北京");
detailInfo.Rows.Add("2", "136XXXX", "上海");
detailInfo.Rows.Add("5", "186XXXX", "广州");
Console.WriteLine("2个表进行合并");
var query = from t1 in dt.AsEnumerable()
join t2 in detailInfo.AsEnumerable()
on t1.Field<string>("id") equals t2.Field<string>("ID")
into g
from t in g.DefaultIfEmpty()
select new
{
id = t1.Field<string>("id"),
username = t1.Field<string>("username"),
phoneNumber = t == null ? "" : t.Field<string>("phoneNumber"),
addRess = t == null ? "" : t.Field<string>("addRess")
};
foreach (var item in query)
{
Console.WriteLine("{0}\t{1}\t{2}\t{3}", item.id, item.username, item.addRess, item.phoneNumber);
}
}
}
}
2个表进行合并
1 张三 北京 135XXXX
2 李四 上海 136XXXX
3 王五
Press any key to continue . . .
[其他解释]
static void Main(string[] args)
{
//创建一个UserInfo的数据表
DataTable dt = new DataTable("UserInfo");
//添加id和username列
dt.Columns.Add("id");
dt.Columns.Add("username");
//创建行
DataRow dr = dt.NewRow();
dr["id"] = 1;
dr["username"] = "张三";
dt.Rows.Add(dr);
DataRow dr1 = dt.NewRow();
dr1["id"] = 2;
dr1["username"] = "李四";
dt.Rows.Add(dr1);
dt.Rows.Add("3", "王五");
DataTable detailInfo = new DataTable("detailInfo");
detailInfo.Columns.Add("ID");
detailInfo.Columns.Add("phoneNumber");
detailInfo.Columns.Add("addRess");
detailInfo.Rows.Add("1", "135XXXX", "北京");
detailInfo.Rows.Add("2", "136XXXX", "上海");
detailInfo.Rows.Add("5", "186XXXX", "广州");
Console.WriteLine("2个表进行合并");
var query = from t1 in detailInfo.AsEnumerable()
join t2 in dt.AsEnumerable()
on t1.Field<string>("ID") equals t2.Field<string>("id") into temp
from u in temp.DefaultIfEmpty()
select new
{
id = u==null?"null":u.Field<string>("id"),
username = u==null?"null":u.Field<string>("username"),
phoneNumber = t1.Field<string>("phoneNumber"),
addRess = t1.Field<string>("addRess")
};
Console.WriteLine();
foreach (var v in query)
{
Console.WriteLine("id=" + v.id);
Console.WriteLine("username=" + v.username);
Console.WriteLine("phoneNumber=" + v.phoneNumber);
Console.WriteLine("addRess=" + v.addRess);
Console.WriteLine();
}
Console.Read();
}
[其他解释]
不好意思，修改一下：
var query = from t1 in dt.AsEnumerable()
join t2 in detailInfo.AsEnumerable()
on t1.Field<string>("id") equals t2.Field<string>("ID") into temp
from u in temp.DefaultIfEmpty()
select new
{
id = t1.Field<string>("id"),
username = t1.Field<string>("username"),
phoneNumber = u==null?"null":u.Field<string>("phoneNumber"),
addRess = u==null?"null":u.Field<string>("addRess")
};
[其他解释]
是我想要的结果，可以了，谢谢了！
[其他解释]
谢谢了！