Programmatically load a LINQ2SQL partial class

Question

I am working on project that allows a user to add Time to a Task. On the Task I have a field for EstimatedDuration, and my thoughts are I can get ActualDuration from the Time added to the Task.

I have a LINQ2SQL class for the Task, as well as and additional Task class (using partials).

I have the following for my query so far:

    public IQueryable<Task> GetTasks(TaskCriteria criteria)
    {
        // set option to eager load child object(s)
        var opts = new System.Data.Linq.DataLoadOptions();
        opts.LoadWith<Task>(row => row.Project);
        opts.LoadWith<Task>(row => row.AssignedToUser);
        opts.LoadWith<Task>(row => row.Customer);
        opts.LoadWith<Task>(row => row.Stage);
        db.LoadOptions = opts;

        IQueryable<Task> query = db.Tasks;

        if (criteria.ProjectId.HasValue())
            query = query.Where(row => row.ProjectId == criteria.ProjectId);

        if (criteria.StageId.HasValue())
            query = query.Where(row => row.StageId == criteria.StageId);

        if (criteria.Status.HasValue)
            query = query.Where(row => row.Status == (int)criteria.Status);

        var result = query.Select(row => row);

        return result;
    }

What would be the best way to get at the ActualDuration, which is just a sum of the Units in the TaskTime table?

Answer 1

Add a property to your Task partial class, similiar to this:

    public int ActualDuration
    {
        get {
            YourDataContext db = new YourDataContext();
            return
                db.TaskDurations.Where(t => t.task_id == this.id).
                    Sum (t => t.duration);
        }
    }

Then you can reference the actual duration as Task.ActualDuration.

Update: You asked about how to do this with a partial class. Of course it hits the database again. The only way to get data from the database that you don't know yet is to hit the database. If you need to avoid this for performance reasons, write a subquery or SQL function that calculates the actual duration, and use a Tasks view that includes the calculated value. Now, the function/query will still have to aggregate the entered durations for every task row in the result set, so it will still be performance-intensive. If you have a very large task table and performance issues, keep a running tally on the task table. I think that even the partial class solution is fine for several 100,000's of tasks. You rarely retrieve large numbers at once, I would assume. Grid controls with paging only get a page at the time.