Comparing Generic Type Properties in ADO.NET LINQ to Entities Queries

Question

The Goal

I'm trying to compare primary keys of two generic types in ADO.NET's LINQ to Entities. The trick is, I can't explicitly compare the properties in the LINQ query because I'm using generic types. Thus, I have to dynamically retrieve the value of the property from both objects in order to compare them.

While I can manually loop through each row in the DB on the application side (my current production approach), I'd still prefer to retrieve direct from the DB using LINQ. It would just keep things simpler I suppose.

The Problem

When trying to use a custom helper, or anything that isn't directly related to the entity itself, the LINQ query fails as not being supported. It is my understanding that when using 'LINQ to Entities', the actual LINQ query is executed using SQL. Being that as it is, I'm extremely limited on how I can handle the conditionals.

Disclamer

This in NOT me asking how to use methods in a LINQ query. Most of the stuff I'm outlining right now will more or less serve for any future visitors who have the same question/issue. I'm aware that it's not possible to handle this the way I'm attempting. My question is whether or not there is a creative way to handle LINQ to Entities queries using generic types. I'm sure the answer will be 'it's not possible', but I'd like to get some more expert knowledge on the topic. Perhaps somebody who has attempted this before.

Exception

System.NotSupportedException: 'LINQ to Entities does not recognize the method
'Boolean CompareKeys[MyEntityType](System.Collections.Generic.List`1[System.String],
MyApp.DB.MyEntityType, MyApp.DB.MyEntityType)' method, 
and this method cannot be translated into a store expression.'

Action

private static void MergeEntityListToDB<T>(List<T> list)
{
    var primaryKeys = new List<string>() { "MyEntityTypeID" };
    foreach (var row in list)
    {
        T ExistingRecord = dbRows
            .Where(x => CompareKeys(primaryKeys, row, x)).FirstOrDefault();

        // Remaining Logic
    }
}

Helper

private static bool CompareKeys<T>(List<string> PKs, T obj1, T obj2)
{
    foreach (var key in PKs)
    {
        var val1 = obj1.GetType().GetProperty(key).GetValue(obj1, null).ToString();
        var val2 = obj2.GetType().GetProperty(key).GetValue(obj2, null).ToString();
        if (val1 != val2)
            return false;
    }

    return true;
}

Answer 1

I think you need to combine LINQ Dynamic Query and possibly LINQKit to achieve your goals. EF (and LINQ to SQL) can't translate reflection to SQL to query a database.