Chapter 13
Querying Relational Data with LINQ
In This Chapter
Defining Table Objects
Connecting to Relational Data with DataContext Objects
Querying DataSets
SqlMetal: Using the Entity Class Generator Tool
Using the LINQ to SQL Class Designer
“If we could sell our experiences for what they cost us, we’d all be millionaires.”
—Abigail Van Buren
Everything you have learned in the last 12 chapters can be leveraged here—extension methods, expression trees, Lambda Expressions, queries, joins, and everything else. The biggest difference is that you will learn how to get data from a new kind of provider, SQL Server.
Recall from Chapter 12, “Querying Outlook and Active Directory,” that Language INtegrated Query (LINQ) supports custom providers and these providers convert LINQ expression trees to the language of the provider. (In Chapter 12, this was demonstrated by using Active Directory as the provider.) In this chapter, a provider for Structured Query Language (SQL) already exists and ADO.NET objects are accessible via LINQ through the IQueryable interface.
LINQ can be used to query DataSets directly or you can use the DataContext and Table<T> (which implements ITable, IQueryable, and IEnumerable). The basic chore is to define a standard object-relational mapping (ORM) that maps a C# entity class to a table in a database. It’s not that much work because you can use the SqlMetal (for more on SqlMetal refer to the later section “SqlMetal: Using the Entity Class Generator Tool”) command-line utility or LINQ to SQL class designer to perform the ORM chore for you.This chapter looks at creating the ORM mapping, using SqlMetal, and the LINQ to SQL Class designer. This chapter begins by manually defining an ORM to see that it’s not that much work and then progresses to using the tools and exploring additional features of LINQ to SQL. The important thing to keep in mind is that LINQ is completely compatible with ADO.NET 2.0, so you can use new LINQ features with existing ADO.NET code.
Defining Table Objects
Object-relational mapping is not a new concept. Defining an ORM might sound ominous but any time you define a custom class that maps to a database table (called an entity class), you are defining an ORM. The first thing you might be interested in is how much work is involved. The answer is not much.
You need the following information to make LINQ to SQL work:
A database—you can use your old friend Northwind
A reference to System.Data.Linq and that namespace added to your code with a using statement
An instance of the DataContext, which connects to your database with a connection string
A class that represents your entity and the TableAttribute, mapping the class to the table
The ColumnAttribute to map properties to columns in the table
The rest is easy. You can use automatic properties. You don’t have to use any of the name fields for the attributes, and you are ready to start using LINQ with SQL. The code in Listing 13.1 is a bare-bones, hand-coded ORM and LINQ over SQL example. Two interesting features are the assignment of the Console.Out stream to the DataContext’s Log property and the override ToString method. The Loq property lets LINQ show you the queries that it’s generating from your ORM, and the ToString method uses reflection to dump the Customer class’ state.
Listing 13.1 A Bare-Bones ORM and LINQ to SQL Example
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data.Common;
using System.Reflection;
namespace ContextAndTable
{
class Program
{
private static readonly string connectionString =
@“Data Source=.SQLEXPRESS;AttachDbFilename=” +
“”C:\Books\Sams\LINQ\Northwind\northwnd.mdf“;” +
“Integrated Security=True;Connect Timeout=30;User Instance=True”;
static void Main(string[] args)
{
DataContext customerContext = new DataContext(connectionString);
Table<Customer> customers = customerContext.GetTable<Customer>();
var startsWithA = from customer in customers
where customer.CustomerID[0] == ‘A’
select customer;
customerContext.Log = Console.Out;
Array.ForEach(startsWithA.ToArray(), c => Console.WriteLine(c));
Console.ReadLine();
}
}
[Table(Name=“Customers”)]
public class Customer
{
[Column()]
public string CustomerID{ get; set; }
[Column()]
public string CompanyName{ get; set; }
[Column()]
public string ContactName{ get; set; }
[Column()]
public string ContactTitle{ get; set; }
[Column()]
public string Address{ get; set; }
[Column()]
public string City{ get; set; }
[Column()]
public string Region{ get; set; }
[Column()]
public string PostalCode{ get; set; }
[Column()]
public string Country{ get; set; }
[Column()]
public string Phone{ get; set; }
[Column()]
public string Fax{ get; set; }
public override string ToString()
{
StringBuilder builder = new StringBuilder();
PropertyInfo[] props = this.GetType().GetProperties();
// using array for each
Array.ForEach(props.ToArray(), prop =>
builder.AppendFormat(“{0} : {1}”, prop.Name,
prop.GetValue(this, null) == null ? “<empty>
” :
prop.GetValue(this, null).ToString() + “
”));
return builder.ToString();
}
}
}
Notice that the Table attribute indicates that the Customer class is mapped to the Customers table. Further notice that none of the named arguments were needed for the Column attribute.
In the Program class, a connection string was added. You’ll need to change the connection string for your database. The DataContext plays a role similar to the connection class, and you declare an instance of Table<Customer> and get the table data from DataContext.GetTable. The rest is a simple LINQ query and a few lines for displaying the results. The output is shown in Figure 13.1. The first part of Figure 13.1 is the SELECT statement written by LINQ and the rest is the output from the Array.ForEach statement.
Figure 13.1 The output from the DataContext.Log property and the Customer object state for the resultset.
Mapping Classes to Tables
A class that maps to a database table is called an entity. For LINQ, entities are decorated with TableAttribute and ColumnAttribute. The ColumnAttribute can be applied to any field or property, public, private, or internal, but only those elements of the entity with the ColumnAttribute will be persisted when LINQ saves changes back to the database.
The ColumnAttribute supports several named arguments, including AutoSync, CanBeNull, DbType, Expression, IsDbGenerated, IsDiscriminator, IsPrimaryKey, IsVersion, Name, Storage, and UpdateCheck. For example, Name is used to indicate the table column name. Storage can be used to indicate the underlying field name and LINQ will write to the field rather than through the property. DbType is one of the supported types in the DbType enumeration, for example NChar. Enum.Parse appears to be used to convert the DbType string argument to one of the DbType enumeration values. Using this new information, you can define the Customer class more precisely using a named argument for the ColumnAttribute, as shown in Listing 13.2.
Listing 13.2 Providing Values for Named Arguments of the ColumnAttribute Class
[Table(Name=“Customers”)]
public class Customer
{
private string customerID;
private string companyName;
private string contactName;
private string contactTitle;
private string address;
private string city;
private string region;
private string postalCode;
private string country;
private string phone;
private string fax;
[Column(Name=“CustomerID”, Storage=“customerID”,
DbType=“NChar(5)”, CanBeNull=false)]
public string CustomerID
{
get { return customerID; }
set { customerID = value; }
}
[Column(Name=“CompanyName”, Storage=“companyName”,
DbType=“NVarChar(40)”, CanBeNull=true)]
public string CompanyName
{
get { return companyName; }
set { companyName = value; }
}
[Column(Name=“ContactName”, Storage=“contactName”,
DbType=“NVarChar(30)”)]
public string ContactName
{
get { return contactName; }
set { contactName = value; }
}
[Column(Name=“ContactTitle”, Storage=“contactTitle”,
DbType = “NVarChar(30)”)]
public string ContactTitle
{
get { return contactTitle; }
set { contactTitle = value; }
}
[Column(Name=“Address”, Storage=“address”,
DbType = “NVarChar(60)”)]
public string Address
{
get { return address; }
set { address = value; }
}
[Column(Name=“City”, Storage=“city”,
DbType = “NVarChar(15)”)]
public string City
{
get { return city; }
set { city = value; }
}
[Column(Name = “Region”, Storage = “region”, DbType = “NVarChar(15)”)]
public string Region
{
get { return region; }
set { region = value; }
}
[Column(Name=“PostalCode”, Storage=“postalCode”,
DbType = “NVarChar(10)”)]
public string PostalCode
{
get { return postalCode; }
set { postalCode = value; }
}
[Column(Name = “Country”, Storage = “country”, DbType = “NVarChar(15)”)]
public string Country
{
get { return country; }
set { country = value; }
}
[Column(Name = “Phone”, Storage = “phone”, DbType = “NVarChar(24)”)]
public string Phone
{
get { return phone; }
set { phone = value; }
}
[Column(Name = “Fax”, Storage = “fax”, DbType = “NVarChar(24)”)]
public string Fax
{
get { return fax; }
set { fax = value; }
}
public override string ToString()
{
StringBuilder builder = new StringBuilder();
PropertyInfo[] props = this.GetType().GetProperties();
// using array for each
Array.ForEach(props.ToArray(), prop =>
builder.AppendFormat(“{0} : {1}”, prop.Name,
prop.GetValue(this, null) == null ? “<empty>
” :
prop.GetValue(this, null).ToString() + “
”));
return builder.ToString();
}
}
For the CustomerID Name argument, we indicate the underlying table column name. The Storage named attribute is customerID (note the lowercase c), which means that LINQ will use the underlying field to set the value of the customer ID. The DbType field is an NChar up to five characters long. (The DbType string argument is not case sensitive.) And, finally, the CanBeNull argument is associated with the NOT NULL capability of SQL databases.
Table 13.1 contains a brief description for (or references to locations in this book for examples of) uses for the other named arguments of ColumnAttribute.
Table 13.1 Named Arguments for Columnattribute
Viewing the Query Text Generated by LINQ
If a TextWriter is assigned to the DataContext.Log, the DataContext will display information as the LINQ to SQL provider is doing its job. If you want to see the SQL text specifically, it can be requested from the DataContext.GetCommand() method. GetCommand returns a DBCommand, from which the SQL can be requested. Listing 13.3 contains a sample that uses LINQ to SQL for the Northwind.Order Details table and requests the DBCommand.CommandText.
Listing 13.3 Requesting the Underlying Command Generated By the LINQ to SQL Provider
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Reflection;
namespace GetCommandDemo
{
class Program
{
private static readonly string connectionString =
“Data Source=BUTLER;Initial Catalog=Northwind;Integrated Security=True”;
static void Main(string[] args)
{
DataContext context = new DataContext(connectionString);
Table<OrderDetail> details = context.GetTable<OrderDetail>();
Console.WriteLine(“SELECT: {0}”, context.GetCommand(details).CommandText);
Console.WriteLine();
var results = from detail in details
where detail.OrderID == 10248
select detail;
Array.ForEach(results.ToArray(), d=>Console.WriteLine(d));
Console.ReadLine();
}
}
[Table(Name=“Order Details”)]
public class OrderDetail
{
[Column()]
public int OrderID{get; set;}
[Column()]
public int ProductID{get; set;}
[Column()]
public decimal UnitPrice{get; set;}
[Column()]
public Int16 Quantity{get; set;}
[Column()]
public float Discount{get; set;}
public override string ToString()
{
StringBuilder builder = new StringBuilder();
PropertyInfo[] props = this.GetType().GetProperties();
// using array for each
Array.ForEach(props.ToArray(), prop =>
builder.AppendFormat(“{0} : {1}”, prop.Name,
prop.GetValue(this, null) == null ? “<empty>
” :
prop.GetValue(this, null).ToString() + “
”));
return builder.ToString();
}
}
}
The statement
Console.WriteLine(“SELECT: {0}”, context.GetCommand(details).CommandText);
returns the SQL SELECT generated by the LINQ to SQL provider. The results from the sample instance of the code run produces the following output:
SELECT [t0].[OrderID], [t0].[ProductID], [t0].[UnitPrice], [t0].[Quantity],
[t0].[Discount]
FROM [Order Details] AS [t0]
Connecting to Relational Data with DataContext Objects
So far, the examples have used the DataContext class directly. You can inherit from DataContext and embed information such as the connection string into the new class. This approach is a recommended practice and makes LINQ to SQL even easier to use. Listing 13.4 is a revision of Listing 13.3. In Listing 13.4, a generalized DataContext for the Northwind database has been added.
Listing 13.4 Inheriting from DataContext and Embedding the Connection String, Resulting in a Strongly Typed DataContext (In This Example, the Northwind Database)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Reflection;
namespace DataContextChildClass
{
public class Northwind : DataContext
{
private static readonly string connectionString =
“Data Source=BUTLER;Initial Catalog=Northwind;Integrated Security=True”;
public Northwind() : base(connectionString)
{}
}
class Program
{
static void Main(string[] args)
{
Northwind context = new Northwind();
Table<OrderDetail> details = context.GetTable<OrderDetail>();
Console.WriteLine(“SELECT: {0}”, context.GetCommand(details).CommandText);
Console.WriteLine();
var results = from detail in details
where detail.OrderID == 10248
select detail;
Array.ForEach(results.ToArray(), d=>Console.WriteLine(d));
Console.ReadLine();
}
}
[Table(Name=“Order Details”)]
public class OrderDetail
{
[Column()]
public int OrderID{get; set;}
[Column()]
public int ProductID{get; set;}
[Column()]
public decimal UnitPrice{get; set;}
[Column()]
public Int16 Quantity{get; set;}
[Column()]
public float Discount{get; set;}
public override string ToString()
{
StringBuilder builder = new StringBuilder();
PropertyInfo[] props = this.GetType().GetProperties();
// using array for each
Array.ForEach(props.ToArray(), prop =>
builder.AppendFormat(“{0} : {1}”, prop.Name,
prop.GetValue(this, null) == null ? “<empty>
” :
prop.GetValue(this, null).ToString() + “
”));
return builder.ToString();
}
}
}
In the revised Listing 13.4, there is a new class Northwind. Northwind inherits from DataContext with the connection string embedded in the Northwind class, resulting in a named, strongly typed DataContext.
The DataContext is the starting point for LINQ to SQL. One instance of a DataContext represents a “unit of work” for related operations. The DataContext is a lightweight class and is usually used at the method scope level, rather than creating one instance of the DataContext and reusing it.
A connection can be reused in a DataContext, for example, when using transactions. Simply inherit from the DataContext and embed a SqlConnection object in the class as well as the connection string. Then initialize the base class—the DataContext—with the SqlConnection object instead of the connection string.
Querying DataSets
Visual Studio and C# provide compile-time syntax checking, static typing, and IntelliSense support. By writing queries in LINQ/C# instead of another query language such as SQL, you get the support of all of these Integrated Development Environment (IDE) and language features. You can still write stored procedures and use your favorite query editor, but you also have the choice of writing the queries against DataSets in your C# code.
LINQ to DataSet is not intended to replace ADO.NET 2.0 code. Rather, the LINQ to DataSet code sits on top of and works in conjunction with the ADO.NET 2.0 code and might be useful in scenarios where data is coming from multiple data providers, is querying locally, and is performing reporting and analysis. Figure 13.2 illustrates the relationship between LINQ to DataSet and ADO.NET.
Figure 13.2 The LINQ to DataSet technology stack.
Support for DataSets with LINQ is exposed primarily through the DataRowExtensions and the DataTableExtensions classes. DataRowExtensions introduces the extension methods Field and SetField, and the DataTableExtensions class introduces the extension methods AsDataView, AsEnumerable, and CopyToDataTable.
Selecting Data from a DataTable
The key to LINQ to DataSet is using the DataTableExtension and DataRowExtension methods. Table extension methods yield a queryable sequence, and row extensions provide access to field-level data. Listing 13.5 demonstrates plain vanilla ADO.NET followed by a LINQ to DataSet query that uses the AsEnumerable extension method. (The LINQ query is shown in bold font.)
Listing 13.5 LINQ to DataSet, Querying a DataTable via the AsEnumerable Extension Method
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace LINQToDataSetSimple
{
class Program
{
private static readonly string connectionString =
“Data Source=BUTLER;Initial Catalog=Northwind;Integrated Security=True”;
static void Main(string[] args)
{
DataSet data = new DataSet();
using(SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command =
new SqlCommand(“SELECT * FROM Suppliers”, connection);
command.CommandType = CommandType.Text;
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(data, “Suppliers”);
}
DataTable supplierTable = data.Tables[“Suppliers”];
IEnumerable<DataRow> suppliers =
from supplier in supplierTable.AsEnumerable()
select supplier;
Console.WriteLine(“Supplier Information”);
foreach(DataRow row in suppliers)
{
Console.WriteLine(row.Field<string>(“CompanyName”));
Console.WriteLine(row.Field<string>(“City”));
Console.WriteLine();
}
After the DataSet is populated using ADO.NET code, a single table is obtained from the DataSet’s Tables collection. The type that stores the query results can be defined as an anonymous type or the type it is, IEnumerable<DataRow>. The from clause includes a range—supplier here—and the sequence that is obtained from the AsEnumerable extension method of the DataTable. In a nutshell for the basic query syntax, use AsEnumerable to get a sequence that LINQ can work with.
The rest of the example displays some of the results of the query. Notice that the data is obtained from the fields using the Field generic extension method.
Querying the DataTable with a Where Clause
The fundamental LINQ grammar of LINQ queries doesn’t change because the source of data is an ADO.NET DataSet. The only change relative to DataSets is that you call extension methods to get at the underlying data. For example, to use a field value in a where clause, you need to use the Field extension method to request the field value. Listing 13.6 uses plain vanilla ADO.NET code to get data from the Northwind Order Details and Products table with a join. The LINQ query uses the resulting view via the DataSet’s DataTable and uses the Field extension method on the range variable and compares the Discount with 0.10 (or Discounts greater than 10%). (To help you spot the LINQ query in the example, a bold font is used.)
Listing 13.6 Using the Field Extension Method on the Detail Range Variable to Filter By Discounts > 10%
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace LinqToDataSetWithWhere
{
class Program
{
private static readonly string connectionString =
“Data Source=BUTLER;Initial Catalog=Northwind;Integrated Security=True”;
static void Main(string[] args)
{
string sql = “SELECT * FROM [Order Details] od “ +
“INNER JOIN Products p on od.ProductID = od.ProductID”;
DataSet data = new DataSet();
using(SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command =
new SqlCommand(sql, connection);
command.CommandType = CommandType.Text;
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(data);
}
DataTable detailTable = data.Tables[0];
IEnumerable<DataRow> details =
from detail in detailTable.AsEnumerable()
where detail.Field<float>(“Discount”) > 0.10f
select detail;
Console.WriteLine(“Big-discount orders”);
foreach(DataRow row in details)
{
Console.WriteLine(row.Field<int>(“OrderID”));
Console.WriteLine(row.Field<string>(“ProductName”));
Console.WriteLine(row.Field<decimal>(“UnitPrice”));
Console.WriteLine(row.Field<float>(“Discount”));
Console.WriteLine();
}
Console.ReadLine();
}
}
}
Using Partitioning Methods
The partitioning methods, Skip and Take, work directly on sequences as described in Chapter 6, “Using Standard Query Operators,” in the section “Partitioning with Skip and Take.” For example, if you add the following line
details = details.Take(25);
after the LINQ query in Listing 13.6, then (per the fragment) you can partition the resultset by grabbing the first five elements in the sequence.
Rather than going through all of the basic capabilities again, it is worth noting that the LINQ capabilities can all be used with LINQ to DataSets, too. Aggregation, partitioning, filtering, generation operators, concatenation, and quantifiers are all supported.
Sorting Against DataTables
To sort a query, you add the orderby clause as before. The only change for sorting, when working with DataSets, is that you have to use the extension method Field on the range variable to obtain the field value to sort by. The following fragment can be plugged in to Listing 13.6 to sort the DataTable’s data by the ProductID.
IEnumerable<DataRow> details =
from detail in detailTable.AsEnumerable()
where detail.Field<float>(“Discount”) > 0.10f
orderby detail.Field<int>(“ProductID”)
select detail;
Defining a Join with DataSets
Joins, of course, are supported by LINQ to DataSets, but you need to use table and data row extensions in the query. You can use the data table extensions to get the sequence and the data row extensions to get the fields (because the correlation occurs at the field level).
A brand-new listing, Listing 13.7, was created to demonstrate the join and blend in a few techniques you might find useful. Listing 13.7 shows that you can send multiple select statements to SQL Server in one call by separating the queries with a semicolon. (This is also how malicious SQL injection attacks work.) The LINQ join and a projection create a new type by shaping elements from each of the source sequences.
Listing 13.7 A LINQ to DataSet Example That Demonstrates Multiple SQL Statements in One ADO.NET Call, Join for DataSets, and a Projection Using Elements from Both Sequences in the Join
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace LinqToDataSetWithJoin
{
class Program
{
private static readonly string connectionString =
“Data Source=BUTLER;Initial Catalog=Northwind;Integrated Security=True”;
static void Main(string[] args)
{
const string sql = “SELECT * FROM Orders;” +
“SELECT * FROM [Order Details];”;
DataSet data = new DataSet();
using(SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command =
new SqlCommand(sql, connection);
command.CommandType = CommandType.Text;
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(data);
}
DataTable orders = data.Tables[0];
DataTable orderDetails = data.Tables[1];
var orderResults =
from order in orders.AsEnumerable()
join detail in orderDetails.AsEnumerable()
on order.Field<int>(“OrderID”)
equals detail.Field<int>(“OrderID”)
select new {
OrderID=order.Field<int>(“OrderID”),
CustomerID=order.Field<string>(“CustomerID”),
ProductID=detail.Field<int>(“ProductID”),
UnitPrice=detail.Field<decimal>(“UnitPrice”),
Quantity=detail.Field<Int16>(“Quantity”),
Discount=detail.Field<float>(“Discount”)};
Console.WriteLine(“Orders & Details”);
foreach(var result in orderResults)
{
Console.WriteLine(“Order ID: {0}”, result.OrderID);
Console.WriteLine(“Customer ID: {0}”, result.CustomerID);
Console.WriteLine(“Product ID: {0}”, result.ProductID);
Console.WriteLine(“Unit Price: {0}”, result.UnitPrice);
Console.WriteLine(“Quantity: {0}”, result.Quantity);
Console.WriteLine(“Discount: {0}”, result.Discount);
Console.WriteLine();
}
Console.ReadLine();
}
}
}
Because the LINQ query defines a projection, the return type is an IEnumerable<T>, where T is an anonymous type derived from the projection not IEnumerable<DataRow>. The from clause defines the first range (order) and the join clause defines the second range (detail) and the correlation on OrderID by using the Field extension method. The example uses an equijoin—join..in..on..equals—but you can derive a nonequijoin by correlating data with a where clause. Finally, the anonymous type is a projection composed of Orders.OrderID, Orders.CustomerID, and the remainder of the fields from the Order Details table.
For more information on joins in general, check out Chapters 15 and 16, “Joining Database Tables with LINQ Queries” and “Updating Anonymous Relational Data,” respectively, which cover stored procedures and transactions used in conjunction with LINQ.
SqlMetal: Using the Entity Class Generator Tool
SqlMetal is an external Microsoft utility installed with Visual Studio 2008 in [drive:]Program FilesMicrosoft SDKsWindowsv6.0Ain that is used to generate .dbml (database markup language) files or ORM source files for LINQ to SQL. .dbml files are Extensible Markup Language (XML) files that contain information describing the schema of the thing defined. (The XML context in the .dbml file looks a lot like the ColumnAttributes we added to the entity classes earlier.)
The following command line uses SqlMetal.exe to generate a DataContext and entity classes for the entire Northwind database, generating a DataContext class and an entity class for each of the tables in the database.
SqlMetal /server:butler /database:Northwind /code:northwind.cs
There are several options for SqlMetal. The server, database, username, password, connection string, and timeout values can be expressed as command-line options. There are also command-line switches for extracting views, functions, and stored procedures. Output can be created as a map, dbml, or source code file. You can express the generated code language or let the tool infer the language from the extension of the source code file (the /code: filename.ext parameter). In addition to the language switch, a namespace, the name of the data context class, an entity base class (to inherit from), pluralization of entity class names, and whether the classes are serializable can all be expressed on the command line.
The previous SqlMetal statement refers to the server “butler,” the Northwind database, and generates all of the output as C# in a file named northwind.cs. As is, the statement will generate a DataContext called Northwind and an entity (or table-mapped) class for every table in the Northwind database but nothing for the functions, views, or stored procedures (or “sprocs,” in technical jargon).
Using the LINQ to SQL Class Designer
The LINQ to SQL Class designer is an integrated aspect of Visual Studio. The class designer is a designer in Visual Studio. A designer is some code that has the ability to interact with Visual Studio in some specific way. In this instance, the designer has the ability to drag and drop tables, stored procedures, and functions to a graphical user interface (GUI) representation of a Database Markup Language (DBML) file and have those elements converted to XML and source code. The class designer is like SqlMetal integrated in a visual way into Visual Studio.
For example, if you create a new project and add a new “LINQ to SQL Classes” item from the Add New Item dialog box (see Figure 13.3), then Visual Studio generates a .dbml file, a .cs file, a .dbml.layout file, and a .designer.cs file to your project. These files represent the designer arrangement, XML describing the schema of the elements dragged to the designer, and the code that contains the DataContext and entity classes of the elements on the designer.
Figure 13.3 Add a “LINQ to SQL Classes” element to your project to code generate the DataContext and entity class (or classes) to your project.
Figure 13.4 illustrates what the LINQ to SQL Class designer will look like as elements are added. Adding elements to the designer generates a DataContext and entities for the items added to the designer, and the designer supports adding functions and stored procedures, which are represented as independent classes with properties representing the resultset of the function or query.
Figure 13.4 A representation of the LINQ to SQL Class designer in Visual Studio.
Summary
LINQ for data is supported in two basic ways. You can use LINQ to DataSets, which uses extension methods to make table data and rows and fields accessible in LINQ queries. No preparation work is necessary—this is pure ADO.Net coding. The other way to use LINQ with data is to use LINQ to SQL. LINQ to SQL requires that you create an ORM. In this context, an ORM is a DataContext class representing the database and classes decorated with TableAttribute and the elements that map to database columns decorated with ColumnAttribute.
Using LINQ to SQL results in cleaner code, and you can use SqlMetal or the LINQ to SQL Class designer to generate the ORM plumbing. (In addition, a new version of ADO.NET is coming, the ADO.NET Entity Framework. Because the entity framework is based on LINQ, it is introduced in Chapter 17, “Introducing ADO.NET 3.0 and the Entity Framework.”)