score:0
this question probably won't get a lot of action, so i am posting a related question that has helped me try to find a better solution:
refactoring linq iqueryable expression to remove duplicated portions of queries
here's the code for my particular variation of a solution:
public class adatrainingservice : adatraining.web.models.iadatrainingservice, idisposable
{
private adatrainingentities _context = new adatrainingentities();
public iqueryable<employeelistitem> getemployeelisting()
{
return from e in _context.employees
join evsws in employeevideoaggregatesview() on e.id equals evsws.employeeid
select new employeelistitem
{
id = e.id,
firstname = e.firstname,
lastname = e.lastname,
company = e.company,
haswatchedallvideos = evsws.haswatchedallvideos,
starttime = evsws.starttime,
endtime = evsws.endtime
};
}
private class employeevideoserieswatchingstats
{
public int employeeid { get; set; }
public datetime? starttime { get; set; }
public datetime? endtime { get; set; }
public bool haswatchedallvideos { get; set; }
}
private iqueryable<employeevideoserieswatchingstats> employeevideoaggregatesview()
{
return from ev in _context.employeevideos
group ev by ev.employeeid into mygroup
select new employeevideoserieswatchingstats
{
employeeid = mygroup.key,
starttime = mygroup.min( x => x.starttime),
endtime = mygroup.max( x => x.endtime),
haswatchedallvideos = mygroup.count() == _context.videos.count()
};
}
public void dispose()
{
_context.dispose();
}
}
-- update 5/13/2011 --
the example above performs an inner join and will not work for instances where you want to include all employees even if employeevideoaggregatesview() returns no results, so to allow for left outer joining, i had to tweak the code a bit:
public iqueryable<employeedetails> getemployeelisting()
{
return from e in _context.employees
join evsws in employeevideoaggregatesview() on e.id equals evsws.employeeid into myjoin
from mj in myjoin.defaultifempty()
select new employeedetails
{
id = e.id,
firstname = e.firstname,
lastname = e.lastname,
company = e.company,
badgenumber = e.badgenumber,
title = e.title,
haswatchedallvideos = (mj.haswatchedallvideos == null) ? false : mj.haswatchedallvideos,
starttime = mj.starttime,
endtime = mj.endtime
};
}
score:0
// don't count every time
var totalcount = _context.videos.count();
from e in _context.employees
let haswatchedallvideos =
totalcount ==
_context.employeevideos.count(ev => ev.employeeid == e.id && ev.endtime.hasvalue)
// count just once per employee
let employeevideos = _context.employeevideos.count(ev => ev.employeeid == e.id)
let endtime = haswatchedallvideos ? employeevideos.max() : null
let starttime = haswatchedallvideos ? employeevideos.min() : null
select new employeelistitem
{
id = e.id,
firstname = e.firstname,
lastname = e.lastname,
company = e.company,
haswatchedallvideos = haswatchedallvideos,
starttime = starttime,
endtime = endtime
};
Source: stackoverflow.com
Related Query
- Refactoring LINQ to Entities queries with let variables and sub-queries
- SQL to LINQ with sub joins and sub queries
- Problem with LINQ to Entities and String.StartsWith
- Enumerable.Empty<T>().AsQueryable(); This method supports the LINQ to Entities infrastructure and is not intended to be used directly from your code
- IN and NOT IN with Linq to Entities (EF4.0)
- Getting COUNT and SKIP TAKE in one operation with Linq to Entities
- Refactor Linq code and "LINQ to Entities does not recognize the method"
- Count of distinct with linq to entities and custom IEqualityComparer
- Only parameterless constructors and initializers are supported in LINQ to Entities in linq join with tuples
- C# - Linq optimize code with List and Where clause
- Stubbing Code for Test With Linq Expressions and Lambdas
- Linq sub query when using a repository pattern with EF code first
- Insert into multiple tables (one to one relationships) with Linq queries and SQLite
- LINQ to Entities Any() and Contains() slow with small list
- Write Dynamic LINQ queries for sorting and projecting with EF Core
- Is it possible to prevent LINQ from using variables in queries and instead use string literals?
- LINQ to Entities performance issue with Where and Contains
- dealing with Linq XML and complex queries
- Silverlight and RIA Services with LINQ-to-SQL DataContext - Unable to use client side linq queries
- Grouping and Sum some field with Sub query in LINQ
- The famous old issue of LINQ to Entities queries with filter on nullable fields
- LINQ with sub select query using sum and group by?
- LINQ query - Multiple where with multiple sub queries based on int array
- Linq performance with subqueries and LET
- Select everything and let with LINQ
- How can I join data with Linq to Entities and WCF Data Services?
- Joining or Including multiple tables with Entity Framework Core LINQ to Entities - One to Many and then Many to One
- How to write a LINQ query with inner join conditions and IN sub query
- How to write sub queries with LINQ
- Join into with column and variable value compared using Linq or Entity Framework queries
More Query from same tag
- Generating a LINQ query using Expression Trees
- XDocument to Dictionary
- Writing a sub query in LINQ with Top X
- Remove elements in a list considering duplicated subelements
- Adding rows from one IEnumerable to another based on conditions
- Are values in LINQ expressions passed by reference?
- Extract words in brackets distinctly and randomized
- Adding where clause to nested Linq selects
- Converting a LINQ query into a Dictionary<string, string[]>
- C# LinqToSQL how Select new just first table from 2 tables from CSV file
- LINQ Equivalent MYSQL Query
- Get a page from a collection without going past the last page?
- ASP.NET MVC getting Distinct Values From Database using LINQ
- How to count below extensions? by using c#
- Using a native SQL Server function in Nhibernate?
- Extract bits that are set into a HashSet<uint>?
- C# Groupby then Sum after Split CSV (no Headers)
- C# - If list contains list
- How to compare IEnumerable<string> for null in Linq query
- How to get the count of values in table using linq
- Filter List of a model using linq
- Does place of Where clause matter in Linq query?
- How to display the sum of the rest of the Transaction types
- Sum of 2 values in same lambda?
- Error when using XDocument in code behind
- Create XML from CSV using LINQ and XELEMENT
- Searching a list of strings in C#
- Add Column to current DataTable with linq
- Query DTO objects through WCF with linq to sql backend
- Select with formatted string into object using Linq