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
};

Related Query