score:21
the iqueryable<dailyresult>
dtable
will attempt to load the entire query result into memory when enumerated... before any iterations of the foreach loop. it does not load one row during the iteration of the foreach loop. if you want that behavior, use datareader.
score:0
i would suggest using sql instead to modify this data.
score:1
use .asnotracking() - it tells dbentities not to cache retrieved rows
using (var conn = new dbentities() { commandtimeout = 600*100})
{
var dtable = conn.dailyresults
.asnotracking() // <<<<<<<<<<<<<<
.where(dr => dr.dailytransactiontypeid == 1);
foreach (var dailyresult in dtable)
{
//math here, results stored in-memory, but this table is very small.
//at the very least compared to stuff i already have in memory. :)
}
}
score:2
using datareader is a step backward unless there is a way to use it within linq. i thought we were trying to get away from ado.
the solution suggested above works, but it's truly ugly. here is my code:
int itake = 40000;
int iskip = 0;
int iloop;
ent.commandtimeout = 6000;
while (true)
{
iloop = 0;
iqueryable<viewclaimsbinfo> iinfo = (from q in ent.viewclaimsbinfo
where q.workdate >= dtstart &&
q.workdate <= dtend
orderby q.workdate
select q)
.skip(iskip).take(itake);
foreach (viewclaimsbinfo qinfo in iinfo)
{
iloop++;
if (lstclerk.contains(qinfo.clerk.substring(0, 3)))
{
/// various processing....
}
}
if (iloop < itake)
break;
iskip += itake;
}
you can see that i have to check for having run out of records because the foreach loop will end at 40,000 records. not good.
updated 6/10/2011: even this does not work. at 2,000,000 records or so, i get an out-of-memory exception. it is also excruciatingly slow. when i modified it to use oledb, it ran in about 15 seconds (as opposed to 10+ minutes) and didn't run out of memory. does anyone have a linq solution that works and runs quickly?
score:7
you call ~10gb smallish? you have a nice sense of humor!
you might consider loading rows in chunks, aka pagination.
conn.dailyresults.where(dr => dr.dailytransactiontypeid == 1).skip(x).take(y);
Source: stackoverflow.com
Related Query
- Iterating through IQueryable with foreach results in an out of memory exception
- Iterating over IQueryable with forEach vs. calling ToList() and then doing forEach - performance
- XDocument + IEnumerable is causing out of memory exception in System.Xml.Linq.dll
- Antlr exception with message "plan b" when walking IQueryable of NHibernate entities
- IQueryable with Foreach on the result
- Read huge table with LINQ to SQL: Running out of memory vs slow paging
- Why am I getting different results filtering with foreach vs LINQ .Where()?
- How can I iterate through a collection with Foreach to build an XDocument?
- LINQ GroupBy with SQLite.EF6 results in 'APPLY joins are not supported' exception
- C# \ avoid out of memory exception
- .Except / Yield return out of memory exception
- Is looping through IQueryable the same as looping through a Datareader with Reader.Read() in C#
- LINQ + join with nested foreach razor output writing out title lines from groupby
- Speeding up iterating through two foreach loops
- Iterating through LinQ to Entities results
- Subscribing to a event in a closure while iterating through a collection results in the last item subscribing to the event
- Out Of Memory Exception when finding combinations of List of string
- Linq C# - Join from RAM with Database cause out of memory
- ForEach loop with Lambda expression in Razor code MVC 5 For List<T>
- The wait operation timed out exception with Linq
- ASP.NET Deserialize JSON with LINQ and loop through results
- Getting an index was out of range exception with NHibernate
- Linq 2 Entities bombs out with no exception depending on query construction method
- Show null results through the LEFT JOIN with Linq
- LINQ: Call Stored Procedure and Join its results with IQueryable
- Issue with the Foreach loop, coming out of loop once find the first first condition
- c# linq to sql iterating through left join results
- Iterating through a list of elements inside an element with a specific index
- LINQ NotSupportedOperation exception with Code First
- Replace all values in IQueryable with string based on results using LINQ
More Query from same tag
- How can I access elements in a LINQ query that is utilizing a groupBy?
- Removing attribute using LINQ to XML does not persists
- How to build expression tree for Contains<T>
- How can I split a List<T> into two lists, one containing all duplicate values and the other containing the remainder?
- EF tables with Many to many relationship, Will this consume memory?
- Merging two tables and concatenating the values using linq
- Linq to Nhibernate Bulk Update Query Equivalent?
- How to do a Expression.New for a generic type where GenericType Arguments are not know at compile time
- linq exression returning value
- LINQ Combine Queries
- How to query related objects with nhibernate using contains
- Group a list of url strings based on folder structure
- How to take count from DB by LINQ and where
- Finding out time before a set period of tme before a meeting
- Constructing a Sorted List with priority items
- sql joining two tables on one string match
- implement repository in asp.net mvc 5
- Could not translate expression
- Better code for avoiding one dictionary - Case Sensitivity Issue
- How to do an outer join in LINQ?
- Convert SQL - Linq with SQL IN
- Join LINQ tables based on model definition
- Linq group by optimization
- Use a delegate for the equality comparer for LINQ's Distinct()
- C# Using LINQ to select max ID of row
- EF LINQ to SQL is getting whole table from DB instead of selection - where clause contains list with Any statement
- SQL code to join to, and sum data in, a table referenced by comma delimited keys
- What is the logic here with LINQ C# query?
- How to assign LINQ Query to a variable and then use it later in the code
- 'Invoke' node is not supported in LINQ when nesting Expression Functions