To continue on
my last blog post regarding
DLinq, I brought up the issue of "what will the actual T-SQL query look like when you do <x>." Specifically, if you do a query like this to get customer data...
var custs =
from c in Customers
where c.CompanyName.StartsWith("a")
select c;
Now let's say you take the above DLinq query and then reference an associated entity using an aggregate function like so...
Console.WriteLine("ID={0}, CompanyName={1}, NumOrders={1}", cust.CustomerID, cust.CompanyName, cust.Orders.Count);
You end up with 1 query for customers and then 1 query for *each* associated entity reference (cust.Orders.Count) - yikes, not a good thing! If I run that Console.WriteLine command in a loop, that's one query per iteration. But this is transparent to the C# developer, so SQL Profiler can be your friend! (Note: take a look at my last blog post to see what SQL Profiler reported based on the above Dlinq query).
So, let's refactor that code into something that will make a single database trip that will give us all of the info we need....
var custs =
from c in Customers
where c.CompanyName.StartsWith("A")
select new {
c.CustomerID,
c.CompanyName,
OrdersForCust =
c.Orders.Count
};
foreach(var cust in custs)
{
Console.WriteLine("ID={0}, CompanyName={1}, NumOrders:{2}",
cust.CustomerID,
cust.CompanyName,
cust.OrdersForCust);
}
SQL Profiler tells me that a single query (w/ subquery) was executed as follows:
SELECT [t0].[CompanyName],
[t0].[CustomerID],
(
SELECT COUNT(*) AS [C0]
FROM [Orders] AS [t1]
WHERE [t1].[CustomerID] = [t0].[CustomerID]
) AS [OrdersForCust]
FROM [Customers] AS [t0]
WHERE [t0].[CompanyName] LIKE 'A%'
Much nicer!
Well, now it's time for me to fess up. Refactoring out the grouping problem into an anonymous type took me several attempts, which hurt a little because I pride myself with being able to write some pretty heinous queries with T-SQL. But as Aaron Skonnard points out, this is a lot of stuff to take in at once (anonymous types, Linq syntax, lambdas even though I'm not using any, etc). Interestingly enough, I was able to write the same query using the LLBLGen Pro object model (which is definitely more verbose) in less time....
using (DataAccessAdapter adapter = new DataAccessAdapter())
using (DataTable table = new DataTable())
{
ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(CustomersFieldIndex.CustomerId, 0, "CustomerId");
fields.DefineField(CustomersFieldIndex.CompanyName, 1, "CompanyName");
fields.DefineField(OrdersFieldIndex.OrderId, 2, "NumOrders");
fields[2].AggregateFunctionToApply = AggregateFunction.Count;
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(CustomersEntity.Relations.OrdersEntityUsingCustomerId);
IPredicateExpression filterExpression = new PredicateExpression();
filterExpression.Add(PredicateFactory.Like(CustomersFieldIndex.CompanyName, "A%"));
filter.PredicateExpression.Add(filterExpression);
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.Add(fields[1]);
adapter.FetchTypedList(fields, table, filter, 0, null, true, groupByClause);
}
Thankfully C# 3.0 won't be out for a while so I have time to digest all of this stuff. :-)
