LINQ加入两个DataTable [英] LINQ join two DataTables

问题描述

您好我有加入使用LINQ两个DataTable的一个问题。
表中有这样的列：

Hi I have a problem joining two DataTables using LINQ. Tables have columns like this:

table1        table2
ID, name       ID, stock
1, item1       1, 100
2, item2       3, 50
3, item3

我使用LINQ加入这样的：

I used linq to join like this:

DataTable dtResult = new DataTable();
dtResult.Columns.Add("ID", typeof(string));
dtResult.Columns.Add("name", typeof(string));
dtResult.Columns.Add("stock", typeof(int));

var result = from dataRows1 in table1.AsEnumerable()
             join dataRows2 in table2.AsEnumerable()
             on dataRows1.Field<string>("ID") equals dataRows2.Field<string>("ID")

             select dtResult.LoadDataRow(new object[]
             {
                dataRows1.Field<string>("ID"),
                dataRows1.Field<string>("name"),
                dataRows2.Field<int>("stock"),
              }, false);
result.CopyToDataTable();

问题是，结果仅显示的ID这是在表2。

Problem is, result only shows IDs which are in the table2.

dtResult
ID, name, stock
1, item1, 100
3, item3, 50

我也需要表现出缺少的项目。这是通缉的结果：

I need to show also the missing items. This is the wanted result:

dtResult
ID, name, stock
1, item1, 100
2, item2, 0  //Prefer if it is "0", otherwise can be left "null"
3, item3, 50

我相信我应该做左外连接，但我没有对LINQ足够的知识。帮助AP preciated。
谢谢！

I believe I should do left outer join, but I do not have enough knowledge about linq. Help appreciated. Thank you!

推荐答案

这会让你默认为0，如果该行不存在表2：

This will let you default to 0 if the row doesn't exist in table2:

var result = from dataRows1 in table1.AsEnumerable()
             join dataRows2 in table2.AsEnumerable()
             on dataRows1.Field<string>("ID") equals dataRows2.Field<string>("ID") into lj
             from r in lj.DefaultIfEmpty()
             select dtResult.LoadDataRow(new object[]
             {
                dataRows1.Field<string>("ID"),
                dataRows1.Field<string>("name"),
                r == null ? 0 : r.Field<int>("stock")
              }, false);

这篇关于LINQ加入两个DataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！