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.

post your loop code and also the runtime lib version/build nr. (see guidelines thread in this forum for more info about the information we need). Your query looks similar to group join queries in our test suite so it should work

Frans,

Apologies on not providing the version information. I don't know how to get the build number but we are using what I understand to be the latest version of the 2.6 runtime libraries. The date on the files is July 10 and were from the installer downloaded by a member of our team last week.

The loop code:

 foreach (var c in q)
 {
      Console.WriteLine("Name: " + c.Name + " cats: " + c.Count);
 }

Please let me know if you would like me to upload detailed trace information as well.

Reproduced

var q = from c in metaData.Customer
        join o in metaData.Order on c.CustomerId equals o.CustomerId into co
        select new { c.CustomerId, Count = co.Count() };

Will look into it. Group joins + aggregates are nasty, as there's no real documentation about what to do in that case, as 'groupjoin' really doesn't exist in SQL. Linq to Sql produces the same query as the one produced in the workaround below.

A workaround for this is the following: var q = from p in md.Product select new ProductWithCount{ Name = p.Name, Count = p.ProductCategoryMaps.Count() };

(the idea is to simply follow the field mapped onto the relation).

So in the case of my query: var q = from c in metaData.Customer select new { c.CustomerId, Count = c.Orders.Count() };

I'll check whether this can be fixed easily, though you should work with the workaround as stated above.

Fixed in next build.

The more and more these edge cases pop up, the more it shows how crappy the inner design of Linq really is, at least how the C# compiler handles it (merging of projections from the 'select' statement into the projection of a join statement... ).

Frans,

Thanks for all of your help.

kvarley wrote:

Frans,

Thanks for all of your help.

Btw, the fixed dll is attached to this thread, (and you get a another bugfix for free! . -> http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=77694&ThreadID=13949

Just updated the DLL and reran my test. Worked like a champ. Thanks again.