Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Summing multiple columns--problem with polymorphic entities
 

Pages: 1
LLBLGen Pro Runtime Framework
Summing multiple columns--problem with polymorphic entities
Page:1/1 

  Print all messages in this thread  
Poster Message
ww
User



Location:

Joined on:
01-Oct-2004 22:58:25
Posted:
65 posts
# Posted on: 07-Jun-2011 15:18:15.  
I am trying to use DataAccessAdapter.FetchTypedList to sum three columns from an entity table. To get the correct records I have to join to another entity, and I'm having trouble getting the joins to work correctly.

The two entities in question, Entity1 and Entity2, are both derived from BaseEntity, so each has a one-to-one relation with the BaseEntity table. They are related to each other through Entity2.Entity1ID==Entity1.ID.

What I'm trying to get is:
Code:
SELECT sum(Entity2.Column1),sum(Entity2.Column2),sum(Entity2.Column3)
FROM Entity2 LEFT JOIN Entity1 on Entity2.Entity1ID=Entity1.ID
WHERE Entity1.GroupID='somevalue'


The code I'm using is:
Code:
RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(Entity2.Relations.Entity1UsingEntity1ID);

ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(Entity2Fields.Column1, 0, "Column1");
fields[0].AggregateFunctionToApply = AggregateFunction.Sum;
fields.DefineField(Entity2Fields.Column2, 1, "Column2");
fields[1].AggregateFunctionToApply = AggregateFunction.Sum;
fields.DefineField(Entity2Fields.Column3 , 2, "Column3");
fields[2].AggregateFunctionToApply = AggregateFunction.Sum;

RelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(Entity1Fields.GroupID == 'somevalue');

GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(Entity1Fields.Entity1GroupID);

DataTable results = new DataTable();
dataAdapter.FetchTypedList(fields, results, filter, 0, null, true, groupBy,0,0);




The SQL that gets generated from this is:

Code:
SELECT SUM([LPA_L2].[Column1]) AS [Column1], SUM([LPA_L2].[Column2]) AS [Column2], SUM([LPA_L2].[Column3]) AS [Column3]
FROM (( [database1].[dbo].[baseentity] [LPA_L1]
    LEFT JOIN [database1].[dbo].[entity2] [LPA_L2] ON [LPA_L1].[id]=[LPA_L2].[id])
    LEFT JOIN [database1].[dbo].[entity1] [LPA_L3] ON [LPA_L1].[id]=[LPA_L3].[id])
WHERE ( ( [LPA_L3].[GroupID] = @p1)) GROUP BY [LPA_L3].[GroupID]


So it's treating Entity1 and Entity2 as if they have a 1-1 relation through BaseEntity, rather than joining them to separate BaseEntity records.

What do I need to change?
  Top
MTrinder
User



Location:
London by day, Milton Keynes by night.
Joined on:
08-Oct-2008 17:55:47
Posted:
1461 posts
# Posted on: 07-Jun-2011 19:49:20.  
I cant see anywhere where you use the RelationPredicateBucket defined in the first two lines
Code:

RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(Entity2.Relations.Entity1UsingEntity1ID);


Indeed, further down you define one called "filter" and use that in the query. Do you perhaps need to combine them into one bucket...?

Matt


  Top
ww
User



Location:

Joined on:
01-Oct-2004 22:58:25
Posted:
65 posts
# Posted on: 07-Jun-2011 22:04:56.  
Oops. That was indeed the problem. Thanks for straightening me out...
  Top
MTrinder
User



Location:
London by day, Milton Keynes by night.
Joined on:
08-Oct-2008 17:55:47
Posted:
1461 posts
# Posted on: 07-Jun-2011 23:37:55.  
No problem, always happy to help!

Matt


  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.