Child-tables and Where clauses (or Sum/Count operations)

Hi,

Currently we're testing out LightSpeed 3.0 on a new project and I was wondering if there's any way to change the following behaviour:

var mainTable = uow.MainTable.Select(m => new { m.Id, Count = m.ChildTable.Count() });

Doing the above will not actually do a "select count(*) from ChildTable where blablabla", but instead load all the ChildTable records associated with MainTable. The same behaviour happens if a where clause gets added to ChildTable, for example:

var mainTable = uow.MainTable.Select(m => new { m.Id, Count = m.ChildTable.Where(c => c.Status == Status.New).Count() });

Linq2Sql actually showed the same behaviour and usually we get around it by doing something like this:

var mainTable = uow.MainTable.Select(m => new { m.Id, Count = uow.ChildTable.Where(c => c.MainId = m.Id).Count() });

Or:

var mainTable = uow.MainTable.Select(m => new { m.Id, Count = uow.ChildTable.Where(c => c.MainId == m.Id && c.Status == Status.New).Count() });

While this works, I was wondering if LightSpeed had a "smart" way for this so we can skip the constant adding of "c.MainId == m.Id".

Regards,

Jerremy

*whoops*

My bad, the above example actually almost works how I expected (it generated 4 instead of 2 select statements?).

What didnt work was this:

// First get a record
var project = uow.Projects.FirstOrDefault(p => p.Id == _projectId);

// do some stuff with project

// Now do a query using project
var tasks = project.Select(task => new { task.Id, task.Status, EstimatedHours = task.UserTasks.Sum(ut => ut.EstimatedHours) });

The above query will not do a sum-query but rather a normal select and probably sum in memory.

Also noticed a bug, this query will give a cast-error:

UnitOfWork.Tasks.Where(t => t.ProjectId == _projectId).Select(task => new { task.Id, task.Status, EstimatedHours = task.UserTasks.Sum(ut => ut.EstimatedHours) })

"Invalid cast from 'System.Int32' to 'TPoint.DAL.TaskStatus'."

While this query works fine:

project.Tasks.Select(t => new { t.Id, t.Status, EstimatedHours = t.UserTasks.Sum(ut => ut.EstimatedHours) })

Hi Jerremy,

Your original query of:

var mainTable = uow.MainTable.Select(m => new { m.Id, Count = m.ChildTable.Count() });

should be sufficient to generate a single server side query with a COUNT based subexpression. You shoudnt need to be adding in any other joining hints or criteria for this yourself.

e.g.

var members = UnitOfWork.Members.Select(m => new { m.Id, CommentCount = m.Comments.Count() }).ToList();

will generate:

SELECT
  Members.Id AS [Members.Id],
  Members.CountComments AS [Members.CountComments]
FROM
  (
    SELECT
      sxt0.Id AS [Id],
      [... remaining member columns ...],
      (SELECT
        COUNT(sx_CountComments.Id) AS [sx_CountComments.Id]
      FROM
        (
          SELECT
            Comments.Id AS [Id],
            Comments.MemberId AS [MemberId]
          FROM
            Comments
        )
        sx_CountComments
      WHERE
        sx_CountComments.MemberId = sxt0.Id
      ) AS CountComments
    FROM
      Members sxt0
  )
  Members

Jeremy

Hi Jerremy,

I assume the query you are writing is actually project.Tasks.Select() since project is an already loaded memory of a Project and would not be IQueryable ;)

What you are seeing is that you are actually using LINQ to Objects over the EntityCollection (an IEnumerable<> object) of Tasks which is attached to your project. That is going to execute in memory, and will lazily load the child collections as required to complete the query.

If you wanted to execute this as an entirely server side query, you should either use the Query() IQueryable extension method for entity collections - see details on that here: http://www.mindscape.co.nz/blog/index.php/2009/08/30/linq-queries-over-a-lightspeed-entitycollection/ or set up a new query with a filter on the project Id.

So you could rewrite your query such as:

var tasks = project.Query(p => t.Tasks).Select(task => new { task.Id, task.Status, EstimatedHours = task.UserTasks.Sum(ut => ut.EstimatedHours) });

or:

var tasks = uow.Tasks.Where(t => t.ProjectId == project.Id).Select(task => new { task.Id, task.Status, EstimatedHours = task.UserTasks.Sum(ut => ut.EstimatedHours) });

Both of which should generate you the same result.

Jeremy

I figured as much, I dont really get why EntityCollections are IEnumerable instead of IQueryable though, I personally figured it was due to caching.

However, the Query method seems to be exactly what I was looking for, thanks!