get the unmatched records from DataTable1 by comparing DataTable2

Dear All,

I want to get the unmatched records from DataTable1 by comparing DataTable2

eg :

Table 1

CallId Destination Call type

1 15515643 MOSE

2 16464996 MOSE

3 46169789 MOSTT

4 48892269 MOSTT

Table 2

CallId Destination Call type

1 15515643 Local

4 48892269 STD

I want to match the above two table by using column "CallId" and I want the result as Below

(Unmatched records of Table 1)

CallId Destination Call type

2 16464996 MOSE

3 46169789 MOSTT

How can I achive this by using LINQ ?????????

Please Help......

Re: get the unmatched records from DataTable1 by comparing DataTable2

try this

http://www.dotnetspark.com/kb/705-compare-two-datatables-and-get-result.aspx

Re: get the unmatched records from DataTable1 by comparing DataTable2

assuming you have a unique key for the datatables and dt1, dt2 are the datatables.

List<DataRow> DifferentRows = new List<DataRow>();
List<DataRow> dt1Rows = dt1.Rows.OfType<DataRow>().ToList();
List<DataRow> dt2Rows = dt2.Rows.OfType<DataRow>().ToList();
DifferentRows.AddRange(dt1Rows.Where(a => (dt2Rows.FindAll(b => b["CallID"] == a["CallID"]).Count <= 0)));
DifferentRows.AddRange(dt2Rows.Where(a => (dt1Rows.FindAll(b => b["CallID"] == a["CallID"]).Count <= 0)));

try that and see it works for you

Re: get the unmatched records from DataTable1 by comparing DataTable2

Hai,

Thanks for your response.

I use the above code but the system hangs at the execution of the below line

DifferentRows.AddRange(dt1Rows.Where(a => (dt2Rows.FindAll(b => b["CallID"] == a["CallID"]).Count <= 0)));

Plese guide me ...

Re: get the unmatched records from DataTable1 by comparing DataTable2

how many rows do you have in those DataTables.

Re: get the unmatched records from DataTable1 by comparing DataTable2

Hai,

As of now i having 18000 rows. It may chance to more than this..

I feel there may be a error in the Lambda expression.

While i view the same line in the Quick watch i show the exception as "Expression cannot contain lambda expressions"

Re: get the unmatched records from DataTable1 by comparing DataTable2

I think it will take some time to compare as its a row by row comparison.

I'm not sure if there is any optimised way to do.

If you are using Oracle, you can easily do minus query

SELECT A,B FROM DUAL

MINUS

SELECT C,D FROM DUAL

I'll check if there is any other better method to do this

Re: get the unmatched records from DataTable1 by comparing DataTable2

Thanks for your reply.

I don't want to do this in backend (SQL). I try to achive this in LINQ itself..

Once again I thank you..

Re: get the unmatched records from DataTable1 by comparing DataTable2

Hai,

Is there any chance to achive the result by using JOIN (Join the two DataTable) in LINQ ??

Re: get the unmatched records from DataTable1 by comparing DataTable2

Hai,

Is there any chance to achive the result by using JOIN (Join the two DataTable) in LINQ ??

Re: get the unmatched records from DataTable1 by comparing DataTable2

Just try Left Outer Join approach in LINQ

Not sure about performance ..For me its working fine with 18,00 rows table though ..

// Converting your DataTable Table1 to IEnumerable<>
        var table1Values = from c in Table1.AsEnumerable()
                           select new
                           {
                               callID = c.Field<string>("CallId"),
                               destination = c.Field<string>("Destination"),
                               callType = c.Field<string>("Call type")
                           };


        // Converting your DataTable Table2 to IEnumerable<>
        var table2Values = from c in Table2.AsEnumerable()
                           select new
                           {
                               callID = c.Field<string>("CallId"),
                               destination = c.Field<string>("Destination"),
                               callType = c.Field<string>("Call type")
                           };




        // Doing a Left Outer Join on Table1 and Table2
        // And then selecting the rows which have null in right hand side
        var table1OnlyValues = from c in table1Values
                               join d in table2Values
                               on c.callID equals d.callID
                               into cds


                               from f in cds.DefaultIfEmpty()
                               where f == null
                               select new
                               {
                                   callID = c.callID,
                                   destination = c.destination,
                                   callType = c.callType,
                               };




        foreach (var table1onlyValue in table1OnlyValues)
        {
            Response.Write(table1onlyValue.callID + ",");
            Response.Write(table1onlyValue.destination + ",");
            Response.Write(table1onlyValue.callType);
            Response.Write("</br>");


        }

Re: get the unmatched records from DataTable1 by comparing DataTable2

you can try this way which should be faster

IEnumerable<DataRow> dt1Enum = dt1.AsEnumerable();
            IEnumerable<DataRow> dt2Enum = dt2.AsEnumerable();
            DataTable dtUncommon = dt1Enum.Except(dt2Enum).CopyToDataTable();

http://rsanidad.wordpress.com/2007/10/16/linq-except-and-intersect/

it should be mix of both I think.

Re: get the unmatched records from DataTable1 by comparing DataTable2

Hai,

This code returns the first table (whole table rows) as result. In this where I should give the comparator field like "CallId".

where CallId is the column which i have to compare with the second table.

Re: get the unmatched records from DataTable1 by comparing DataTable2

Enumerable.Except<TSource> Method -

Produces the set difference of two sequences by using the default equality comparer to compare values.

So here Both the sets have DataRow Objects.
Say your first set has 4 DataRow objects and your second set have 2 dataRow objects
The Objects in your second set is different from objects in your first set even though they may have similar values.
That is the reason why Except operator returns all the values in the first set.

If you want to compare sequences of objects of some custom data type, you can implement the IEqualityComparer<(Of <(T>)>) generic interface in your class.
if you want this approach to work you may have to create your own custom type, Implement IEqualityComparer interface and use Except operator on this class.

Re: get the unmatched records from DataTable1 by comparing DataTable2

Hai,

This will work for me, thanks.

How can i convert the "var table1OnlyValues" to DataTable object ???

Re: get the unmatched records from DataTable1 by comparing DataTable2

mohanaraj@3485
How can i convert the "var table1OnlyValues" to DataTable object ???

DataTable Dt = new DataTable();


        Dt.Columns.Add("CallId");
        Dt.Columns.Add("Destination");
        Dt.Columns.Add("Call type");


        foreach (var table1onlyValue in table1OnlyValues)
        {
            Dt.Rows.Add(table1onlyValue.callID, table1onlyValue.destination, table1onlyValue.callType);
        }

Re: get the unmatched records from DataTable1 by comparing DataTable2

Hai,

Thanks.

Is any other option to convert the Var to DataTable without looping???

Or Any other option to use the LINQ as IEnuberable<DataRow> as

IEnumerable<DataRow> RoamingQuery = from d in ..............................
select d

for the above case (Your answer for get unmatched records) ??

Re: get the unmatched records from DataTable1 by comparing DataTable2

mohanaraj@3485
Is any other option to convert the Var to DataTable without looping???
Or Any other option to use the LINQ as IEnuberable<DataRow> as

IEnumerable<DataRow> RoamingQuery = from d in ..............................
select d

for the above case (Your answer for get unmatched records) ??

Hi,

Actually For converting LINQ to DataTable, There is no direct solution.

There is a method CopyToDataTable<T>, But that will work only for IEnumerable<DataRows> and you cannot directly cast your LINQ query result which is an IEnumerable<(Anonymous Type)> (In our case)

to IEnumerable<DataRows>

Even if we try a direct cast, It will return null.

However there is a solution mentioned in msdn

Here the idea is to implement CopyToDataTable<T> when the generic type T is not a DataRow.

You need to write two classes like this for extending the CopyToDataTable<T> method:

public static class DataSetLinqOperators
{
    public static DataTable CopyToDataTable<T>(this IEnumerable<T> source)
    {
        return new ObjectShredder<T>().Shred(source, null, null);
    }

    public static DataTable CopyToDataTable<T>(this IEnumerable<T> source,
                                                DataTable table, LoadOption? options)
    {
        return new ObjectShredder<T>().Shred(source, table, options);
    }

}

public class ObjectShredder<T>
{
    private FieldInfo[] _fi;
    private PropertyInfo[] _pi;
    private Dictionary<string, int> _ordinalMap;
    private Type _type;

    public ObjectShredder()
    {
        _type = typeof(T);
        _fi = _type.GetFields();
        _pi = _type.GetProperties();
        _ordinalMap = new Dictionary<string, int>();
    }

    public DataTable Shred(IEnumerable<T> source, DataTable table, LoadOption? options)
    {
        if (typeof(T).IsPrimitive)
        {
            return ShredPrimitive(source, table, options);
        }


        if (table == null)
        {
            table = new DataTable(typeof(T).Name);
        }

        // now see if need to extend datatable base on the type T + build ordinal map
        table = ExtendTable(table, typeof(T));

        table.BeginLoadData();
        using (IEnumerator<T> e = source.GetEnumerator())
        {
            while (e.MoveNext())
            {
                if (options != null)
                {
                    table.LoadDataRow(ShredObject(table, e.Current), (LoadOption)options);
                }
                else
                {
                    table.LoadDataRow(ShredObject(table, e.Current), true);
                }
            }
        }
        table.EndLoadData();
        return table;
    }

    public DataTable ShredPrimitive(IEnumerable<T> source, DataTable table, LoadOption? options)
    {
        if (table == null)
        {
            table = new DataTable(typeof(T).Name);
        }

        if (!table.Columns.Contains("Value"))
        {
            table.Columns.Add("Value", typeof(T));
        }

        table.BeginLoadData();
        using (IEnumerator<T> e = source.GetEnumerator())
        {
            Object[] values = new object[table.Columns.Count];
            while (e.MoveNext())
            {
                values[table.Columns["Value"].Ordinal] = e.Current;

                if (options != null)
                {
                    table.LoadDataRow(values, (LoadOption)options);
                }
                else
                {
                    table.LoadDataRow(values, true);
                }
            }
        }
        table.EndLoadData();
        return table;
    }

    public DataTable ExtendTable(DataTable table, Type type)
    {
        // value is type derived from T, may need to extend table.
        foreach (FieldInfo f in type.GetFields())
        {
            if (!_ordinalMap.ContainsKey(f.Name))
            {
                DataColumn dc = table.Columns.Contains(f.Name) ? table.Columns[f.Name]
                    : table.Columns.Add(f.Name, f.FieldType);
                _ordinalMap.Add(f.Name, dc.Ordinal);
            }
        }
        foreach (PropertyInfo p in type.GetProperties())
        {
            if (!_ordinalMap.ContainsKey(p.Name))
            {
                DataColumn dc = table.Columns.Contains(p.Name) ? table.Columns[p.Name]
                    : table.Columns.Add(p.Name, p.PropertyType);
                _ordinalMap.Add(p.Name, dc.Ordinal);
            }
        }
        return table;
    }

    public object[] ShredObject(DataTable table, T instance)
    {

        FieldInfo[] fi = _fi;
        PropertyInfo[] pi = _pi;

        if (instance.GetType() != typeof(T))
        {
            ExtendTable(table, instance.GetType());
            fi = instance.GetType().GetFields();
            pi = instance.GetType().GetProperties();
        }

        Object[] values = new object[table.Columns.Count];
        foreach (FieldInfo f in fi)
        {
            values[_ordinalMap[f.Name]] = f.GetValue(instance);
        }

        foreach (PropertyInfo p in pi)
        {
            values[_ordinalMap[p.Name]] = p.GetValue(instance, null);
        }
        return values;
    }



}

Then you can directly call the method on our LINQ Query result

        // Create a table from the query.
        DataTable boundTable = table1OnlyValues.CopyToDataTable();

Re: get the unmatched records from DataTable1 by comparing DataTable2

Hai,

Thanks for your reply.

I need your help to Delete the rows present in the DataTable, after filter those record from the DataTable.

eg: In DataTable1 have 100 records by using LINQ I filter the 10 records by condition as below

IEnumerable<DataRow> MobileQuery = (from d in dtBillDetail.AsEnumerable()
where (String.Compare(d.Field<string>("Destination"), "MOBILE", StringComparison.InvariantCultureIgnoreCase) == 0) select d );

I want to detlete the rows present in the "MobileQuery" from DataTable1 using LINQ or Witout Looping those.

Re: get the unmatched records from DataTable1 by comparing DataTable2

you can do this

(from d in dtBillDetail.AsEnumerable()
             where (String.Compare(d.Field<string>("Destination"),
                 "MOBILE", StringComparison.InvariantCultureIgnoreCase) == 0)
             select d).ToList().ForEach(a => dtBillDetail.Rows.Remove(a));

(from d in dtBillDetail.AsEnumerable()
             where (String.Compare(d.Field<string>("Destination"),
                 "MOBILE", StringComparison.InvariantCultureIgnoreCase) == 0)
             select d).ToList().ForEach(a => dtBillDetail.Rows.Remove(a));

If you get any error saying that Enumeration Value cannot be altered blah blah,

you can do this

DataTable dtBillDetailCopy = dtBillDetail.Copy();
            (from d in dtBillDetailCopy.AsEnumerable()
             where (String.Compare(d.Field<string>("Destination"),
                 "MOBILE", StringComparison.InvariantCultureIgnoreCase) == 0)
             select d).ToList().ForEach(a => dtBillDetail.Rows.Remove(a));

Try the first one and see if it works for you.