LINQ to SQL Primer
I frequently see misconceptions about LINQ to SQL, and I have recently felt the need to provide some basic instruction to those looking to start using it.
LINQ to SQL is an Object-Relational Mapping tool, or an "ORM". ORMs are used to work with code objects like collections of classes rather than direct SQL manipulation. This ORM will only map to MS Sql Server Databases. Entity Framework 4 is similar and allows you to map to many different data sources.
This is not meant to be an exhaustive tutorial. This is just a breif introduction to get you started working with LINQ to SQL.
Why is this important to learn about?
Well, if you don't plan on using one, it's not. Simple as that. But if you'd rather not manually manage your database connections, queries, and table/field names in your program, then this is a great option.
When using ADO.NET DataSets, you write a query, and you get back a table or set of tables. You use string indexes to get rows out of the tables. When you use LINQ to SQL, you're working with collections (similar to a List) of type-safe classes. This will become self explanatory further in the tutorial.
Definitions of terms used.
LINQ to SQL
Object-Relational Mapping / "ORM"
This tutorial makes use of Lambda expressions. Read about them here:
Note: All examples were created using Visual Studio 2010, targetting the .NET Framework 4.0. We'll do our best to point out anything that might not work in older versions.
LINQ to SQL Primer
One of the best ways to learn is to do. So let's get started. I'm using SQL Server 2008 with the AdventureWorks database for my examples, if you want to follow along. You can also try this with a database of your own. Also, I'm using a Console Application for the example, but you can use any type of application (except for Silverlight -- Silverlight does not support data connections).
Creating the LINQ to SQL Classes
First, we add the LINQ to SQL Classes to our project. Open the Add New Item dialog:
Go to the Data tab, and choose LINQ to SQL Classes. I like to give my classes the same name as the database, so I've named it "AdventureWorks.dbml"
VS should automatically open the new file for you. If it doesn't, double click the DBML file in your project. It's just a big, empty page at the moment, but it's telling you to drag tables from the server explorer. So, click the Server Explorer link to open up your Server Explorer:
Note I've blanked out a few of my connections for personal reasons. Click the "Add Conection" button (boxed in red in the previous picture), and this familiar dialog should appear:
Enter the proper information here, and click OK.
The DB connection has been added to your server explorer, and all the tables in the database are available to you. In this example, I'm just going to select all and drag them all onto the LINQ to SQL file. You can choose only the ones you need, if you like.
It creates a graphical representation of our database, complete with Foreign Key relationships! Note: relationships are only created if you created them in the database. They are not inferred.
We can go back to the Program.cs file now. We'll start with a very simple query. Let's take the first 100 Products from the table "Products", and print out the names.
Well, that was easy, right? This code (at least, the .Take portion) is translated to SQL automatically and executed for you, then the returned data is inserted into a list of pre-built objects. You never saw any SQL, but you did query a database.
LINQ uses "lazy evaluation", which basically means that it's not evaluated until it has to be. What that means is, the .Take method didn't actually go to the database. In fact, if that was the only line in the program, we'd never actually make a query. The first time the query has to be evaluated is when we try to take one of the results in the foreach loop. So that's when it's executed.
Note: If you ever want/need to force immediate evaluation, put .ToList() on the end of your query. That forces the query's values to be iterated through and into a new List. It also changes the return type to List<T> (T being whatever type of object your collection is full of).
That was a simple example. Let's do a more useful example. Let's get all the products that start with the letter "A".
In this example, we also used the ForEach method instead of the foreach construct. No particular reason, I just felt like showing it. Here's the SQL generated by this example:
Now, as you can see there, the .ToLower and .StartsWith methods were translated to SQL. Only methods that have direct SQL equivalants can be used. For example, we couldn't use a RegEx match in the .Where method, since there's no SQL mapping for Regular Expressions. The compiler won't create a warning, though, if you use an unsupported method. You'll get a runtime exception when it's used.
Ok, what about inserting data? Piece of cake. Here's an example of adding an address to the Addresses table:
First, we grabbed a state to use, since it's an FK relationship. Then we created a new Address instance, filled with fake data. We used the Insertonsubmit method on the Addresses table in the DB. Then we submitted the changes.
After that, we selected the same address back out of the table, just to prove that it was created and saved:
Let's update that same address we just selected out.
That's all there is. Updating is as easy as taking an object from the database, changing it, and calling SubmitChanges.
If we open the database, we can see that it was actually updated:
Let's delete that same address:
Again, incredibly simple. Just call Deleteonsubmit on the table you want to delete from, and submit changes.
Other Important Methods
I've shown you Insertonsubmit, Deleteonsubmit, and how updates are handled. Note that you can do more than one action at a time. You can do several inserts, a few deletes, and lots of updates, then SubmitChanges.
Also, there are methods to Insert and Delete several items at once: InsertAllonsubmit and DeleteAllonsubmit. These can be useful when you want to create or remove several items.
Again, this is by no means exhaustive. I've barely scratched the surface. This is intended to get you started trying LINQ to SQL out (just not on a production database ).
See all the C# Learning Series tutorials here!
This post has been edited by Curtis Rutland: 13 April 2011 - 07:50 AM