Cannot perform LINQ complex object search in Entity Framework

Question

I have entities in the DB which each contain a list of key value pairs as metadata. I want to return a list of object by matching on specified items in the metadata.

Ie if objects can have metadata of KeyOne, KeyTwo and KeyThree, I want to be able to say "Bring me back all objects where KeyOne contains "abc" and KeyThree contains "de"

This is my C# query

 var objects = repository.GetObjects().Where(t =>
                    request.SearchFilters.All(f =>
                        t.ObjectChild.Any(tt =>
                            tt.MetaDataPairs.Any(md =>
                                md.Key.ToLower() == f.Key.ToLower() && md.Value.ToLower().Contains(f.Value.ToLower())
                            )
                        )
                    )
                ).ToList();

and this is my request class

[DataContract]
public class FindObjectRequest
{
    [DataMember]
    public IDictionary<string, string> SearchFilters { get; set; }
}

And lastly my Metadata POCO

[Table("MetaDataPair")]
    public class DbMetaDataPair : IEntityComparable<DbMetaDataPair>
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long Id { get; set; }

        [Required]
        public string Key { get; set; }

        public string Value { get; set; }
}

The error I get is

Error was Unable to create a constant value of type 'System.Collections.Generic.KeyValuePair`2[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]'. Only primitive types or enumeration types are supported in this context.

The issue here is not in the code in your post. Somewhere else you are converting to a KeyValuePair<string, string> before the query has been executed. Can you find the code that does that and post it? — Michael Coxon
I think that's the Dictionary "request.SearchFilters" which you can see in the C# query. I think EF converts the Dictionary to an IList<KeyValuePair> — NZJames
Oh wait, you aren't converting - you are using a kvp in your Any() with f. Save f.Key and f.Value to local variables before using them — Michael Coxon
I'm going to assume that you are using Sql Server and your Collation is set to CI (default), in which case all the ToLower() calls are superfluous. — Erik Philips

Michael Coxon Michael Coxon · Accepted Answer · 2016-10-13T15:55:03

So it looks like the f variable in your query is a KeyValuePair<string, string>. What you need to do is save them as local variables before you use them as a KVP cannot be converted to SQL.

var filters = request.SearchFilters.Select(kvp => new[] { kvp.Key, kvp.Value }).ToArray();

var objects = repository.GetObjects().Where(t =>
    filters.All(f =>
        t.ObjectChild.Any(tt =>
            tt.MetaDataPairs.Any(md =>
                md.Key.ToLower() == f[0] && md.Value.ToLower().Contains(f[1])
                )
            )
         )
    ).ToList();

What you have to remember is that anything you do in LINQ in EF when it is still of the type IQueryable<T> - which is before you call ToList(), ToArray(), ToDictionary() and maybe even AsEnumerable() (I have honestly never tried AsEnumerable()) - must be able to be represented as SQL. That means that only SQL types (string, int, long, byte, date, etc..) and entity types defined in your DbContext can be used in the query. Everything else needs to be broken apart into one of those forms.

EDIT:
You could also try coming from the other side of the query, but you are going to need a few things first..

The Model ...

[Table("MetaDataPair")]
public class DbMetaDataPair : IEntityComparable<DbMetaDataPair>
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long Id { get; set; }

    [Required]
    public string Key { get; set; }

    public string Value { get; set; }

    // this is the navigation property back up to the ObjectChild
    public virtual ObjectChild ObjectChild { get;set; }
}

public ObjectChild
{
    ...
    public ICollection<MetaDataPair> MetaDataPairs { get; set; }

    // this is the navigation property back up to the "Object"
    public virtual Object Object { get; set; }
    ...
}

Now for the query...

public IEnumerable<Object> GetObjectsFromRequest(FindObjectRequest request)
{
    foreach(var kvp in request.SearchFilters)
    {
        var key = kvp.Key;
        var value = kvp.Value;

        yield return metaDataRepository.MetaDataPairs
            .Where(md => md.Key.ToLower() == key && md.Value.ToLower().Contains(value))
            .Select(md => md.ObjectChild.Object)
    }
}

This should execute 'n' number of SQL queries for the number of meta pairs you need to match. The better option would be trying to union them somehow but without some code to play with that might be a mission.

As a side note: I don't really know the names of your classes so I have used > > what I can work out. Obviously Object is not the name of the model..

Cannot perform LINQ complex object search in Entity Framework

1 Answers