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 Query
- Insert/Select with LINQ - getting around the entity construction in a query
- Linq to Sql NotSupportedException "The explicit construction of the entity in a query is invalid" when selecting new Type of other Database Table
- How to write aggregate query in LINQ reusing part of the select code
- Select the most recent date in a LINQ query with an inner join
- How to run linq query with select when a primary key column returns null and thus the related attribute is null
- Can a linq query using join do the select part using a lambda function with no extension methods?
- Select the Object with linq that best fit the Query strings
- Where clause in complex linq query giving the following error "A query body must end with a select clause or a group clause linq"
- Proper Linq Query for objects with many to many relation ship generated with code first entity framework
- Is there any overhead with LINQ or the Entity Framework when getting large columns as part of an entity?
- 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 query throws entity or complex type cannot be constructed in a linq to entity, even when i remove the class name just using select new { ..}
- Entity Framework Code First - The entity or complex type cannot be constructed in a LINQ to Entities query
- How to select only the records with the highest date in LINQ
- Should the order of LINQ query clauses affect Entity Framework performance?
- Select only the lowest values with Linq
- How to insert a record with LINQ and C# and return the Primary Key of that record
- 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
- Convert string to int in an Entity Framework linq query and handling the parsing exception
- Deleting multiple records with Entity Framework using a single LINQ query
- Getting stored procedure output parameter with LINQ and Entity Framework
- The entity or complex type ... cannot be constructed in a LINQ to Entities query
- why .ToList().Distinct() throws error but not the .Distinct().ToList() with linq query
- Entity Framework with Oracle using odp.net not taking parameters in linq query
- Is there any way to create a LINQ query as a variable without having the data source (yet)?
- The entity or complex type cannot be constructed in a LINQ to Entities query
- C# LINQ Select objects with the same value of one property join values of other
More Query from same tag
- Extension for Umbraco /base
- Get string range in Lambda Expression
- Concatenate string property of a custom object from json array
- Display a single message with records when Id exists in C# Linq
- Dynamic Expressions LINQ Select - SelectMany from nested collection
- LINQ query finding the minimum value of one column
- Return value of property based on property name
- Using ternary operation without else in linq?
- Find n-level nested SubDocument MongoDB
- Is there a more succinct way of adding items to a collection property using LINQ?
- How to only select items from a list with certain property set to true
- Is it possible to recreate this statement without using a foreach?
- How to left join multiple tables with LINQ
- Querying JSON from C#
- How can I control all of these situations that I want to find no reservation at that selected checkin and checkout time?
- LINQ Lambda Expression (Fluent Programming) to retrieve an average of multiple occurrences
- Select From DataTable with Multiple Conditions
- LINQ Join inside Join, how to?
- Trying to get a dynamic order by clause in Linq to Entities
- How to restore the quantity and consumption fields to their original numbers in the Item table after deleting an Order with Items
- How can store variable while select query in LINQ?
- How to dynamically GroupBy using Linq
- Returning single value from SQL Stored Procedure using LINQ
- Need to combine two lambda expressions in one to execute two separate conditions of one parameter
- LINQ Returning a set of results
- How to join Enum with LINQ Query
- Linq to SQL with multiple Unions
- How to group one row values in Listview
- Fastest algorithm to compare two collections or list
- Learning the Entity Framework