10

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

Improve this question
2
  • 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. Commented Sep 22, 2011 at 5:38
  • The table only has a couple thousand rows Commented Sep 22, 2011 at 14:19

4 Answers 4

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

Comments

0

Just include your additional condition in your compiled query. 

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

3 Comments

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.
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.
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.
0

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.