Building and using a LINQ for SQL Class Library with ASP.NET 2.0
In my previous posts on doing data access using LINQ and ASP.NET, I used the built-in SQLMetal command-line utility to automatically generate the data model classes for my LINQ data classes. Recently I've been using the LINQ for SQL (aka DLINQ) designer to define my data models instead, and have been really impressed with how easily it enables me to build a re-usable class library that nicely encapsulates my data and business logic.
The below walkthrough demonstrates how you can get started with it yourself. Simply install the LINQ May CTP download on top of VS 2005, and you can then follow along all of the steps below yourself. For the sample below I am using C#, but everything works equally well in VB as well.
Step 1: Create a New LINQ Enabled Class Library
Choose File->New Project within Visual Studio and navigate to the "LINQ Preview" node in the project-type tree-explorer. Then select the "LINQ Library" project icon and create a new LINQ enabled class library:
This will create a class library project whose MSBuild project file enables compilation support for LINQ. A default "Class1.cs" file is added to the project -- I recommend just deleting it to start with an empty project.
Step 2: Add a LINQ for SQL Data Model to the Project
Right-click on the project and choose the Add->New Item context menu item. Scroll down in the dialog and select the "DLINQ Object" icon, and give it an appropriate name (in this sample I'm naming it "Northwind" since I'm going to be modeling the Northwind database):
This will add a Northwind.dlinq file to your project, and bring up its designer:
You can now use the designer to map your data models and entities graphically, as well as setup associations to map relationships between them.
Step 3: Quickly creating a Northwind data model
One of the really easy ways to quickly define your data model classes is to open up the Server Explorer tab within VS 2005 and connect to your database. You can then drill into the Tables and Views within your database, and drag/drop them from the server explorer onto the DLINQ designer surface to automatically create data models for them. For example, if I dragged/dropped the Suppliers, Customers, Orders, Order Details, and Products tables onto the designer surface, I'd by default get a data model that looks like this:
Note how the DLINQ designer automatically sets up default associations between the different entities based on the Primary Key/Foreign Key relationships in the database. These will automatically cause properties to be created on the various data classes so that we can perform rich querying and/or easily traverse between them. For example, if I had a "Customer" instance above I could simply access the "Customer.Orders" property to get a collection of all of that Customer's orders in the database.
If you want, you can add, delete or alter these associations by simply clicking on the association links in the designer and then use the property grid to change its settings. For example, if I wanted to modify the "Order Details" associations above to instead be named "OrderDetails", I would click on the association line in the designer and change it either via the property grid:
Or because it supports inline editing for the name, just click on the name in the designer and rename it in place:
Additionally, I could use the designer to easily rename the entities and/or rename/remove/add properties to them. Once we are done defining your data models, we'll just save the file. The designer will then automatically generate a .cs or .vb file containing the LINQ data class definitions. This is saved in a nested file underneath the Northwind.dlinq item in the solution explorer:
The LINQ data model classes created are defined as "partial" classes -- which means we will be able to go in later and add entity and property validation rules that enforce our business logic, as well as add any additional properties or helper methods we want to the classes.
Step 4: Creating a LINQ Enabled Web-Site that References our LINQ enabled Class Library
We'll now want to go ahead and build a LINQ-enabled ASP.NET web-site that uses our LINQ class library. Choose File->Add->Web Site within Visual Studio to add a new project to the solution. Choose the LINQ web-site template to create an ASP.NET web-site that is enabled with the LINQ compilers:
Your VS solution will then look like this:
The last two steps you'll want to take are to:
1) Add a project reference from the web-site project to the LINQ class library. To-do this, right-click on the web-site and select "Add Reference", select the "Projects" tab and add the reference as normal.
2) Add a connection-string to your web.config file in the web-site project for the LINQ class library. This will enable you to easily configure and change the connection-string that is used at runtime for the application. Simply copy/paste the connection-string value in the LINQ class library's app.config file to the web.config file's <connectionString> section to configure this.
Once the above two steps are done, we are ready to build ASP.NET UI pages against our LINQ class library and associated data models.
Step 5: Build a simple Products Listing Page
To give a simple taste of using LINQ, we'll build a simple Product Listing page that lists products like the image below:
To implement this, I'll add a templated ASP.NET DataList control to the .aspx page using the markup below to define the UI for each product in our list:
<ItemTemplate>
<div class="productimage">
<img src="images/productimage.gif" />
</div>
<div class="productdetails">
<div class="ProductListHead">
<%#Eval("ProductName")%>
</div>
<span class="ProductListItem">
<strong>Price:</strong>
<%# Eval("UnitPrice", "{0:c}") %>
</span>
</div>
</ItemTemplate>
</asp:DataList>
I can then use the below code-behind class to execute a LINQ query against the class library data model we defined in our class library previously to retrieve all of the products in the database supplier by the "Exotic Liquids" supplier and sorted by the product name:
using System.Web;
using System.Query;
using LINQClassLibrary;
public partial class HelloWorld : System.Web.UI.Page {
protected void Page_Load() {
NorthwindDataContext db = new NorthwindDataContext();
DataList1.DataSource = from p in db.Products
where p.Supplier.CompanyName == "Exotic Liquids"
orderby p.ProductName
select p;
DataList1.DataBind();
}
}
Notice above how the LINQ query is strongly-typed, and can incorporate the Product/Supplier entity association we setup earlier (for example: note how the where statement is able to search by the product's Supplier.CompanyName property -- which is stored in the Suppliers table and is linked to the Products table via a primary key/foreign key relationship).
This strong typing model means I get compile-time checking of my LINQ queries today, and will get full intellisense support with the new release of Visual Studio. No more runtime SQL syntax errors!
And that is it. No additional code required.
Step 6: Using Debug Visualizers to inspect what SQL is executed against the database
One of the most common questions people wonder when using ORM data frameworks is "so what SQL code is it running under the covers?". LINQ makes it easy to figure this out. Just set a debugging breakpoint on a LINQ expression variable, and you can use a built-in LINQ debugging visualizer to inspect it while debugging.
For example, set a breakpoint on the DataList1.DataBind() method above and when it is hit hover your mouse over the DataList1.DataSource property and click the magnifying glass to launch its DLINQ query visualizer:
This will bring up a visualizer dialog that displays the SQL statement that LINQ will execute against the database when evaluating that LINQ expression:
If you click the "execute" button on the bottom-right you can even test out the query in the debugger and see the exact data that will be returned from it when the supplier name is "Exotic Liquids" is this:
This makes it really easy to see the exact SQL that is executed and closely watch what is going on as you modify and refine your LINQ queries.
Summary
Hopefully the above walkthrough provides a good introduction to some of the cool things you can do with LINQ and the LINQ May CTP version of it.
To learn more about LINQ with ASP.NET, please download and run the slides + samples from my recent "Building Web Applications Using LINQ" talk.
Hope this helps,
Scott