A important usage that I see with the ScalarQueryExpression is of the following form:
-- SQL
SELECT CustomerID,
(
SELECT COUNT(*)
FROM Orders
WHERE CustomerID = Customers.CustomerID
) AS NumberOfOrders
FROM Customers
What I want to do is of the following form:
SELECT CustomerID,
(
SELECT SUM(Field1 * Field2 + Field3)
FROM ORDERS
WHERE CustomerID = Customers.CustomerID
) AS ComplexExpression
This is what I tried (with my entities of course):
IDataAccessAdapter adapter = DataAccessAdapterFactory.Create();
IRelationCollection jobToBillings = new RelationCollection();
jobToBillings.Add(JobEntity.Relations.BillingEntityUsingJobId);
jobToBillings.ObeyWeakRelations = true;
IRelationCollection jobToPayments = new RelationCollection();
jobToPayments.Add(JobEntity.Relations.PaymentEntityUsingJobId);
jobToPayments.ObeyWeakRelations = true;
IRelationCollection jobToExpenses = new RelationCollection();
jobToExpenses.Add(JobEntity.Relations.ExpenseEntityUsingJobId);
jobToExpenses.ObeyWeakRelations = true;
ResultsetFields fields = new ResultsetFields(6);
fields.DefineField(PersonFields.PersonId, 0, "EstimatorId");
fields.DefineField(PersonFields.Username, 1);
fields.DefineField(new EntityField2("ContractedAmounts",
new ScalarQueryExpression(JobFields.ContractedAmount.SetAggregateFunction(AggregateFunction.Sum),
(PersonFields.PersonId == JobFields.EstimatorId))), 2);
fields.DefineField(new EntityField2("Billings",
new ScalarQueryExpression(BillingFields.Amount.SetAggregateFunction(AggregateFunction.Sum),
(PersonFields.PersonId == JobFields.EstimatorId & JobFields.JobId == BillingFields.JobId),
jobToBillings)), 3);
fields.DefineField(new EntityField2("Payments",
new ScalarQueryExpression(PaymentFields.Amount.SetAggregateFunction(AggregateFunction.Sum),
(PersonFields.PersonId == JobFields.EstimatorId & JobFields.JobId == PaymentFields.JobId),
jobToPayments)), 4);
IEntityField2 expenses = new EntityField2("Expenses",
(ExpenseFields.LaborHours * ExpenseFields.LaborRate + ExpenseFields.MaterialCost),
AggregateFunction.Sum);
fields.DefineField(new EntityField2("Expenses",
new ScalarQueryExpression(expenses,
(PersonFields.PersonId == JobFields.EstimatorId & JobFields.JobId == ExpenseFields.JobId),
jobToPayments)), 5);
DataTable results = new DataTable("EstimatorSummary");
adapter.OpenConnection();
adapter.FetchTypedList(fields, results, null);
adapter.CloseConnection();
The offending lines:
IEntityField2 expenses = new EntityField2("Expenses",
(ExpenseFields.LaborHours * ExpenseFields.LaborRate + ExpenseFields.MaterialCost),
AggregateFunction.Sum);
fields.DefineField(new EntityField2("Expenses",
new ScalarQueryExpression(expenses,
(PersonFields.PersonId == JobFields.EstimatorId & JobFields.JobId == ExpenseFields.JobId),
jobToPayments)), 5);
This compiles just fine but throws the following SqlException:
Invalid column name 'dbo'.
Invalid column name 'dbo'.
Invalid column name 'dbo'.
Invalid column name 'dbo'.
When I take out the sixth (calculated) field, the DataTable has all of the correct data so I know that the sixth Field Definition is messing things up.
I tried to turn tracing on but it did not spit out any code for me. I use LLBLGen 2.0.0.0 Final March 21, 2007. Let me know!
