Help with linq to sql compiled query

I am trying to use compiled query for one of my linq to sql queries. This qu开发者_高级运维ery contains 5 to 6 joins. I was able to create the compiled query but the issue I am facing is my query needs to check if the key is within a collection of keys passed as input. But compiled queries do not allow passing of collection (since collection can have varying number of items hence not allowed).

For instance

input to the function is a collection of keys. Say: List<Guid> InputKeys

    List<SomeClass> output = null;
    var compiledQuery = CompiledQuery.Compile<DataContext, List<Guid>, IQueryable<SomeClass>>(
                        (context, inputKeys) => from a in context.GetTable<A>()
                                     where inputKeys.Contains(a.Key)
                                     select a);

   using(var dataContext = new DataContext())
   {
          output = compiledQuery(dataContext, InputKeys).ToList();
   }

   return output;

The above query does not compile since it is taking list as one of the inputs. Is there any work around or better way to do the above?

---

I'm not sure this is possible using only Linq to SQL. I think you'll need to have a stored procedure or function written on the server that lets you pass in a delimited string representing your list, and parses that returning a table, which you can then compare against.

I think the easiest way to accomplish this would be to write (or have your DBA write) the entire thing as a stored procedure, which will still need to take your list as a string for its argument, calling the aforementioned splitter function. The stored procedure will have its execution plan precompiled by the server.

You can easily make your list into a string using Linq with something like

string[] strings = new string[4] { "1", "2", "3", "4" };

string listOfStrings = strings.Aggregate((acc, s) => acc = acc + ", " + s);

You can turn a list of anything that can be cast to string into an IEnumerable of strings with

IEnumerable<string> strings = list.Cast<string>();

You can then add your stored procedure to your dbml file and call it using Linq to SQL.

I seem to recall that Linq to SQL, in order to remain general, doesn't deal with lists of things, and converts all lists you pass into a parameter for each entry in the list.