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:

<asp:DataList ID="DataList1" RepeatColumns="2" runat="server">

    
<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;
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

Published Sunday, August 27, 2006 1:41 AM by ScottGu
Filed under: , , ,

Comments

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Sunday, August 27, 2006 4:49 AM by Torkel
Great stuff! I am really looking forward to the Orcas release of visual studio that should include the LINQ (C# 3.0) update. To bad that it such a long way untill you can actully use LINQ in production :/

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Sunday, August 27, 2006 2:56 PM by Steve
Scott, Great post...i havent really got into .net 3, linq or blinq yet (still having fun with Atlas!) anyway i just wondered...is LINQ going to take us away from the dataset table adapter (DAL) with a BLL class for each table adapter? Maybe my junior status is showing but i see LINQ (sql) code on the page and that doesnt seem right?? Perhaps i should have a look through your samples and see if there is a webcast on www.asp.net ? Steve

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Sunday, August 27, 2006 3:30 PM by Steve
One more question....Does installing the LINQ CTP affect current Visual Studio 05 projects? The installer warns of C#3 features being installed? Thanks

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Monday, August 28, 2006 12:42 AM by vikram
Hi This is great stuff. I really want to get more and more of LINQ. Where can I get more articles regarding LINQ Thanks

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Monday, August 28, 2006 10:19 AM by James Alexander
This is scary cool. I can't wait until these tools see the light of day so I can actually start using them in production apps.

# Drag &amp; Drop no-show

Monday, August 28, 2006 2:41 PM by Brian Thomas
I'm attempting to drag and drop tables from my server explorer onto the DLINQ designer but the tables are not displayed on my screen. I found this listed as a known issue (can't remember the location) and it stated that my default schema wasn't the same so it suggested the work-around would be to drop a class from the toolbox, and then change the TableName to myschema.tablename. I have done that but none of the class information is automatically generated yet base off of the existing table. This is quite frustrating. Any other ideas?

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Tuesday, August 29, 2006 1:08 AM by ScottGu

Hi Steve,

The nice thing about LINQ is that you don't need to write or define any custom SQL code.  Instead you model your data model classes (Product, Customer, Order, etc) against your database schema or mapping.

You can then query and bind against your data model entities -- rather than against the database tables directly.  This gives you nice flexibility while also preserving data model encapsulation.

Hope this helps,

Scott

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Tuesday, August 29, 2006 1:09 AM by ScottGu

Hi Steve,

You can install the LINQ CTP on a VS 2005 system without worrying about it breaking things.  One thing to-do to ensure you don't affect any existing VS 2005 projects is to not update the intellisense engine to use the new compiler.  This ensures that when you use normal VS projects you are using the same binaries as of today.  Only when you work on a LINQ enabled project will you use the newer compiler.

Hope this helps,

Scott

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Tuesday, August 29, 2006 1:10 AM by ScottGu

Hi Brian,

In cases where you want two applications to go against the same database, I generally recommend using a class library approach like I described above.  This provides encapsulation for you, and allows you to easily re-use it.  Just have both web applications referene the class library and you are good to go.

Hope this helps,

Scott

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Tuesday, August 29, 2006 1:11 AM by ScottGu
Hi Brian, If you want to send me email (scottgu@microsoft.com) about the schema you are using, I can help you with the designer issue you are seeing. Hope this helps, Scott

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Tuesday, August 29, 2006 5:26 AM by Liming
I haven't followed up with Linq lately, does it work in a medium trust environment yet? thanks a lot.

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Tuesday, August 29, 2006 10:54 AM by ScottGu

Hi Leming,

The current version of LINQ doesn't support medium trust - but the final version will fully support medium trust environments.

Hope this helps,

Scott

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Thursday, August 31, 2006 3:54 PM by Martin
Hi Scott! I'm involved in a project where support for several types of databases is required (i.e. MS SQL, Postgres). Currently i'm using strongly typed datasets and stored procedures with seperate buisness objects to handle the data. My question is this: With DLINQ, is there support for modeling against something like Postgres? What's required? Thanks!

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Thursday, August 31, 2006 10:26 PM by ScottGu

Hi Martin,

The plan with DLINQ and ADO.NET vNext is to support a provider model -- which will allow users to integrate it with different database implementations.  So as long as a Postgres provider is out there, you'll be able to use LINQ against it.

Hope this helps,

Scott

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Saturday, September 2, 2006 11:37 AM by ScottGu

Hi Vikram,

If you want to send me email I can loop you in with the person who runs the blog aggregator.

Thanks,

Scott

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Saturday, September 2, 2006 12:05 PM by ScottGu

Hi Brian,

LINQ queries are strongly typed, so you don't have the same types of SQL Injection Attack worries that you need to watch out for with ad-hoc SQL queries today.

Having said that, you can also fully use stored procedures with LINQ as well.  This blog post provides more details on how to-do this: http://weblogs.asp.net/scottgu/archive/2006/06/18/DLINQ-with-Stored-Procedures.aspx

Hope this helps,

Scott

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Wednesday, September 6, 2006 3:54 AM by Maulikk
Hi Scott, from p in db.Products where p.Supplier.CompanyName == "Exotic Liquids" orderby p.ProductName select p; How can I return the value p from any method For e.g. I want to write something like this: DataList1.DataSource = SomeMethod(); DataList1.DataBound(); pubic var (or other return type) SomeMethod() { var q = (from p in db.Products where p.Supplier.CompanyName == "Exotic Liquids" orderby p.ProductName select p).ToList(); return q; } //The above throws an error.. Thanks in advance Maulik Khandwala

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Thursday, September 7, 2006 3:51 AM by ScottGu

Hi Maulik,

You'd want your method to have a signature like this:

public IEnumerable<Product> SomeMethod() {

}

Hope this helps,

Scott

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Thursday, September 7, 2006 5:04 AM by Maulikk
Hi Scott, My code looks something like this: protected void Page_Load(Object o, EventArgs e) { NorthwindDatacontext db = new NorthwindDatacontext() GridView1.DataSource = SomeMethod(db); GridView1.DataBind(); } public IEnumerable SomeMethod(NorthwindDatacontext db) { IEnumerable q = from c in db.Customers select new {c.ContactName, c.companyName}; return q } It throws "Cannot implicitly convert type System.Query.IQueryablef_4> to System.Collections.Generic.IEnumerable.." error. :( what am I missing over here? Thanks in advance Maulik

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Monday, November 6, 2006 8:47 PM by inzaghi
感觉就是把数据逻辑层单独抽取出来,另外减少在DataBase VIEW构建业务逻辑,不知道说的对么?

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Wednesday, November 29, 2006 3:16 AM by linx

我是中文VS2005,按网上方法安装了LINQ ,但是没发现“DLINQObjects”,why?

# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0

Wednesday, November 29, 2006 1:01 PM by Morten

I have started testing a little on Orcas September CTP (October CTP doesn't support August CTP of Entities and hence no tools for generating code from data store). I have May CTP of Linq, August CTP of ADO.Net vNext and a graphical tool for Linq to Entities that suck.

So I went for the dlinq designer you describe here. And it lets me define inheritance etc and all seems good. Except for one thing; How do I save to database?

Morten

# DLINQ Designer and SQL Server 2005 schemas

Friday, December 8, 2006 10:52 AM by Kevin Fitzgerald

Hi Scott,

Not sure if this the the right place to ask this, if not point me in the right direction if you can.

I'm trying to use SQL Server 2005 schemas to compartmentalize my database. If I try to drag one of the tables using the designer, I don;t see anything and the code is not generated. SQLMETAL will generate it easily enough, but I don;t want all the tables modelled. The only work around I have found is scripting the schema tables to a new database without the schemas (just using dbo), use the designer and then tweak the generated code to use schemas. Or use SQLMETAL and copy the bits I need. Is there an easier solution to this? I'm sure I'm not the first to run into this, but I can't find any forum subjects that look like it...

Thanks

Kev