使用LINQ在C＃中使用两个数据表的区别 [英] Difference of two datatables in C# using LINQ

问题描述

II有两个数据表如下

II have two data tables as follows

dtOne
-------------------------
  ID  |   Name
--------------------------
 101  |  ABC
 102  |  XYZ
 103  |  MNO
 104  |  PQR
--------------------------

dtTwo
-------------------------
  ID  |   Name
--------------------------
 101  |  FGH
 102  |  XYZ
 104  |  GPS
--------------------------

我只想把结果作为'在dtOne`和`不在dtTwo`中的数据（dtOne-dtTwo）

I just want the result as data which is `in dtOne` and `not in dtTwo` (dtOne-dtTwo)

dtResult
-------------------------
  ID  |   Name
--------------------------
 103  |  MNO
--------------------------

我怎样才能实现这个目标。



我尝试过：



我使用了LINQ的方法，但是给出了这样的结果

How can i achieve this .

What I have tried:

I have used except method of LINQ but that is giving the result like this

101  |  ABC
103  |  MNO
104  |  PQR
101  |  FGH
104  |  GPS

这意味着匹配除方法

Thats means matching both column in except method

推荐答案

之外的两个列。试试这个

try this

DataTable dt1 = new DataTable();
      dt1.Columns.Add("ID");
      dt1.Columns.Add("Name");
      dt1.Rows.Add(101, "ABC");
      dt1.Rows.Add(102, "XYZ");
      dt1.Rows.Add(103, "MNO");
      dt1.Rows.Add(104, "PQR");

      DataTable dt2 = new DataTable();
      dt2.Columns.Add("ID");
      dt2.Columns.Add("Name");
      dt2.Rows.Add(101, "FGH");
      dt2.Rows.Add(102, "XYZ");
      dt2.Rows.Add(104, "GPS");


     DataTable dtOutput = dt1.Rows.OfType<DataRow>().Where(a=>  dt1.Rows.OfType<DataRow>().Select(k=>  Convert.ToInt32(k["ID"])).Except( dt2.Rows.OfType<DataRow>().Select(k=>  Convert.ToInt32(k["ID"])).ToList()).Contains( Convert.ToInt32( a["ID"]))).CopyToDataTable();

DataSet ds=new DataSet();
          DataSet ds1 = new DataSet();

          ds1.ReadXml(HttpContext.Current.Server.MapPath("XML/myFile.xml"));
          DataTable dt1 = ds1.Tables[0];



    var  query = (from p in dt.AsEnumerable()join t in dt1.AsEnumerable()
                                on p.Field<string>("Station")  equals t.Field<string>("Station")
                            select new
                           {
                               Station = p.Field<string>("Station"),
                               Max_Temp = p.Field<string>("Max_Temp"),
                               Min_Temp = p.Field<string>("Min_Temp"),
                              Weather_Detail = p.Field<string>("Weather_Detail"),
                              DateTime = p.Field<string>("DateTime")

                       });




          DataTable myDataTable = new DataTable();
          myDataTable.Columns.Add("DateTime", typeof(string));
          myDataTable.Columns.Add("Station", typeof(string));
          myDataTable.Columns.Add("Max_Temp", typeof(string));
          myDataTable.Columns.Add("Min_Temp", typeof(string));
          myDataTable.Columns.Add("Weather_Detail", typeof(string));



          foreach (var element in query)
          {
              var row = myDataTable.NewRow();
              row["DateTime"] = element.DateTime;

              row["Station"] = element.Station;
              row["Max_Temp"] = element.Max_Temp;

              row["Min_Temp"] = element.Min_Temp;

              row["Weather_Detail"] = element.Weather_Detail;


             myDataTable.Rows.Add(row);
          }



          DataTable dtExtraRowOfXml = new DataTable();

          if ((dt !=null && dt.Rows.Count > 0) && (myDataTable != null && myDataTable.Rows.Count > 0))
          {
              var temp = dt.Rows.OfType<DataRow>().Where(a => dt.Rows.OfType<DataRow>().
               Select(k => Convert.ToString(k["Station"])).Except(myDataTable.Rows.OfType<DataRow>().Select(k => Convert.ToString(k["Station"])).ToList()).Contains(Convert.ToString(a["Station"]))).CopyToDataTable().AsEnumerable();
              if (temp != null && temp.Count() > 0)
                  dtExtraRowOfXml = temp.CopyToDataTable();

          }

          DataTable dtfinalXml;
          if (dtExtraRowOfXml.Rows.Count > 0)
              dtfinalXml = myDataTable.AsEnumerable().Union(dtExtraRowOfXml.AsEnumerable(), DataRowComparer.Default).CopyToDataTable();

          else
          {
              dtfinalXml = myDataTable.Clone();
          }


          ds.Tables.Add(dtfinalXml);

         // ds.Tables.Add(myDataTable);
          string strXml = ds.GetXml();
          XmlDocument xDoc = new XmlDocument();
          xDoc.LoadXml(strXml);
          xDoc.Save(HttpContext.Current.Server.MapPath("XML/myFile.xml")); //XML is the folder name and myFile.xml is ur new file name

public DataTable getDiffRecords(DataTable dtDataOne, DataTable dtDataTwo)
        {
            DataTable returnTable = new DataTable("returnTable");

            using (DataSet ds = new DataSet())
            {
                ds.Tables.AddRange(new DataTable[] { dtDataOne.Copy(), dtDataTwo.Copy() });

                DataColumn[] firstColumns = new DataColumn[ds.Tables[0].Columns.Count];
                for (int i = 0; i < firstColumns.Length; i++)
                {
                    firstColumns[i] = ds.Tables[0].Columns[i];
                }

                DataColumn[] secondColumns = new DataColumn[ds.Tables[1].Columns.Count];
                for (int i = 0; i < secondColumns.Length; i++)
                {
                    secondColumns[i] = ds.Tables[1].Columns[i];
                }

                DataRelation r1 = new DataRelation(string.Empty, firstColumns, secondColumns, false);
                ds.Relations.Add(r1);

                DataRelation r2 = new DataRelation(string.Empty, secondColumns, firstColumns, false);
                ds.Relations.Add(r2);

                for (int i = 0; i < dtDataOne.Columns.Count; i++)
                {
                    returnTable.Columns.Add(dtDataOne.Columns[i].ColumnName, dtDataOne.Columns[i].DataType);
                }

                returnTable.BeginLoadData();
                foreach (DataRow parentrow in ds.Tables[0].Rows)
                {
                    DataRow[] childrows = parentrow.GetChildRows(r1);
                    if (childrows == null || childrows.Length == 0)
                        returnTable.LoadDataRow(parentrow.ItemArray, true);
                }

                foreach (DataRow parentrow in ds.Tables[1].Rows)
                {
                    DataRow[] childrows = parentrow.GetChildRows(r2);
                    if (childrows == null || childrows.Length == 0)
                        returnTable.LoadDataRow(parentrow.ItemArray, true);
                }
                returnTable.EndLoadData();
            }
            return returnTable;
        }

这篇关于使用LINQ在C＃中使用两个数据表的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！