Partager via

Appendix C: Data Access Technology Matrix

  • Article

For more details of the topics covered in this guide, see Contents of the Guide.

Contents

  • Overview
  • Data Access Technologies Summary
  • Benefits and Considerations Matrix
  • General Recommendations
  • Common Scenarios and Solutions
  • LINQ to SQL Considerations
  • Mobile Considerations
  • Additional Resources

Overview

This appendix will help you to understand the tradeoffs you have to make when choosing a data access technology. It will help you to understand the design impact of each technology, and assist when choosing a data access technology for your scenario and application type.

Your choice of data access technology will be related to both the application type you are developing and the type of business entities you use. Use the Data Access Technologies Summary to review each technology and its description. Use the Benefits and Considerations Matrix to understand the range of technologies available for data access. Use the Common Scenarios and Solutions section to map your application scenarios to common data-access technology solutions.

Data Access Technologies Summary

The following data access technologies are available on the Microsoft platform:

  • ADO.NET Core. ADO.NET Core provides facilities for the general retrieval, update, and management of data. It includes providers for SQL Server, OLE DB, Open Database Connectivity (ODBC), SQL Server Compact Edition, and Oracle databases.
  • ADO.NET Data Services Framework. This framework exposes data using the Entity Data Model, through RESTful Web services accessed over HTTP. The data can be addressed directly using Uniform Resource Identifiers (URIs). The Web service can be configured to return the data as plain Atom and JavaScript Object Notation (JSON) formats.
  • ADO.NET Entity Framework. This framework gives you a strongly typed data access experience over relational databases. It moves the data model from the physical structure of relational tables to a conceptual model that accurately reflects common business objects. The Entity Framework introduces a common Entity Data Model within the ADO.NET environment, allowing developers to define a flexible mapping to relational data. This mapping helps to isolate applications from changes in the underlying storage schema. The Entity Framework also supports LINQ to Entities, which provides LINQ support for business objects exposed through the Entity Framework. When used as an Object/Relational Mapping (O/RM) product, developers use LINQ to Entities against business objects, which Entity Framework will convert to Entity SQL that is mapped against an Entity Data Model managed by the Entity Framework. Developers also have the option of working directly with the Entity Data Model and using Entity SQL in their applications.
  • ADO.NET Sync Services. ADO.NET Sync Services is a provider included in the Microsoft Sync Framework, and is used to implement synchronization for ADO.NET-enabled databases. It enables data synchronization to be built into occasionally connected applications. It periodically gathers information from the client database and synchronizes it with the server database.
  • Language Integrated Query (LINQ). LINQ provides class libraries that extend C# and Visual Basic with native language syntax for queries. It is primarily a query technology supported by different assemblies throughout the .NET Framework; for example, LINQ to Entities is included with the ADO.NET Entity Framework assemblies, LINQ to XML is included with the System.Xml assemblies, and LINQ to Objects is included with the .NET Framework core system assemblies. Queries can be performed against a variety of data formats, including DataSet (LINQ to DataSet), XML (LINQ to XML), in-memory objects (LINQ to Objects), ADO.NET Data Services (LINQ to Data Services), and relational data (LINQ to Entities).
  • LINQ to SQL. LINQ to SQL provides a lightweight, strongly typed query solution against SQL Server. LINQ to SQL is designed for easy, fast object persistence scenarios where the classes in the mid-tier map very closely to database table structures. Starting with .NET Framework 4.0, LINQ to SQL scenarios will be integrated and supported by the ADO.NET Entity Framework; however, LINQ to SQL will continue to be a supported technology. For more information, see the ADO.NET team blog at https://blogs.msdn.com/adonet/archive/2008/10/31/clarifying-the-message-on-l2s-futures.aspx.

Benefits and Considerations Matrix

The following tables contain lists of benefits and liabilities for the data access technologies described in the previous sections. The individual tables cover a range of usage scenarios: object-relational data access, disconnected and offline data access, SOA and service scenarios, and n-tier and general scenarios. Some general recommendations for the data access technologies discussed in this appendix follow the tables.

Object-Relational Data Access

Technology

Benefits

Considerations

ADO.NET Entity Framework (EF)

Decouples the underlying database structure from the logical data model.

Entity SQL provides a consistent query language across all data sources and database types.

Separates metadata into well-defined architectural layers.

Allows business logic developers to access the data without knowing database specifics.

Provides rich designer support in Visual Studio to visualize data entity structure.

Use of a provider model allows it to be mapped to many databases.

Requires you to change the design of your entities and queries if you are coming from a more traditional data access method.

Uses separate object models.

Has more layers of abstraction than LINQ to DataSet.

Can be used with or without LINQ to Entities.

If your database structure changes, you must regenerate the Entity Data Model and re-deploy the EF libraries.

LINQ to Entities

A LINQ-based solution for relational data in the ADO.NET Entity Framework.

Provides strongly typed LINQ access to relational data.

Supports LINQ-based queries against objects built on top of the EF Entity Data Model.

Processing occurs on the server.

Requires the ADO.NET Entity Framework.

LINQ to SQL

Simple way to read/write objects when the data object model matches the physical database model.

Provides strongly typed LINQ query access to SQL data.

Processing occurs on the server.

Functionality integrated into the Entity Framework as of .NET Framework 4.0.

Maps LINQ queries directly to the database instead of through a provider, and therefore works only with Microsoft SQL Server.

Disconnected and Offline

Technology

Benefits

Considerations

LINQ to DataSet

Allows full-featured queries against a DataSet.

All processing occurs on the client.

ADO.NET Sync Services

Enables synchronization between databases, collaboration, and offline scenarios.

Synchronization can execute in the background.

Provides a hub-and-spoke type of architecture for collaboration between databases.

You must implement your own change tracking.

Exchanging large chunks of data during synchronization can reduce performance.

SOA/Service Scenarios

Technology

Benefits

Considerations

ADO.NET Data Services Framework

Data can be addressed directly via a URI using a REST-like scheme.

Data can be returned in either Atom or JSON formats.

Includes a lightweight versioning scheme to simplify the release of new service interfaces.

The .NET Framework, Silverlight, and AJAX client libraries allow developers to work directly with objects and provide strongly typed LINQ access to ADO.NET Data Services.

The .NET Framework, Silverlight, and AJAX client libraries provide a familiar API surface to Windows Azure Tables, SQL Data Services, and other Microsoft services.

Is only applicable to service-oriented scenarios.

LINQ to Data Services

Allows you to create LINQ-based queries against client-side data returned from ADO.NET Data Services.

Supports LINQ-based queries against REST data.

Can only be used with the ADO.NET Data Services client-side framework.

N-Tier and General

Technology

Benefits

Considerations

ADO.NET Core

Includes .NET managed code providers for connected access to a wide range of data stores.

Provides facilities for disconnected data storage and manipulation.

Code is written directly against specific providers, thereby reducing reusability.

The relational database structure may not match the object model, requiring you to create a data-mapping layer.

ADO.NET Data Services Framework

Data can be addressed directly via a URI using a REST-like scheme.

Data can be returned in either Atom or JSON formats.

Includes a lightweight versioning scheme to simplify the release of new service interfaces.

Provider model allows any IQueryable data source to be used.

The .NET Framework, Silverlight, and AJAX client libraries provide a familiar API surface to Windows Azure Tables, SQL Data Services, and other Microsoft services.

Is only applicable to service-oriented scenarios.

Provides a resource-centric service that maps well to data-heavy services, but may require more work if a majority of the services are operation-centric.

ADO.NET Entity Framework

Separates metadata into well-defined architectural layers.

Supports LINQ to Entities for querying complex object models.

Use of a provider model allows it to be mapped to many database types.

Allows you to build services that have well defined boundaries, and data/service contracts for sending and receiving well defined entities across the service boundary.

Instances of entities from your Entity Data Model are directly serializable and consumable by Web services.

Full flexibility in structuring the payload — send individual entities, collections of entities, or an entity graph to the server.

Eventually will allow for true persistence-ignorant objects to be shipped across service boundaries.

Requires you to change the design of your entities and queries if you are coming from a more traditional data access method.

Entity objects can be sent across a network, or you can use the Data Mapper pattern to transform entities into objects that are more generalized DataContract types. The planned POCO support will eliminate the need to transform objects when sending them over a network.

Building service endpoints that receive a generalized graph of entities is less service oriented than endpoints that enforce stricter contracts on the types of payload that might be accepted.

LINQ to Objects

Allows you to create LINQ-based queries against objects in memory.

Represents a new approach to retrieving data from collections.

Can be used directly with any collections that support IEnumerable or IEnumerable<T>.

Can be used to query strings, reflection-based metadata, and file directories.

Works only with objects that implement the IEnumerable interface.

LINQ to XML

Allows you to create LINQ-based queries against XML data.

Is comparable to the Document Object Model (DOM), which brings an XML document into memory, but is much easier to use.

Query results can be used as parameters to XElement and XAttribute object constructors.

Relies heavily on generic classes.

Is not optimized to work with untrusted XML documents, which require different security mitigation techniques.

LINQ to SQL

Provides a simple technique for retrieving and updating data as objects when the object model and the database model are the same.

As of .NET Framework 4.0, the Entity Framework will be the recommended data access solution for LINQ-to-relational scenarios.

LINQ to SQL will continue to be supported and will evolve based on feedback received from the community.

General Recommendations

Consider the following general recommendations when choosing a data access technology:

  • Flexibility and performance. If you need maximum performance and flexibility, consider using ADO.NET Core. ADO.NET Core provides the most capabilities and is the most server-specific solution. When using ADO.NET Core, consider the tradeoff of additional flexibility versus the need to write custom code. Keep in mind that mapping to custom objects will reduce performance. If you require a thin framework that uses the ADO.NET providers and supports database changes through configuration, consider the Enterprise Library Data Access Application Block.
  • Object relational mapping (O/RM). If you are looking for an O/RM-based solution and/or must support multiple databases, consider the Entity Framework. This is ideal for implementing Domain Model scenarios.
  • Offline scenario. If you must support a disconnected scenario, consider using DataSets or the Sync Framework.
  • N-Tier scenario. If you are passing data across layers or tiers, available options include passing entity objects, Data Transfer Objects (DTO) that are mapped to entities, DataSets, and custom objects. If you are building resource-centric services (REST), consider ADO.NET Data Services. If you are building operation-centric services (SOAP), consider Windows Communication Foundation (WCF) services with explicitly defined service and data contracts.
  • SOA and services scenarios. If you expose your database as a service, consider ADO.NET Data Services. If you want to store your data in the cloud, consider SQL Data Services.
  • Microsoft Windows Mobile. Many data technologies are too heavy for the limited memory capabilities of most Windows Mobile devices. Consider using SQL Server Compact Edition database and ADO.NET Sync Services to maintain data on a mobile device and synchronize it with a server-based database system. Features such as merge replication can also be useful in Windows Mobile scenarios.

Common Scenarios and Solutions

The following sections provide guidance on choosing the appropriate type of data access technology for your application.

Consider using ADO.NET Core if:

  • You must use low-level APIs for full control over data access in your application.
  • You want to leverage the existing investment in ADO.NET providers.
  • You are using traditional data access logic against the database.
  • You do not need the additional functionality offered by the other data access technologies.
  • You are building an application that must support a disconnected data access experience.

Consider using ADO.NET Data Services Framework if:

  • You want to access data that is exposed as a service using REST-like URIs.

Consider using ADO.NET Entity Framework if:

  • You must share a conceptual model across applications and services.
  • You must map a single class to multiple tables via inheritance.
  • You must query relational stores other than the Microsoft SQL Server family of products.
  • You have an object model that you must map to a relational model using a flexible schema.
  • You need the flexibility of separating the mapping schema from the object model.

Consider using ADO.NET Sync Services if:

  • You must build an application that supports occasionally connected scenarios.
  • You are using Windows Mobile and want to synchronize with a central database server.

Consider using LINQ to Data Services if:

  • You are using data returned from ADO.NET Data Services in a client.
  • You want to execute queries against client-side data using LINQ syntax.
  • You want to execute queries against REST data using LINQ syntax.

Consider using LINQ to DataSets if:

  • You want to execute queries against a Dataset, including queries that join tables.
  • You want to use a common query language instead of writing iterative code.

Consider using LINQ to Entities if:

  • You are using the ADO.NET Entity Framework.
  • You must to execute queries over strongly typed entities.
  • You want to execute queries against relational data using the LINQ syntax.

Consider using LINQ to Objects if:

  • You must execute queries against a collection.
  • You must execute queries against file directories.
  • You must execute queries against in-memory objects using the LINQ syntax.

Consider using LINQ to XML if:

  • You are using XML data in your application.
  • You want to execute queries against XML data using the LINQ syntax.

LINQ to SQL Considerations

LINQ to Entities is the recommended solution for LINQ to relational database scenarios. LINQ to SQL will continue to be supported but will not be a primary focus for innovation or improvement. If you are already relying on LINQ to SQL, you can continue using it. For new solutions, consider using LINQ to Entities instead. For more information, see the ADO.NET team blog at https://blogs.msdn.com/adonet/.

Mobile Considerations

A number of the technologies listed above are not available on the Windows Mobile operating system. The following technologies are not available on Windows Mobile at the time of publication:

  • ADO.NET Entity Framework
  • ADO.NET Data Services Framework
  • LINQ to Entities
  • LINQ to SQL
  • LINQ to Data Services
  • ADO.NET Core; Windows Mobile supports only SQL Server and SQL Server Compact Edition

Be sure to check the product documentation to verify availability for later versions.

Additional Resources

For more information, see the following resources: