2

I've been searching here and Google, but I'm at a loss. I need to let users search a database for reports using a form. If a field on the form has a value, the app will get any reports with that field set to that value. If a field on a form is left blank, the app will ignore it. How can I do this? Ideally, I'd like to just write Where clauses as Strings and add together those that are not empty.

.Where("Id=1")

I've heard this is supposed to work, but I keep getting an error: "could not be resolved in the current scope of context Make sure all referenced variables are in scope...".

Another approach is to pull all the reports then filter it one where clause at a time. I'm hesitant to do this because 1. that's a huge chunk of data over the network and 2. that's a lot of processing on the user side. I'd like to take advantage of the server's processing capabilities. I've heard that it won't query until it's actually requested. So doing something like this

    var qry = ctx.Reports
          .Select(r => r);

does not actually run the query until I do:

    qry.First()

But if I start doing:

    qry = qry.Where(r => r.Id = 1).Select(r => r);
    qry = qry.Where(r => r.reportDate = '2010/02/02').Select(r => r);

Would that run the query? Since I'm adding a where clause to it. I'd like a simple solution...in the worst case I'd use the Query Builder things...but I'd rather avoid that (seems complex).

Any advice? :)

LindsayFester
  • 23
  • 3

3 Answers3

2

Linq delays record fetching until a record must be fetched. That means stacking Where clauses is only adding AND/OR clauses to the query, but still not executing.

Execution of the generated query will be done in the precise moment you try to get a record (First, Any etc), a list of records(ToList()), or enumerate them (foreach).

.Take(N) is not considered fetching records - but adding a (SELECT TOP N / LIMIT N) to the query

Marino Šimić
  • 7,318
  • 1
  • 31
  • 61
1

Try checking out this dynamic Linq dll that was released a few years back - it still works just fine and looks to be exactly what you are looking for.

naspinski
  • 34,020
  • 36
  • 111
  • 167
1

No, this will not run the query, you can structure your query this way, and it is actually preferable if it helps readability. You are taking advantage of lazy evaluation in this case.

The query will only run if you enumerate results from it by using i.e. foreach or you force eager evaluation of the query results, i.e. using .ToList() or otherwise force evaluation, i.e evaluate to a single result using i.e First() or Single().

BrokenGlass
  • 158,293
  • 28
  • 286
  • 335
  • Awesome! Thank you. I definitely prefer just adding where clauses, it seems much easier to read/maintain. :) I chose your answer because you were more clear about what would trigger the query to execute. :) – LindsayFester May 09 '11 at 21:45