Basics of Linq to SQL Plumbing

A few notes and getting started with Linq to SQL using the O/R Mapper – including a peak at the generated code. We’ll assume VS2008 is used for all this.

So, to start using Linq to SQL I’d recommend adding a new class library to your project and naming it something like BLL or DAL – busines or data access layer, as Linq effectively rolls these to jobs into one you can take your pick on this.

Next  up, to get started add a “Linq to SQL” class to your project using the Add New Item and then look under the Data templates. This will give you a datacontext you can use to update a set of tables, so depending on your needs name this either after your entire database, or give it a name representing a subset of database tables (like UserContext or OrderContext).

Once you have done this, you will see a new .dbml file in your solution, and once this opens you will be looking at the Visual Studio Object Relational Mapper – O/R Mapper for short.

Next step is to test this on some table. Bear in mind that updates and retrieval by default is on a 1-to-1 table basis with the code generated from the O/R Mapper – however, as you will see there are fairly easy ways to extend your classes to be able to load relational data effectively.
In many ways having the 1-to-1 limitation can be seen as a good thing. There are ORM tools out there like EntitySpaces, IronSpeed and many more who will build the whole database in code for you, many-to-many relations and all – however, while it’s nice to auto-generate a complete datalayer with two clicks and a cup of coffee, you can also very easily begin to feel that you don’t really know 100% of what is actually really going on in your application.
And let me just assure you that for a developer – that is not a good thing! If you don’t know how it works, no-one knows how it works.

In contrast to this, Linq to SQL strikes a healthy balance between the convenience of generating classes and a data context (which is easy to query and understand) and leaving the implementation of more advanced features (like retrieving and updating related data) and the core business logic for the developer to implement.

Anyways – moving on. Once you have your O/R Designer window open, get the Server Explorer window up and drill into you database and pick two or three good tables to play around with. Preferably these should be related in the database, and you might also want to add a TimeStamp column to each table (I call mine RowVersion).

First thing to do here is to look at the visual tables in the O/R mapper – check your properties window and see that you have a nice set of data for each one. Note that you have description of the SQL datatype, the matching CLR type, you have null indication, primary key, update checks (check your Timestamp columns! (if you are unfamiliar with timestamps google this, it’s an important SQL feature. Each table can have one Timestamp column (with whatever name you give it) and this is changed every time that row is updated – so it’s a concurrency tool) .
Also note that your relations form the database are displayed with the correct parent/child (PK/FK)  relation.

Next thing you might want to do is save and build, and then grab the full path from the .dbml file tab in Visual Studio and open this file in your favourite text editor (which should of course be the most excellent EditPlus). So here you can see how your SQL tables are described – and note that your relations  are also included in both the FK and PK table.

Next, jump back to VS2008 and have a look at the generated designer.cs class. Here you can see a load of properties, and you should be able to link these back to the information in the .dbml file pretty easily. Don’t worry too much about the details for now.

Next go back to the visual view of the .dbml file in VS2008, right-click one of your tables and choose “View Code” – note that this gives you access to a partial class, that extends the autogenerated class. For now just make a note of this, and realise that this has quite a lot of potential for extending your autogenerated class.


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: