score:2
AsEnumerable()
will do the same thing as ToList()
in terms of bringing the processing into linq-to-objects, but won't waste time and memory storing all of them first. Instead when you iterate through it, it'll create the objects one at a time.
As a rule, you should use AsEnumerable()
to move operations from another source into memory, rather than ToList()
unless you really want a list (e.g. if you'll hit the same data more than once, so the list acts a a cache).
So so far we have:
var statistics = (
from record in startTimes
group record by record.startTime
into g
select g;
).AsEnumerable().Select(
g => new e_activeSession
{
workerId = wcopy,
startTime = g.Key.GetValueOrDefault(),
totalTasks = g.Count(),
totalTime = g.Max(o => o.record.timeInSession).GetValueOrDefault(),
/* ... */
});
But there's a bigger problem. You want to be careful with group by
too. When done along with aggregate methods, it's normally okay, but otherwise it can end up being turned into many database calls (one to obtain the different values for the keys, and then one per each value).
Considering the above (with my elision to not mention every single column). Without using AsEnumerable()
(or ToList()
or what have you), since wcopy
is presumably outside of the query entirely (I can't see where it's defined), the SQL produced by the first would be (if it were allowed), something like:
select startTime, count(id), max(timeInSession), /* ... */
from tasks
group by startTime
Which should be handled pretty efficiently by the database (if it isn't, check indices and run the Database Engine Tuning Advisor on the query produced).
With grouping in memory though, it's likely going to first perform:
select distinct startTime from tasks
And then
select timeInSession, /* ... */
from tasks
where startTime = @p0
For each and every distinct startTime
found, passing it in as the @p0
. This can quickly become disastrous no matter how efficient the rest of the code.
We have two choices. Just which is best varies from case to case, so I'll give both, though the second is the most efficient here.
Sometimes our best approach is to load all relevant rows and do the grouping in memory:
var statistics =
from record in startTimes.AsEnumerable()
group record by record.startTime
into g
select new e_activeSession
{
workerId = wcopy,
startTime = g.Key.GetValueOrDefault(),
totalTasks = g.Count(),
totalTime = g.Max(o => o.record.timeInSession).GetValueOrDefault(),
/* ... */
};
We can maybe make it a bit more efficient still by only selecting columns we care about (irrelevant if the above uses every column in the table anyway)
var statistics =
from record in (
from dbRec in startTimes
select new {dbRec.startTime, dbRec.timeInSession, /*...*/}).AsEnumerable()
group record by record.startTime
into g
select new e_activeSession
{
workerId = wcopy,
startTime = g.Key.GetValueOrDefault(),
totalTasks = g.Count(),
totalTime = g.Max(o => o.record.timeInSession).GetValueOrDefault(),
/* ... */
};
I don't think this would be the best case here though. I would use this in cases where I was going to enumerate the groups, and then enumerate each group. In your case where you do an aggregate on every group and don't enumerate through them, it would be better to keep that aggregate work in the database. Databases are good at them, and it'll reduce the total amount of data sent over the wire considerably. The best I can think of in this case is to force a new object other than the entity type that mirrors it, but which is not recognised as an entity. You could create a type just for this (useful if you're doing several variants on this), otherwise just use an anonymous type:
var statistics = (
from record in startTimes
group record by record.startTime
into g
select new{
startTime = g.Key.GetValueOrDefault(),
totalTasks = g.Count(),
totalTime = g.Max(o => o.record.timeInSession).GetValueOrDefault(),
/* ... */
}).AsEnumerable().Select(
d => new e_activeSession
{
workerId = wcopy,
startTime = d.startTime,
totalTasks = d.totalTasks,
/* ... */
});
The obvious downside to this is the sheer verbosity. However, it will keep operations best done in the db, in the db, while still not wasting time and memory like ToList()
does, not hitting the db repeatedly, and dragging the e_activeSession
creation out of the linq2sql and into linq2objects, so it should be allowed.
(Incidentally, the convention in .NET is for class and member names to begin with capitals. There's no technical reason for that, but doing so means you'll match more people's code, including that of the BCL and other libraries you use).
Edit: Second incidentally; I've just seen your other question. Note that in a way, the AsEnumerable()
here is a variant on exactly what was causing the problem with that one. Grok that, and you'll have grokked a lot about the boundaries between different linq query providers.
Source: stackoverflow.com
Related Articles
- Insert/Select with LINQ - getting around the entity construction in a query
- Proper Linq Query for objects with many to many relation ship generated with code first entity framework
- Entity Framework EF Query using LINQ for related Entities - Getting Customers with their orders from specific period of time only
- How to join an entity object with select query in LINQ c#
- LINQ To SQL: Delete entity (by ID) with one query
- LINQ select query with Anonymous type and user Defined type
- Entity Framework - Linq query with order by and group by
- Deleting multiple records with Entity Framework using a single LINQ query
- Getting stored procedure output parameter with LINQ and Entity Framework
- Entity Framework with Oracle using odp.net not taking parameters in linq query
- Entity Framework Core LINQ query with multiple where clauses
- SELECT NEW with a potentially null field using LINQ and Entity Framework
- Reusable linq select query in Entity Framework
- Linq to Entity Framework: Select with only subset of child elements
- Optimize SQL generated by LINQ Query in Entity Framework 4.1 with one-to-many associations
- Wrong select query generated with Entity Framework (mysterious extra column)
- How to use a LINQ query to include index in Select with F#
- How to query many-to-many relationship with 'AND' condition using LINQ and Entity Framework
- Programmatically building a query with LINQ statement with entity framework
- Select data from multiple unrelated tables with LINQ to Entity Framework
- LINQ to XML : A query body must end with a select clause or a group clause
- Getting Href property with anglesharp linq query
- Convert String to DateTime in LINQ query with Entity Framework Core
- Getting Error "Delegate 'System.Func<EntityType,int,bool> does not take 1 arguments" from Entity Framework LINQ query
- Linq to Sql NotSupportedException "The explicit construction of the entity in a query is invalid" when selecting new Type of other Database Table
- Linq query with two joins on inner select in EF
- Exception raised when using a Linq query with Entity Framework
- Linq sub query when using a repository pattern with EF code first
- Mocking (moq) a GetAllAsync linq query with select
- Getting single result from a query with anonymous types in Linq To Sql
- Creating linq expression trees for dynamic objects
- C#: async in LINQ
- Select everything and let with LINQ
- Deserialize json string to C# object in desktop application
- Query Object Pattern vs LINQ
- List of objects with duplicate keys combine field into comma list
- Add on condition with Lambda
- Simple Linq to Sql
- C# median value among group calculation
- Get index of nth element with specific condition
- MVC Search Functionality Implementation
- Custom OrderBy on a List<T>
- I want compare two string and want to get possible matches
- MVC Linq for multiple selects that allow multiple selections
- How to pass comma separated values to LINQ query without for loop
- How to Group Data between DateTime using Linq
- System.Linq.Dynamic not working for the Entity Framework
- Querystring display details
- LINQ to SQL advice - why won't it work?
- Update datatable field based on the value exist in another datatable in Asp.net c#