Matching objects by property name and value using Linq

Question

I need to be able to match an object to a record by matching property names and values using a single Linq query. I don't see why this shouldn't be possible, but I haven't been able to figure out how to make this work. Right now I can do it using a loop but this is slow.

Heres the scenario:

I have tables set up that store records of any given entity by putting their primary keys into an associated table with the key's property name and value.

If I have a random object at run-time, I need to be able to check if a copy of that object exists in the database by checking if the object has property names that match all of the keys of a record in the database ( this would mean that they would be the same type of object) and then checking if the values for each of the keys match, giving me the same record.

Here's how I got it to work using a loop (simplified a bit):

public IQueryable<ResultDataType> MatchingRecordFor(object entity)
{
    var result = Enumerable.Empty<ResultDataType>();
    var records = _context.DataBaseRecords

    var entityType = entity.GetType();
    var properties = entityType.GetProperties().Where(p => p.PropertyType.Namespace == "System");

    foreach (var property in properties)
    {
        var name = property.Name;
        var value = property.GetValue(entity);
        if (value != null)
        {
            var matchingRecords = records.Where(c => c.DataBaseRecordKeys.Any(k => k.DataBaseRecordKeyName == name && k.DataBaseRecordValue == value.ToString()));
            if (matchingRecords.Count() > 0)
            {
                records = matchingRecords;
            }
        }
    }

    result = (from c in records
                from p in c.DataBaseRecordProperties
                select new ResultDataType()
                {
                    ResultDataTypeId = c.ResultDataTypeID,
                    SubmitDate = c.SubmitDate,
                    SubmitUserId = c.SubmitUserId,
                    PropertyName = p.PropertyName
                });

    return result.AsQueryable();
}

The last statement joins a property table related to the database record with information on all of the properties.

This works well enough for a single record, but I'd like to get rid of that loop so that I can speed things up enough to work on many records.

Are you able to store the type in the table? That way, you'd not have to investigate properties, as you'd already know what type everything in the table was. — dyson, Aug 05 '14 at 22:22
Thats being done. The problem with that is in some cases we need to compare a view model or DTO to an EF entity, so the properties would all be the same but the type would be different. — b350z, Aug 06 '14 at 14:19
Apologies, I don't think I made that clear. I meant type as in "fully qualified name of the type" rather than as in "instance of a type". If you already know which properties in the database relate to instances of `System.a.b.c`, then you only need to compare the values of those properties, rather than having to check each property to see if the incoming object contains that property. — dyson, Aug 06 '14 at 19:12
Right, but in some cases we would be comparing system.a.b.c to System.a.d.f — b350z, Aug 06 '14 at 20:22

Trevor Merrifield · Answer 1 · 2014-08-07T14:58:52.797

0

using System.Reflection;

public IQueryable<ResultDataType> MatchingRecordFor(object entity)
{
    var records = _context.DataBaseRecords;

    var entityType = entity.GetType();
    var properties = entityType.GetProperties().Where(p => p.PropertyType.Namespace == "System");

    Func<KeyType, PropertyInfo, bool> keyMatchesProperty =
       (k, p) => p.Name == k.DataBaseRecordKeyName && p.GetValue(entity).ToString() == k.DataBaseRecordValue;

    var result =
        from r in records
        where r.DataBaseRecordKeys.All(k => properties.Any(pr => keyMatchesProperty(k, pr)))
        from p in r.DataBaseRecordProperties
        select new ResultDataType()
        {
            ResultDataTypeId = r.ResultDataTypeId,
            SubmitDate = r.SubmitDate,
            SubmitUserId = r.SubmitUserId,
            PropertyName = p.PropertyName
        });

    return result.AsQueryable();
}

Hopefully I got that query language right. You'll have to benchmark it to see if it's more efficient than your original approach.

edit: This is wrong, see comments

edited Aug 07 '14 at 14:58

answered Aug 06 '14 at 16:51

Trevor Merrifield

4,541
2
21
24

When I run this I get the error "Unable to create a constant value of type 'System.Reflection.PropertyInfo'. Only primitive types or enumeration types are supported in this context." If that error wasnt there, wouldnt using Any cause it to return a row where just one key matched? Would it make sense to use All() on the DataBaseRecord keys? – b350z Aug 06 '14 at 19:07
Yes I think you're right that should be All(). Sorry my mind's melting a bit, I've changed it to All() in my answer but I'm not sure what the solution to the error is yet. – Trevor Merrifield Aug 06 '14 at 20:29
When I wrote that answer I didn't understand the distinction between IEnumerable and IQueryable. I am guessing DataBaseRecords is an IQueryable, so the LINQ query is converted to some more implementation specific query language like SQL, with the limitation that only primitives and enumerables of primitives may be referenced from outside the query. A workaround might be to create an `IEnumerable propertyNames` and `IEnumerable propertyValues` *before* the query, but I'm not sure what to do from there. How should they be referenced in the query itself? – Trevor Merrifield Aug 06 '14 at 21:21
Right, its using Entity Framework so the IQueryable DataBaseRecords is mapped to a sql database. I can create those IEnumerables for the names and values, but if there are two separate lists I dont know how I could pair them together so that I can check to see if a given key matches its value. – b350z Aug 06 '14 at 21:33
I can make it work using the two IEnumerable methods like this: `dataBaseRecords.Where(c => c.DataBaseRecordKeys.All(k => propertyNames.Contains(k.DataBaseRecordKeyName) && propertyValues.Contains(k.DataBaseRecordKeyValue)));` but unless I'm mistaken, _any_ property value that would happen to match a db record's key value would be accepted? I can reduce the probability of this happening by first removing all properties whos names aren't in the collection of change key names, i just don't know if that's enough. – b350z Aug 06 '14 at 21:40
This might be worth looking at http://stackoverflow.com/questions/5595338/add-the-where-clause-dynamically-in-entity-framework – Trevor Merrifield Aug 07 '14 at 14:43

Matching objects by property name and value using Linq

1 Answers1