Morning Break

In this article I'll cover the basics of LINQ. You'll see how to query the database, insert, update and delete records using LINQ to SQL implementation. People often equate LINQ with its LINQ to SQL implementation, but it is just one of several implementations. In my next articles, I'll go deeply into LINQ and see how you can use it with XML, Datasets and other objects.

LINQ basics

LINQ is shortcut for Language Integrated Query, which means that classic database model has been moved into OOP model. So basically, queries become a part of .NET programming languages. Is this really true? Well, since you can debug the queries, get compile-time checking and intellisense, I would say yes.

By using LINQ you can query any object that implements IEnumarable<T> interface (more on this interface can be found here and here). That means you can query the database, XML structures, DataSets and even your custom objects. The great thing is that you can do aggregation, joining, grouping, ordering and many more data operations.

The fact that we have now a unified way to query different data sources will bring LINQ even more in focus in the future.

If you are interested more in basics of LINQ, check out LINQ Project website, and LINQ section in MSDN articles. You'll find more than enough information there.

Now let's move to LINQ to SQL.

LINQ to SQL

This is the most known implementation so far. It gives you the ability to query the database and perform inserts, updates and deletes. It supports transactions and store procedure calls. Let's see how easy is it to use LINQ.

I'll use slightly modified database Milica and I used for WebFu presentation. We have a database with Region and Country tables. Each country belongs to one region. For each country we have country name, country symbol, number of internet users, and Long and Lat coordinates.

Now let's move to Visual Studio. Open a new website and in "Add new item" dialog chose LINQ to SQL Class like in the image below.

This will generate .dbml file in App_Code folder. Its designer will be empty by default. You can drag and drop tables from Server Explorer to the designer (outlined in red in image below) in order to use them in code. Beside that you can drag and drop stored procedures from Server Explorer to Method pane (outlined in blue in image below).

This will generate DataContext class that will be the main entry point for your model. DataContext Class will have a property for each table you added to the designer. This property is of System.Data.Linq.Table type. Also, for each stored procedure you dropped on to method pane, DataContext class will have one method with the same name as the stored procedure. This will enable you to call stored procedures directly. You'll have one DataContext Class for each .dbml file you add. For more information about DataContext, I strongly recommend you to read MSDN documentation where you can get informed about its purpose, methods, properties, etc.

1. Getting the data 

In our example, we dragged Region and Country tables to the designer. Now we are ready to query the data. Let's say that we want to display all countries in a GridView.

    protected void Page_Load(object sender, EventArgs e)
    {
        CountryDataContext db = new CountryDataContext();

        var countries = from c in db.Countries select c;

        GridView1.DataSource = countries;
        GridView1.DataBind();
    }

The code above shows LINQ simplicity. First, we instantiate our DataContext class and use it to create this simple query. LINQ syntax might be odd to you now, but soon you'll see how simple it is.

The result will look like this (sample table):

If we want to display the countries that have a million internet users or more, the query would look like this:

         var countries = from c in db.Countries
             where c.InternetUsers >= 1000000 select c;

and we'll get a result table similar to the previous one.

2. Inserting new data

Inserting new records is as simple as querying, and what is more important it will have minor changes to model you are used to working with. You create an entity, populate it and pass it to data storage. Let's look at the code below. We created a new Country entity, populated it, inserted it to countries table and submitted all changes. Simple as this.

        CountryDataContext db = new CountryDataContext();
        Country c = new Country();
        c.CountryName = "SomeCountry";
        c.InternetUsers = 1000;
        c.RegionID = 1;
        c.Symbol = "ABC";

        db.Countries.InsertOnSubmit(c);
        db.SubmitChanges();
 

3. Updating the data

To update the data we'll use a similar logic to insertion. We'll query the data, change something and submit the changes.

        CountryDataContext db = new CountryDataContext();

        Country cnt = from c in
            db.Countries.Single(c => c.CountryName == "Serbia")
            select c;

        if (cnt != null)
        {
            cnt.InternetUsers += 1000;
            db.SubmitChanges(System.Data.Linq.ConflictMode.FailOnFirstConflict);
        }

You probably noticed some new stuff here.

First, method db.Countries.Single() will return a single entity that matches the condition. The condition is represented by a Lambda Expression. Lambda expressions are new feature in C# 3.0: a new generation of anonymous methods that we used so far. In our example, lambda expression is literally doing this: return country where country name equals to Serbia. Simple, eh?

Second, we used a different overload of db.SubmitChanges() method. This time we said: if any conflict occurrs, drop the action. There are two ConflictMode enumerations: FailOnFirstConflict and ContinueOnConflict.

4. Deleting the data

We'll use similar scenario as the previous one: we'll retrieve and delete the data that matches a condition. Let's say we want to delete all countries that have less than 10,000 internet users.

        CountryDataContext db = new CountryDataContext();

        Country cnt = from c in db.Countries
            where c.InternetUsers < 10000 select c;
        db.Countries.DeleteAllOnSubmit(countries);
        db.SubmitChanges(System.Data.Linq.ConflictMode.FailOnFirstConflict);

Summary

This was an overview of LINQ and LINQ to SQL implementation. You saw how to create DataContext Class, how to query the database, how to insert, update and delete the data in the database. You've been introduced to Lambda Expressions, a new feature in C# 3.0. In my next articles I'll cover more LINQ features and Lambda Expressions, so stay tuned!

Meanwhile, I recommend you to take a look at these articles:

The LINQ Project

LINQ on Wikipedia

LINQ articles from Scott Guthrie

Lambda Expressions Extension Methods and LINQ in C# 3.0 by David Hayden