Linq to Entities - complex search matching criteria

Question

I have a table of objects in the database, which each have a dictionary of key/value pairs as metadata associated with them. I need to be able to pass in to my function a list of search criteria and return results that match on each of these, including wildcards. Eg passing in

Name: "TEST%" Version: "1" Creator: "jamesk"

Should return all objects created by jamesk, with version 1 and name beginning with TEST.

My code to do this is as follows

itemsFound = (from h in _objectStoreRepository.GetHeaders()
       where
        (from i in h.Items
         where i.CreatedAt <= request.AsAtTime &&
               (from f in request.SearchFilters select f)
                   .All(f =>
                        (from mdp in i.MetaDataPairs
                         where mdp.Key.ToLower() == f.Key.ToLower() &&
                         mdp.Value.ToLower().Like(f.Value.ToLower())
                         select mdp).Any()
                    )          
          select i).Any()
         select h).ToList();

I would like to do it all in one query if possible but trying to get Linq to Entities to do everything in one go is proving tough. The above returns the following error.

ERROR: Failed getting objects. 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.

I think I understand the problem, I am trying to create a set to use in my LINQ to Entities query syntax from request.SearchFilters, which is a dictionary but it is saying it can only create a collection from primitive values.

I understand that, but I cannot see how I can then achieve what I need to do as I need to match on both the key and the value of the pair.

Can anyone suggest how to get this working?

UPDATE: I have gotten a little further, splitting out the KVP as below

itemsFound = (from h in _objectStoreRepository.GetHeaders()
           where
            (from i in h.Items
             where i.CreatedAt <= request.AsAtTime &&
               (from f in request.SearchFilters.Keys select f)
                   .All(k => 
                       (from mdp in t.TradeMetaDataPairs
                        where mdp.Key.ToLower() == k.ToLower() &&
                        mdp.Value.ToLower().Like(request.SearchFilters[k].ToLower())
                           select mdp).Any()
                         )                                                      
                      select t).Any()
                   select th).ToList();

Which appears to be accepted, however I now run into the problem that my extension method Like() isn't accepted.

I wrote this because I needed to simulate the SQL "like" query and there was no way to do this. Contains only simulates %x%, startswith only simulates x%, endswith only simulates %x and nothing simulates x%x. Besides I need them in a single statement. Why isn't there direct support for SQL LIKE in linq to entities? its a native SQL function, surely this should be supported :-/

UPDATE 2: Added my like method

 public static class LikeStringExtension
    {
        public static bool Like(this string toSearch, string toFind)
        {
            return new Regex(@"\A" + new Regex(@"\.|\$|\^|\{|\[|\(|\||\)|\*|\+|\?|\\").Replace(toFind, ch => @"\" + ch).Replace('_', '.').Replace("%", ".*") + @"\z", RegexOptions.Singleline).IsMatch(toSearch);
        }        
    }