We're having a problem with the way we are trying to do a particular group join.
Given the following tables:
CREATE TABLE [dbo].[Product](
[Id] [uniqueidentifier] NOT NULL DEFAULT (newsequentialid()),
[Name] [nvarchar](255) NOT NULL,
[Description] [ntext] NULL,
[UrlId] [int] IDENTITY(1,1) NOT NULL
)
CREATE TABLE [dbo].[ProductCategoryMap](
[CategoryId] [uniqueidentifier] NOT NULL,
[ProductId] [uniqueidentifier] NOT NULL
)
We are attempting to get the names of each product along with a count of the categories to which a product has been mapped and are projecting this into another class. This is a contrived example based on a similar issue we were having with another database but tried to simplify the example.
The query in question is:
var q = from p in md.Product
join pc in md.ProductCategoryMap on p.Id equals pc.ProductId into pCats
select new ProductWithCount{ Name = p.Name, Count = pCats.Count() };
When we try to loop over the results of the query, we are getting the following exception:
System.InvalidCastException : Unable to cast object of type 'ProductReviews.DAL.HelperClasses.EntityCollection`1[ProductReviews.DAL.EntityClasses.ProductEntity]' to type 'System.Collections.Generic.IEnumerable`1[ProductReviews.Data.ProductWithCount]'.
We attempted the same query with LinqToSql and got the results we would expect.
We did try to approximate a similar query by using a separate group by clause on the ProductId in ProductCategoryMap but then were unable to reference the product name in the projection.
Thanks.