LINQ: When to use Compiled Queries?

I'd like some expe开发者_Go百科rt advice on this. I've used compiled queries before, but for this particular case, i'm not sure whether it's appropriate.

It's a search form where the query changes and is dependent on what is being searched on.

static Func<DBContext, int, IQueryable<Foo>> Search = CompiledQuery.Compile(
    (DBContext db, int ID) =>
        db.Person
            .Where(w => w.LocationID = ID)
            .Select(s => 
                new Foo 
                { 
                    Name = s.PersonName, 
                    Age = s.Age,
                    Location = s.LocationName,
                    Kin = s.Kin
                }));

Now if someone fills in the search box, i want to extend the query by adding another Where statement to the query:

var query = Search(context, 123);
query = query.Where(w => w.Name.Contains(searchString));

So my question is, is it returning all the results where LocationID == 123, then checking the results for a searchString match? Or is it actually extending the compiled query?

If it's the former (which i suspect it is), should scrap the CompiledQuery and just create a method that extends the query then return it as a list?

Also, what are the best practices for CompiledQuery usage and is there a guideline of when they should be used?

Note: I'm using the above in an ASP.NET website with Linq to SQL. Not sure if that makes any difference.

Thanks

The problem is that the compiled query is set in stone; it knows what SQL it will run against the database. The lambda expression is lazy loaded however, and cannot modify the compile query as it is being run during run time. The bad news is that it will return all of the records from the database, but it will query those records in memory to further refine them.

If you want to compile the query then I would suggest writing two queries with different signatures.

As far as I know, it is good practice to compile your query once, that is the whole point of pre-compiled query(and that's why your pre-compiled query is static), it saves time to compile that query into SQL. If it extend that pre-compiled query, then it is compiling that query again, which you loose gains.

Query result on result (your query variable) is no longer LINQ to SQL.

Just include your additional condition in your compiled query.

DB.Person.Where(w => w.LocationID == ID 
                     & (searchString=="" || w.Name.Contains(searchString)))

If i am right then you need some dynamic where clause in linq. So for that i would suggest go this way

IEnumerable list;

if(condition1)
{
  list = Linq Statement;
}

if(condition2)
{
  list = from f in list where con1=con && con2=con select f;
}

if(condition3)
{
 list = from n in list  con1=con && con2=con select f;
}

I hope you got my words.