How to get the best performance from LINQ querying several rows in a table

Question

my goal is to get lots of rows from a translation table. I use an ID to get a subset of the table (say 50 rows) then I use another ID to the rows I want from this subset. Using typed datasets I do the following to get the main dataset:

funderTextsDS.tbl_funderTextsDataTable fd = 
(funderTextsDS.tbl_funderTextsDataTable)(new funderTextsDSTableAdapters.tbl_funderTextsTableAdapter()).GetData();

Then for each value I want to get:

fd.Select("eng_code = '" + element + "' and funderID = '" + funderID + "'")[0]["funderText"].ToString();

Using ANTS profiler to check the code I found that this method used about 170ms over 10 page refreshes (220 calls to the fd.select...)

When I rewrote this to LINQ it took more than 2000ms to do the same work. Here is the LINQ code I used:

IrmDatabaseContext irmDB = new IrmDatabaseContext();
irmDB.tbl_funderTexts.Single(f => f.funderID == funderId && f.eng_code == element).funderText;

Anyone have a good way of doing this with LINQ? By looking into sql server profiler i saw that the LINQ actually generated a single select for each text i retrieved. (ie LINQ= 220 selects from the db, tableadapter method = 10 selects)

Solution: After having read around the net I found that David B was on the right track, although the for loop threw me off for quite a while. Anyway, the trick as he said, is to use a list as this actually forces linq to run the query against the DB and cache it localy. http://blogs.msdn.com/wriju/archive/2007/07/17/linq-to-sql-caching-the-query-execution.aspx.

So my solution ended up like this:

List<tbl_funderText> fd = (from tf in irmDB.tbl_funderTexts
                      where tf.funderID == (int)cpcrow.cpc_fundingPartnerID
                      select tf).ToList();

Then everytime I want an element I do:

fd.Single(f => f.eng_code == element).funderText;

Analyzing with ANTS I then found that time was reduced to 150ms (about the same as the tableAdapter. SQL query analyzer shows that the SQL is run only one time.

Answer 1

I'm not sure if this will help but try and use a .where instead of .single. Somthing like this:

var data = mDB.tbl_funderTexts.where(f => f.funderID == funderid && f.eng_code == element)
var fundertext = data.single().funderText

Answer 2

Ah, so in the TableAdapter method, you're pulling rows into memory and then querying those in-memory rows further. That's easy to do in LINQ.

myDataContext dc = new myDataContext();
List<FunderText> myList = myDataContext.tbl_funderTexts.ToList();

List<string> result1 = new List<string>();
foreach(var theValue in myValues)
{
  result1.Add(
    myList.First(f => f.funderID == theValue.funderId && f.eng_code == element).funderText
  );
}