DataContext class in depth : LINQ

In this part of the series I will explain the DataContext class in depth through a series of examples and explanations.

Introduction

Whenever someone asks me about the DataContext I always refer to it as the “brains” of Linq to SQL, why? Because it is. It is responsible for the translation of Linq to T-SQL, and the mapping of the results (rows) of that query to objects, and so much more.

If you run any codegen tool (SqlMetal, or Linq to SQL file (VS Orcas designer)) you always get a class that derives from the DataContext class. The DataContext can be equated to a database, in that it contains a series of tables (normally this is implemented as a property that returns a Table), and sprocs (methods calls that invoke underlying sprocs at the DB layer).

What’s so special about the DataContext class?

Most notably the DataContext takes some Linq code and generates the appropriate T-SQL statement for that query; if that query returns rows then the DataContext object will take care of mapping those rows to objects.

Figure 5-1: DataContext core functionality

I want to see the T-SQL generated by my LINQ

If you are a control freak/perfectionist like me then you probably want to know exactly what T-SQL is being generated for you at all times.

Before I show you some code please remember that the T-SQL generation by the DataContext object is not perfect, if you know of a very efficient T-SQL solution then implement that in a sproc. Saying that I have found the T-SQL generation of the DataContext object to be efficient in some advanced queries. Like so many things in programming one way, and one way alone is often not the best approach – experiment to see what offers the best performance for your query.

There are a few ways in which we can monitor the T-SQL being generated by our DataContext object, these include:

• Visual Studio Orcas
• ToString() override of query
• DataContext Log property
• SQL Server 2005 profiler (not included with Express edition)

Visual Studio Orcas

As Linq is a 1st class citizen within the C# and VB.NET languages it should be no surprise to you whatsoever to see rich debugging support within the IDE.

Figure 5-2: Seeing the T-SQL generated by the DataContext object in VS Orcas

ToString() override

This is a really nice feature – if you call the ToString() method on any variable whose value is a query you can see the associated T-SQL for that query.

Figure 5-3: Calling ToString()

using System; using System.Linq; using System.Collections.Generic; namespace ConsoleApplication4 { public class Program { public static void Main(string[] args) { using (BookShopDataContext db = new BookShopDataContext()) { IEnumerable<Book> query = from b in db.Books select b; Console.WriteLine(query); } } } }

Figure 5-4: Result of running the code in Figure 5-3

Concurrency

Concurrency is always an issue – Linq to SQL by default uses optimistic concurrency. Take an example of where we retrieve a particular piece of data and then update that data, we then try to submit the updated data to the server but we get an exception as Linq to SQL knows that we have violated a concurrency check as we were acting upon “old” data.

Just to show this I did a pretty simple operation by adding a breakpoint on the line where I call the SubmitChanges(...) method and then executed some T-SQL code to alter that record and then continued the execution of my application so I would get an exception.

Figure 5-6: ChangeConflictException

The SubmitChanges(...) method is overrided to take a ConflictMode enum where we can change the default behaviour when data is submitted to the database, these are:

• ContinueOnConflict
• FailOnFirstConflict

The above can be used when within a TransactionScope. There are a few attribute values we can apply to our generated code to specifically say “we don’t care if this particular bit of data is changed – just overwrite it”. We do this using the UpdateCheck parameter to the Column attribute.

Figure 5-7: We don’t care about this data – just overwrite the value with what we have!

[global::System.Data.Linq.Column(Storage=”_Title”, Name=”Title”, DBType=”NVarChar(50) NOT NULL”, CanBeNull=false, UpdateCheck=System.Data.Linq.UpdateCheck.Never)]

The UpdateCheck enum values:

• Always
• Never
• WhenChanged

I’m pretty sure I’ve not covered everything in Linq to SQL with regards to concurrency – remember though that you can use the TransactionScope type in the .NET framework to assist you.

Using VS Orcas to look at data

Remember – queries only execute when we call the GetEnumerator() of the variable that holds the query! In previous CTP’s of VS Orcas it was hard to see what data we pulled back from the database as the debugger didn’t call the GetEnumerator() method when you wanted to look at that particular variable – the March CTP does however which is really great!

Let’s go ahead and look at using VS Orcas to look at data returned from our database using the Locals window when debugging. First place a breakpoint after the line you define a query, you don’t need a foreach to call the GetEnumerator() – the IDE will do that for us. When you get to your breakpoint you will see a screen similar to that in Figure 5-8 where the variable holding our query has a message saying something like “expand me to enumerate this IEnumerable”.

Figure 5-8: Thinking about enumerating the IEnumerable

Expand the results! You know you want to.

Figure 5-9: Viewing the results

You can see that in Figure 5-9 I have two objects returned from my query, I can drill down into the values of their properties and so forth.

On the face of it this is a pretty basic enhancement to the IDE, and you would be right in thinking so – but this is a great visual tool which I’m sure you will love when using Linq to SQL!

LINQ to SQL FAQ

Will LINQ to SQL support Oracle?

This is a common question, the answer to which is no. Linq to SQL is a lightweight O/R mapper targeted for use with SQL Server 2000/2005. For a more powerful O/R mapper look at the Entity Data Model (EDM) – EDM will support Oracle and other 3rd party databases.

I hate codegen tools, how much code does it really take to hook up to my DB using LINQ to SQL?

Surprisingly little, essentially you need to create a property with the correct access (get, set, or both?) with the appropriate Column attribute. The class with your properties should be pluralized and use the Table attribute to map it to the corresponding table in the database.

Finally you will need to create a class that derives from DataContext. In this class you will want to expose some properties that return a generic Table.

Below is the code for the TodoListDataContext and Task types:

Task.cs (type that is associated with records in Tasks):

using System; using System.Data.Linq; namespace ConsoleApplication5 { [Table(Name=”Tasks”)] public class Task { private int _taskId; [Column(Name = “TaskID”, Storage = “_taskId”, IsDBGenerated = true, IsPrimaryKey = true, CanBeNull = false, DBType = “int not null identity”)] public int TaskID { get { return _taskId; } } [Column(Name = “Title”, CanBeNull = false, DBType = “nvarchar(50) not null”)] public string Title { get; set; } [Column(Name = “Complete”, CanBeNull = false)] public bool Complete { get; set; } } }

TodoListDataContext.cs (type that exposes a Table of type Task, and defines connection string)

using System; using System.Data.Linq; namespace ConsoleApplication5 { public class TodoListDataContext : DataContext { public TodoListDataContext(string conn) : base(conn) { } public Table<Task> Tasks { get { return GetTable<Task>(); } } } }

Why are the changes to my data in SQL Server not being persisted?

Remember, in order to persist changes to SQL Server you need to call the SubmitChanges(...) method on your DataContext object after you have made changes to any data.

Can I use XML to define the O/R mapping?

Yes. You can use SqlMetal to generate an XML mapping file, when creating a DataContext you need to specify that your mapping is defined in an XML file by using the XmlMappingSource type.

I’m hitting the database every time for two identical queries, can I prevent this?

To prevent hitting the database every time you can load the results into memory (e.g. Append ToList() to a query) any subsequent calls to that query will use the in-memory collection rather than going off and querying the database again.

Is it possible to create code from XML?

Yes. You can create a .dbml file using SqlMetal and then generate a codefile (either VB.NET or CS) based on the XML defined in the .dbml.

What’s this deferred loading stuff?

Linq to SQL uses deferred loading when accessing properties of a related entity. Given a simple select query returning all Books, if I then want the publisher for each book then I can do so, however, behind the scenes the DataContext is doing a select where query to get the publisher for the current book. This is deferred loading.