LINQ: When to use Compiled Queries?

Question

I'd like some expert 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

Answer 1

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.