- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Grouping on a field in a prefetch path
Joined: 02-Nov-2007
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)
});
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.
Joined: 02-Nov-2007
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:-)
Joined: 02-Nov-2007
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()
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;
Joined: 02-Nov-2007
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.
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.