LINQ
to SQL allows .NET developers to write “queries” in their .NET language
of choice to retrieve and manipulate data from a SQL Server database.
In a general sense, LINQ to SQL allows us to create SQL queries in our
preferred .NET language syntax and work with a strongly types collection
of objects as a return result. We can make changes to these objects
then save changes back to the database.
To get an idea of the syntax for LINQ to SQL, we will be using the
following SQL database schema. It is a simple software registration and
helpdesk. It is populated with sample data and has foreign-key
relationships defined where appropriate.
SQL Database Schema used for LINQ to SQL examples.
For the moment I ask you to ignore the fact that we will be coding
against a type HookedOnLINQ, I’ll get to how that was created in a few
pages time, for now just understand it is an object structure that
mimics this database schema.
HookedOnLINQ db =
new HookedOnLINQ("Data Source=(local);Initial Catalog=HookedOnLINQ");
var q = from c in db.Contact
where c.DateOfBirth.AddYears(35) > DateTime.Now
orderby c.DateOfBirth descending
select c;
foreach(var c in q)
Console.WriteLine("{0} {1} b.{2}",
c.FirstName.Trim(),
c.LastName.Trim(),c.DateOfBirth.ToString("dd-MMM-yyyy"));
Output:
Mack Kamph b.17-Sep-1977
Armando Valdes b.09-Dec-1973
LINQ to SQL Query Expression on a SQL Server Database – Contacts younger than 35 years of age, youngest first
The moment we entered the foreach loop (that’s important, the SQL was
only executed the first time we requested data, until then the query was
just kept in-memory as an expression, this is called Deferred
Execution), the following SQL Statement was formulated by LINQ and
executed on the server.
SELECT [t0].[ContactId], [t0].[FirstName], [t0].[LastName], [t0].[DateOfBirth],[t0].[Phone], [t0].[Email], [t0].[State]
FROM [Contact] AS [t0]
WHERE DATEADD(YEAR, @p0, [t0].[DateOfBirth]) > @p1
ORDER BY [t0].[DateOfBirth] DESC
SQL Statement generated by LINQ returning Contacts older than a given date that was passed in as a parameter.
Our C# query expression was translated into parameterized SQL code,
parameters were created and the query executed on the server. Not
everyone is going to be thrilled by this epiphany. Those purists who
believe that all database access should be carried out through stored
procedures will be horrified. Fear not, LINQ to SQL allows these
developers to continue to use stored procedures rather than SQL,
although you now have to write the stored procedure code yourself,
missing out on some of the flexibility LINQ offers. We cover this is
more detail later, for now just understand that LINQ to SQL supports
stored procedure in addition to dynamically generated SQL calls in all
circumstances.
If your database has Foreign Key relationships defined, then their
hierarchy is reflected in the generated object models. You can access
the related records data simply by specifying the child table, or from a
child table, the parent table to access relational data. The next
example demonstrates how you can navigate the foreign-key relationship
chain without writing a Join statement explicitly.
HookedOnLINQ db =
new HookedOnLINQ("Data Source=(local);Initial Catalog=HookedOnLINQ");
var q = from o in db.Orders
where o.Products.ProductName.StartsWith("Asset") &&
o.PaymentApproved == true
select new { name = o.Contacts.FirstName + " " +
o.Contacts.LastName,
product = o.Products.ProductName,
version = o.Products.Version +
(o.Products.SubVersion * 0.1)
};
foreach(var x in q)
Console.WriteLine("{0} - {1} v{2}",
x.name, x.product, x.version);
Output:
Barney Gottshall - Asset Blaster v1
Barney Gottshall - Asset Blaster v1.1
Armando Valdes - Asset Blaster Pro v1
Jeffery Deane - Asset Blaster Pro v1.1
Stewart Kagel - Asset Blaster Pro v1.1
Blaine Reifsteck - Asset Blaster Pro v1.1
Ariel Hazelgrove - Asset Blaster v1.1
Accessing foreign-key relationships is simple. No join syntax necessary, you just access the sub-members directly.
This hierarchical object model works for updates as well. You can
assign, add and delete records in related tables just by manipulating
objects and adding/removing objects from tables. Behind the scenes LINQ
to SQL generated the following SQL query command and executed it. The
results were used to populate our result object collection which is a
collection of an Anonymous Type (a dynamically created compile time type
that has public properties called Name, Product and Version).
SELECT ([t2].[FirstName] + @p2) + [t2].[LastName] AS [value], [t1].[ProductName], [t1].[Version] + ([t1].[SubVersion] * @p3) AS [value2]
FROM [Orders] AS [t0], [Products] AS [t1], [Contacts] AS [t2]
WHERE ([t2].[ContactId] = [t0].[ContactId]) AND ([t1].[ProductName] LIKE @p0) AND ([t0].[PaymentApproved] = @p1) AND ([t1].[Product_Id] = [t0].[ProductId])
SQL code showing how the joins to related table through foreign-keys were added.
If your database doesn’t have foreign-key relationships defined between
two tables, LINQ to SQL still allows relational access by explicitly
specifying Joins in the Query Expression. The following query
demonstrates how to join where a foreign-key is not defined between two
loosely related tables Contacts.Phone and CallLogs.Number.
HookedOnLINQ db =
new HookedOnLINQ("Data Source=(local);Initial Catalog=HookedOnLINQ");
var q = from call in db.CallLogs
join contact in db.Contacts on call.Number equals contact.Phone
select new {contact.FirstName, contact.LastName,
call.When, call.Duration};
foreach(var call in q)
Console.WriteLine("{0} - {1} {2} ({3}min)",
call.When.ToString("ddMMM HH:mm"),
call.FirstName.Trim(), call.LastName.Trim(), call.Duration);
If no foreign-key relationship exists, you can use the Join operator in the query expression.
To change and add a record to our database, you just need to make the
changes to the in-memory objects and then call SubmitChanges method (be
careful, I once mistakenly called AcceptChanges which accepts the
changes and marks all records as original but doesn’t save to the DB).
LINQ to SQL keeps track of the changes and generates SQL statements to
affect all of the required updates, inserts and deletes. You can
override this default behavior and specify your own implementation
methods (which can call stored procedures) to use instead. LINQ to SQL
provides a transaction around the database updates, so if any part fails
you have a chance to capture the error, rectify, and then try again.
You can also control how LINQ to SQL handles concurrency errors (when
someone else changes data you were editing before you had a chance to
save).
HookedOnLINQ db =
new HookedOnLINQ("Data Source=(local);Initial Catalog=HookedOnLINQ");
// Change - Get an object, make the change in memory, Call SubmitChanges
Contacts q = (from c in db.Contacts
where c.FirstName == "Armando" && c.LastName == "Valdes"
select c).FirstOrDefault();
if (q != null) {
q.Email = "Armando.Valdes@aspiring-technology.com";
}
try {
db.SubmitChanges();
}
catch (OptimisticConcurrencyException e) {
// You have your choice of RefreshMode to resolve concurrency conflicts.
// You can KeepChanges, KeepCurrentValues, OverwriteCurrentValues.
e.Resolve(RefreshMode.OverwriteCurrentValues);
db.SubmitChanges();
}
Update showing how to handle concurrency errors. You make changes to objects and then call SubmitChanges.
Inserting new records are as simple as creating a new instance of the
object and adding it to the appropriate collection, then calling
SubmitChanges. It is also just as easy adding sub-records that are
related using Foreign Key’s by creating the sub-object and adding it to
the new record we just created before calling SubmitChanges which will
save both records and their relationship to the database.
HookedOnLINQ db =
new HookedOnLINQ("Data Source=(local);Initial Catalog=HookedOnLINQ");
// Adding Records – (1) Create a new object and sub-objects,
// (2) Add it to the DataContext collection, (3) Call SubmitChanges
// (1)
Contacts newContact = new Contacts();
newContact.FirstName = "Troy";
newContact.LastName = "Magennis";
newContact.Phone = "425 749 0494";
newContact.Email = "troy@aspiring-technology.com";
newContact.DateOfBirth = new DateTime(1980, 08, 07);
// Create sub-record and add to this contact
Orders newOrder = new Orders();
newOrder.Products = (from p in db.Products
where p.ProductName == "Asset Blaster Pro"
select p).FirstOrDefault();
newOrder.DateOfPurchase = DateTime.Now;
// (2)
newContact.Orders.Add(newOrder);
db.Contacts.Add(newContact);
// (3)
db.SubmitChanges();
Inserting a new record and a related sub-record. Simply create objects and add to a collection.
On the SubmitChanges, LINQ to SQL generates SQL statements in the
correct order to save the new records to the database and to correctly
reference each other. In our example, LINQ to SQL needs to insert the
new Contact first to get the primary key (which in an identity column),
then use that value when writing the new order to the database. The
whole process is carried out in a Transaction, so if any step fails then
the whole database is returned to the state before the SubmitChanges
was called.
Start LOCAL Transaction (ReadCommitted)
INSERT INTO [Contacts](FirstName, LastName, DateOfBirth, Phone, Email, State) VALUES(@p0, @p1, @p2, @p3, @p4, @p5)
SELECT [t0].[ContactId]
FROM [Contacts] AS [t0]
WHERE [t0].[ContactId] = (CONVERT(Int,@ @IDENTITY))
INSERT INTO [Orders](ContactId, ProductId, DateOfPurchase, PaymentApproved, Quantity, Discount, AccessCode) VALUES(@p0, @p1, @p2, @p3, @p4, @p5, @p6)
SELECT [t0].[OrderId]
FROM [Orders] AS [t0]
WHERE [t0].[OrderId] = (CONVERT(Int,@ @IDENTITY))
Commit LOCAL Transaction
SQL Executed when writing out a record and sub-record. Notice the transaction wrapping the whole process.
These records were added after the SubmitChanges method was called in the Figure 16 example.
Contacts table:
ContactId
|
FirstName
|
LastName
|
DateOfBirth
|
Phone
|
Email
|
13
|
Troy
|
Magennis
|
1980-08-07
|
425 749 0494
|
troy@aspiring-technology.com
|
Orders Table:
OrderId
|
ContactId
|
ProductId
|
DateOfPurchase
|
12
|
13
|
3
|
2006-11-30 18:50:24.187
|
Products Table, nothing was added, but a reference to the ProductId of
Asset Blaster Pro was used in the order table record. All of this
looking up of primary keys was automatically handled.
ProductId
|
ProductName
|
Version
|
SubVersion
|
Released
|
3
|
Asset Blaster Pro
|
1
|
0
|
2006-01-03
|
Deleting records is just as simple. You remove an object from the
current in-memory collection of objects gathered from a previous query.
// Delete the record(s) we just created (do sub-items first)
db.Orders.Remove(newOrder);
db.Contacts.Remove(newContact);
db.SubmitChanges();
Example of deleting records from the database.
Until now I’ve omitted an important step. We have been writing queries
against a type called HookedOnLINQ initialized with a database
connection string, and instance types Contacts, Orders and Products. The
HookedOnLINQ type inherits from the anchor of LINQ to SQL, a class
called DataContext. This class manages marshalling our query expressions
to SQL expressions and handles change tracking in preparation for
calling SubmitChanges. In addition we need to have types to represent
our data tables and the aspects of mapping objects and relationships to
their SQL equivalents and vice-versa. Although all of these classes can
be created by hand, it will hardly ever (if ever) be advisable. There is
built-in design-time support in Visual Studio as well as a command line
tool which does all of the heavy lifting in code generation on our
behalf.
Our custom DataContext class –
- Inherit from System.Data.DLINQ.DataContext type
- Hold
and initialize collections of our instance types (Table<[type]>)
and make them accessible (For example, so we can call db.Contacts from
within our query expressions)
Our custom instance object classes –
- Be decorated with a [Table] attribute
- Contain public fields or properties decorated with [Column] attributes
- Define foreign key relationships with a [Association] attribute
- Override
the default Update, Insert and Delete behavior by defining methods
marked with [Update], [Insert] and [Delete] attributes
- Define Store Procedure, View and Function wrappers with methods marked with a [StoredProcedure], [View] or [Function] attribute.
- Ensure that PropertyChanging and PropertyChanged events are raised whenever a value is altered.
To generate the wrapper classes and DataContext derivative that allow
LINQ to SQL functionality over the tables and other database objects
here are our choices:
- Do it all manually by hand;
- Use the built-in designer for Visual Studio 2005;
- Use the SQLMetal command line tool;
- Use
an XML mapping file to link database tables and columns to types and
properties. This allows database and mapping changes to occur without an
application recompile.
To generate the object wrapper for our sample database called
HookedOnLINQ, using the command line tool, you run the SqlMetal
application with the following arguments.
sqlmetal /server:(local) /database:HookedOnLINQ /code:HookedOnLINQ.cs
It creates a HookedOnLINQ.cs fully functional for all the examples shown
so far. I just copied it into the main project and compiled the
solution.
The built-in designer allows you to create a DLINQ Object surface. From
the Server Explorer window you can drag table instances onto that
surface. Foreign Key relationships are automatically added to the
surface if they are defined in the database, or you can manually add
them from the Toolbox. When you compile, the DataContext and instance
types are created for you. Here is a DLINQ Object surface representing
our HookedOnLINQ schema from the database.
LINQ to SQL Designer Surface. Dragging tables from the server exploer
creates object model and automatically defines relationships.
The alternative method to using attributes that link the relational
model to the object model is to move the mappings to an XML file. The
SQLMetal command line tool will create this XML file for you, but you
could also automate its generation in any way you desire. When you
create your DataContext, you can pass in the mapping XML, and this will
have exactly the same effect as using attributes, except it’s not
hardcoded into your application when you compile.
Many people believe that database access should always be performed
through Stored Procedure to improve security (permissions can be granted
only for those stored procedures an application should run), and for
improved performance (query plans are cached between calls and better
optimization can be carried out). LINQ to SQL fully supports Stored
Procedures for general calls and the update, insert and delete
operations, and in many cases improves the developer experience by
freeing you from having to create input parameters by hand or having to
create a strongly typed object collections to work with any returned
results. However, solely using Stored Procedures eliminates the benefits
of writing Query Expressions in the developer’s native coding language.
There is middle ground though; you can use Stored Procedures for all
Insert, Update and Delete operations and use Query Expressions for data
retrieval. This allows the database to be secured against data
corruption, while still allowing the developers to construct query
expressions in VB or C#.
Calling stored procedures is made extremely easy. Using traditional
ADO.NET you were forced to construct parameters by hand prior to
constructing a database connection and actually calling the procedure.
The code generation tools supplied as part of LINQ to SQL create wrapper
functions for stored procedures, and also create strongly typed objects
to hold the return values.
The following stored procedure code retrieves a list of overdue
payments. The number of days overdue is passed in as a parameter. The
result is a cursor with a number of columns, definitely not a type we
have declared in C# objects before.
ALTER PROCEDURE [dbo].[GetOverdueAccounts]
@daysOverdue int = 15
AS
BEGIN
SET NOCOUNT ON;
SELECT o.OrderId, o.Quantity, o.DateOfPurchase, o.Discount,
c.FirstName + ' ' + c.LastName AS CustomerName,
c.Phone, c.Email,
p.ProductName, p.Price,
((p.Price*o.Quantity)*((100-o.Discount)/100)) AS Cost,
DATEDIFF(day, o.DateOfPurchase, GETDATE()) AS OverdueDays
FROM Orders o,
Contacts c,
Products p
WHERE o.ContactId = c.ContactId
AND o.ProductId = p.Product_Id
AND o.PaymentApproved = 0
AND p.IsBeta = 0
AND DATEADD(day, @daysOverdue, o.DateOfPurchase) < GETDATE()
END
SQLMetal, the command like code generation tool has a switch that generates the wrapper and result type for stored procedures.
sqlmetal /server:(local) /database:HookedOnLINQ /sprocs /code:HookedOnLINQ.cs
HookedOnLINQ db =
new HookedOnLINQ("Data Source=(local);Initial Catalog=HookedOnLINQ");
var overdue = db.GetOverdueAccounts(30);
foreach (GetOverdueAccountsResult c in overdue)
Console.WriteLine("{0} days - {1:c}: {2}",
c.OverdueDays, c.Cost, c.CustomerName);
Output:
215 days - $300.00: Armando Valdes
30 days - $180.00: Adam Gauwain
30 days - $247.50: Adam Gauwain
We can call stored procedures simply as methods on our DataContext.
Parameters become strongly typed arguments, and a strongly-typed result
collection is returned.
