MSSQL Projection error

With the release of 3.11, my code was working fine. I upgraded to the 12/13 nightly, and the code is now throwing exceptions. I just upgraded to test out the 12/29 nightly, and it's still broken.

The generated (MSSQL) SQL is:

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
SELECT
  SUM(COALESCE(t0.Amount, 0))
FROM
  Collections t0
INNER JOIN
  CollectionDetails t1
ON
  t0.Id = t1.CollectionId
INNER JOIN
  MachineFields t2
ON
  t1.MachineFieldId = t2.Id
WHERE
  (((t0.CollectionDate >= '1/1/2010 12:00:00 AM' AND t0.CollectionDate <= '12/28/2010 12:00:00 AM') AND EXISTS (
    SELECT
      Locations.*
    FROM
      Locations
    WHERE
      Locations.Id = t0.LocationId AND
      Locations.CompanyId = 1
  )) AND EXISTS (
    SELECT
      t2.*
    FROM
      MachineFields t2
    WHERE
      t2.Id = t1.MachineFieldId AND
      t2.FieldType = 'I'
  ))

Note the bolded line. It should read:

  SUM(COALESCE(t1.Amount, 0))

The MSSQL schema is a simple 1:M.

The LINQ query looks like this:

from c in UnitOfWorkScope.Current.Collections
join cd in UnitOfWorkScope.Current.CollectionDetails on c.Id equals cd.CollectionId
select cd.Amount ?? 0

If you need me to put together a sample, I can. I was hoping this info would be enough to get a quick fix going.

Thanks.

Dan

Hi Dan,

If you could either attach or mail through your model then that should be all we need to investigate this further.

Jeremy

Hi Dan,

Sorry for the delay in getting back to you on this. I cant actually replicate this behavior. When performing the query as you specified in the post above it does perform the projection server side correctly on t1 (CollectionDetails).

However the SQL you posted does look a bit different from the query would be generating - Ive somewhat replicated this, however the projection is done client side for me - are you able to post the actual LINQ query which generates the SQL you specified?

Here is the one I have been using which roughly gets the same SQL (but not the projection list):

var q = from c in unitOfWork.Collections
                join cd in unitOfWork.CollectionDetails on c.Id equals cd.CollectionId
                join m in unitOfWork.MachineFields on cd.MachineFieldId equals m.Id
                where c.CollectionDate >= new DateTime(2010, 1, 1) && c.CollectionDate <= DateTime.Now &&
                      c.Location.CompanyId == 1 && cd.MachineField.FieldType == "I"
                select cd.Amount ?? 0;

Thanks!

Jeremy

I bundled up a test project and sent it to you. Thanks for looking into this.

Thanks Dan,

Have reproed this issue and included an update into the next nightly build (20110112) which correctly supports this.

Jeremy