Accessing a database for persisted data retrieval is usually a time-consuming operation. Within the .NET framework, ADO.NET is the sub-framework responsible for database access. In 2008, Microsoft introduced Entity Framework (EF) as a multiplatform object relational-mapping (O/RM) database access subsystem within ADO.NET itself.
Although classical ADO.NET (low level) database access techniques have been superseded by EF's features, some specific usage is still a prerogative of such legacy programming classes.
In this chapter, we will take a tour of EF's querying capabilities, trying all available techniques to emphasize the performance results of each one. We will cover the following topics in this chapter:
- Overview of ADO.NET
- Overview of Entity Framework
- Entity Framework querying optimization
- Entity Framework persistence optimization
- Performance comparison
- ADO.NET specific features
The ActiveX Data Objects (ADO) .NET is the main data provider for database access for any CLR-based application. ADO.NET was designed to supercede the old ADO. ADO used to be another framework for database access and manipulation. It works as a middleware, similar to a control or component, able to make available underlying database features as simplified features. It uses ActiveX technology (which is the heir to COM and OLE) to encapsulate its features as reusable cross-language features. This is why ADO was able to work in applications made with Visual Basic 6 or C++. ADO.NET, instead, is available only to CLR-based applications. As the ADO could, ADO.NET can also interact with any database using a specific data provider or cross provider, such as OLEDB or ODBC.
ADO.NET is completely extensible with a full interface-based architecture, relying on the abstract factory pattern. This makes the framework usable in situations other than in a classical tightly-coupled scenario with path to the specific database, as well as in an agnostic way without having to know exactly which data provider is actually running.
Made for ADO.NET, Entity Framework is the main O/RM created by Microsoft to interact with any database in an object-oriented way. The usage of EF relies on the data provider's ability to translate C# queries in specific SQL-dialect, such as TSQL or others. Thus, the underlying ADO.NET data provider must be compliant with EF. At this time, almost all database engines have a specific EF-compliant data provider, each one optimized for its database language.
Within ADO.NET, we have the ability to use all specific database features, even if they are not standardized across all database producers. On the contrary, Entity Framework uses only standardized features to give you the ability to change the data provider at any time.