field value from a table using linq c#

Question

There are 2 identical tables with same name on different servers. Let say Table1 and Table2. I need to compare the tables and have to report if any field value is not matching. I'm using C# and Entity framework. I was able to retrieve the rows of the tables(recordsets) but how can i check each and every field of the datarow. The number and name of columns in both tables are same and are in same order. There are such several tables. I need to fetch the field values from each table and compare with same field from another table. How can i retrieve field value using EF and Linq. If its complex using EF, is there any other approach to access tables and compare field values.

Task to perform: Compare Table1.Row[1]Column[2].value with Table2.Row[1]Column[2].value

var query1 = (from prod in con1.Products
                          select new { }).ToList();    
var query2 = (from prod in con2.Products
                              select new { }).ToList();

                for (int i = 0; i < query1.Count; i++)
                {
                    Console.WriteLine(query1.GetType());
                    if (query1[i] != query2[i])
                    {
                        Console.WriteLine("Data not matching at");

                    }
                }

You should have an attempt at this yourself, then if you get stuck post the code that has the error and we can help you. — Ben Clarke, Jan 14 '16 at 13:56
Thanks for suggestion @BenClarke query1 and query2 contains the datarows. but when inspected the object query1/query2, you will see data with Anonymous type. How can i retrieve the data from query1/query2. — Ashi, Jan 14 '16 at 14:26

score 0 · Answer 1 · answered Jan 14 '16 at 15:20

Given that each object has the same properties, what you need to do is to iterate over all the properties for both objects and compare values.

So, create a map of properties:

var sourceProperties = typeof(DbModel1.Product).GetProperties();
var destinationProperties = typeof(DbModel2.Product).GetProperties();
var commonProperties = sourceProperties
    .Join(destinationProperties,
        propInfo => propInfo.Name,
        propInfo => propInfo.Name,
        (source, dest) => new KeyValuePair<PropertyInfo, PropertyInfo>(source, destination))
    .ToArray();

After that, create a method to compare the value of common properties for two objects.

public bool AreEqual(IEnumerable<KeyValuePair<PropertyInfo, PropertyInfo>> properties,
    DbModel1.Product left,
    DbModel2.Product right)
{
    foreach(var property in properties)
    {
        var leftValue = property.SourceProperty.GetValue(left, null);
        var rightValue = property.DestinationProperty.GetValue(right, null);
        if(!leftValue.Equals(rightValue))
            return false;
    }
    return true;
}

Now, just Zip query1 and query2 and select the items that don't match:

var differentItems = query1.Zip(query2,
        (first, second) => new { First = first, Second = second})
    .Where(x => !AreEqual(commonProperties, x.First, x.Second));

field value from a table using linq c#

1 Answers1