I tried to do a POC of Linq to LLBLgen on our company's internal project. The first problem that I run into is grouping by multiple columns. I didn't see any unit tests in the beta source code for this, so I guess there is no guarantee this works at all.

The query that I used is similar to the one listed below (the query is just an example):

LinqMetaData metaData = new LinqMetaData(adapter);
var q = from c in metaData.Customer
            group c by new {c.Country, c.CustomerId} into g
            select new { SomeKey = g.Key, NumberOfCustomers = g.Count() };

The error message is posted below, the class name or method name may be different from the example above. It seems it is trying to instantiate the anonymous type used in the group by but failed.

================== POC.LinqToLLBL.HelloLinq.Test:

System.MissingMethodException : Constructor on type 'System.Linq.IQueryable1[[System.Linq.IGrouping2[[<>f__AnonymousType0`2[[System.Int64, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]], NextMedium.POC.LinqToLLBL, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null],[NextMedium.Data.EntityClasses.OccurrenceEntity, NextMedium.Data, Version=1.0.3000.28381, Culture=neutral, PublicKeyToken=null]], System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]' not found.

at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) at SD.LLBLGen.Pro.ORMSupportClasses.DataProjectorToCustomClass1.CreateNewInstance(List1 ctorArguments) at SD.LLBLGen.Pro.ORMSupportClasses.DataProjectorToCustomClass1.AddProjectionResultToContainer(IList projectors, Object[] rawProjectionResult) at SD.LLBLGen.Pro.ORMSupportClasses.DataProjectorToCustomClass1.SD.LLBLGen.Pro.ORMSupportClasses. IGeneralDataProjector.AddProjectionResultToContainer(List1 valueProjectors, Object[] rawProjectionResult) at SD.LLBLGen.Pro.ORMSupportClasses.ProjectionUtils.FetchProjectionFromReader(List1 valueProjectors, IGeneralDataProjector projector, IDataReader datasource, Int32 maxNumberOfItemsToReturn, Int32 pageNumber, Int32 pageSize, Boolean clientSideLimitation, Boolean clientSideDistinctFiltering, Boolean clientSidePaging) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\LinqSupport\LinqSupportClasses .NET 3.5\LLBLGenProProvider2.cs:line 154 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\LinqSupport\LinqSupportClasses .NET 3.5\LLBLGenProProviderBase.cs:line 233 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\LinqSupport\LinqSupportClasses .NET 3.5\LLBLGenProProviderBase.cs:line 92 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\LinqSupport\LinqSupportClasses .NET 3.5\LLBLGenProProviderBase.cs:line 653 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.Execute() in C:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\LinqSupport\LinqSupportClasses .NET 3.5\LLBLGenProQuery.cs:line 84 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() in C:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\LinqSupport\LinqSupportClasses .NET 3.5\LLBLGenProQuery.cs:line 129 at POC.LinqToLLBL.HelloLinq.Test() in C:\dev\src\POC\LINQToLLBL\HelloLinq.cs:line 38

Code for this is in the library so it should work (it's similar to join into on multiple columns using anonymous types).

You used the latest build ?

I used the build on the 12th. I am trying the new build now. I will let you know in a sec.

Thanks a lot.

I tried it on the release on 19th. It has the same error. Am I missing something?

After digging into it more. I found out where the error is. Below is what I did.

I tried to step into the source code. If I change the query to group by on one column only, everything works.

var q = from c in metaData.Customer
            group c by c.Country into g
            select new { SomeKey = g.Key, NumberOfCustomers = g.Count() };

If I change the query to group by multiple columns

LinqMetaData metaData = new LinqMetaData(adapter); var q = from c in metaData.Customer group c by new {c.Country, c.CustomerId} into g select new { SomeKey = g.Key, NumberOfCustomers = g.Count() };

The code breaks at class DataProjectorToCustomClass<T>: line 141

T newInstance = CreateNewInstance(ctorArguments);

The "T" here is the type of "g" which is the grouping variable. But sadly the type is an interface type System.Linq.IQueryable1[[System.Linq.IGrouping2[[<>f__AnonymousType0`2, I think that's why this line breaks when trying to creating an instance.

I am using the build 03192008. Hope this helps.

Yes, there's a bug in the projector pipeline. The query is ok, the issue is with instantiating the final types.

This for example is similar and also doesn't work: var q = from c in metaData.Customer select new { A = new {c.CustomerId, c.CompanyName} };

because it needs to instantiate an anonymous type (can be another type as well) before it instantiates the real instance to return.

This is only solveable if I compile the whole projection lambda into a delegate. This is done at other places so it's not unknown territory. Then I simply feed the row of values to the delegate and the delegate calls all ctor's and produces the object. I hope to fix this today.

Ok, deep analysis of the code reveals that at least a week of work can be tossed out as it's no longer needed. That's of course a good thing, code becomes simpler, the downside is that I have to refactor some complex areas, so it's not going to be fixed today unfortunately.

The scenario described above is now working, so the ends in the code meet, I now have to clean up a lot of code which can be removed, as it's replaced by a compiled delegate

(edit) Ok, your group by works now too. Still don't think I'll make it today with this code, but we'll see

Thanks a lot for your quick response Otis. This is great news. I am looking forward to the hot fresh new build.

Ok!

After a lot of hard work, all tests again pass plus the nested anonymous types, your group by etc.

There's still code to cleanup, so I won't release an official new build till monday evening, but I'll attach a build to this post so you can try it out to see if something else breaks perhaps. As there's still code to clean up, it might be other code will fail, as with unittests you know you'll cover all the situations with api's like this (as there are infinite possibilities), so the code has to be reviewed again.

(edit) attached

Thank you Otis. I just tried the new binary. The group by that I had definitely works! Woohoo!

Then I tried to make it a little bit more complicated. I may be writing bad queries, so correct me if I am wrong.

1. if I use columns other than the current table in group by, it fails.

var q = from o in metaData.Order group by new {o.CustmerID, o.OrderDetail.SomeOrderDetailColumn} into g select g.Key;

1. if I call an aggregate function on a nullable type, it fails saying cannot instantiate a value type with database null value.

var q = from o in metaData.Order group by o.CustermerID into g select new {ID = g.Key, Sum = g.Sum(a=>(a.SomeNullableValue??0))}

I don't have the source right now. Otherwise I am more than happy to provide you with more information.

Issue 2 has been fixed yesterday, I'm not sure if issue 1 also has been fixed with some additional changes I made to the groupby code due to some issue I ran into as well. I'll check it out.

(edit) I can reproduce issue 1, I can't reproduce issue 2 anymore, as that has been fixed properly in the place where it in general went wrong (a nullable check wasn't ok).

The first issue crashes here with a wrong alias being applied to the correlation relation added to tie the related entity (in my case customer) to the entity being targeted (order). You likely get another error as some core code around groupby was changed yesterday as it didn't behave properly in scenario's where the target was a select into .

(edit) issue 1 is caused by the fact that a correlation relation is added twice: once correctly to the groupby and also once when the key is referenced. This shouldn't be done: the relations are already added when the groupby is handled.

Fixed in next build. Hopefully today

Hi, Otis

I just downloaded the new build and tried my queries. Here is one case it fails.

1. var results = from o in mdata.Order group o by new { o.OrderDate, o.Customer.City } into g select new { Key = g.Key, Sum = g.Sum(o => (o.OrderDetails.Sum(od => od.Quantity * od.UnitPrice))) };

Another case fails too. I know I don't have to create the anonymous type on group by 2. var results = from o in mdata.Order group o by new { o.Customer.City } into g select new { Key = g.Key, Sum = g.Sum(o => (o.OrderDetails.Sum(od => od.Quantity * od.UnitPrice))) };

A simpler case will succeed. 3. var results = from o in mdata.Order group o by new { o.Customer.City } into g select new { Key = g.Key, Sum = g.Sum(o => (o.OrderDetails.Sum(od => od.Quantity * od.UnitPrice))) };

I run these queries against the Northwind database. The entities are generated using the LLBLGen project that comes with the source that you provided.

The error message for case 1 is listed below: POC.LinqToLLBL.HelloLinq.MyFirstTest: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException : An exception was caught during the execution of a retrieval query: The multi-part identifier "LPLA_2.City" could not be bound. The multi-part identifier "LPLA_2.City" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ----> System.Data.SqlClient.SqlException : The multi-part identifier "LPLA_2.City" could not be bound. The multi-part identifier "LPLA_2.City" could not be bound.

at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\RuntimeLibraries 2.6 .NET 2.x\ORMSupportClasses\RetrievalQuery.cs:line 127 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\RuntimeLibraries 2.6 .NET 2.x\ORMSupportClasses\DataAccessAdapterBase.cs:line 1876 at NW26.Adapter.DatabaseSpecific.DataAccessAdapter.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in C:\dev\src\POC\DataAccess\DatabaseSpecific\DataAccessAdapter.cs:line 294 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\RuntimeLibraries 2.6 .NET 2.x\ORMSupportClasses\DataAccessAdapterBase.cs:line 1985 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\RuntimeLibraries 2.6 .NET 2.x\ORMSupportClasses\DataAccessAdapterBase.cs:line 1964 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\RuntimeLibraries 2.6 .NET 2.x\LinqSupportClasses\LLBLGenProProvider2.cs:line 170 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\RuntimeLibraries 2.6 .NET 2.x\LinqSupportClasses\LLBLGenProProviderBase.cs:line 237 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\RuntimeLibraries 2.6 .NET 2.x\LinqSupportClasses\LLBLGenProProviderBase.cs:line 92 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\RuntimeLibraries 2.6 .NET 2.x\LinqSupportClasses\LLBLGenProProviderBase.cs:line 717 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.Execute() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\RuntimeLibraries 2.6 .NET 2.x\LinqSupportClasses\LLBLGenProQuery.cs:line 84 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\RuntimeLibraries 2.6 .NET 2.x\LinqSupportClasses\LLBLGenProQuery.cs:line 129 at POC.LinqToLLBL.HelloLinq.MyFirstTest() in C:\dev\src\POC\LINQToLLBL\HelloLinq.cs:line 34 --SqlException at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\RuntimeLibraries 2.6 .NET 2.x\ORMSupportClasses\RetrievalQuery.cs:line 120

Case 2 and 3 are the same it seems

I'll look into why these things keep failing. It's a pain because the last thing I want is special case code, as that will never guarantee it will work with all cases.

Reproduced.

SELECT [LPA_L1].[OrderDate], [LPA_L1].[City], [LPA_L1].[LPAV_] AS [Sum] 
FROM 
(
    SELECT [LPA_L3].[OrderDate], [LPLA_2].[City], SUM([LPA_L3].[LPAV_]) AS [LPAV_] 
    FROM 
    (
        SELECT  [LPA_L5].[OrderDate], [LPA_L4].[City], [LPA_L5].[OrderID] AS [OrderId], 
                (
                    SELECT SUM([LPA_L6].[LPAV_]) AS [LPAV_] 
                    FROM 
                    (
                        SELECT  DISTINCT [LPLA_4].[OrderID] AS [OrderId], (CONVERT(DECIMAL, [LPLA_4].[Quantity])) * [LPLA_4].[UnitPrice] AS [LPAV_] 
                        FROM    [Northwind].[dbo].[Order Details] [LPLA_4]  
                        WHERE ( ( [LPA_L5].[OrderID] = [LPLA_4].[OrderID]))
                    ) LPA_L6
                ) AS [LPAV_] 
        FROM ( [Northwind].[dbo].[Customers] [LPA_L4]  RIGHT JOIN [Northwind].[dbo].[Orders] [LPA_L5]  
                ON  [LPA_L4].[CustomerID]=[LPA_L5].[CustomerID])
    ) LPA_L3 
    GROUP BY [LPA_L3].[OrderDate], [LPLA_2].[City]
) LPA_L1

The objectalias of the field in the groupby points to the wrong set. This is likely due to a clone error. I'll check it out.

(it's till amazes me sometimes how much sql is spit out as a result of these little linq queries )

It was a dumb issue. The scalar query folder assumed all aliases in a groupby were the same . So it overwrote just 1, not all, when the target to group on was folded into a subquery...

Second issue in progress.

Sorry. Case 3 was supposed to be without the anonymous type. Copy/paste issue It is not that important since case 3 works anyway.

savanna wrote:

Sorry. Case 3 was supposed to be without the anonymous type. Copy/paste issue It is not that important since case 3 works anyway.

I've fixed the issues you reported. I went ahead and implemented some more complex group by's and some failed, like grouping on an expression. There's are currently 2 issues left to fix for this, so that will be tomorrow, as it takes too long to fix them today.

I made good progress with making it more robust. I had a hard time getting rid of the expression (c.Country + c.City) from the g.Key, as it was a catch 22 situation, but after taking 2 steps back it was actually pretty simple. (though it took me 4 hours to realize that! ).

Anyway, I'll attach the build I have now which fixes the issue you ran into. Please keep on hammering the library with queries, as the more edge cases are weeded out the better

Hi, Otis I tried out your new binary. All the old queries work. Then I went back to my big old real query and it broke. It took me a while to narrow it down and create a query that you can run against Northwind.

Now this is the one. It seems to me nullable type is still not handled perfectly. [BirthDate] column is the key. It is a nullable type.

var results = from o in mdata.Order where o.OrderId < 1000 group o by o.CustomerId into g select new { Key = g.Key, Data = g.Max( a => a.Employee.BirthDate ) };

Hmm. Thanks, I'll check it out. The two other issues currently open are one where two aggregates are used in a group by and a column has to be folded into a subquery but the original function call expression is kept (so it doesn't refer to the right derived table field) and one where the alias of the aggregated field refers to the outer query where the groupby is in, not the source of the groupby. Both should be fixable today, I'll also look at your example query and check it out

Cleaned up about a hundred bugs it seems. It took a big chunk of the day to get rid of all the alias issues. Still one to go, but the code is much more consistent now. I discovered a bad usage of alias scopes which was fundamental for a lot of the issues I ran into with creepy groupby queries. It was comforting that linq to sql also failed to produce valid sql (duplicate field names) on one query, so I'm not alone hehe

I hope to nail these last issues in the coming hour and also your last query. I then hope your code works once and for all

(edit) Ok, it's down to your query now. I still get an alias error on the field in the MAX. Checking it out...

(edit) your query failed because of some code which is no longer necessary as it was introduced to correct the object alias on an aggregate field but these now have the correct alias already so re-re-recorrecting it (and to the wrong value!) wasn't correct. Your query (and all other queries) works now.

I'll wrap it up and release a new build within half an hour.

(edit) released

Hi, Otis

I tried the new binary. There is still something not working After a little bit digging. It seems to be the null value issue again.

            var results = from o in mdata.Employee
                          group o by o.City into g
                          select new { Key = g.Key, Data = g.Sum(a => a.ReportsTo) };

This query runs against the NorthWind database. The "sum" does not make too much sense other than testing this feature. The "ReportsTo" is a nullable int value and there IS a null value in the sample database. The null value in the database will trigger an exeption. So make sure you have an null value in the database

Thanks a lot!

Thanks I'll check it out. Please next time post the exception and stacktrace if possible

var results = from o in mdata.Employee group o by o.City into g select new { Key = g.Key, Data = g.Sum(a => a.ReportsTo) };

I don't know if I understood well but Linq to Sql throw the same result and you can fix it doing this:

var results = from o in mdata.Employee group o by o.City into g select new { Key = g.Key, Data = (int?)g.Sum(a => a.ReportsTo) ?? 0};

I hope this help

Ah! I now see what is the problem indeed. For the C#/VB.NET compiler, Sum() returns a numeric, valuetyped value. So 'Data' is also a valuetype. If the query itself runs into a null value, the result is null/can be null, so the null has to be converted to the value type, which of course fails. Defining it as a Nullable<T> based value type in the projection of the query, this doesn't happen .

Otis, you are so smart! You solved all my problems now