怎么实现和sql一样的左连接效果

如何实现和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")
                        };

[其他解释]

引用:

C# code??12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758        static void Main(string[] args)        {             //创建一个User……

是我想要的结果，可以了，谢谢了！
[其他解释]

引用:

C# code??123456789101112131415161718192021222324252627282930313233343536373839 //创建一个UserInfo的数据表            DataTable dt = new DataTable("UserInfo");            //添加id和username列         ……

谢谢了！

查看更多 下一篇