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).
Note: The DataContext can also handle other SQL Server features like views, and table functions etc.
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)
Note: As the majority of people reading this article will be using the Express editions of the developer tools I will skip the SQL Server 2005 profiler demo. Those who have used the profiler before will know that it is a real time T-SQL dump of everything that a particular SQL Server 2005 instance is doing so seeing what T-SQL the DataContext is generating is a trivial task.
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 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:
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 Tasks
- {
- get { return GetTable(); }
- }
-
- }
- }
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.
Note: You can disable deferred loading using the EnableDeferredLoading property of a DataContext object.
Summary
There is a ton of stuff that’s special about the DataContext that I haven’t covered – I encourage everyone to go check out the DataContext class and experiment. I hope that I have provided you with the knowledge to get started with Linq, and Linq to SQL – if you have any questions about a particular feature of Linq to SQL I have/haven’t covered then please feel free to email me via the address on http://gbarnett.org/about.
read original
