Linq to SQL - Working with Databases

Clique aqui para ver a versão em português deste post

In this post I continue to show the Linq technology. This tiime we'll see how it's used to access and manipulate data in relational database tables.

In order to use Linq with databases we need to establish a relationship between the business objects in our software and the database tables. To achieve this we use the class/attributes mapping.

This mapping is simple: We create a class that represent a database table and in this class we set which table it is mapped to. In the attributes we set to which database fields they're mapped.

In the current version, Linq only supports the MS SQL Server, but there are already many developers out there working on solutions to cover other RDBMSs too.

To begin let's show a table in the database: Produtos

CREATE TABLE [dbo].[Produtos](
 [ProdutoID] [uniqueidentifier] ROWGUIDCOL
        NOT NULL CONSTRAINT [DF_Produtos_ProdutoID]  DEFAULT (newid()),
 [Descricao] [nvarchar](100) COLLATE Latin1_General_CI_AS NOT NULL,
 [Preco] [decimal](10, 2) NOT NULL,
 [Saldo] [int] NOT NULL,
 [Versao] [timestamp] NOT NULL
)

Let's pay close attention at the column Versao, type timestamp. This datatype stores the record version. This is very useful to work with optimistic updates and concurrency issues and Linq makes good use of it.

Before we advance, insert some records in this table.

Now let's create a class that uses this table. In order to use Linq to SQL we need to add reference to the assembly System.Data.Linq to the project. Here is the code:

using System;
using System.Data.Linq.Mapping;

[Table(Name="Produtos")]
class Produto {

    [Column(IsPrimaryKey = true,
            IsDbGenerated = true,
            AutoSync = AutoSync.OnInsert)]
    public Guid ProdutoID { get; set; }

    [Column(CanBeNull = false)]
    public string Descricao { get; set; }

    [Column(CanBeNull = false)]
    public decimal Preco { get; set; }

    [Column(CanBeNull = false)]
    public int Saldo { get; set; }

    [Column(IsDbGenerated = true,
            IsVersion = true, AutoSync = AutoSync.Always)]
    public System.Data.Linq.Binary Versao { get; set; }
}

Now let's create a Linq query in this database table. Up until here we just defined the entity and attributes mapping, but we didn't define anything about the database itself. How do we do that?

There is an object in Linq called DataContext. We can think of it more like a database connection in other technologies, but it hardly acts like. It does so much more! It's responsible for the database connection, making and the exectution of the queries, mapping, record updates and even transactions and record concurrency!

We just need to create a instance and it does everything on its own. In other words, we don't need to make code to open or close connection, build string queries and treat results anymore. Let's see the code:

using System;
using System.Data.Linq;
using System.Linq;

class Program {
    static void Main(string[] args) {

        var connStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=DadosSql;User ID=felipe;Password=123";

        using (var db = new DataContext(connStr)) {
            var Produtos = db.GetTable<Produto>();

            var qry =
                from p in Produtos
                orderby p.Descricao
                select p;

            foreach (var prod in qry) {
                Console.WriteLine("{0}, {1}, {2}", prod.Descricao,
                                                   prod.Preco,
                                                   prod.Saldo);
            }
        }
    }
}

What we've done:

- Created a DataContext to connect to the database;
- Created a local variable to represent our mapped table;
- Built the query;
- Display the results;

The wonder of wonders is: Will the DataContext retrieve the entire Produtos table when we create the local variable Produtos? Answer is no! Then, will it occur when we create the qry? Nope. So, where is it?

One of the coolest things in Linq is the ability to send the query to the database only at the moment its data is actually required. In our case, in the foreach loop. That query returned an instance of IOrderedQueryable that seems like a List. But it isn't quite a common list. When its data is required the DataContext build a query based on its definition and send the query to the database and then convert the resultset in an object list, thanks to the mapping features. This technique is called Deferred execution.

How would we do in order to open connection, build a parametrized query and convert the resultset in a list of instances of our business object? And all other tables?

I'll change the code a bit and add some verbose. The DataContext has a property called Log that allows us to view the DataContext's log:

using System;
using System.Data.Linq;
using System.Linq;

class Program {
    static void Main(string[] args) {

        var connStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=DadosSql;User ID=felipe;Password=123";

        using (var db = new DataContext(connStr)) {
            db.Log = Console.Out;

            Console.WriteLine("** Pegando a tabela Produtos");
            var Produtos = db.GetTable<Produto>();

            Console.WriteLine("** Criando a query");
            var qry =
                from p in Produtos
                where p.Descricao.Contains("a")
                orderby p.Descricao
                select p;

            Console.WriteLine("** Começando a iteração");
            foreach (var prod in qry) {
                Console.WriteLine("{0}, {1}, {2}", prod.Descricao,
                                                   prod.Preco,
                                                   prod.Saldo);
            }
        }
    }
}

Here's the console output:

** Pegando a tabela Produtos
** Criando a query
** Começando a iteração
SELECT [t0].[ProdutoID], [t0].[Descricao], [t0].[Preco], [t0].[Saldo], [t0].[Ver
sao]
FROM [Produtos] AS [t0]
WHERE [t0].[Descricao] LIKE @p0
ORDER BY [t0].[Descricao]
-- @p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%a%]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

Caderno Universitário 500 folhas, 7,90, 50
Caneta BIC, 1,99, 25
Lapiseira 0,7mm, 5,00, 10
Pasta polionda A4, 2,40, 6

The DataContext produces a query with parameters, instead of concatenate the criteria and build one simple query string. This is very nice, because the RDBMS can make an execution plan for this query and use the same play every time we run this query, even with different parameter values. It means better performance.

Well, that's it for this post, but Linq to SQL goes way further than this. In the next issue I'll write about CRUD operations with Linq to SQL. Thnnk you and take care.