Software Development

Working with LINQ in Visual Studio 2008

Tony Patton details how the O/R Designer feature in Visual Studio 2008 allows you to use the .NET Language-Integrated Query (LINQ) to access SQL Server.

Last week, I introduced the basics about the .NET Language-Integrated Query (LINQ) technology. Now I'll cover how the O/R Designer feature in Visual Studio 2008 that allows you to use LINQ to access SQL Server.

(Before diving into the code, I want to mention that TechRepublic member hajray posted a question about LINQ's inclusion in the .NET Framework. It is worth noting that LINQ was first introduced in C# 3.0 and added to Visual Basic 9.0 with .NET 3.5.)

O/R Designer

LINQ and the Visual Studio 2008 IDE make it easy to pull data from a SQL Server database. Visual Studio includes a template item called LINQ to SQL Classes that you may add to a project. A dbml resource is created when you add this item type with a default name (that you may change) of DataClasses1.dbml.

Once the item is added, the O/R Designer pane opens within Visual Studio. This allows you to drag and drop database items (tables, views, stored procedures) to the O/R Designer. The O/R Designer generates the dbml file, which provides the mapping between the LINQ to SQL classes and database objects. The O/R Designer also generates the typed DataContext and the entity classes.

There are two main areas within the O/R Designer pane: Entities and Methods. The main area of the design area is for entities like Tables and Views, along with their hierarchies and relationships. The Methods pane includes stored procedures and functions that are mapped to methods of the DataContext class. The O/R Designer currently supports SQL Server 2000, SQL Server 2005, and SQL Server Express Edition.

Before diving into an example, let's take a closer look at LINQ queries.

The query

There are three steps with accessing data via LINQ: obtain data source, create query, and execute the query. A query is used to define what data is retrieved from a data source. In addition, you may manipulate the data via sorting, grouping, and so forth. A query is stored in a query variable and initialized with a query expression.

Queries have their own syntax, which strongly resembles standard SQL. A query expression has three clauses: from, where, and select. The from clause defines the data source; the select clause defines what is returned; and the where clause (which is optional) lets you refine a search to include only data that meets certain criteria. MSDN provides more information on LINQ queries.

The query is not executed until it is accessed; this is referred to as deferred execution. The next example provides a peek at its use.

An example

I created a Web site project that includes a LINQ to SQL Classes item, which connects to the standard Northwind database on SQL Server 2000. One entity is created for the Customers table. In addition, a method is created for the 'Ten Most Expensive Products' stored procedure. The O/R Designer creates the dbml file named DataClasses1.dbml.

Once the DataContext class is created for the table and the stored procedure in the Northwind database, I can use it in other code. When using the created DataContext class, it is the class name with DataContext appended to the name. For my example, I use DataClasses1DataContext. (Visual Studio IntelliSense fully supports LINQ and its classes, so you can easily avoid a typo.)

I use the following ASP.NET Web Form in my example:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="LINQTest._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

<title>LINQ Example</title></head>

<body>

<form id="frmLINQTest" runat="server">

<div>

<asp:GridView ID="gvCustomers" runat="server"></asp:GridView>

</div></form></body></html>

This Web Form contains an instance of the GridView control; this control displays data via LINQ. Code is placed in the form's Page_Load event, so the data is loaded when the page is opened.

The first step is to create an instance of the DataContext class. This provides database access, so I can access its included table as a property of the DataContext class. Once the DataContext class is created, I can pull data from it via a query.

The code includes a query that pulls all data records and columns from the Customers table. The data is sorted by the second column, and the query is executed when it is bound to the GridView control on the page.

protected void Page_Load(object sender, EventArgs e) {

DataClasses1DataContext db = new DataClasses1DataContext();

var customers = from p in db.Customers

orderby 1

select p;

gvCustomers.DataSource = customers;

gvCustomers.DataBind();

}

Here is the equivalent Visual Basic code:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim db As DataClasses1DataContext = New DataClasses1DataContext()

Dim customers As Object

customers = From p In db.Customers Order By 1 Select p

gvCustomers.DataSource = customers

gvCustomers.DataBind()

End Sub

When the page loads, all data from the Customers table is displayed in the GridView control.

As a further example, I can easily call the SQL Server stored procedure as a method of my DataContext object. The results of the method -- stored procedure -- can be used to populate a data control like the previously used GridView, as the next code snippet demonstrates:

protected void Page_Load(object sender, EventArgs e) {

DataClasses1DataContext db = new DataClasses1DataContext();

var top_customers = db.Ten_Most_Expensive_Products();

gvCustomers.DataSource = top_customers;

gvCustomers.DataBind();

}

Here is the equivalent Visual Basic code:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim db As DataClasses1DataContext = New DataClasses1DataContext()

Dim customers As Object

customers = db.Ten_Most_Expensive_Products()

gvCustomers.DataSource = customers

gvCustomers.DataBind()

End Sub

These simple examples demonstrate query syntax and how to use the O/R Designer within Visual Studio 2008. LINQ allows you to manipulate (update, delete, add) and read data.

Will developers use LINQ?

I wonder how widely developers will adopt LINQ. My guess is that experienced developers will stick with their current approach, and newer developers will dive in with LINQ. I see promise for manipulating other data sources like XML with LINQ. I will take a look at using LINQ to work with XML next week.

Do you see a future for LINQ over existing techniques for working with databases? Do you plan on using LINQ in current or upcoming projects? Share your thoughts with the Visual Studio Developer community.

Tony Patton began his professional career as an application developer earning Java, VB, Lotus, and XML certifications to bolster his knowledge.

---------------------------------------------------------------------------------------

Get weekly development tips in your inbox TechRepublic's free Visual Studio Developer newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET. Automatically subscribe today!

About

Tony Patton has worn many hats over his 15+ years in the IT industry while witnessing many technologies come and go. He currently focuses on .NET and Web Development while trying to grasp the many facets of supporting such technologies in a productio...

9 comments
etruss
etruss

When I try to print this article, nothing happens. Other articles print OK.

PJfromOttawa
PJfromOttawa

It's just this example that's designed to show how it can be. It's about treating your collection-ish objects like tables and their members like fields. Say you have a collection of Smarties objects and you wanted to get the red ones. You'd write someting like: cRedOnes = SELECT s FROM cSmarties WHERE s.Color=Red I think that's kinda cool to have that ability in my code. And you're not really treating your business objects like tables but you're being given a "familiar" method of iterating conditionally thru your collections. Think about it differently and not so adversarially. LINQ is not an SQL replacement. (and I haven't even used it yet and know that much)

jreddy
jreddy

I personally won't be using LINQ anytime soon. I don't like the idea that it is putting data access code in with my business code. Now granted I can work around this but I think it is perpetuating a poor design practice. The second poor design practice I think LINQ encourages is people equating their business objects with database tables. Arrrgh! My guess is that I will eventually have to use it and that like many new technologies I will develop my own way of dealing with it so it does not corrupt my programming practices.

aureolin
aureolin

"How not to Construct an Application" 1) Separate your code from your presentation. VS makes this easy, but you have to do it. Put HTML and VB/C# in a separate file. 2) Use stored procedures. You can't optimize the database and how it's queried if all the DB access code is spread all over the application code. Think of this as optimizing by changing one thing (the stored proc) or changing many, many things (all your direct access LINQ statements) Yes, I know this is just an example - but remember, bad examples get copied and made into bad production code. :-P

hans16
hans16

My gut feeling, on first introduction to LINQ is that it is an interesting quick method for generating prototypes. However, the more "traditional" SQL mechanisms provide greater control and security for deployed code. Personally, I use NetTiers and CodeSmith to generate and maintain all my data access layer.

dejan.krunic
dejan.krunic

I have smoe experience in php, and have recently started with C#. I like the idea of LINQ, but I feel that it would take some time before SQL is to be replaced...

Justin James
Justin James

I tried it myself, it printed great. You might want to try again. If it still doesn't work, send me a message with more in-depth details please, and I will pass it on to the tech team. Thanks! J.Ja

Justin James
Justin James

LINQ most certainly is a SQL replacement, when used in the way you describe against a SQL Server database. J.Ja

alaniane
alaniane

My feeling from seeing LINQ is that they're trying to make one program language the catch all. IMO it weakens the language. When you write a proc to return a specific dataset then you can leverage the specific advantages of the database being used on the backend. Also, by not allowing the frontend app to perform backend queries and calculations, it makes it easier to switch out the backend database without having to make extensive changes to the front end.

Editor's Picks