LINQ to SQL

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:

1. Do it all manually by hand;
2. Use the built-in designer for Visual Studio 2005;
3. Use the SQLMetal command line tool;
4. 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.