Hi,
The SQL generated for the following scenario is not syntactically correct:
- ClassTableInheritance descriminated by FK to a Lookup table
- Query Order By <DescriminatorTable>.<SomeField>
- Query.Page = Page.At(0,20)
- Named Aggregate used
EG:
I get the following error message:
"The multi-part identifier \"Pet.PetTypeId\" could not be bound.\r\nThe multi-part identifier \"Pet.PetTypeId\" could not be bound.
For query to model:
Entity Pet with FK to PetType (schema Id, Description)
Entities Dog, Cat inherit from Pet descriminated by PetTypeId
Pet has FK to Person (not sure that this matters though).
SQL generated is:
exec sp_executesql N'SELECT
Pet.*
FROM
(
SELECT
Pet.Id AS [Pet.Id],
Pet.CreatedOn AS [Pet.CreatedOn],
Pet.DeletedOn AS [Pet.DeletedOn],
Pet.LockVersion AS [Pet.LockVersion],
Pet.NickName AS [Pet.NickName],
Pet.PersonId AS [Pet.PersonId],
Pet.PetTypeId AS [Pet.PetTypeId],
Pet.SafetyLevelId AS [Pet.SafetyLevelId],
Pet.UpdatedOn AS [Pet.UpdatedOn],
Cat.CatBreedId AS [Cat.CatBreedId],
Cat.HasFurBalls AS [Cat.HasFurBalls],
Dog.DogBreedId AS [Dog.DogBreedId],
Dog.LikesToBePatted AS [Dog.LikesToBePatted],
ROW_NUMBER() OVER(ORDER BY PetType.Description) as RowNumber
FROM
Pet
LEFT OUTER JOIN
Cat Cat
ON
Pet.Id = Cat.Id
LEFT OUTER JOIN
Dog Dog
ON
Pet.Id = Dog.Id
INNER JOIN
PetType
ON
Pet.PetTypeId = PetType.Id
WHERE
(Pet.PersonId = @p2 AND Pet.DeletedOn IS NULL)
)
Pet
WHERE
RowNumber > @p0 AND
RowNumber <= @p1;
SELECT
PetType.Id,
PetType.CreatedOn,
PetType.Description,
PetType.UpdatedOn
FROM
PetType
WHERE
EXISTS (
SELECT
Pet.*
FROM
(
SELECT
Pet.*,
ROW_NUMBER() OVER(ORDER BY PetType.Description) as RowNumber
FROM
Pet
INNER JOIN
PetType
ON
Pet.PetTypeId = PetType.Id
WHERE
(Pet.PersonId = @p2 AND Pet.DeletedOn IS NULL)
)
Pet
WHERE
Pet.PetTypeId = PetType.Id AND
RowNumber > @p0 AND
RowNumber <= @p1
);
SELECT
SafetyLevel.Id,
SafetyLevel.CreatedOn,
SafetyLevel.Description,
SafetyLevel.UpdatedOn
FROM
SafetyLevel
WHERE
EXISTS (
SELECT
Pet.*
FROM
(
SELECT
Pet.*,
ROW_NUMBER() OVER(ORDER BY PetType.Description) as RowNumber
FROM
Pet
INNER JOIN
PetType
ON
Pet.PetTypeId = PetType.Id
WHERE
(Pet.PersonId = @p2 AND Pet.DeletedOn IS NULL)
)
Pet
WHERE
Pet.SafetyLevelId = SafetyLevel.Id AND
RowNumber > @p0 AND
RowNumber <= @p1
);
SELECT
CatBreed.Id,
CatBreed.CreatedOn,
CatBreed.Description,
CatBreed.UpdatedOn
FROM
CatBreed
WHERE
EXISTS (
SELECT
Cat.*
FROM
(
SELECT
Cat.*,
ROW_NUMBER() OVER(ORDER BY PetType.Description) as RowNumber
FROM
Cat
INNER JOIN
PetType
ON
Pet.PetTypeId = PetType.Id
LEFT OUTER JOIN
Pet
ON
Cat.Id = Pet.Id
WHERE
(Pet.PersonId = @p2 AND Pet.DeletedOn IS NULL)
)
Cat
WHERE
Cat.CatBreedId = CatBreed.Id AND
RowNumber > @p0 AND
RowNumber <= @p1
);
SELECT
DogBreed.Id,
DogBreed.CreatedOn,
DogBreed.Description,
DogBreed.UpdatedOn
FROM
DogBreed
WHERE
EXISTS (
SELECT
Dog.*
FROM
(
SELECT
Dog.*,
ROW_NUMBER() OVER(ORDER BY PetType.Description) as RowNumber
FROM
Dog
INNER JOIN
PetType
ON
Pet.PetTypeId = PetType.Id
LEFT OUTER JOIN
Pet
ON
Dog.Id = Pet.Id
WHERE
(Pet.PersonId = @p2 AND Pet.DeletedOn IS NULL)
)
Dog
WHERE
Dog.DogBreedId = DogBreed.Id AND
RowNumber > @p0 AND
RowNumber <= @p1
)',N'@p0 int,@p1 int,@p2 uniqueidentifier',@p0=0,@p1=20,@p2='00000000-0000-0000-0000-000000000001'
