I am using LLBLGen 2.0.0.0 Final (Marc 21st, 2007)

I am trying to get a list of records from a Project Table along with some aggregate information from some Child Tables. Here is the code:

            /*
            IRelationCollection projectToInvoices = new RelationCollection();
            projectToInvoices.Add(ProjectEntity.Relations.InvoiceEntityUsingProjectId);

            IRelationCollection projectToWorkorders = new RelationCollection();
            projectToWorkorders.Add(ProjectEntity.Relations.WorkorderEntityUsingProjectId);

            IRelationCollection projectToInvoicePayment = new RelationCollection();

            projectToInvoicePayment.Add(ProjectEntity.Relations.InvoiceEntityUsingProjectId);
            projectToInvoicePayment.Add(InvoiceEntity.Relations.InvoicePaymentEntityUsingInvoiceId);
            */
            List<Account> accountData = new List<Account>();
            ResultsetFields fields = new ResultsetFields(11);
            fields[0] = ProjectFields.CompanyId;
            fields[1] = ProjectFields.ProjectId;
            fields[2] = ProjectFields.Name;
            fields[3] = ProjectFields.Notes;
            fields[4] = ProjectFields.Started;
            fields[5] = ProjectFields.Updated;
            fields[6] = ProjectFields.Completed;


            EntityField2 hoursBought = InvoiceFields.Hours;
            hoursBought.Alias = "HoursBought";
            hoursBought.AggregateFunctionToApply = AggregateFunction.Sum;

            EntityField2 moneyUsed = InvoiceFields.Total;
            moneyUsed.Alias = "MoneyUsed";
            moneyUsed.AggregateFunctionToApply = AggregateFunction.Sum;

            EntityField2 hoursUsed = WorkorderFields.Total;
            hoursUsed.Alias = "HoursUsed";
            hoursUsed.AggregateFunctionToApply = AggregateFunction.Sum;

            EntityField2 moneyPaid = InvoicePaymentFields.Amount;
            moneyPaid.Alias = "MoneyPaid";
            moneyPaid.AggregateFunctionToApply = AggregateFunction.Sum;

            fields[7] = hoursBought;
            fields[8] = moneyUsed;
            fields[9] = hoursUsed;
            fields[10] = moneyPaid;

            /*
            fields.DefineField(new EntityField2("HoursBought",
                new ScalarQueryExpression(InvoiceFields.Hours,
                (ProjectFields.CompanyId == InvoiceFields.CompanyId &
                ProjectFields.ProjectId == InvoiceFields.ProjectId &
                ProjectFields.ProjectId == ProjectFields.ProjectId),
                projectToInvoices), AggregateFunction.Sum), 7);

            fields.DefineField(new EntityField2("MoneyUsed",

                new ScalarQueryExpression(InvoiceFields.Total,
                (ProjectFields.CompanyId == InvoiceFields.CompanyId &
                ProjectFields.ProjectId == InvoiceFields.ProjectId),
                projectToInvoices), AggregateFunction.Sum), 8);

            fields.DefineField(new EntityField2("HoursUsed",
                new ScalarQueryExpression(WorkorderFields.Total,
                (ProjectFields.CompanyId == WorkorderFields.CompanyId & 
                ProjectFields.ProjectId == WorkorderFields.ProjectId & 
                ProjectFields.ProjectId == ProjectFields.ProjectId &
                WorkorderFields.WorkorderStatusId != WorkorderStatus.Instance.Open),
                projectToWorkorders), AggregateFunction.Sum), 9);

            fields.DefineField(new EntityField2("MoneyPaid",
                new ScalarQueryExpression(InvoicePaymentFields.Amount,
                (ProjectFields.CompanyId == InvoiceFields.CompanyId &
                ProjectFields.ProjectId == InvoiceFields.ProjectId &
                InvoiceFields.InvoiceId == InvoicePaymentFields.InvoiceId),
                projectToInvoicePayment), AggregateFunction.Sum), 10);
            */

            DataProjectorToCustomClass<Account> projectProjector =
                new DataProjectorToCustomClass<Account>(accountData);

            List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();
            valueProjectors.Add(new DataValueProjector("CompanyId", 0, typeof(int)));
            valueProjectors.Add(new DataValueProjector("ProjectId", 1, typeof(int)));
            valueProjectors.Add(new DataValueProjector("Name", 2, typeof(string)));
            valueProjectors.Add(new DataValueProjector("Notes", 3, typeof(string)));
            valueProjectors.Add(new DataValueProjector("Created", 4, typeof(DateTime)));
            valueProjectors.Add(new DataValueProjector("Updated", 5, typeof(DateTime)));
            valueProjectors.Add(new DataValueProjector("Completed", 6, typeof(DateTime)));
            valueProjectors.Add(new DataValueProjector("HoursBought", 7, typeof(decimal)));
            valueProjectors.Add(new DataValueProjector("MoneyUsed", 8, typeof(decimal)));
            valueProjectors.Add(new DataValueProjector("HoursUsed", 9, typeof(decimal)));
            valueProjectors.Add(new DataValueProjector("MoneyPaid", 10, typeof(decimal)));

            IRelationPredicateBucket selectFilter = new RelationPredicateBucket(ProjectFields.CompanyId == this.CompanyId);
            selectFilter.Relations.ObeyWeakRelations = true;
            selectFilter.Relations.Add(ProjectEntity.Relations.InvoiceEntityUsingProjectId);
            selectFilter.Relations.Add(ProjectEntity.Relations.WorkorderEntityUsingProjectId);
            selectFilter.Relations.Add(ProjectEntity.Relations.InvoiceEntityUsingProjectId);
            selectFilter.Relations.Add(InvoiceEntity.Relations.InvoicePaymentEntityUsingInvoiceId);

            IGroupByCollection groupByClause = new GroupByCollection();
            for (int i = 0; i < 7; i++)
                groupByClause.Add(fields[i]);

            using (IDataAccessAdapter adapter = DataAccessAdapterFactory.Create())
            {
                adapter.OpenConnection();
                adapter.FetchProjection(valueProjectors, projectProjector, fields, selectFilter, 0, null, groupByClause, true, 0, 0);
                adapter.CloseConnection();
            }

            this.InnerList.AddRange(accountData);

You can see both attempts. The actually coded attempt produces the following SQL:

SELECT 
[InsideTrack].[dbo].[Project].[CompanyId], 
[InsideTrack].[dbo].[Project].[ProjectId], 
[InsideTrack].[dbo].[Project].[Name], 
[InsideTrack].[dbo].[Project].[Notes], 
[InsideTrack].[dbo].[Project].[Started], 
[InsideTrack].[dbo].[Project].[Updated], 
[InsideTrack].[dbo].[Project].[Completed], 
SUM([InsideTrack].[dbo].[Invoice].[Hours]) AS [HoursBought], 
SUM([InsideTrack].[dbo].[Invoice].[Total]) AS [MoneyUsed], 
SUM([InsideTrack].[dbo].[Workorder].[Total]) AS [HoursUsed], 
SUM([InsideTrack].[dbo].[InvoicePayment].[Amount]) AS [MoneyPaid] 
FROM ((( [InsideTrack].[dbo].[Project]  LEFT JOIN [InsideTrack].[dbo].[Invoice]  
ON  [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Invoice].[ProjectId]) LEFT JOIN [InsideTrack].[dbo].[Workorder]  
ON  [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Workorder].[ProjectId]) LEFT JOIN [InsideTrack].[dbo].[InvoicePayment]  
ON  [InsideTrack].[dbo].[Invoice].[InvoiceId]=[InsideTrack].[dbo].[InvoicePayment].[InvoiceId]) 
WHERE ( ( [InsideTrack].[dbo].[Project].[CompanyId] = 8)) 
GROUP BY 
[InsideTrack].[dbo].[Project].[CompanyId], 
[InsideTrack].[dbo].[Project].[ProjectId], 
[InsideTrack].[dbo].[Project].[Name], 
[InsideTrack].[dbo].[Project].[Notes], 
[InsideTrack].[dbo].[Project].[Started], 
[InsideTrack].[dbo].[Project].[Updated], 
[InsideTrack].[dbo].[Project].[Completed]

And the commented out one produces:

SELECT 
[InsideTrack].[dbo].[Project].[CompanyId], 
[InsideTrack].[dbo].[Project].[ProjectId], 
[InsideTrack].[dbo].[Project].[Name], 
[InsideTrack].[dbo].[Project].[Notes], 
[InsideTrack].[dbo].[Project].[Started], 
[InsideTrack].[dbo].[Project].[Updated], 
[InsideTrack].[dbo].[Project].[Completed], 

(SELECT TOP 1 SUM([InsideTrack].[dbo].[Invoice].[Total]) AS [Total] 
FROM ( [InsideTrack].[dbo].[Project]  INNER JOIN [InsideTrack].[dbo].[Invoice]  
ON  [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Invoice].[ProjectId]) 
WHERE ( [InsideTrack].[dbo].[Project].[CompanyId] = [InsideTrack].[dbo].[Invoice].[CompanyId] 
AND [InsideTrack].[dbo].[Project].[ProjectId] = [InsideTrack].[dbo].[Invoice].[ProjectId])) AS [MoneyUsed], 

(SELECT TOP 1 SUM([InsideTrack].[dbo].[Invoice].[Hours]) AS [Hours] 
FROM ( [InsideTrack].[dbo].[Project]  INNER JOIN [InsideTrack].[dbo].[Invoice]  
ON  [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Invoice].[ProjectId]) 
WHERE ( [InsideTrack].[dbo].[Project].[CompanyId] = [InsideTrack].[dbo].[Invoice].[CompanyId] 
AND [InsideTrack].[dbo].[Project].[ProjectId] = [InsideTrack].[dbo].[Invoice].[ProjectId]
AND [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Project].[ProjectId])) AS [HoursBought],

(SELECT TOP 1 SUM([InsideTrack].[dbo].[Workorder].[Total]) AS [Total] 
FROM ( [InsideTrack].[dbo].[Project]  INNER JOIN [InsideTrack].[dbo].[Workorder]  
ON  [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Workorder].[ProjectId]) 
WHERE ( ( [InsideTrack].[dbo].[Project].[CompanyId] = [InsideTrack].[dbo].[Workorder].[CompanyId] 
AND [InsideTrack].[dbo].[Project].[ProjectId] = [InsideTrack].[dbo].[Workorder].[ProjectId]) 
AND [InsideTrack].[dbo].[Workorder].[WorkorderStatusId] <> 1)) AS [HoursUsed], 

(SELECT TOP 1 SUM([InsideTrack].[dbo].[InvoicePayment].[Amount]) AS [Amount] 
FROM (( [InsideTrack].[dbo].[Project]  
INNER JOIN [InsideTrack].[dbo].[Invoice]  
ON  [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Invoice].[ProjectId]) 
INNER JOIN [InsideTrack].[dbo].[InvoicePayment]  
ON  [InsideTrack].[dbo].[Invoice].[InvoiceId]=[InsideTrack].[dbo].[InvoicePayment].[InvoiceId]) 
WHERE ( ( [InsideTrack].[dbo].[Project].[CompanyId] = [InsideTrack].[dbo].[Invoice].[CompanyId] 
AND [InsideTrack].[dbo].[Project].[ProjectId] = [InsideTrack].[dbo].[Invoice].[ProjectId]) 
AND [InsideTrack].[dbo].[Invoice].[InvoiceId] = [InsideTrack].[dbo].[InvoicePayment].[InvoiceId])) AS [MoneyPaid]

FROM [InsideTrack].[dbo].[Project]  
WHERE ( ( [InsideTrack].[dbo].[Project].[CompanyId] = 8))

Here is the output from commented out one:

8 13 Lawn Project Fix daves lawn 2007-06-29 11:28:21.087 2007-07-25 18:36:29.853 NULL 1420.00 20.00 25.00 1200.00

8 14 Awning Project Best project ever! 2007-07-28 21:20:51.390 2007-07-28 21:21:16.583 NULL 1420.00 20.00 25.00 1200.00

And the actual:

8 13 Lawn Project Fix daves lawn 2007-06-29 11:28:21.087 2007-07-25 18:36:29.853 NULL 20.00 2840.00 13.00 2400.00

8 14 Awning Project Best project ever! 2007-07-28 21:20:51.390 2007-07-28 21:21:16.583 NULL NULL NULL 5.00 NULL

Both are returning some erroneous values. The correct values should be:

HoursBought: 10 (ProjectId 13) and NULL (ProjectId 14) MoneyUsed: 1420 (ProjectId 13) and NULL (ProjectId 14) HoursUsed: 13 (ProjectId 13) and 5 (ProjectId 14) MoneyPaid: 1200 (ProjectId 13) and NULL (ProjectId 14)

Hopefully everything isnt too verbose. Any ideas?

-Seth

Seth wrote:

You can see both attempts. The actually coded attempt produces the following SQL:

SELECT
[InsideTrack].[dbo].[Project].[CompanyId],
[InsideTrack].[dbo].[Project].[ProjectId],
[InsideTrack].[dbo].[Project].[Name],
[InsideTrack].[dbo].[Project].[Notes],
[InsideTrack].[dbo].[Project].[Started],
[InsideTrack].[dbo].[Project].[Updated],
[InsideTrack].[dbo].[Project].[Completed],
SUM([InsideTrack].[dbo].[Invoice].[Hours]) AS [HoursBought],
SUM([InsideTrack].[dbo].[Invoice].[Total]) AS [MoneyUsed],
SUM([InsideTrack].[dbo].[Workorder].[Total]) AS [HoursUsed],
SUM([InsideTrack].[dbo].[InvoicePayment].[Amount]) AS [MoneyPaid]
FROM ((( [InsideTrack].[dbo].[Project] LEFT JOIN [InsideTrack].[dbo].[Invoice]
ON [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Invoice].[ProjectId]) LEFT JOIN [InsideTrack].[dbo].[Workorder]
ON [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Workorder].[ProjectId]) LEFT JOIN [InsideTrack].[dbo].[InvoicePayment]
ON [InsideTrack].[dbo].[Invoice].[InvoiceId]=[InsideTrack].[dbo].[InvoicePayment].[InvoiceId])
WHERE ( ( [InsideTrack].[dbo].[Project].[CompanyId] = 8))
GROUP BY
[InsideTrack].[dbo].[Project].[CompanyId],
[InsideTrack].[dbo].[Project].[ProjectId],
[InsideTrack].[dbo].[Project].[Name],
[InsideTrack].[dbo].[Project].[Notes],
[InsideTrack].[dbo].[Project].[Started],
[InsideTrack].[dbo].[Project].[Updated],
[InsideTrack].[dbo].[Project].[Completed]

As you are using more than one tables, I would recommend to use allowDuplicates=false in your retrieval:

adapter.FetchProjection(valueProjectors, projectProjector, fields, selectFilter, 0, null, groupByClause, false, 0, 0);

Seth wrote:

And the commented out one produces:

SELECT
[InsideTrack].[dbo].[Project].[CompanyId],
[InsideTrack].[dbo].[Project].[ProjectId],
[InsideTrack].[dbo].[Project].[Name],
[InsideTrack].[dbo].[Project].[Notes],
[InsideTrack].[dbo].[Project].[Started],
[InsideTrack].[dbo].[Project].[Updated],
[InsideTrack].[dbo].[Project].[Completed],

(SELECT TOP 1 SUM([InsideTrack].[dbo].[Invoice].[Total]) AS [Total]
FROM ( [InsideTrack].[dbo].[Project] INNER JOIN [InsideTrack].[dbo].[Invoice]
ON [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Invoice].[ProjectId])
WHERE ( [InsideTrack].[dbo].[Project].[CompanyId] = [InsideTrack].[dbo].[Invoice].[CompanyId]
AND [InsideTrack].[dbo].[Project].[ProjectId] = [InsideTrack].[dbo].[Invoice].[ProjectId])) AS [MoneyUsed],

(SELECT TOP 1 SUM([InsideTrack].[dbo].[Invoice].[Hours]) AS [Hours]
FROM ( [InsideTrack].[dbo].[Project] INNER JOIN [InsideTrack].[dbo].[Invoice]
ON [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Invoice].[ProjectId])
WHERE ( [InsideTrack].[dbo].[Project].[CompanyId] = [InsideTrack].[dbo].[Invoice].[CompanyId]
AND [InsideTrack].[dbo].[Project].[ProjectId] = [InsideTrack].[dbo].[Invoice].[ProjectId]
AND [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Project].[ProjectId])) AS [HoursBought],

(SELECT TOP 1 SUM([InsideTrack].[dbo].[Workorder].[Total]) AS [Total]
FROM ( [InsideTrack].[dbo].[Project] INNER JOIN [InsideTrack].[dbo].[Workorder]
ON [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Workorder].[ProjectId])
WHERE ( ( [InsideTrack].[dbo].[Project].[CompanyId] = [InsideTrack].[dbo].[Workorder].[CompanyId]
AND [InsideTrack].[dbo].[Project].[ProjectId] = [InsideTrack].[dbo].[Workorder].[ProjectId])
AND [InsideTrack].[dbo].[Workorder].[WorkorderStatusId] <> 1)) AS [HoursUsed],

(SELECT TOP 1 SUM([InsideTrack].[dbo].[InvoicePayment].[Amount]) AS [Amount]
FROM (( [InsideTrack].[dbo].[Project]
INNER JOIN [InsideTrack].[dbo].[Invoice]
ON [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Invoice].[ProjectId])
INNER JOIN [InsideTrack].[dbo].[InvoicePayment]
ON [InsideTrack].[dbo].[Invoice].[InvoiceId]=[InsideTrack].[dbo].[InvoicePayment].[InvoiceId])
WHERE ( ( [InsideTrack].[dbo].[Project].[CompanyId] = [InsideTrack].[dbo].[Invoice].[CompanyId]
AND [InsideTrack].[dbo].[Project].[ProjectId] = [InsideTrack].[dbo].[Invoice].[ProjectId])
AND [InsideTrack].[dbo].[Invoice].[InvoiceId] = [InsideTrack].[dbo].[InvoicePayment].[InvoiceId])) AS [MoneyPaid]

FROM [InsideTrack].[dbo].[Project]
WHERE ( ( [InsideTrack].[dbo].[Project].[CompanyId] = 8))

You need LEFT JOIN's in your ScalarQueryExpression, so try this for each one:

projectToInvoices.ObeyWeakRelations = true;

 fields.DefineField(new EntityField2("HoursBought",
                new ScalarQueryExpression(InvoiceFields.Hours,
                (ProjectFields.CompanyId == InvoiceFields.CompanyId &
                ProjectFields.ProjectId == InvoiceFields.ProjectId &
                ProjectFields.ProjectId == ProjectFields.ProjectId),
                projectToInvoices), AggregateFunction.Sum), 7);

... (and so on)

Hope helpful

It doesnt seem to work using both variants. Here is some more detail: First Version:

            IRelationCollection projectToInvoices = new RelationCollection();
            projectToInvoices.Add(ProjectEntity.Relations.InvoiceEntityUsingProjectId);
            projectToInvoices.ObeyWeakRelations = true;

            IRelationCollection projectToWorkorders = new RelationCollection();
            projectToWorkorders.Add(ProjectEntity.Relations.WorkorderEntityUsingProjectId);
            projectToWorkorders.ObeyWeakRelations = true;

            IRelationCollection projectToInvoicePayment = new RelationCollection();
            projectToInvoicePayment.Add(ProjectEntity.Relations.InvoiceEntityUsingProjectId);
            projectToInvoicePayment.Add(InvoiceEntity.Relations.InvoicePaymentEntityUsingInvoiceId);
            projectToInvoicePayment.ObeyWeakRelations = true;

            List<Account> accountData = new List<Account>();
            ResultsetFields fields = new ResultsetFields(11);
            fields[0] = ProjectFields.CompanyId;
            fields[1] = ProjectFields.ProjectId;
            fields[2] = ProjectFields.Name;
            fields[3] = ProjectFields.Notes;
            fields[4] = ProjectFields.Started;
            fields[5] = ProjectFields.Updated;
            fields[6] = ProjectFields.Completed;

            fields.DefineField(new EntityField2("HoursBought",
                new ScalarQueryExpression(InvoiceFields.Hours.SetAggregateFunction(AggregateFunction.Sum),
                (ProjectFields.ProjectId == InvoiceFields.ProjectId),
                projectToInvoices)), 7);

            fields.DefineField(new EntityField2("MoneyUsed",
                new ScalarQueryExpression(InvoiceFields.Total.SetAggregateFunction(AggregateFunction.Sum),
                (ProjectFields.ProjectId == InvoiceFields.ProjectId),
                projectToInvoices)), 8);

            fields.DefineField(new EntityField2("HoursUsed",
                new ScalarQueryExpression(WorkorderFields.Total.SetAggregateFunction(AggregateFunction.Sum),
                (ProjectFields.ProjectId == WorkorderFields.ProjectId &
                WorkorderFields.WorkorderStatusId != WorkorderStatus.Instance.Open),
                projectToWorkorders)), 9);

            fields.DefineField(new EntityField2("MoneyPaid",
                new ScalarQueryExpression(InvoicePaymentFields.Amount.SetAggregateFunction(AggregateFunction.Sum),
                (ProjectFields.ProjectId == InvoiceFields.ProjectId &
                InvoiceFields.InvoiceId == InvoicePaymentFields.InvoiceId),
                projectToInvoicePayment)), 10);

            DataProjectorToCustomClass<Account> projectProjector =
                new DataProjectorToCustomClass<Account>(accountData);

            List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();
            valueProjectors.Add(new DataValueProjector("CompanyId", 0, typeof(int)));
            valueProjectors.Add(new DataValueProjector("ProjectId", 1, typeof(int)));
            valueProjectors.Add(new DataValueProjector("Name", 2, typeof(string)));
            valueProjectors.Add(new DataValueProjector("Notes", 3, typeof(string)));
            valueProjectors.Add(new DataValueProjector("Created", 4, typeof(DateTime)));
            valueProjectors.Add(new DataValueProjector("Updated", 5, typeof(DateTime)));
            valueProjectors.Add(new DataValueProjector("Completed", 6, typeof(DateTime)));
            valueProjectors.Add(new DataValueProjector("HoursBought", 7, typeof(decimal)));
            valueProjectors.Add(new DataValueProjector("MoneyUsed", 8, typeof(decimal)));
            valueProjectors.Add(new DataValueProjector("HoursUsed", 9, typeof(decimal)));
            valueProjectors.Add(new DataValueProjector("MoneyPaid", 10, typeof(decimal)));

            IRelationPredicateBucket selectFilter = new RelationPredicateBucket(ProjectFields.CompanyId == this.CompanyId);

            using (IDataAccessAdapter adapter = DataAccessAdapterFactory.Create())
            {
                adapter.OpenConnection();
                adapter.FetchProjection(valueProjectors, projectProjector, fields, selectFilter, 0, false);
                adapter.CloseConnection();
            }

Query:

Query: SELECT DISTINCT [InsideTrack].[dbo].[Project].[CompanyId], [InsideTrack].[dbo].[Project].[ProjectId], [InsideTrack].[dbo].[Project].[Name], [InsideTrack].[dbo].[Project].[Notes], [InsideTrack].[dbo].[Project].[Started], [InsideTrack].[dbo].[Project].[Updated], [InsideTrack].[dbo].[Project].[Completed], (SELECT TOP 1 SUM([InsideTrack].[dbo].[Invoice].[Hours]) AS [Hours] FROM ( [InsideTrack].[dbo].[Project]  LEFT JOIN [InsideTrack].[dbo].[Invoice]  ON  [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Invoice].[ProjectId]) WHERE ( [InsideTrack].[dbo].[Project].[ProjectId] = [InsideTrack].[dbo].[Invoice].[ProjectId])) AS [HoursBought], (SELECT TOP 1 SUM([InsideTrack].[dbo].[Invoice].[Total]) AS [Total] FROM ( [InsideTrack].[dbo].[Project]  LEFT JOIN [InsideTrack].[dbo].[Invoice]  ON  [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Invoice].[ProjectId]) WHERE ( [InsideTrack].[dbo].[Project].[ProjectId] = [InsideTrack].[dbo].[Invoice].[ProjectId])) AS [MoneyUsed], (SELECT TOP 1 SUM([InsideTrack].[dbo].[Workorder].[Total]) AS [Total] FROM ( [InsideTrack].[dbo].[Project]  LEFT JOIN [InsideTrack].[dbo].[Workorder]  ON  [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Workorder].[ProjectId]) WHERE ( [InsideTrack].[dbo].[Project].[ProjectId] = [InsideTrack].[dbo].[Workorder].[ProjectId] AND [InsideTrack].[dbo].[Workorder].[WorkorderStatusId] <> @WorkorderStatusId1)) AS [HoursUsed], (SELECT TOP 1 SUM([InsideTrack].[dbo].[InvoicePayment].[Amount]) AS [Amount] FROM (( [InsideTrack].[dbo].[Project]  LEFT JOIN [InsideTrack].[dbo].[Invoice]  ON  [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Invoice].[ProjectId]) LEFT JOIN [InsideTrack].[dbo].[InvoicePayment]  ON  [InsideTrack].[dbo].[Invoice].[InvoiceId]=[InsideTrack].[dbo].[InvoicePayment].[InvoiceId]) WHERE ( [InsideTrack].[dbo].[Project].[ProjectId] = [InsideTrack].[dbo].[Invoice].[ProjectId] AND [InsideTrack].[dbo].[Invoice].[InvoiceId] = [InsideTrack].[dbo].[InvoicePayment].[InvoiceId])) AS [MoneyPaid] FROM [InsideTrack].[dbo].[Project]  WHERE ( ( [InsideTrack].[dbo].[Project].[CompanyId] = @CompanyId2))
Parameter: @WorkorderStatusId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.
Parameter: @CompanyId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 8.

Output:

System.Nullable1[System.Decimal] HoursBought = 20.0000 **(Should be: 10.0000)** System.Nullable1[System.Decimal] HoursUsed = 28.0000 (Should be: 13.0000) System.Decimal HoursBalance = -8.0000 System.Nullable1[System.Decimal] MoneyUsed = 1420.0000 **(Right!)** System.Nullable1[System.Decimal] MoneyPaid = 1200.0000 (Right!) System.Decimal MoneyBalance = 220.0000 System.String Name = Lawn Project System.String Notes = Fix daves lawn System.DateTime Created = 6/29/2007 11:28:21 AM System.DateTime Updated = 7/25/2007 6:36:29 PM System.Nullable`1[System.DateTime] Completed = Boolean IsCompleted = False Int32 CompanyId = 8

System.Nullable`1[System.Int32] ProjectId = 13

System.Nullable1[System.Decimal] HoursBought = 20.0000 **(Should be: NULL)** System.Nullable1[System.Decimal] HoursUsed = 28.0000 (Should be: 5.0000) System.Decimal HoursBalance = -8.0000 System.Nullable1[System.Decimal] MoneyUsed = 1420.0000 **(Should be: NULL)** System.Nullable1[System.Decimal] MoneyPaid = 1200.0000 (Should be: NULL) System.Decimal MoneyBalance = 220.0000 System.String Name = Awning Project System.String Notes = Best project ever! System.DateTime Created = 7/28/2007 9:20:51 PM System.DateTime Updated = 7/28/2007 9:21:16 PM System.Nullable`1[System.DateTime] Completed = Boolean IsCompleted = False Int32 CompanyId = 8

System.Nullable`1[System.Int32] ProjectId = 14

It appears that the 28.0000 is the sum of all of the workorder hours for that particular company. Second Version:

            List<Account> accountData = new List<Account>();
            ResultsetFields fields = new ResultsetFields(11);
            fields[0] = ProjectFields.CompanyId;
            fields[1] = ProjectFields.ProjectId;
            fields[2] = ProjectFields.Name;
            fields[3] = ProjectFields.Notes;
            fields[4] = ProjectFields.Started;
            fields[5] = ProjectFields.Updated;
            fields[6] = ProjectFields.Completed;

            EntityField2 hoursBought = InvoiceFields.Hours;
            hoursBought.Alias = "HoursBought";
            hoursBought.AggregateFunctionToApply = AggregateFunction.Sum;

            EntityField2 moneyUsed = InvoiceFields.Total;
            moneyUsed.Alias = "MoneyUsed";
            moneyUsed.AggregateFunctionToApply = AggregateFunction.Sum;

            EntityField2 hoursUsed = WorkorderFields.Total;
            hoursUsed.Alias = "HoursUsed";
            hoursUsed.AggregateFunctionToApply = AggregateFunction.Sum;

            EntityField2 moneyPaid = InvoicePaymentFields.Amount;
            moneyPaid.Alias = "MoneyPaid";
            moneyPaid.AggregateFunctionToApply = AggregateFunction.Sum;

            fields[7] = hoursBought;
            fields[8] = moneyUsed;
            fields[9] = hoursUsed;
            fields[10] = moneyPaid;

            DataProjectorToCustomClass<Account> projectProjector =
                new DataProjectorToCustomClass<Account>(accountData);

            List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();
            valueProjectors.Add(new DataValueProjector("CompanyId", 0, typeof(int)));
            valueProjectors.Add(new DataValueProjector("ProjectId", 1, typeof(int)));
            valueProjectors.Add(new DataValueProjector("Name", 2, typeof(string)));
            valueProjectors.Add(new DataValueProjector("Notes", 3, typeof(string)));
            valueProjectors.Add(new DataValueProjector("Created", 4, typeof(DateTime)));
            valueProjectors.Add(new DataValueProjector("Updated", 5, typeof(DateTime)));
            valueProjectors.Add(new DataValueProjector("Completed", 6, typeof(DateTime)));
            valueProjectors.Add(new DataValueProjector("HoursBought", 7, typeof(decimal)));
            valueProjectors.Add(new DataValueProjector("MoneyUsed", 8, typeof(decimal)));
            valueProjectors.Add(new DataValueProjector("HoursUsed", 9, typeof(decimal)));
            valueProjectors.Add(new DataValueProjector("MoneyPaid", 10, typeof(decimal)));

            IRelationPredicateBucket selectFilter = new RelationPredicateBucket(ProjectFields.CompanyId == this.CompanyId);

            selectFilter.Relations.ObeyWeakRelations = true;
            selectFilter.Relations.Add(ProjectEntity.Relations.InvoiceEntityUsingProjectId);
            selectFilter.Relations.Add(ProjectEntity.Relations.WorkorderEntityUsingProjectId).CustomFilter = new 

PredicateExpression(WorkorderFields.WorkorderStatusId != WorkorderStatus.Instance.Open);
            selectFilter.Relations.Add(ProjectEntity.Relations.InvoiceEntityUsingProjectId);
            selectFilter.Relations.Add(InvoiceEntity.Relations.InvoicePaymentEntityUsingInvoiceId);

            IGroupByCollection groupByClause = new GroupByCollection();
            for (int i = 0; i < 7; i++)
                groupByClause.Add(fields[i]);

            using (IDataAccessAdapter adapter = DataAccessAdapterFactory.Create())
            {
                adapter.OpenConnection();
                adapter.FetchProjection(valueProjectors, projectProjector, fields, selectFilter, 0, null, groupByClause, 

false, 0, 0);
                adapter.CloseConnection();
            }

SQL Output:

Query: SELECT DISTINCT [InsideTrack].[dbo].[Project].[CompanyId], [InsideTrack].[dbo].[Project].[ProjectId], 

[InsideTrack].[dbo].[Project].[Name], [InsideTrack].[dbo].[Project].[Notes], [InsideTrack].[dbo].[Project].[Started],

[InsideTrack].[dbo].[Project].[Updated], [InsideTrack].[dbo].[Project].[Completed],

SUM([InsideTrack].[dbo].[Invoice].[Hours]) AS [HoursBought], SUM([InsideTrack].[dbo].[Invoice].[Total]) AS [MoneyUsed],

SUM([InsideTrack].[dbo].[Workorder].[Total]) AS [HoursUsed], SUM([InsideTrack].[dbo].[InvoicePayment].[Amount]) AS

[MoneyPaid] FROM ((( [InsideTrack].[dbo].[Project] LEFT JOIN [InsideTrack].[dbo].[Invoice] ON

[InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Invoice].[ProjectId]) LEFT JOIN

[InsideTrack].[dbo].[Workorder] ON [InsideTrack].[dbo].[Project].[ProjectId]=[InsideTrack].[dbo].[Workorder].[ProjectId]

AND ( [InsideTrack].[dbo].[Workorder].[WorkorderStatusId] <> @WorkorderStatusId1)) LEFT JOIN

[InsideTrack].[dbo].[InvoicePayment] ON

[InsideTrack].[dbo].[Invoice].[InvoiceId]=[InsideTrack].[dbo].[InvoicePayment].[InvoiceId]) WHERE ( (

[InsideTrack].[dbo].[Project].[CompanyId] = @CompanyId2)) GROUP BY [InsideTrack].[dbo].[Project].[CompanyId],

[InsideTrack].[dbo].[Project].[ProjectId], [InsideTrack].[dbo].[Project].[Name], [InsideTrack].[dbo].[Project].[Notes],

[InsideTrack].[dbo].[Project].[Started], [InsideTrack].[dbo].[Project].[Updated], [InsideTrack].[dbo].[Project].[Completed] Parameter: @WorkorderStatusId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1. Parameter: @CompanyId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 8.

Output:

System.Nullable1[System.Decimal] HoursBought = 20.0000 **(Should be: 10.0000)** System.Nullable1[System.Decimal] HoursUsed = 13.0000 (Right!) System.Decimal HoursBalance = 7.0000 System.Nullable1[System.Decimal] MoneyUsed = 2840.0000 **(Should be: 1420.0000)** System.Nullable1[System.Decimal] MoneyPaid = 2400.0000 (Should be: 1200.0000) System.Decimal MoneyBalance = 440.0000 System.String Name = Lawn Project System.String Notes = Fix daves lawn System.DateTime Created = 6/29/2007 11:28:21 AM System.DateTime Updated = 7/25/2007 6:36:29 PM System.Nullable`1[System.DateTime] Completed = Boolean IsCompleted = False Int32 CompanyId = 8

System.Nullable`1[System.Int32] ProjectId = 13

System.Nullable1[System.Decimal] HoursBought = **(Right!)** System.Nullable1[System.Decimal] HoursUsed = 5.0000 (Right!) System.Decimal HoursBalance = -5.0000 System.Nullable1[System.Decimal] MoneyUsed = **(Right!)** System.Nullable1[System.Decimal] MoneyPaid = (Right!) System.Decimal MoneyBalance = 0 System.String Name = Awning Project System.String Notes = Best project ever! System.DateTime Created = 7/28/2007 9:20:51 PM System.DateTime Updated = 7/28/2007 9:21:16 PM System.Nullable`1[System.DateTime] Completed = Boolean IsCompleted = False Int32 CompanyId = 8

System.Nullable`1[System.Int32] ProjectId = 14

It appears that the second version multiplies the incorrect values by the number of Workorder Records. I hope this is not too verbose. Thank you for your help!

-Seth

t appears that the second version multiplies the incorrect values by the number of Workorder Records.

Yes that could be

About your should be parts, could you post the SQL that retrieves what you are expecting?