Grouping on a field in a prefetch path

Posts   
 
    
neilx
User
Posts: 267
Joined: 02-Nov-2007
# Posted on: 11-Oct-2011 18:52:59   

2.6.10.809 (SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll) 2.6.10.930 (SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll) 2.6.10.917 (SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll)

Not sure if this simply a Linq question or LLB related.

I am grouping on 3 fields. The third one - TopLevelHeadingCode in the code below - is way down in the prefetch path of the main object.

I get the correct number of rows returned but the unfiltered selection of rows is returned the same for each row in the groupby.

i.e. in the code below, g returns the same set of objects for each TopLevelHeadingCode.

I had assumed, obviously wrongly, that the 3 select keys would have returned rows grouped by the TopLevelHeadingCode. Where I am I going wrong?

(The 'first' in the code is an object all in memory from an earlier .First() on an IQueryable<EntityBase2>)

            var dashboard =
    first.FacilityDomainName
    .Select(a1 => a1.Facility)
    .GroupBy(
        a => new 
            {
                a.IDClient,
                a.FacilityCode,
                TopLevelHeadingCode =
                a.FacilityRegion
                    .SelectMany(b => b.RegionBase.RegulationCountryRegion)
                        .Select(c => c.RegulationBase)
                            .SelectMany(d => d.RegulationHeading)
                                .Select(e => e.Heading)
                                    .Select(f => f.TopLevelHeadingCode)
                                        .First()
             } ,
                a=> a.FacilityRegion
                    .SelectMany(b => b.RegionBase.RegulationCountryRegion)
                        .Select(c => c.RegulationBase))

    .Select(g => new Dashboard
                {
                    IDClient = g.Key.IDClient,
                    FacilityCode = g.Key.FacilityCode,
                    TopLevelHeadingCode = g.Key.TopLevelHeadingCode,
                    Regulations = g.SelectMany(a=>a)
                });
daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 11-Oct-2011 22:03:45   

I re-formatted a little bit your query to understand the groupby. This is totally a Linq2Objects question, as the objects you are querying are already in memory. The GroupBy is indeed very complex, I don't know really what are you trying to achieve here. It may help if you show us how the "first" data set looks like and what you really want to obtain.

David Elizondo | LLBLGen Support Team
neilx
User
Posts: 267
Joined: 02-Nov-2007
# Posted on: 11-Oct-2011 22:46:40   

daelmo wrote:

I re-formatted a little bit your query to understand the groupby. This is totally a Linq2Objects question, as the objects you are querying are already in memory. The GroupBy is indeed very complex, I don't know really what are you trying to achieve here. It may help if you show us how the "first" data set looks like and what you really want to obtain.

It is probably best I get an example together in Northwind so at least the data is familiar. I'll post that tomorrow. BTW, I would really like to avoid doing this in memory so LLB can get the data needed (all counts of various types) directly, but I was unable to get anywhere with that.

The 'first' is even more complex unfortunately.

I have a good result when I filter the required entities by the g.Key.TopLevelHeadingCode like this:

                                    Regulations = g.SelectMany(a=>a).Where(b=>b.RegulationHeading.Any(c=>c.HeadingCode.Substring(0,2) == g.Key.TopLevelHeadingCode))

(It turns out that the TopLevelHeadingCode is actually the first 2 characters of the HeadingCode. I would have preferred the syntax to avoid the SubString(0,2) but that crashed )

Thanks for looking at it and I appreciate it must look very complex. I have been with it for ages and you wouldn't believe how much simpler it is now:-)

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 12-Oct-2011 05:07:00   

I bet it's simpler to your simple_smile And yes, a Northwind example that exposes the same situation would be very nice so we can help you to figure this out.

David Elizondo | LLBLGen Support Team
neilx
User
Posts: 267
Joined: 02-Nov-2007
# Posted on: 12-Oct-2011 11:50:55   

Here is as simple an example as I can get to show the problem. The idea is to get a list of cities, each with the CompanyName and orders collection for each customer in that city.

I really want to do this in the database, so this is the kind of thing I want to do. It entails a multiple select key which then needs a correlation query in the grouped object that is returned. I tried what I thought should work, but it fails:

             var q = from c in metaData.Customer
                    group c by new {c.City, c.CompanyName}
                    into g
                    select new {g.Key.City, 
                        g.Key.CompanyName, 
                        Orders = from o in metaData.Order 
                                 where o.EmployeeId == 3 && g.Any(h => h.CustomerId == o.CustomerId) 
                                 select o};
            ObjectDumper.Write(q);

Using ObjectDumper to see the values, I get


TestCase 'NorthwindExamples/CountExamples/FindOrdersWithGroupBy' failed: 
    Execute
    SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Invalid column name 'CustomerID'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.Data.SqlClient.SqlException: Invalid column name 'CustomerID'.
    Errors: System.Data.SqlClient.SqlErrorCollection
    Class: 16
    LineNumber: 1
    Number: 207
    Procedure: 
    Server: (local)
    State: 1
    ErrorCode: -2146232060
    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()
    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)
       --- End of inner exception stack trace ---
    QueryExecuted: 
        Query: SELECT [LPA_L1].[City], [LPA_L1].[CompanyName], @LO11 AS [LPFA_2], [LPA_L1].[CustomerID] AS [CustomerId] FROM (SELECT [LPLA_1].[City], [LPLA_1].[CompanyName] FROM [Northwind].[dbo].[Customers] [LPLA_1]  GROUP BY [LPLA_1].[City], [LPLA_1].[CompanyName]) [LPA_L1]
        Parameter: @LO11 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    
    Parameters: System.Data.SqlClient.SqlParameterCollection
    DbSpecificExceptionInfo: System.Collections.Generic.Dictionary`2[SD.LLBLGen.Pro.ORMSupportClasses.ExceptionInfoElement,System.Object]
    RuntimeVersion: 2.6.0.0
    RuntimeBuild: 09302010
    at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior)
    at Examples.NorthwindAdapter.DatabaseSpecific.DataAccessAdapter.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary`2 typeConvertersToRun)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 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.ExecuteHierarchicalValueListProjection(QueryExpression toExecute, IRelationPredicateBucket additionalFilter, ITemplateGroupSpecificCreator frameworkElementCreator)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.IEnumerable.GetEnumerator()
    C:\Work_Neil\Samples\New folder\LinqSamples\ObjectDumper\ObjectDumper.cs(79,0): at ObjectDumper.WriteObject(String prefix, Object element)
    C:\Work_Neil\Samples\New folder\LinqSamples\ObjectDumper\ObjectDumper.cs(30,0): at ObjectDumper.Write(Object element, Int32 depth, TextWriter log)
    C:\Work_Neil\Samples\New folder\LinqSamples\ObjectDumper\ObjectDumper.cs(23,0): at ObjectDumper.Write(Object element, Int32 depth)
    C:\Work_Neil\Samples\New folder\LinqSamples\ObjectDumper\ObjectDumper.cs(18,0): at ObjectDumper.Write(Object element)
    Neil\CountExamples.cs(77,0): at NorthwindExamples.Neil.CountExamples.FindOrdersWithGroupBy()

Walaa avatar
Walaa
Support Team
Posts: 14995
Joined: 21-Aug-2005
# Posted on: 12-Oct-2011 18:06:19   

Reproduced and looking into it.

Oh, I think the problem is that you are trying to have a where on the Grouping, and that's not doable in SQL. Please check this thread for more details: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=19447

So as a workaround you can do the following:

var q1 = from c in metaData.Customer
         join o in metaData.Order on c.CustomerId equals o.CustomerId
         where o.EmployeeId == 3
         select new 
         {
             Cisty = c.City,
             Company = c.CompanyName,
             CustomerId = c.CustomerId,
             OrderId = o.OrderId,
             OrderDate = o.OrderDate
         };

var list1 = q1.ToList();

var q2 = from d in list1
         group d by new { d.Cisty, d.Company } into g
         select g;
neilx
User
Posts: 267
Joined: 02-Nov-2007
# Posted on: 14-Oct-2011 22:06:07   

Yes. That is actually what I had to do. Put the whole data structure in-memory then group on it there. The trouble with that is that is isn't scalable.

My tests on a small data sample are fine, but scaled up to the several hundred thousand rows involved, it becomes a 6 second job instead of a 0.6 second job in the database with the grouping and counts done there.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 15-Oct-2011 08:42:38   

You can try to do in-memory as Walaa suggested or you can try to move the where outside the group projection. Example:

var q1 = from c in metaData.Customer
        group c by new { c.City, c.CompanyName, c.CustomerId }
            into g
            select new
            {
                g.Key.CustomerId,
                g.Key.City,
                g.Key.CompanyName,
            };
            
var q2 = from g in q1
            select new
            {
                g.City,
                g.CompanyName,
                Orders = from o in metaData.Order
                        where o.EmployeeId == 3 && g.CustomerId == o.CustomerId
                        select o
            };

If you want more grained control on the executed query though, you may consider using LLBLGen API (predicate expressions) or QuerySpec.

David Elizondo | LLBLGen Support Team