Introduction

If you want to query a database you have to learn SQL. If you want to query XML then you have to learn XPath and XQuery. If you want to query objects then you have to write custom code. The basic idea behind LINQ (or Language INtegrated Query) is the homogenization of querying into an integrated part of .NET languages: One query language regardless of the data. If you learn LINQ then you can use it to query anything, pretty much.

In this article you will learn how to use LINQ to SQL and SqlMetal (a command line utility) to generate entity classes that map to database elements, specifically Views in this article, and a DataContext to query a view from the Northwind database. There are two basic steps:

• Use SqlMetal to generate entity classes and a custom DataContext
• Use LINQ to query the underlying view

The technologies used for the demo are C#, .NET 3.5, Visual Studio 2008, the Northwind database and SQL 2005. All of the work is in C#; LINQ handles the SQL plumbing for you. (You will need to obtain a copy of the Northwind database because it doesn’t ship with SQL Server 2005. You can get it here. And, SQL Server 2000 should work too.)

Using SqlMetal to Generate the ORM

An ORM is an object relational map. Basically LINQ to SQL works by using a DataContext and entity classes mapped to database elements like tables or views.

SqlMetal is installed by default with Visual Studio 2008 in the c:\Program Files\Microsoft SDKs\Windows\v6.0A\bin. The command line option used for the demo is

"C:\Program Files\Microsoft SDKs\Windows\V6.0A\bin\sqlmetal"
    /code:northwind.cs /views "c:\temp\northwnd.mdf"

The first argument specifies the name of the source code file to generate. The extension (.cs) tells SqlMetal the language or you can specify the language explicitly. The second argument (/views) instructs SqlMetal to generate entity maps for the views in the database, and the last argument indicates the database file. (For the example, I installed northwind.mdf in the c:\temp folder.)

Tip: There are a lot of options for SqlMetal. Run SqlMetal for command line help.

One drawback to SqlMetal is that it generates very verbose listings. You can simply add the northwind.cs source code to your project and use the elements in it.