Thursday, September 18, 2008 8:57 PM
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?
Friday, September 19, 2008 1:57 PM
Oh sure!Code Snippet
var queryToDatabase =
from c in db.Customers
List<Customer> theCustomers = queryToDatabase.ToList();
var queryInMemory =
from c in theCustomers
Or this way:Code Snippet
List<Customer> theCustomersB =
.Where(c => c.Name.StartsWith("B"))
List<Customer> theCustomersBY =
.Where(c => c.Name.EndsWith("Y"))
Friday, September 19, 2008 2:00 PM
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();
Sunday, September 21, 2008 1:33 PM
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.
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?
Sunday, September 21, 2008 5:38 PM
If all you want to do is apply multiple filters, you don't need Intersect. Just keep piling on the Where methods.
Monday, September 22, 2008 8:28 PM
Thanks for that. I just assumed that it would be more complex than that