In the previous chapter, we learned new features of C# 3.0 including LINQ. In this chapter and the next, we will explain how to use LINQ to query a database, or in other words, how to use LINQ to SQL in C#. After reading these two chapters, we will have a good understanding of LINQ to SQL, so that we can rewrite the data access layer of our WCF service with LINQ to SQL, to securely, and reliably communicate with the underlying database.
In this chapter, we will cover the basic concepts and features of LINQ to SQL, which include:
- What ORM is
- What LINQ to SQL is
- What LINQ to Entities is
- Comparing LINQ to SQL with LINQ to Objects and LINQ to Entities
- Modeling the Northwind Database in LINQ to SQL
- Querying and updating a database with a table
- Deferred execution
- Lazy loading and eager loading
- Joining two tables
- Querying with a view
In the next chapter, we will cover the advanced concepts and features of LINQ to SQL, such as stored procedure support, inheritance, simultaneous updating, and transaction processing.
LINQ to SQL is considered to be one of Microsoft's new ORM products. So before we start explaining LINQ to SQL, let us first understand what ORM is.
ORM stands for Object-Relational Mapping. Sometimes it is called O/RM, or O/R mapping. It is a programming technique that contains a set of classes that map relational database entities to objects in a specific programming language.
Initially, applications could call specified native database APIs to communicate with a database. For example, Oracle Pro*C is a set of APIs supplied by Oracle to query, insert, update, or delete records in an Oracle database from C applications. The Pro*C pre-compiler translates embedded SQL into calls to the Oracle runtime library (SQLLIB).
Then, ODBC (Open Database Connectivity) was developed to unify all of the communication protocols for various RDBMS. ODBC was designed to be independent of programming languages, database systems, and operating systems. So with ODBC, one application can communicate with different RDBMS by using the same code, simply by replacing the underlying ODBC drivers.
No matter which method is used to connect to a database, the data returned from a database has to be presented in some format in the application. For example, if an Order record is returned from the database, there has to be a variable to hold the Order number, and a set of variables to hold the Order details. Alternatively, the application may create a class for the Orders, and another class for Order details. When another application is developed, the same set of classes may have to be created again, or if it is designed well, they can be put into a library, and re-used by various applications.
This is exactly where ORM fits in. With ORM, each database is represented by an ORM context object in the specific programming language, and database entities such as tables are represented by classes, with relationships between these classes. For example, the ORM may create an Order class to represent the Order table, and an OrderDetail class to represent the Order Details table. The Order class will contain a collection member to hold all of its details. The ORM is responsible for the mappings and the connections between these classes and the database. So, to the application, the database is now fully-represented by these classes. The application only needs to deal with these classes, instead of with the physical database. The application does not need to worry about how to connect to the database, how to construct the SQL statements, how to use the proper locking mechanism to ensure concurrency, or how to handle distributed transactions. These databases-related activities are handled by the ORM.
The following diagram shows the three different ways of accessing a database from an application. There are some other mechanisms to access a database from an application, such as JDBC, and ADO.NET. However, to keep the diagram simple, they have not been shown here.
LINQ to SQL is a component of the .NET framework 3.5 that provides a run-time infrastructure for managing relational data as objects.
In LINQ to SQL, the data model of a relational database is mapped to an object model expressed in the programming language of the developer. When the application runs, LINQ to SQL translates the language-integrated queries in the object model into SQL, and sends them to the database for execution. When the database returns the results, LINQ to SQL translates the results back to objects that you can work with in your own programming language.
LINQ to SQL fully supports transactions, views, stored procedures, and user-defined functions. It also provides an easy way to integrate data validation and business logic rules into your data model, and supports single table inheritance in the object model.
LINQ to SQL is one of Microsoft's new ORM products and competes with many existing ORM products for the .NET platform, such as open source products NHibernate and NPersist, and commercial products LLBLGen and WilsonORMapper. LINQ to SQL has many overlaps with other ORM products, but because it is designed and built specifically for .NET and SQL Server, it has many advantages over other ORM products. For example, it takes the advantages of all of the LINQ features, and it fully supports SQL Server stored procedures. You get all of the relationships (foreign keys) for all of the tables, and the fields of each table just become properties of its corresponding object. You even have the intellisense
popup when you type in an entity (table) name, which will list all of the table's fields in the database. Also, all the fields and the query results are strongly-typed, which means that you will get a compilation error instead of a runtime error if you have misspelled the query statement or have cast the query result to a wrong type. In addition, because it is part of the .NET framework, you don't need to install and maintain any third-party ORM product in your production or development environments.
Under the hood of LINQ to SQL, ADO.NET SqlClient adapters are used to communicate with the actual SQL Server databases. We will show how to capture the generated SQL statements in runtime later in this book.
The following diagram shows the use of LINQ to SQL in a .NET application:
We will explore detailed LINQ to SQL features in the following two chapters, and use LINQ to SQL in our WCF services later in this book.