A Brief Introduction to LINQ to SQL

Introduction to LINQ to SQL

I know there are a lot of LINQ 2 SQL introductions, and you already are sick about them. So I will keep this section as brief as possible.

Over the years the community kept fancying strongly typed objects rather then not OO friendly Datatables and Datasets, Microsoft kept pushing them to go in that way. Since there are understandable reasons, (such as performance) this didn’t change the fact that in an object oriented world, whose requirements are already getting more complex and complex. Typed Datasets couldn’t be response to these requirements even it was making things easier to manage.

This approach also had a consequence of ignoring multi tier and professional real world applications (It would be more difficult to support these scenarios in a 5 minutes of drag & drop presentation!)

Lots of companies(such as CodeSmith)had seen an opportunity here, and they were damn right, so they made lots of money out of that.

So What is Linq 2 SQL and Does it fit in this picture ?

Linq 2 SQL is both an ORM and Code Generator. Although (mostly java guys) humiliate it, I see it as a great step forward for .NET environment, and has a lot of nice features. It speeds up the data managing & generating entities process (no tricks this time, really drag & drop & tweak and it works). And of course it has lots of cons which I will talk about in the later posts.

Following gives an idea where Linq 2 SQL resides in all this LINQ 2 X family :


                                            Figure 1.1 Linq Architecture Overview

Linq 2 SQL is coming with very easy to use tools to ease the code generation. And the generated code is surprisingly good, and using the new .NET framework 3.5 features, such as automatic properties, extension methods and lambda expressions. It is full object oriented and provides a very good level of abstraction.

Scott Guthrie has explained a while ago how to use designer in several blog posts and a very nice video . So be calm, I wont go down in that way J .

The good thing about designer is it picks up about everything that you defined in your db, tables, functions, stored procedures, primary-foregin keys and other constraints etc. It builds the object structure and relations between them based on these, and puts it into a DataContext that you specified before. You can manage your own DataContext and inherit from your DataContextBase for your architectural and business needs again, the designer tool is friendly to this approach.

To understand how LINQ 2 SQL works, understanding DataContext is essential. DataContext itself has lots of good features.

Being Aware of Context

There are 4 DataContext Constructors lets you create a data context. When you create a DataContext of your choice through designer, it creates and associates a connectionstring in settings file or app.config if it exists. Beware that if you want to replace this behaviour in configuration file (a common practice it to put it to web.config for web applications for instance) you need to make sure that a connection string with the same name exists within the current context.

But if you want to do it programmatically, you need to create the DataContext with an IDbConnection .

Understanding How DataContext Works

If I say that the LINQ 2 SQL world revolves around DataContext, I wouldn’t be much exaggerating. Our aim is to do basic insert, update and delete operations as quick & efficient as possible, so we need to understand that DatContext is the gateway to perform this goal.

DataContext is pretty flexible and enables you to work directly with objects or run your own queries / stored procedures or user functions.

1 – Working with the Objects

A cool way of using DataContext is making it to generate Insert, Update and Delete SQL statements against your data model. This works pretty well if you really know what you are dealing with. After generating your context via designer or SqlMetal code generator tool, you have your DataContext with your Entities generated and attached to it. Below is how Northwind Data Context looks like after a generation is performed :


                                                   Picture 1 : Northwind Entities

As you see categories table is mapped to Categories Generic Table that consists of a Category Type that’s mapped to each row.In this table class, there is a set of extension methods that you can use (we will in later posts) to construct SQL statements in a better object oriented way.

However, as you would expect, the relations between types shouldn’t be Table object or something that reminds db to us. EntitySet and EntityRef is there for this purpose. EntitySet in the context. EntitySet is a generic set of entities specified, Category in our case. It implements IList interface so there is no harm to say this is basically a list and we can use every method, including extension methods that a Generic IList can benefit.

If the relationship is one-to-one between the tables then an EntityRef is created for that entity.

Just to keep in mind that these two are NOT serializable, we are going to discuss this issue in later posts.

There are 2 cool features of data context that we need to know here: Object Tracking and Deferred (or Lazy) Loading.

- Object Tracking

This is the change tracking system that Linq to SQL provides. If you want your queries to be generated on the fly automatically, this is the system that provides it. However, if you only do want to select and perform read only operations and don’t want to track any changes, disabling it will improve the retrieval performance.

To disable it, you need to set ObjectTrackingEnabled property of your context to false. The default is true.

The golden rule working with object tracking is : “Data Context needs to be aware of every insert, update and delete to generate appropriate sql statement”. You need to tell DataContext what objects to insert, what to delete and what to update.

You can work with a DataContext that has ObjectTrackingEnabled in 2 ways : Connected and Disconnected.

In connected mode, everything is easy, and world is a very nice place to live. You add, delete, update from/on context and these are all trivial operations: You just manage the collection, add, update or remove an object, and call the magical context.SubmitChanges() method, and that’s it. Your statements are generated and executed. Everyone is happy.

But things aren’t as simple and this is usually not the case. In a service oriented, n-tier world, or long running sessions, keeping DataContext alive is an unreasonable hope. So in disconnected mode you need to tell explicitly what you need to insert, update and delete.

You do this with a bunch of method provided in the generic table that is associated . I will try to show how to use these methods in action in another post but to name them here :

For Insertions => InsertOnSubmit for a single entity, InsertAllOnSubmit for collections.

For Deletions => DeleteOnSubmit for a single entity and DeleteAllOnSubmit for collections.

For Updates => You need to use Attach for a single entity and AttachAll for collections. You’ll see that Attach methods have 2 overloads, one with the entity and the other one with asModified boolean. Setting this to true means that your entity will be included in the generation process even if you made any changes on that.

The way only way of informing that a disconnected object had been changed is through Attach methods. For instance, If you want to delete an object that is not in the context, you first need to attach it to the context. Otherwise, your delete will fail with this message

System.Data.Linq.ChangeConflictException: Row not found or changed

We are doing these to inform context about object tracking states. Each method changes the object’s state to a corresponding value to be able to generate the appropriate query at the end.

- Lazy Loading (Deferred Loading)

In LINQ, if you query for an object, you get the object that you requested. Nothing else, no child relations or back references are loaded. This is achieved because DeferredLoadingEnabled is true by default. You can set this to false if you want to disable it, but that is usually not the best thing to do. We usually need to customize what to load and what not to load.

In this sense, there is a property called LoadOptions of type DataLoadOptions in System.Data.Linq namespace.

The code below illustrates an example usage of this.

1:  private IQueryable<Category> GetDescribedCategoriesWithPicturesAndProducts()
2:  {
3:     using (NorthwindDataContext context = new NorthwindDataContext ())
4:     {
5:         System.Data.Linq.DataLoadOptions options = new System.Data.Linq.DataLoadOptions();
6:         options.LoadWith<Category>(ct => ct.Picture);
7:         options.LoadWith<Category>(ct => ct.Products); // first level
8:         options.LoadWith<Product>(p => p.Supplier);
9:         options.LoadWith<Product>(p => p.Order_Details); // second level
11:         return context.Categories.Where<Category>(ct => !string.IsNullOrEmpty(ct.Description));
12:      }
13:  }

As you already noticed, you can submit a request to load second or Nth level loading (I would appreciate if you let me know if you know the upper limit of this N). One good thing to keep in mind here is to avoid cyclic loads.

2 – Running Plain Text-Based SQL Queries

One other usage of LINQ is to use context as a gateway to run your predefined sql statements.

a) ExecuteCommand

You can call DataContext.ExecuteCommand to achieve this.

1:  using (NorthwindDataContext context = new NorthwindDataContext())
2:  {
3:     int rowCount = context.ExecuteCommand(“SELECT * FROM CATEGORIES”);
4:  }

b) ExecuteQuery

You can call ExecuteQuery generic method and map the results to an entity of your choice as follows.

1:  using (NorthwindDataContext context = new NorthwindDataContext())
2:  {
3:    IEnumerable<Category> categories =   

context.ExecuteQuery<Category>(“SELECT * FROM CATEGORIES”);
4:  }

This was the end of the introduction. Hope it helped you a bit to figure the concepts out and get started. In later posts I will try to get into more advanced topics and create a real world application using LINQ in a multi tiered environment in conjunction with WCF.
kick it on DotNetKicks.com

Technorati Tags: ,,,

Share it on: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google
  • Blogosphere News
  • e-mail
  • YahooMyWeb
  • DotNetKicks
  • DZone

11 Responses so far »

  1. 1

    Turhal Temizer said,

    April 21, 2008 @ 11:56 am

    Super article, congratulations :)

  2. 2

    Sidar Ok said,

    April 21, 2008 @ 12:03 pm

    Thanks man, you are my first technical commenter !! :)

  3. 3

    Can BAKIR said,

    April 21, 2008 @ 12:51 pm

    Hey guy, this is such a great article. I am also waiting articles about WCF, LINQ and WPF. just added your site to my favourites. Run baby run.

  4. 4

    Javier Crespo said,

    April 21, 2008 @ 6:14 pm

    Great post man, quality stuff. Having worked with NHibernate, I think that Linq is a great thing :)I’m looking forward for more posts!!

  5. 5

    Sidar Ok said,

    April 21, 2008 @ 10:58 pm

    Thanks man, with all those interceptors, Sessions and messy XML mapping files with no tool suppport at all (we suffered together :) ) yes, NHibernate was a complete PITA in contrast. I think LINQ 2 SQL also has also a long way to go, but it has kicked off a good start. I am planning to write more advanced stuff in next posts, lets see the lazy coder :)

    Thanks for your great encourage !

  6. 6

    Harun said,

    April 24, 2008 @ 1:43 pm

    Thats great, It helped me a lot. Thanks

  7. 7

    firstX said,

    December 27, 2009 @ 12:15 am

    you ommit to include options in call to datacontext !
    the datacontext won’t know about the load options if you don’t tell him.
    please review the example for lazy loading !
    and sorry i don’t speak english .. so i do with what i have :)

  8. 8

    progrish said,

    December 21, 2010 @ 10:51 am

    Good job

  9. 9

    Varun Maggo said,

    October 3, 2011 @ 9:55 am

    thanks bro


  10. 10

    Guadalupe Girsh said,

    November 1, 2011 @ 12:11 am

    Hi. I needed to drop you a quick note to impart my thanks. I’ve been watching your webpage for a month or so and have picked up a heap of sound information as well as enjoyed the way you’ve structured your article. I am setting about to run my own webpage however I think its too general and I would like to focus more on smaller topics.

  11. 11

    Sudhanshu said,

    March 7, 2012 @ 11:14 am

    Impressive Post,

Comment RSS · TrackBack URI

Say your words

You must be logged in to post a comment.