Hooked on LINQ

Hooked on LINQ - Developers' Wiki
for .NET Language Integrated Query

Companion book for this site
LINQ to Objects Using C# 4.0:
Using and Extending LINQ to Objects and Parallel LINQ (PLINQ)
Quick Search

Advanced Search »

LINQ to SQL - 5 Minute Overview

This Page is locked
Modified: 2008/04/24 20:09 by t_magennis - Categorized as: LINQ to SQL, Samples
I want feedback on this article. Please leave your comments in the discussion page (click on the Discuss button on the top-right of this page)

(See also: Main LINQ to SQL Page)

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
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 FirstNameLastNameDateOfBirthPhoneEmail
13TroyMagennis1980-08-07425 749 0494troy@aspiring-technology.com

Orders Table:
121332006-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.

3Asset Blaster Pro102006-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
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.

(See also: Main LINQ to SQL Page)

LINQ to SQL - 5 Minute Overview is Copyright © Troy Magennis.

If you would like to comment on this page, click on the Discuss button located on the top-right of each page. Feel free to edit any mistakes or omissions you find. If you have an objection or find in-appropriate content then contact the administrator. This website is not affiliated with Microsoft®, all content and opinions are those of the specific author and some advice, solutions and article may contain unintentional errors - please use care. Other websites by this author: Focused Objective, Geek Speak Decoded.