Published: 06 Jun 2007
By: Granville Barnett

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

Introducing LINQ Series

  • Part 1 In this part you will learn how to the query in-memory collections.
  • Part 2 In this part we will look at querying relational data.
  • Part 3 In this part we will look a little more at what entities are, as well as taking a closer look at the key types we can use and their application.
  • Part 4 In this part of the series we will use LINQ, ASP.NET and ASP.NET AJAX to replicate the to-do list that Scott Guthrie created a while back to show off the features of ASP.NET AJAX.
  • Part 5 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).

    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()

    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!

    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):

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

    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.

    Introducing LINQ Series

  • Part 1 In this part you will learn how to the query in-memory collections.
  • Part 2 In this part we will look at querying relational data.
  • Part 3 In this part we will look a little more at what entities are, as well as taking a closer look at the key types we can use and their application.
  • Part 4 In this part of the series we will use LINQ, ASP.NET and ASP.NET AJAX to replicate the to-do list that Scott Guthrie created a while back to show off the features of ASP.NET AJAX.
  • Part 5 In this part of the series I will explain the DataContext class in depth through a series of examples and explanations.
  • <<  Previous Article Continue reading and see our next or previous articles Next Article >>

    About Granville Barnett

    Sorry, no bio is available

    This author has published 32 articles on DotNetSlackers. View other articles or the complete profile here.

    Other articles in this category


    C# 4.0 Reflection Programming - Part 2
    As introduced in the first article, the most typically-used tools associated with .NET reflection ar...
    C# 4.0 Reflection Programming - Part 3
    In the previous article, we used the reflection to obtain the information of an assembly, module, ty...
    C# 4.0 Reflection Programming - Part 4
    In this last article of this series, we will learn what to do with reflection. But before making the...
    Understanding and Using Extension Methods
    Extension methods were new to C# 3.0. They allow you to add a method to an existing type without hav...
    Introduction to C# 3.0 features
    C# 3.0 introduced some of very useful features built on top of 2.0. This article explains the usage,...

    You might also be interested in the following related blog posts


    Oredev Wrap-Up read more
    Introducing SharePoint 2010 Training at U2U read more
    The Underground at PDC read more
    10 resources to learn Moq read more
    Building A Product For Real read more
    My History of Visual Studio (Part 6) read more
    BeginDialOut with Office Communicator Clients read more
    DotNetNuke Fusion Results for Q3 read more
    Dynamic in C# 4.0: Introducing the ExpandoObject read more
    GiveCamps Get a new Sponsor read more
    Top
     
     
     

    Discussion


    Subject Author Date
    placeholder Why there is no sorting option for grid view if object datasrouce is using linq in Bll Najam Sikander 6/1/2009 3:29 AM
    Great!! really all articles of Linq are nice. Must go through it. Anandu 4u 1/4/2010 10:05 AM
    placeholder Possible to execute SQL queries using Linq? Shanta Lokappal 10/19/2008 12:28 PM
    Great article series Jakob Flygare 3/7/2009 1:02 PM

    Please login to rate or to leave a comment.