LearnNowOnline

Blog

LINQ and Relational Data

Excerpt by Ken Getz | September 20, 2013

At its most basic level, LINQ provides the ability to query any data source that supports the IEnumerable or generic IEnumerable(T) interface. The data you want to query in an application can come from a variety of sources. The data may reside in in-memory objects. If so, you can use LINQ to Objects. The data may reside in XML. If so, you can use LINQ to XML. The primary source of data in most applications is a relational database, such as SQL Server. If you have built database applications with Visual Studio, you are familiar with using ADO.NET and building SQL statements to query and modify data in a relational database.

In this chapter, you will see how to use LINQ to SQL to query and modify data in a SQL Server database. You may find yourself thinking that ADO.NET works great, so why do I need another way to work with relational data? What advantage does LINQ to SQL offer me? LINQ provides a consistent model for querying all types of data. With LINQ, a query that works with objects looks similar to a query that works with XML. It also looks similar to a query that works with relational data. If you know how to use LINQ to Objects to write a query, you already know most of what you need to write a query by using LINQ to SQL. LINQ to SQL is part of the ADO.NET family of technologies.

It abstracts away the various ADO.NET classes such as SqlConnection, SqlCommand, SqlDataAdapter, SqlDataReader, etc. You get the same functionality by writing less code. You can also easily mix LINQ to SQL with existing ADO.NET code. LINQ to SQL also bridges the gap between object programming and traditional database programming. As a .NET developer, you build applications based on objects. The .NET Framework consists of classes. To use the functionality that a class provides, you create an instance of the class and then use its properties, methods, and events. To query a database, you build a SQL statement as a string. You can then use ADO.NET classes to send the query to the database. You can also store the results by using the ADO.NET DataSet and DataTable classes. Is this object programming? Yes and no.

The use of ADO.NET is object programming; however, the data model is based on tables and rows, not on objects. To model the data as objects, you can create classes for each table in the database. For example, you could create a Customer class with properties for company name, address, city, region, and so on. When you query the Customer table, you store the results in one or more instances of the Customer class. LINQ to SQL provides a runtime infrastructure for managing relational data as objects. To use LINQ to SQL, you map the structure of a relational database to an object model. Each table in the database maps to a class.

This class is an entity class. At runtime, the .NET Framework translates LINQ queries into SQL and sends them to the database. When the database returns results, the runtime populates the objects with the returned data. Once you create your object model, you can use LINQ to SQL to query and change data in a SQL Server database.

Note: Microsoft built LINQ to SQL to support any relational database. However, the implementation of LINQ to SQL that ships with the .NET Framework 3.5 and 4.0 support only SQL Server. It is possible that Microsoft will support other databases in future releases.

This post is an excerpt from the online courseware for ourWindows 8 Using XAML: Views, Resources, and Toastscourse written by expert Ken Getz.