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

How many records/how long does a typical search take? If you're searching 100,000 rows and it takes a couple of seconds, then pre-compiling the LINQ query is an insignificant part compared to proper indexing of your tables. — mellamokb, Sep 22 '11 at 05:38

score 3 · Accepted Answer · answered Sep 22 '11 at 19:21

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.

score 0 · Answer 2 · answered Sep 22 '11 at 03:40

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.

score 0 · Answer 3 · answered Sep 22 '11 at 05:21

0

Just include your additional condition in your compiled query.

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

answered Sep 22 '11 at 05:21

Robert Harvey

178,213
47
333
501

That wasn't the point of the question. I used that as an example of extending the query. So there are other options that conditionally extend the query. – mnsr Sep 22 '11 at 05:54
1

Well, then I suppose the title of your question should have been "Can you extend an already-compiled Linq query without recompiling, and if so, how?" I doubt there is, without some incredibly complex IL gyrations. That's why it's called a "compiled" query. – Robert Harvey Sep 22 '11 at 15:12
Note that if you are querying on a table with only a couple thousand rows, the performance benefit you get from a compiled query is probably insignificant. – Robert Harvey Sep 22 '11 at 18:32

score 0 · Answer 4 · answered Sep 22 '11 at 17:42

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.

LINQ: When to use Compiled Queries?

4 Answers4