Difference of two datatables in C# using LINQ

Question

0.00/5 (No votes)

See more:

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
--------------------------

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

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

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

Posted 31-May-16 1:43am

kumari567

Updated 20-Dec-18 22:16pm

v2

Add a Solution

3 solutions

Solution 3

C#

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;
        }

Posted 20-Dec-18 22:17pm

Amit Kumar Mohanty

Solution 2

C#

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

Posted 1-Jun-16 0:34am

kumari567

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Accepted Answer · 2016-05-31T02:51:00

Solution 1

try this

C#

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();

Posted 31-May-16 2:51am

Karthik_Mahalingam

Comments

kumari567 1-Jun-16 1:51am

this code throw an exception.... source table does not contains no row

Karthik_Mahalingam 1-Jun-16 2:19am

validate it

if ((dt1 != null && dt1.Rows.Count > 0) && (dt2 != null && dt2.Rows.Count > 0))
{
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();
}

kumari567 1-Jun-16 5:24am

still I have exception
An exception of type 'System.InvalidOperationException' occurred in System.Data.DataSetExtensions.dll but was not handled in user code

Additional information: The source contains no DataRows.

kumari567 1-Jun-16 5:28am

my code is this
if ((dt != null && dt.Rows.Count > 0) && (myDataTable != null && myDataTable.Rows.Count > 0))
{
dtExtraRowOfXml = 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();
}

Karthik_Mahalingam 1-Jun-16 5:43am

try this..


DataTable dtOutput = new DataTable(); 

        if ((dt1 != null && dt1.Rows.Count > 0) && (dt2 != null && dt2.Rows.Count > 0))
        {
            var temp = 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"])));
            if(temp != null && temp.Count() >0)
              dtOutput = temp.CopyToDataTable();
        }

kumari567 1-Jun-16 5:59am

is this right code or I have to modify it .
Because I dont think that we can directly copy var type temp to datadable
if(temp != null && temp.Count() >0)
dtOutput = temp.CopyToDataTable();

Karthik_Mahalingam 1-Jun-16 6:01am

just copy paste it , it will work. whats harm in trying..
change the value ID to your's

kumari567 1-Jun-16 6:06am

yes I did but some error are coming

Karthik_Mahalingam 1-Jun-16 6:17am

post your code.

kumari567 1-Jun-16 6:35am

sir I posted below please see

Karthik_Mahalingam 1-Jun-16 6:40am

try this

DataTable dtfinalXml;
if (dtExtraRowOfXml.Rows.Count > 0)
{
var temp = myDataTable.AsEnumerable().Union(dtExtraRowOfXml.AsEnumerable(), DataRowComparer.Default);
if (temp != null && temp.Count() > 0)
dtfinalXml = temp.CopyToDataTable();
}

keep breakpoint and check in which line you are getting the error

kumari567 1-Jun-16 6:37am

the code is okk If we have more row in dt1 which is not in dt2 but problem occure when there is same row in both the table

kumari567 1-Jun-16 6:45am

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();
I am getting error in above code when there is no extra row in dt1

Karthik_Mahalingam 1-Jun-16 7:09am

see, you are getting this error because of [.CopyToDataTable() ]
if the datarow[] is null or count is 0 then you will get the error
so before calling that function [.CopyToDataTable() ], validate for not null and count >0

Member 13041105 4-Apr-17 6:04am

what if i want to check all columns if there is a mistmatch ? how do i do taht please assist im new in c#

Karthik_Mahalingam 4-Apr-17 7:49am

same code by adding all the columns condition.

kumari567 1-Jun-16 7:24am

Thanku thanku so much ......U understand the problem very well ...and give good explanation ..thanku so much for your time.. thanku so much ...:)

Karthik_Mahalingam 1-Jun-16 7:26am

welcome :)

kumari567 1-Jun-16 8:21am

:)