LEFT JOIN with COUNT by single DB server query

Hello,

Is there a way to perform this query on LightSpeed (either using LINQ or QueryExpressions) like a single DB server call (not using separate DB server calls for calculation count of ContainerItems for each Container:

SELECT
    Containers.SerialNumber,
    COUNT(ContainerItems.ContainerItemId) AS ContainerItemsCount
FROM    Containers
LEFT    JOIN ContainerItems
ON  ContainerItems.ContainerId = Containers.ContainerId
AND ContainerItems.DateOut IS NULL
GROUP     BY    Containers.SerialNumber

You should be able to issue an equivalent query via the querying API using something like this:

var query = new Query(typeof(Container));

query.Projection.Add("SerialNumber");
query.Projection.Add(Entity.Attribute<ContainerItem>("ContainerItemId").Function("COUNT"));

query.Join = Join.Outer<Container, ContainerItem>("ContainerId", "ContainerId");
query.QueryExpression = Entity.Attribute<ContainerItem>("DateOut") == null;    
query.Group = Group.By("SerialNumber");        

var reader = unitOfWork.Project(query);

Jeremy

Thanks a lot for your reply. This code produces t-sql script:

SELECT
  g1.SerialNumber
FROM
(
  SELECT
    t0.SerialNumber AS [SerialNumber]
  FROM
    Containers t0
  LEFT OUTER JOIN
    ContainerItems t1
  ON
    t0.ContainerId = t1.ContainerId
  WHERE
    t1.DateOut IS NULL
  GROUP BY
    t0.SerialNumber
)
g1

Script is almost successful, but doesn't return ContainerItems count, maybe some alias should be added for this value.

Are you able to post the code you are using? It would seem you are missing the following line:

query.Projection.Add(Entity.Attribute<ContainerItem>("ContainerItemId").Function("COUNT"));

In particular make sure you are using the generic version of Entity.Attribute specifying the ContainerItem type.

Jeremy

The code is exactly the same as you've posted for me:

        LightSpeedContext<LightSpeedModelUnitOfWork> context = new LightSpeedContext<LightSpeedModelUnitOfWork>("default");

        var query = new Query(typeof(Container));

        query.Projection.Add("SerialNumber");
        query.Projection.Add(Entity.Attribute<ContainerItem>("ContainerItemId").Function("COUNT"));

        query.Join = Join.Outer<Container, ContainerItem>("ContainerId", "ContainerId");
        query.QueryExpression = Entity.Attribute<ContainerItem>("DateOut") == null;
        query.Group = Group.By("SerialNumber");

        using (LightSpeedModelUnitOfWork uow = context.CreateUnitOfWork())
        {
            var reader = uow.Project(query);
        }

And t-sql which has been produced (copied directly from sql profiler):

SELECT
  g1.SerialNumber
FROM
  (
    SELECT
      t0.SerialNumber AS [SerialNumber]
    FROM
      Containers t0
    LEFT OUTER JOIN
      ContainerItems t1
    ON
      t0.ContainerId = t1.ContainerId
    WHERE
      t1.DateOut IS NULL
    GROUP BY
      t0.SerialNumber
  )
  g1

I'm using MS SQL server 2008. Any ideas what kind of problem could be inside query expressions?

Thats very odd as Ive constructed a model here based on whats present in the query and using the code above it runs fine for me and generates the correct statement with the COUNT() selection. There is a ContainerItemId property on your ContainerItem object correct? If so could you send through your model and I can check it here.

Jeremy

Thanks a lot for your help. The problem was that I have ContainerItem table primary key column named ContainerItemId in the DB (the model was generated based on existent DB) and after generation it was mapped as Id entity property in the model. The correct query expression for me is:

var query = new Query(typeof(Container));

query.Projection.Add("SerialNumber");
query.Projection.Add(Entity.Attribute<ContainerItem>("Id").Function("COUNT"));

query.Join = Join.Outer<Container, ContainerItem>("Id", "ContainerId");
query.QueryExpression = Entity.Attribute<ContainerItem>("DateOut") == null;
query.Group = Group.By("SerialNumber");

This query expression generates exactly what I need. Thanks once more, sorry because of my inattention.