1. Technology

LINQ to SQL in VB.NET

Fast productive VB.NET code for SQL Server!

By

Updated June 10, 2014

Since it was introduced in VB.NET 2008, LINQ has become fundamental in VB.NET - but it's still widely misunderstood. This article is one of a series at About Visual Basic that covers the major ways to program LINQ. The goal is for a beginning Visual Basic .NET programmer to get up to speed on LINQ. If you found this article from a search and you would like to start at the beginning, you can find an introduction to the overall LINQ technology - what it covers and what you can do with it - in the article LINQ - An Example Driven Introduction with an example showing how LINQ could be used to start web pages using selections made with CheckBox controls. There is an index to all of the articles in the series in the beginning of the article.

What is LINQ to SQL?

LINQ to SQL makes database programming a direct part of the VB.NET language. Before it was added to the VB.NET toolkit with Framework 3.5/VB.NET 2008, databases were a world apart from your application programming code and you had to get your data through an interface. The code in my own series on SQL Server programming is a good example. (The series starts here: A Database Centered Tutorial for Beginners) Much of it is focused on how to call the SQL Server API and use the data returned. Coding for SQL Server this way doesn't take advantage of some of the best features of VB.NET development such as the use of Intellisense. (Using older methods, the tables and fields in the database are unknown to the VB.NET code so Visual Studio can't tell you whether your names are right or wrong. And the compiler can't tell you whether your SQL commands are correct because they're passed directly to SQL Server. Your code has to be able to interpret whatever comes back from SQL Server.)

The style of programming in my SQL Server articles is still a good choice for some types of applications. One of the main goals of that series is to help programmers understand exactly how SQL Server works. Even though LINQ to SQL gives you a lot of help, you should still have a good idea about what SQL Server is doing. There is lots of choice in Microsoft's technology store for SQL Server databases. As .NET legend Anders Hejlsberg said in an interview in RedmondDeveloper:

"The bigger a company gets, the more there is to keep track of and the more overlap that you see because we are doing more things." Alvin Toffler invented the word "overchoice" in his 1970 book, Future Shock specifically for this kind of situation.

Is LINQ to SQL Safe?

Not long after LINQ to SQL was introduced by Microsoft, a scare went through the development community when it was rumored that Microsoft would drop this part of LINQ again. The very fact that so many people believed the rumor is a good example of why Microsoft made a big mistake in dropping VB6. (Yes, I know this is ancient history now. But it was such a big mistake! Since then, developers have been very willing to believe the worst about Microsoft.) Here's an example of what was written about it. (December 2008, Directions On Microsoft web page.)

The Language Integrated Query (LINQ) to SQL data access technology will be replaced by the ADO.NET Entity Framework and will not be recommended for development on future versions of the .NET Framework, according to Microsoft.

Fortunately, it was just a rumor. LINQ to SQL is still very much a part of Microsoft's supported technologies. Microsoft is working hard to provide solutions at all levels of development. LINQ to SQL is intended to make development against SQL Server databases faster and easier to code. (Unfortunately, other databases - such as Oracle or MySQL - aren't supported directly by Microsoft. But SQL Server Express is supported. Some third party software for other databases is available, but I can't tell you how well it works.) Another advantage of learning LINQ to SQL is that the same query syntax is used for XML, in-memory arrays and collections, ADO.NET datasets, or any other remote or local data source that supports LINQ.

Entity Framework is intended to be a large scale development technology. (Your light pickup truck would be a poor choice as an iron mine ore hauler.) A lot of Microsoft insiders went out of their way to explain the facts. Here's a typical comment by Damien Guard, at the time working on both the LINQ to SQL and Entity Framework technologies.

"There has been a flurry of posts and comments in the last 24 hours over the future of LINQ to SQL ..."

"... LINQ to SQL – a LINQ provider to SQL Server ... is a great lightweight solution that is easy to get started with and a good data access solution where your database and objects are closely aligned."

You can be assured that your time spent learning LINQ to SQL will be paid back with faster and easier access to SQL Server databases. So let's start learning!

A First LINQ to SQL Code Example

This article uses the Object Relational Designer (O/R Designer) tool in Visual Studio. There is another choice available called SQLMetal. This command-line tool can also be used to generate the LINQ to SQL object relational mapping. (More on "ORM" in a few paragraphs.) One of the problems in learning LINQ to SQL is that there is a lot of "example" code out there that is hard to understand and use in your own application because it's incomplete or because it was written before the version supported in Visual Studio became mature. The goal of this article is to step through all of the development of a very simple app so nothing is left out for someone just learning LINQ to SQL. (This article is based on Visual Studio 2010.)

(Microsoft provides a longish document by all the legends of LINQ (LINQ to SQL: .NET Language-Integrated Query for Relational Data by Dinesh Kulkarni, Luca Bolognese, Matt Warren, Anders Hejlsberg, Kit George). This was based on the beta code for LINQ to SQL and I can't make their code work. Microsoft also provides examples where properties and methods are coded manually. My advice is to use the LINQ template in Visual Studio and avoid problems.)

There are basically two problems to be solved when accessing a SQL Server database from your VB.NET application:

1 - Data in a database doesn't have the same "types" as data in .NET. This is often called the database "impedance mismatch". For example, "string" data types are typically the "nvarchar" type in SQL Server. The .NET Framework includes the SqlString data type in the System.Data namespace specifically to contain SQL Server data types. In the bad old days, database applications often had pages of code just to translate the database types into types that could be used in the application. (And LINQ to SQL generates pages of code ... but you don't have to do write it.) The process of converting types from a database into types in an application is called object relational mapping (ORM). Today, LINQ to SQL provides a new tool in Visual Studio, the Object Relational Designer, and just takes care of the rest of the problem.

2 - SQL statements have to be passed to the database engine for execution and results (or errors) returned to the application. The SqlClient namespace in ADO.NET provides the ExecuteReader, ExecuteNonQuery, and ExecuteScalar methods to run queries. Part of the problem is that it's not always clear which one to use. LINQ to SQL gives you VB.NET statements that you can compile in your application and eliminates the need to use these methods. The new LINQ to SQL syntax looks a lot like traditional SQL too. (But it isn't, really. It's VB.NET - not SQL - something to keep clearly in mind.)

Step One of creating a LINQ to SQL app (using Visual Studio) is to add a database connection to the Visual Studio Server Explorer. This is covered in the SQL Server series so I won't repeat that here.

DataContext - The LINQ to SQL Secret Sauce

Once a database connection is available in Visual Studio, you need to add (Right-click the project, select "Add New Item ...".) a ".dbml" DataContext to your project.

--------
Display "Add New Item ..."
Display DataContext Template"
--------

This is where LINQ to SQL gets real. The DataContext does the work of mapping the tables and columns (fields) of your database to objects that you can use in your application. A new tool, the Object Relational Designer is added to Visual Studio along with the DataContext in Solution Explorer.

--------
Click Here to display the illustration
--------

The tables and columns in the database are represented by entity classes in your project. (Notice that, in the illustration below, the designer automatically created "Bottle", a singular name for the "Bottles" table in the entity class. This class naming helps make your models consistent with the .NET naming conventions.) This is not a database table, but rather a true .NET class. That's why it can be used in your application like any other .NET object.

--------
Click Here to display the illustration
--------

The DataContext class is the generated code. (Which means that you should never change it, but you can look at it.) In this program, it's a hidden file named Booze.designer.vb. The declaration of the class looks like this:


Partial Public Class BoozeDataContext
	Inherits System.Data.Linq.DataContext

You'll see that the initial code example below declares an object:


Dim db As New BoozeDataContext( ...

This is where the type comes from.

The datacontext maps the database to .NET as shown in the table at the end of the article. LINQ to SQL translates your requests into the correct SQL commands and sends those commands to the database.

The DataContext is procesing also handles change tracking, database updates, complete with optomistic concurrency conflict detection and resolution and transaction integrity.

Once the connection has been established, the code to retrieve data is fairly straightforward:


Private Sub btnGetLikkerNote_Click(
    sender As System.Object, e As System.EventArgs
    ) Handles btnGetLikkerNote.Click
    Dim db As New BoozeDataContext(
        "Data Source=Mukuntuweap;" &
        "Initial Catalog=Booze;" &
        "Integrated Security=True")
    Dim boozeQuery =
     From bottle In db.Bottles
        Where bottle.BoozeFriendlyName = "Likker"
    Select bottle.BottleNote
    For Each bottle In boozeQuery
        Console.WriteLine(bottle)
    Next
    txtLikkerNote.Text = boozeQuery.First
End Sub

The easiest way to get the text of the connection string is to copy the connection string from the properties window of the connection in Server Explorer:

--------
Click Here to display the illustration
--------

This gives you the desired result.

--------
Click Here to display the illustration
--------

Note that boozeQuery is an actual query and isn't evaluated until the First method is called.

The bottom line is that LINQ to SQL is a lightweight technology that can make your SQL Server coding faster and easier. It's a good choice for most projects, but if you need the best performance or if your application has complex requirements, you might want to go the traditional route or consider the ADO.NET Entity Framework.

How the Relational Data Model is translated to the LINQ to SQL Object Model

Relational Data ModelLINQ to SQL Object Model
---------------------------------------------
TableEntity class
ColumnClass member
Foreign-key relationshipAssociation
Stored Procedure or FunctionMethod
  1. About.com
  2. Technology
  3. Visual Basic
  4. Using VB.NET
  5. LINQ for VB.NET
  6. Using LINQ to the SQL Server in VB.NET

©2014 About.com. All rights reserved.