- 21.1 Introduction
- 21.2 Relational Databases
- 21.3 Relational Database Overview: Books Database
- 21.4 SQL
- 21.5 LINQ to SQL
- 21.6 LINQ to SQL: Extracting Information from a Database
- 21.7 More Complex LINQ Queries and Data Binding
- 21.8 Retrieving Data from Multiple Tables with LINQ
- 21.9 Creating a Master/Detail View Application
- 21.10 Programming with LINQ to SQL: Address-Book Case Study
- 21.11 Wrap-Up
- 21.12 Tools and Web Resources
This chapter is from the book
This chapter is from the book
This chapter is from the book
21.5 LINQ to SQL
LINQ to SQL provides an API for accessing data in SQL Server databases using the same LINQ syntax used to query arrays and collections. For many applications, the LINQ to SQL API can entirely replace .NET’s older ADO.NET API, though ADO.NET is still used internally by LINQ to SQL.
You interact with LINQ to SQL via classes that are automatically generated by the IDE’s LINQ to SQL Designer based on the database schema. The IDE creates a class for each table, with a property for each column in the table. Objects of these classes hold the data from individual rows in the database’s tables. Foreign-key relationships are taken into account in both directions. For each foreign key, a property is created that returns the row object that the foreign key references. Every object also contains a property that returns a collection of the rows that reference it. Once generated, these classes are normal C# classes with full IntelliSense support in the IDE.
A cache is a temporary store created for fast access to data that would otherwise be costly to retrieve or regenerate. LINQ to SQL caches all row objects that it creates, making interacting with the database more efficient in two significant ways. First, it does not have to recreate row objects each time data is retrieved from the database—it can simply reuse the ones it already has in memory. To do this, LINQ to SQL needs a way to determine if a row returned from the database already exists in the cache—therefore, LINQ to SQL requires every table to have a primary key. Second, having these row objects in memory allows you to manipulate them as much as necessary, then submit the changes you make all at once. This can reduce round trips to the database—a slow operation compared to manipulating objects that are already in memory.
LINQ to SQL works through the IQueryable<T> interface, which inherits from the IEnumerable<T> interface introduced in Chapter 9. With LINQ to Objects, LINQ iterates through the entire collection and applies the query operators one at a time—each operator uses the results of applying the previous operator. In contrast, LINQ queries on an IQueryable object are processed together—LINQ to SQL converts the entire query into a single SQL statement to execute against the database. If each query operator were handled separately, multiple round trips to the database would be needed, and the database management system would not be able to use its intimate knowledge of its data structures to optimize the query. When the results are returned from the database, they are loaded into the corresponding classes generated by LINQ to SQL for convenient access in your code.
All LINQ to SQL queries occur via a DataContext class, which controls the flow of data between the program and the database. A specific DataContext class, which inherits from the class System.Data.Linq.DataContext, is created when the classes representing each row of the table are generated. When instantiated, the DataContext has properties for each table in the database—these can be used as subjects of a LINQ query. When cached objects have been changed, these changes are saved back to the database, using the DataContext’s SubmitChanges method.