How to get the most out of LINQ by querying multiple rows in a table
My goal is to get many rows from the translation table. I use an ID to get a subset of the table (say 50 rows), then I use a different ID for the rows I want from that subset. Using typed datasets, I do the following to get the master 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 inspect the code, I found that this method takes about 170ms on a 10 page refresh (220 calls to fd.select ...)
When I rewrote this LINK, it took over 2000ms to do the same job. Here is the LINQ code I used:
IrmDatabaseContext irmDB = new IrmDatabaseContext();
irmDB.tbl_funderTexts.Single(f => f.funderID == funderId && f.eng_code == element).funderText;
Does anyone have a good way to do this with LINQ? After examining the sql server profiler, I saw that LINQ actually generated a single selection for every text I received. (i.e. LINQ = 220 selects from db, tableadapter = 10 method selects)
Solution: After reading on the net, I found that David B was on the right track, although the loop threw me back for quite some time for me. Anyway, the trick, he says, is to use a list, as this actually forces linq to run a database query and cache it locally. http://blogs.msdn.com/wriju/archive/2007/07/17/linq-to-sql-caching-the-query-execution.aspx .
So my solution turned out like this:
List<tbl_funderText> fd = (from tf in irmDB.tbl_funderTexts
where tf.funderID == (int)cpcrow.cpc_fundingPartnerID
select tf).ToList();
Then every time I want an element I do:
fd.Single(f => f.eng_code == element).funderText;
Analyzing ANTS, I found that the time was reduced to 150ms (about the same as the tableAdapter. The SQL Query Analyzer shows that the SQL is only run once.
source to share
Ah, so in the TableAdapter method, you pop out the rows into memory and then query those rows in memory. This is 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
);
}
source to share