Share your knowledge and create a knowledgebase.
Share your knowledge and create a knowledgebase.
In this part of the series I will explain the DataContext class in depth through a series of examples and explanations.
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).
DataContext can also handle other SQL Server features like views, and table functions etc.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.
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:
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.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.
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.
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.
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:
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.
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.
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”.
Expand the results! You know you want to.
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!
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.
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:
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.
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.
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.
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.
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.
EnableDeferredLoading property of a DataContext object.The ASP.NET application that we will create we be a 3 tier application, consisting of a data access layer (DAL), a business logic layer (BLL) and a presentation layer (PL).
We will be using Visual Studio Orcas Beta 1 for this tutorial.
Before we jump into coding some C# or ASP.NET we need to setup our database, more importantly we first need a database! If you haven’t already go and grab yourself a copy of SQL Express 2005. I will be using SQL Server Management Studio 2005 to create and define my database; however, you can do all of this using Visual Studio 2005 if you want.
Let’s go ahead and create a database for this tutorial. I’m going to create a database called TodoList. To create a database right click the Databases folder in the Object Explorer window and click New Database (Figure 4-1).
When the New Database window is visible enter TodoList (or any other name you want to use) as the Database name (Figure 4-2).
With our database created we will now add a Tasks table. In SQL Server Management Studio right click the Tables folder and select New Table (Figure 4-3).
In the table designer replicate the settings from Figure 4-4.
When you save the table, save it as Tasks (Figure 4-5).
We need to define three stored procedures, one for adding a new task, one for updating the state of a task, and finally one for retrieving all tasks of a specific state (either active, or closed).
Creating a stored procedure is easy, simply right click on the Stored Procedures folder and click New Stored Procedure (Figure 4-6).
Below are the three code listings for the stored procedures we will use.
With our database ready let’s go ahead and create our DAL. We will use the LINQ to SQL file designer in Visual Studio Orcas to do this.
TodoList database in the Server Explorer window.Right click on your website project and add a new LINQ to SQL file called TodoList (Figure 4-10).
With the file created drag and drop the Tasks table and the three stored procedures we created on to the design canvas of the designer (Figure 4-11).
That’s it! Our DAL is all ready to use.
In this tutorial there is not much point to adding a BLL, but we will add one nonetheless.
This layer in a real life application this layer would enforce any business rules (parameter checking etc…). Also in a real life application you would want to separate your DAL and BLL into their own components.
Right click the App_Code folder and add a new class called TasksBll.cs, then copy and paste the class definition given in Figure 4-12 into that file.
Each method of the TasksBll.cs (Figure 4-12) performs the appropriate operation by accessing methods defined in our DAL.
As previously mentioned we will use ASP.NET to define the UI.
The UI will consist of 3 controls:
Before we begin add a new AJAX Web Form to your project, the name you give it is irrelevant. Add all the following code snippets inside the form tag of the page.
Either a task is active or complete. We will use a drop down list control to store these values (Figure 4-12).
Before we add any more controls to our web page we need to hook our presentation layer up with our BLL. To do this we will add an ObjectDataSource and wire it up to the appropriate methods defined in our BLL.
We will now go ahead and add a GridView to our page (Figure 4-15).
To add a new task we will use a FormView (Figure 4-16).
Just to smooth things up I’ve added a few UpdatePanel’s to the page, each wrapping the GridView and FormView respectively.
And on that note we are finished! You can download the full source code at the end of this tutorial.
From personal experience coding the data access layer can be a very boring process – in this tutorial we have seen how effective LINQ to SQL can be when creating our DAL.
Visual Studio Orcas has introduced a few really cool tools to further assist with the creation of the DAL – we no longer need to spend hours on OR mapping. The great thing about LINQ is the ability to further query your DAL, and create some anonymous type.
There is no doubt in my mind that LINQ is set to revolutionize the way programmers interact with data, more so is the fact that we always use a familiar set of standard query operators!
If you download and run the source you will see the site shown in Figure 4-19.
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.
When we talk about entities we are generally talking about a more functional representation of our schema. A perfect view of this is the Visual Studio Orcas LINQ to SQL file designer (Figure 3-1). If you drag a few tables onto the design surface you will see an abstract view of your database schema showing only the entity names and the relationships between the entities.
Essentially when we talk about entities in LINQ to SQL we are more or less talking about our applications view of the data - our entities don’t necessarily need to map to tables in our database.
Go ahead and drag a few tables onto the designer.
If you take a look at the code generated for you by the designer you will see several attributes that map the particular class (entity) to a table in the database (Figure 3-2). You will also see that the properties are associated with columns in the classes associated table (Figure 3-3). An interesting thing to note is that there is not a direct mapping between CLR and SQL Server types so these attributes take care of the relevant plumbing to map the types accordingly.
The DataContext class is the most important class when using LINQ to SQL. If you have inspected the code generated for you by the LINQ to SQL File designer then you will notice that the partial class derives from the System.Data.Linq.DataContext class.
In a nutshell the DataContext is in charge of generating the SQL statement from your language query, and then mapping the rows of data (if any) returned from your database to objects. The DataContext is indeed a very interesting class (we will revisit this class throughout this series!).
If we construct a simple query (Figure 3-4) we can inspect the SQL that the DataContext generates for us (Figure 3-5).
IDisposable then make good use of it!! By wrapping our BookShopDataContext object in a using statement we implicitly call the Dispose() method for this object. Calling Dispose() releases any resources held by our object. If you don’t want to use the using statement call the objects Dispose() method explicitly within a finally block.I’m not going to cover all of the great things that the DataContext class offers to us – we will do that in subsequent parts. Stay tuned!
Let’s take a look at using stored procedures in LINQ to SQL. Up until now we have been composing ad-hoc queries in C#. I like to code my SQL queries as sprocs in the database layer and then invoke those sprocs via my apps DAL – this is a very, very common approach.
First thing we will do is create a simple sproc that simply returns all the names of the publishers, Figure 3-6 shows this.
Microsoft SQL Server Management Studio 2005 to code all my SQL. If you want you can do this in Visual Studio, however, there are some great features in Management Studio like being able to view the execution plan.If you run this query you will get all the names of the publishers in the database as shown in Figure 3-7.
With our sproc defined in our database we will go back into Visual Studio and drag the stored procedure from the server explorer window onto the designer canvas of the LINQ to SQL File (Figure 3-8).
When you have dragged your sproc onto the canvas you will see that the designer generates a method of the same name. There is a method pane on the designer that allows you to see all methods in your DAL (dragging a sproc onto the designer generates a method that executes your sproc).
I mentioned in the previous part of this series that we would be using a tool called SQLMetal in this part. I decided not to use that tool for now purely because the designer is a little more educational in that it creates a visual representation of your DAL. In the next part of this series where we create an application using LINQ to SQL we will use the SQLMetal.exe command line tool.
Before we use this method in our code let us first take some time to look at the code that the designer generated for us (Figure 3-10).
The GetPublishers() method is decorated with a StoredProcedure attribute, this attribute associates this method with the appropriated sproc in our database. What we return is an enumeration of type GetPublisher (coincidentally this type looks exactly the same as Publisher – we will rectify this in a moment!). For now we will ignore the reflection stuff and the IQueryResults interface – we will cover those bits in a few parts time!
Before we move on we will use the GetPublishers() method in a query (Figure 3-11).
Hang on! What the GetPublishers() method returns is an enumeration of type GetPublisher! What the heck is that?! Good question! Well at the moment the designer is not smart enough to recognize that you are returning back an enumeration of type Publisher, which we already have defined! What we have at the moment are two types exactly the same! We will rectify this now.
BookShop.designer.cs and delete the GetPublisher type.GetPublisher in the GetPublishers method – this includes the method signature (return type), and a few other lines with Publisher.Once you have completed the above steps your GetPublishers() method should look like that in Figure 3-12.
You can now modify the code in Figure 3-11 to that shown in Figure 3-13.
This article is based on a single table schema (don’t worry subsequent parts will see a much more complex database schema!) so that you can get to grips with LINQ to SQL (the name for using LINQ with relational data – also known as DLINQ in a previous life, but we won’t go into that…) quickly and feel confident with this awesome new language enhancement.
Conceptual what?! If you are familiar with the concept of a database schema then you don’t have much to worry about! You can think of a conceptual data access layer as being your applications view of the database.
I don’t want to bog you down with entities yet! That will come in part 3 of this series. Just know that what we deal with from our applications point of view is a series of entities describing the tables and relationships in our database schema. I will let you look forward to the diagrams explaining all of this in the next part!
Typically DAL’s have consisted of boilerplate code to map relational data to objects and vice versa, speaking from experience I really enjoyed this the first time I did it – but now it just annoys the heck out of me! With that in mind it’s no surprise that the folks on the Visual Studio team decided to give us a few tools to generate that boilerplate code.
At present there are two tools that come with Visual Studio Orcas (both were in the LINQ May 2006 CTP):
LINQ to SQL file – used to be called DLINQ objects in the LINQ May 2006 CTP, now it has a cooler name, but it’s pretty much the same tool. This is a file template that lives in Visual Studio Orcas – it’s a designer. Personally I don’t really like stuff that is “drag and drop” so I tend to opt for the finer control offered to me by SQLMetal – with that said it’s a bit of a shock to me that I have opted to use the designer for this part of the series.Before we go any further I just want to give you a quick snapshot of the table that we will be using for this part of the series – like I mentioned earlier the next part will have several tables with many relationships…think of it as a throwaway schema (actually I don’t think if even qualifies as a database schema it’s so simple!).
I bet you are questioning my credibility now aren’t you?! Come on its simple so that you can learn the basics quick so when we look at more complex examples in the future you have a good solid understanding of using LINQ with relational data.
Ok, go ahead and add some dummy data to that table.
Fire up Visual Studio Orcas (the March 2007 CTP remember) and create a new .NET 3.5 Console Application. When you’ve done that right click the project and add a LINQ to SQL File – as all we’ll be throwing away all of the code in this part just name it Part2.dbml for now.
At this moment in time we have everything setup so drag the Books table from the server explorer window onto the canvas of Part2.dbml.
If you take a look at the code that has been generated for us you will notice there is a class called Book that implements a few interfaces (more on those in the next part). Take a look at the attribute that is placed before the class definition (fig. 2-4) here we are associating this type (Book) with Books table in our database.
//… [System.Data.Linq.Table(Name="dbo.Books")] //…
Take a moment to look at the fields in the Book type – notice that the designer has created two fields of the same names as the columns in the Books table; properties have also been defined in the class marked with an attribute to associate the fields with their corresponding column in the Books table (Fig. 2-5).
//… [System.Data.Linq.Column(Storage="_BookId", Name="BookId", DBType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDBGenerated=true, CanBeNull=false)] //… [System.Data.Linq.Column(Storage="_Title", Name="Title", DBType="NVarChar(50) NOT NULL", CanBeNull=false)] //…
Before we move on we will talk a little about the Column attribute (defined in the System.Data.Linq namespace). Take a look at Fig. 2-5, notice that we explicity state the field that stores the value of the column, e.g. the BookId columns’ value is stored in the _BookId field, we also state that this property is associated with the BookId column in the Books table (Name="BookId").
If you are familiar with SQL Server data types then you will also know that there is not a 1..1 mapping between SQL Server 2005 data types and CLR types, e.g. the CLR has no money data type. For this reason we can explicitly state the database column type, the designer will choose the most appropriate type for use for that particular property, e.g. for the BookId column the designer has chosen to use the default int value type (Int32).
Before we move on, just be aware that you have to explicitly state what values are generated by the database, and whether or not they can be null. In the case of BookId the database generates a new integer automatically for each row; this column is also a primary key.
Take some time to further examine the Column and Table attributes.
We will now go through a few simple queries against the DAL that we have created in previous steps.
For each example I have provided the query version and the lambda expression version.
using System; using System.Linq; namespace Org.GBarnett.Dns.Linq { public class Program { public static void Main() { Part2DataContext db = new Part2DataContext(); var query = from b in db.Books select b; foreach (var book in query) { Console.WriteLine(“{0} {1}”, book.BookId, book.Title); } } } }
using System; using System.Linq; namespace Org.GBarnett.Dns.Linq { public class Program { public static void Main() { Part2DataContext db = new Part2DataContext(); var query = db.Books.Select(x => x); foreach (var book in query) { Console.WriteLine(“{0} {1}”, book.BookId, book.Title); } } } }
I should note that in fig. 2-7 you can omit the Select extension method and you will achieve the same result as when including the Select extension method and its lambda expression parameter; however, the example code given is clearer than when omitting the Select extension method.
using System; using System.Collections.Generic; using System.Linq; namespace Org.GBarnett.Dns.Linq { public class Program { public static void Main() { Part2DataContext db = new Part2DataContext(); IEnumerable<string> query = from b in db.Books where b.Title.Length > 6 select b.Title; foreach (string title in query) { Console.WriteLine(“{0}”, title); } } } }
using System; using System.Collections.Generic; using System.Linq; namespace Org.GBarnett.Dns.Linq { public class Program { public static void Main() { Part2DataContext db = new Part2DataContext(); IEnumerable<string> query = db.Books.Where(x => x.Title.Length > 6).Select(x => x.Title); foreach (string title in query) { Console.WriteLine(“{0}”, title); } } } }
The hype cycle now lasts less than a day. Take yesterday’s over-hyped launch of stealth search startup Cuil, which was quickly followed by a backlash when everyone realized that it was selling a bill of goods. This was entirely the company’s own fault. It pre-briefed every blogger and tech journalist on the planet, but didn’t allow anyone to actually test the search engine before the launch.
The company’s founders have a good pedigree, and have developed a unique way to index the Web cheaply and at massive scale. But creating a big index is only half the battle. A good search engine has to bring back the best results from that haystack as well, here Cuil falls short.
The story quickly turned from Google-killer to Google’s lunch (make that an amuse bouche). The results Cuil returns aren’t particularly great, and sometimes completely off the mark. For instance, a search for “Cuil” doesn’t even bring up a link to itself on the first page of results.
