LINQ to SQL: A basic tutorial
So, you've heard a lot about LINQ and now you want to get in the game and start
using it. This tutorial is intended to get you started as quickly as possible without
forcing you to worry about any of the nasty lambda function hurdles a lot of other
tutorials try and get you to jump through first. So, if you're trying to access
an SQL database the basic steps are:
- Set up your DataContext object using the Visual Studio GUI
- Create an instance of your DataContext object in code
- Select a dataset out of your DataContext object
- Retrieve the record you're after from the dataset
Step 1: Creating a DataContext
Nothing too complicated here. In Visual Studio 2008, right click your solution and
click "Add New Item". In the resulting window select "LINQ to SQL classes", name
the file and create it. If you're in an ASP.NET application then you'll be prompted
that this file should live in the App_Code directory. Agree to that and get on with
your life.
If you can't see "LINQ to SQL classes then you'll need to make sure that the project
you're working on is targetted for .NET 3.0 or above (to do this, simply right click
on the project, select "Property Pages" and then check the setting under the Build
options).
Conceptually, this file is now your interface to the database so you should give
it a name that relates it to the group of database tables it will be referencing.
Now all you have to do is add the tables you need to use onto your data context.
Open your data context from the solution explorer and you should be given a nice
blank canvas that you may or may not have no idea what to with. All you have to
do is open the database you're after (or a database with a copy of the datastructure
you're after) in the Server Explorer window and drag the relevant table (or group
of tables) onto the empty canvas. If there are any foreign keys that connect tables
then they should show up as dotted lines on the diagram (this will only work if
the database is set up to enforce these foreign keys rather than if it has been
incorrectly set up by the dba to assume the system will enforce these keys). If
you don't have the database listed in the server explorer you will need to add the
database as a new data connection and then add the tables to your canvas.
That's it - your data context is now set up. If you go off and check out your web.config
file or app.config file if you're creating a windows app then you'll see a new connection
string has been added if there wasn't one there already. You can change the connection
string used from the "Connection" property of the data context.
Step 2: Create an instance of your DataContext
This is the easiest step. Just initialise an instance of the data context as you
would any other class.
ContextNameDataContext testDb = new ContextNameDataContext();
Note that the class will automatically have "DataContext" appended to the name you
gave it.
Step 3: Select a dataset
So now the set up is done, you can actually start using LINQ. You can do pretty
much anything that SQL can do using the basic query syntax that LINQ provides. We'll
start with how you do a simple select.
A simple select statement would follow the form var results = from
t in testDb.TableName select t.Field
I've found that one of the really important clues in LINQ is autocomplete. If it's
not working then something is wrong and you should fix it before you continue. Remember
that you'll need a "using System.Data.LINQ" (and potentially a "using System.XML.LINQ")
in your class header and remember that you'll need references to the appropriate
LINQ dlls as well.
So, assuming that all typed out with autocomplete working you should have been given
an autocomplete option for each table and each field in the table as you were typing.
That's one of the major advantages of LINQ so far as I'm concerned - always having
a reminder at your fingertips as to what the fieldnames you need to work with are.
Coming back to the statement we've just typed out though, there are a couple of
major points you need to be aware of before we continue. The first is that you've
just assigned the result set to a variant datatype. You could claim this breaks
the strong-typing of .NET, but you'd be wrong. The variant is still strongly typed
at design time, it's just that the knowledge of what that type is is held in the
development environment rather than the programmer's head. I think the actual type
that is being returned is an IEnumerable but that doesn't really matter.
Something you've probably already noticed is that the order of the syntax has been
re-arranged from SQL. This is simply so autocomplete works properly and thus makes
a certain amount of sense.
The last thing I'll mention about this statement is that it doesn't cause any data
to be retrieved from the database. That only happens on the first attempt to access
the data from out of the results variant. For the moment all you've done is created
an SQL statement that selects the field you chose.
Step 4: Retrieving the record you're after from your record set
So now you have a record set you can retrieve any record you want from that record
set. The most common method I've seen is to loop through the record set using a
foreach loop: foreach(var record in results){...}, though
there are a number of other ways you can access the record set. Whatever you do
though, you do have to remember that you're working with a record set. You might
know that you could only possible have a single record in the results set but the
compiler won't. If you want to work on just a single record use the First() or Single()
methods to access the individual record returned.
Once you've got your record in a variant Visual Studio should be clever enough to
work out the type and give you full auto-complete on all the properties of the record
you're using.
So, now that you know this you might be asking what the advantages are. Well, for
a start you can use exactly the same syntax to access other datasources, including
XML! I've already mentioned the auto-complete goodness that saves quite a lot of
time and fat-finger typing bugs. Then there's the efficiency gain... "Ummm... what
efficiency gain?" has been a response I've gotten to that one a couple of times.
Well, my take on it is that LINQ stops non-SQL people writing really lousy SQL of
the type that'll bring a database to its knees in no time flat. No temporary tables,
no cursors, just clean and simple SQL whereever possible. It can let you filter
down the data set on the client side instead of trying to beat your head against
a wall trying to join together 17 different tables just to get a single stored procedure
that encapsulates a task and takes 45 seconds to run. (Oh, and in case you believe
that stored procedures are faster than remote SQL commands, that's not been the
case for
quite sometime). If you're foolish enough to still be constructing SQL through
string concatenation then this should save you from all those nice SQL injection
attacks too as LINQ creates parameterised queries.
So that's the real basics covered.
The next LINQ tutorial is here..