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.
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.
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.
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.
[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
.
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.
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]
DataContext
ObjectsSo 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.
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.
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.
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
.
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.)
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.
DataTable
with a Where
ClauseThe 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.)
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();
}
}
}
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.
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;
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.
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 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).
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.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.
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.”)
3.138.105.215