Using DLINQ with ASP.NET (Part 2 of my LINQ series)
page 6 of 10
by Scott Guthrie
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 45657/ 121

Step 4: Data Shaping and Projections

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


View Entire Article

User Comments

Title: DLINQ Usage   
Name: Subhashini
Date: 12/18/2010 1:36:06 AM
Comment:
This article solved many of questions about DLINQ.I understand how to do create database structures for DLINQ query using Attribute Based Mapping and through XML Based Mapping.
Title: Helping hand for the DLINQ users   
Name: Tejaswini Jangale-Chaudhari
Date: 4/22/2009 1:02:16 AM
Comment:
The article is real nice, n helped me a lot to understand binding and pagination. Keep posting such helpful articles :)
Title: Great Article   
Name: Yuna
Date: 7/18/2008 3:42:52 AM
Comment:
thank you very much, my article help me understand LINQ
Title: Great Article   
Name: Jaykumar Acharya
Date: 6/18/2008 9:33:59 AM
Comment:
I must say you are my guru of LINQ. These posts really helped me to learn LINQ and DLINQ. It was like a cake walk to learn deep concepts. Please keep posting such blogs for us.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 8/27/2014 10:53:19 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search