Data access with Linq

Clique aqui para ver este post em português

Hello everyone. This is the first post in Estação ZN in English. We decided to give an "English version" to our posts due to the increasing non-Brazilian public of our blog, which we would like say thank you very much. From now on I'll try to make an English translation of every post i make. I ain't a native English speaker, so please bear with me a little bit. :)

Let's get started. In this post I'm bringing an issue that left me and Gerson speechless: A new data access technology present in Visual Studio 2008 and .NET 3.5.

Linq stands for Language-integrated Query. It's a technology (a few of them, in fact) that adds query capabilities at language (compiler) level.

What does it mean? It means that Linq allows us (amongst others things) to query in-memory data, XML files and even relational database tables in a declarative manner (like funcional languages) instead of the traditional, imperative way we're used to.

Linq also came to propose a solution for the "impedance mismatch", that is the difference between the program's object model (OOP) and the relational databases/XML files data model. Going a bit deeper: Let's begin with the principle: Relational data <> XML files <> Objects hierarchy/relationships. At the software we have the OOP model, with inheritance, polimorphism and so on. The data our software usually accesses are represented in relational database tables and XML files. In these worlds we have no compatibility whatsoever; even the data types are different.
In order to work with data nowadays we need to use APIs that the languages provide us. But we always have to write more code than we should, and the code that we write to access the data doesn't clearly represent what we want to do. That is because of the imperative way that the languages require to access and manipulate data on external sources.

Eh..., ..., ... Ok. Here's an example:

We want to query data from a database. There are minor differences between the languages but it all comes down to one path in most cases:

• 1. Open connection with the database


• 2. Instantiate an object to execute the query


• 3. Pass the query as string


• 4. Send the query to the database and get the resultset


• 5. Release the resources and uses the resultset

This model comes with the necessity of implement, in a imperative way, what the program has to do at each moment. We also have to write code to explicitly connect to the database and run the query, which isn't part of our program logic, and at last, but not least, we have to code the query as string, not using the compiler's type checking at design time - if our query has any errors, we'll discover it only at run time. Then, when the application retrieve the data from the database we have to manually "translate" the database's resultset into objects. It takes too many "unnecessary" code (I would say code that doesn't belong there) and produces code that is difficult to read, mantain and debug.

But how's Linq different? First, let's see the several "flavors" of Linq. We'll see the Linq to Objects first, which allows us to query, filter and sort in-memory collections. Let's go on a example:

string[] Months = {
    "January",
    "February",
    "March",
    "April",
    "May",
    "June",
    "July",
    "August",
    "September",
    "October",
    "November",
    "December"};

var MonthsWithA =
    from mon in Months
    where mon.StartsWith("A")
    orderby mon descending
    select mon;

foreach (var m in MonthsWithA) {
    Console.WriteLine(m);
}

Take a look at the part where we query the string array Months. In this fragment we search the Months array for any month that starts with the letter A and sort the results descending. This way we access the data in a declarative way, like we would do with funcional languages. Imagine how would be the code to achieve the same results in the traditional, imperative way...

In Linq to Objects we can query any object that implements the IEnumerable interface (the array implenents this interface even though it doens't look like).

This querying model can be applied in relational data and XML files too. But how? Let's see the relational database first - We won't dive too deep into it for now because this post's objective is to show what is Linq and what it is capable of.

With Linq to SQL we can query the database in the same declarative way we do on object collections. But Felipe, if I have to retrieve the entire table from the database and convert it into an array before I can use the Linq, then I surely don't want it! For sure Microsoft wouldn't do it with a technology that is intended to make the developers' life easier. Linq to SQL works with another new language enhancement: Entity Mapping.

In Linq to SQL we have the classes in our program decorated with the special Mapping attributes (I'll talk about and show it in another posts). In other words, we'll "mark" our classes as "Entity classes" and inform which tables and fields are related to which classes and properties; and then, when we query these objects, Linq "mounts" the query and retrieve the results into the objects - all thanks to the mapping attributes.

Linq to SQL can do way more than this. It also provides APIs for data updates, transactions and concurrency control. We'll see more of that later.

Linq to XML provides a simple and unified API to query and also create/edit XML files; an API that is way simpler than those we know nowadays (DOM, XSL, XPath, XQuery). The query syntax is very alike to the Linq to Objects, and this is very nice to us developers to get used to quickly.

As the goal of this post being talk about a relatively new technology and have a peek into all this, I'll let the specific "flavors" of Linq to other posts. See you all and have fun.

Thank you for spending time with us, and please excuse me about any English errors, as i'm no native English speaker, and feel free to send any suggestions and even corrections (please correct me!). Bye...