In the step above we queried the database for Customers and
returned back a sequence of “Customer” objects. If you look at the underlying
SQL statement executed (just attach the SQL Profiler tool while you run the app
to inspect every data access statement), you’ll notice that it is returning all
of the columns from the Customer table – including the ones we aren’t
displaying. This makes sense given that our query above asked for a sequence
of Customer objects, although it isn’t ideal for this particular page scenario
since we only care about four of the Customer columns. In cases where you
have columns containing a lot of data (for example: blobs of images, or large
text strings, etc) this can lead to performance issues since you might end up
pulling back more data than you really want/need.
One of the nice things about LINQ and DLINQ is that we
aren’t constrained to having to always return fully populated entity objects.
Instead we can shape and transform the data however we want, without having to
drop-down to write SQL directly. LINQ and DLINQ use the new “anonymous type”
features in C# and VB to provide an elegant way to express this (to learn more
about anonymous types in LINQ read my previous
LINQ blog post).
For example, I could modify our query above like so to just
fetch the 4 columns we are going to display like so:
Listing 8
GridView1.DataSource = from customer in db.Customers
where customer.Country == "USA"
orderby customer.CompanyName
select new {
CustomerID = customer.CustomerID,
CompanyName = customer.CompanyName,
City = customer.City,
Region = customer.Region
};If you re-run the page now and look at the SQL Profiler,
you’ll notice that DLINQ has automatically changed the SQL executed to a more
optimized statement that only returns the 4 column values we need from the
database:
Listing 9
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[City], [t0].[Region]
FROM [Customers] AS [t0]
WHERE [t0].[Country] = @p0
ORDER BY [t0].[CompanyName]',N'@p0 nvarchar(3)',@p0=N'USA'
We can also use this database shaping technique to run
aggregate functions and add computed values to our result as well. For
example, I could modify the query above to also add the number of orders the
customer has made, and the most recent order date by writing it like so:
Listing 10
GridView1.DataSource = from customer in db.Customers
where customer.Country == "USA"
orderby customer.CompanyName
select new {
CustomerID = customer.CustomerID,
CompanyName = customer.CompanyName,
City = customer.City,
Region = customer.Region,
NumOrders = customer.Orders.Count,
LastOrder = customer.Orders.Max(o => o.OrderDate)
};Note that both of these new values are computed off of the
rows in the Orders table specific to the referenced Customer (the Order table
has a FK relationship to Customers that SQLMetal automatically detected and
used to setup an association). The above LINQ query will cause the below SQL
statement to execute in the database:
Listing 11
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[City], [t0].[Region], (
SELECT COUNT(*)
FROM [Orders] AS [t1]
WHERE [t1].[CustomerID] = [t0].[CustomerID]
) AS [NumOrders], (
SELECT MAX([t2].[OrderDate])
FROM [Orders] AS [t2]
WHERE [t2].[CustomerID] = [t0].[CustomerID]
) AS [LastOrder]
FROM [Customers] AS [t0]
WHERE [t0].[Country] = @p0
ORDER BY [t0].[CompanyName]',N'@p0 nvarchar(3)',@p0=N'USA'
Notice how DLINQ automatically translated the count and Max
date functions into the single SQL query to execute -- it did not need to
pull down all of the records to compute it (making it very efficient).
And now when we run this page it will generate this html
output:
Figure 4
