Nested LINQ to SQL Queries

Question

Hi,

 

I have a particular coding scenario that I want to code for, but am unsure as to the best approach! Basically I want to run a query against a database. I then want to run a query against the results of the first query, and then finally I want to run another query against the results of the second query, with each query applying a different filter/criteria.

 

The simpliest way for me to do this would be to run the first operation on the database and store the results in memory. Then run each subsequent query on the records held in memory until I am left with the results that I want. However, this is not very efficient.

 

So, my questions is ... is LINQ geared up for the type of operation, and if so, is it very efficient, and can anyone point me in the right direction of how to go about this, preferably with a small example and some basic notes, or a link to a URL?

 

Regards.

 

Ron.

 

Answer 1

Oh sure!

 

Code Snippet

var queryToDatabase =

  from c in db.Customers

  where c.Name.StartsWith("B")

  select c;

 

List<Customer> theCustomers = queryToDatabase.ToList();

 

var queryInMemory =

  from c in theCustomers

  where c.Name.EndsWith("Y")

  select c;

 

 

 

 

Or this way:

 

Code Snippet

List<Customer> theCustomersB =

  db.Customers

  .Where(c => c.Name.StartsWith("B"))

  .ToList();

 

List<Customer> theCustomersBY =

  theCustomersB

  .Where(c => c.Name.EndsWith("Y"))

  .ToList();

 

 

 

 

 

Answer 2

It's unclear if you want to send the extra filters into the database.  If you do, then this is also possible.

 

Code Snippet

var query = db.Customers.Where(c => c.Name.StartsWith("B"));

query = query.Where(c => c.Name.EndsWith("Y"));

query = query.Where(c => c.Name.Contains("A"));

 

List<Customer> results = query.ToList();

 

 

 

Answer 3

Hi David,

 

Firstly, sorry for any confusion with regards to this matter, but hopefully the following will be more useful.

 

OK, your answer with regards to 'query = query.XXX' is exactly what I wanted, ... or so I thought :-(

 

Apparently what is actually required is the ability to perform a more complex query against a database and use the results as a basic for performing another query, and then repeat as necessary.

 

For example:

 

Perform a query against a database to find all customer records where the total sales figure for July was XXX. Then perform a query against these records to find all customer records where the average price of the goods was YYY. This could then be repeated for any other selected criteria.

 

If I was to do this off the top of my head, I would perform several select queries (one for each of the criterias) and then to use the Intersect method to find out which records are in all the returned results. Is there a better way to perform this using LINQ, and how many actual database calls will be made?

Regards

 

Ron.

Answer 4

If all you want to do is apply multiple filters, you don't need Intersect.  Just keep piling on the Where methods.

Answer 5

Hi David,

 

Thanks for that. I just assumed that it would be more complex than that

 

Regards

 

Ron.