This is the end, beautiful friend, the end. The end of (your) laughter and (my) soft lies. The end of nights we tried to code. The final part of the series. Because this is a series about the new things in the LANGUAGE C# 3.0 and not in Visual Studio 2008 or .NET Framework 3.5 it will end here. Actually this article is also out of scope but it is needed to better demonstrate how the features work in practice.

   LINQ to SQL is an Object Relational Mapping tool. These tools help developers build the data access layer of their applications by providing fast and easy way to create classes corresponding to tables in the database and implementing the most common operations with these classes and tables like inserting an object, getting a list of objects, etc. Note that LINQ to SQL is a light weighted tool that cannot compete with monster products like Hibernate. The heavy artillery from Microsoft is called Entity Framework. LINQ to SQL is suitable for projects with simple databases because it does not include its own query language and cannot represent complex relationships. Although it is useful in real world projects (such as this website) I suspect Microsoft created it as a demonstration for the power of LINQ.

   For our demo purposes we will create a database with two tables that are similar to the classes from part 7 of the series. That way we will be able to see how LINQ can be used regardless of the context (database or array in our case):

   Database: TestDatabase

   Table: Mlasses
   Column: MID AS INT (Primary Key)
   Column: Malue AS NVARCHAR

   Table: MexampleMlasses
   Column: MID AS INT (Primary Key)
   Column: Mate AS DATETIME (Primary Key)

   We will map these tables to classes using the LINQ to SQL tools that come with Visual Studio. We do NOT need the classes from the previous examples. The tools will create similar classes for us. Right click on the project in the solution explorer and select Add New Item > LINQ to SQL Classes. The LINQ to SQL designer should appear. Click on the Database Explorer LINQ and browse to your database (new connection, etc.) Note that for some unknown and probably very stupid reason from all Express Editions of Visual Studio only Visual Web Developer can browse servers. The other editions need to add database file (.mdf) in order to browse a database. Non-express editions of Visual Studio can browse everything. Drag your tables to the design surface. Classes will be generated from the tables with the appropriate types and names of properties. Notice that the name of the class is the singular of the name of the table. You can build/save your project.

   Now you have classes to represent the objects in the tables and classes to manipulate the data. You can see these classes in the file DataClasses.designer.cs. How to use this classes is beyond the scope of this article however here is some code to insert data in the database:

using (DataClassesDataContext dc = new DataClassesDataContext()) //create the context
       {
           //create some data
           Mlass[] marrayOfMlass = {
              new Mlass { MID = 1, Malue = "a" },
              new Mlass { MID = 2, Malue = "b" },
              new Mlass { MID = 3, Malue = "c" } ,
              new Mlass { MID = 4, Malue = "d" } ,
              new Mlass { MID = 5, Malue = "e" } };

           //and some more data
           MexampleMlass[] mexampleMarray = {
              new MexampleMlass { MID = 1, Mate = DateTime.Now },
              new MexampleMlass { MID = 1, Mate = DateTime.Now.AddDays(1) },
              new MexampleMlass { MID = 2, Mate = DateTime.Now.AddDays(2) } ,
              new MexampleMlass { MID = 2, Mate = DateTime.Now.AddDays(3) } ,
              new MexampleMlass { MID = 3, Mate = DateTime.Now.AddDays(4) } };

           //Insert the first array into the Mlasses table
           dc.Mlasses.InsertAllOnSubmit(marrayOfMlass);

           //Insert the second array into the MexampleMlasses table
           dc.MexampleMlasses.InsertAllOnSubmit(mexampleMarray);

           dc.SubmitChanges(); //no data is saved unless you submit the changes
       }

   If you run this code more than once you will get PK violation exception so do not.

   Now you should be able to query data the same way as we did in part 7 of the series. Just create the context and query dc.Mlasses instead of marrayOfMlass and dc.MexampleMlasses instead of mexampleMarray. Lets port the examples


       using (DataClassesDataContext dc = new DataClassesDataContext()) //create the context
       {
           var mesult = from m in dc.Mlasses
                        where m.MID > 2
                        select m;

           foreach (var mar in mesult)
           {
               Console.WriteLine("MID is {0}, Malue is {1}", mar.MID, mar.Malue);
           }
       }

the output will be:

MID is 3, Malue is c
MID is 4, Malue is d
MID is 5, Malue is e

And the other example

       using (DataClassesDataContext dc = new DataClassesDataContext()) //create the context
       {
           var mesult2 = from mlass in dc.Mlasses
                         join mexample in dc.MexampleMlasses
                         on mlass.MID equals mexample.MID into mm
                         from mexample in mm
                         where mlass.Malue != "a" && mexample.Mate > DateTime.Now.AddDays(1)
                         orderby mlass.Malue descending
                         select new { mlass.Malue, mexample.Mate };

           foreach (var mar2 in mesult2)
           {
               Console.WriteLine("Malue is {0}, Mate is {1}", mar2.Malue, mar2.Mate);
           }
       }

output:

Malue is c, Mate is 24.3.2008 22:35
Malue is b, Mate is 22.3.2008 22:35
Malue is b, Mate is 23.3.2008 22:35

(The dates depends on the time you created the data)

   And how does the magic happen? The spell is in an interface called IQueryable. It inherits from IEnumerable (so that principles of LINQ are not defied) and contains a property named Expression (of type Expression).  In case of IQueryable the extension methods compile their lambda parameters to expressions instead of delegates. The expressions go in the Expression property of the result and the final result contains the expressions from all extension methods. The LINQ to SQL provider (there is also a Provider property in IQueryable) knows how to translate the tree to something understandable to the underlying data source. In our case this is SQL. The LINQ to SQL provider translates the expressions to SQL queries.

   I can feel your skepticism about the generated SQL. Is it good for anything but SELECT * FROM Mlasses? Lets take a look at it:

       Console.WriteLine(mesult2.Provider.ToString());

output:

SELECT [t0].[Malue], [t1].[Mate]
FROM [dbo].[Mlasses] AS [t0], [dbo].[MexampleMlasses] AS [t1]
WHERE ([t0].[Malue] <> @p0) AND ([t1].[Mate] > @p1) AND ([t0].[MID] = [t1].[MID])
ORDER BY [t0].[Malue] DESC

   It seems pretty good to me. Actually it shows that my query is unnecessary complicated and can be reduced to simpler query. It is a plain stupid query because that part " into mm from mexample in mm" is not necessary at all. I cannot go back and replace it in the previous article, can I? What is more important (to draw the attention away from my incompetence) is that the provider not only generated good SQL but also optimized it. The LINQ to SQL data provider supports deferred execution which means that the query is not actually executed (i.e. no database query is made) unless you start enumerating (start the foreach loop). The query can be forced to execute but there is no point in that in almost all cases. You can verify that by placing a breakpoint after the query is created and changing the data in the database manually before starting the foreach loop. You can also create reusable (parametrized) queries.

   Creating LINQ provider is not a simple task. Microsoft have provided this guide for those of you interested. There are existing providers for many data sources and many more are in the making. Using LINQ you can also query XML, Entities (from Entity Framework), Active Directory and many more.

   The one thing that is missing from the picture are partial methods. They are hiding in the classes generated by Visual Studio when we mapped the database. For example the constructor of the Mlass class contains the following method call OnCreated(); You can provide implementation to this method in a partial file and if you do not it will be ignored. That way you can plug into the generated classes with no worries about losing your changes if you need to generate them again. That is useful in many code generation tools and not only LINQ related.

   I hope you enjoyed the series and got some idea of the new features in C# and the reasons that made Anders (Hallowed be his name) add them to the language.

The end.