Another instance of bad SQL generated

I have another situation where incorrect SQL is being generated. This time it is a little complicated (One of the classes involves Class Table Inheritance)

Derived IS A Base Base HAS Many Children

(http://www.freeimagehosting.net/l9bed.jpg)

and the following LINQ query

from b in unitOfWork.Deriveds
select new {b.Name, Count = b.Childrens.Count()}

the following SQL is generated (by the SQL2008 provider)

SELECT
  Base.Name AS [Base.Name],
  Base.CountChildrens AS [Base.CountChildrens]
FROM
  (
    SELECT
      sxt0.Id AS [Id],
      sxt0.IsDerived AS [IsDerived],
      sxt0.Name AS [Name],
      sxt0.DerivedProperty AS [DerivedProperty],
      (SELECT
        COUNT(sx_CountChildrens.Id) AS [sx_CountChildrens.Id]
      FROM
        (
          SELECT
            Children.Id AS [Id],
            Children.BaseId AS [BaseId]
          FROM
            Children
        )
        sx_CountChildrens
      WHERE
        sx_CountChildrens.BaseId = sxt0.Id
      ) AS CountChildrens
    FROM
      Base sxt0
  )
  Base
WHERE
  Base.IsDerived = True

So, you can see it has assumed the properties of the derived table are part of the base table (maybe code is mixed up with STI?)

Cheers, Andy

Hi Andy,

It appears that subexpressions did not have any specific handling of CTI structures so this is the cause of the issue you are seeing. I have added in some initial support for this that will be in the next nightly build. I still need to add in some more tests etc but let me know if you run into any issues over the next few days.

Jeremy

Thanks Jeremy, will do.

When do nightlies go live by the way?

Midnight NZ time (give or take a few minutes)

Hi Guys

New nightly has fixed the problem we were experiencing.

Great work!