Cheat Sheet: Data Access Technology Matrix

J.D. Meier, Alex Homer, David Hill, Jason Taylor, Prashant Bansode, Lonnie Wall, Rob Boucher Jr, Akshay Bogawat

Objectives

  • Understand the tradeoffs for each data-access technology choice.
  • Understand the design impact of choosing a data-access technology.
  • Understand all data-access technologies across application types.
  • Choose a data-access technology for your scenario and application type.

Overview

Use this cheat sheet to understand your technology choices for the data access layer. Your choice of data-access technology will be related both to the application type you are developing and the type of business entities you choose for your data layer. Use the Data Access Technologies Summary to review each technology and its description. Use the Benefits and Considerations Matrix to make an informed choice of data-access technology based on the advantages and considerations for each one. 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 with the Microsoft .NET platform:
  • ADO.NET Core. ADO.NET Core provides general retrieval, update, and management of data. ADO.NET includes providers for Microsoft® SQL Server®, OLE-DB, 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 via a Uniform Resource Identifier (URI). 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 contains support for LINQ to Entities, which provides LINQ support for business objects exposed through the Entity Framework. Current plans for the Entity Framework will build in functionality so that it can be used to provide a common data model across high-level functions such as data query and retrieval services, reporting, synchronization, caching, replication, visualization, and business intelligence (BI). 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 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 synchronization for ADO.NET-enabled databases. It enables data synchronization to be built in 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 Microsoft Visual Basic® with native language syntax for queries. 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).Understand that LINQ 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 core system assemblies.
  • 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 http://blogs.msdn.com/adonet/archive/2008/10/31/clarifying-the-message-on-l2s-futures.aspx.

Benefits and Considerations Matrix

Object-Relational Access

Technology Benefits Considerations
ADO.NET Entity Framework (EF) Decouples the underlying database structure from the logical data model. Requires you to change the design of your entities and queries if you are coming from a more traditional data-access method.
Entity SQL (ESQL) provides a consistent query language across all data sources and database types. You have separate object models.
Separates metadata into well-defined architectural layers. Have more layers of abstraction than LINQ to DataSet.
Allows business-logic developers to access the data without knowing database specifics. Can be used with or without LINQ to Entities
Provides rich designer support in Microsoft Visual Studio® to visualize your data-entity structure. If your database structure changes, you need to regenerate the Entity Data Model, and the Entity Framework libraries need to be redeployed.
Provider model allows it to be mapped to many databases.
LINQ to Entities Is a LINQ-based solution for relational data in the ADO.NET Entity Framework. Requires the ADO.NET Entity Framework
Provides strongly typed LINQ access to relational data
Supports LINQ-based queries against objects built on top of the ADO.NET Entity Framework Entity Data Model.
LINQ to SQL Simple way to read/write objects when object model matches database model. Functionality to be integrated into Entity Framework as of .NET Framework 4.0
Provides strongly typed LINQ access to SQL data. Maps LINQ queries directly to the database instead of through a provider, and therefore works only with Microsoft SQL Server.
Processing is on the server

Disconnected and Offline

Technology Benefits Considerations
LINQ to DataSet Allows full-featured queries against a DataSet. Processing is all on the client side.
ADO.NET Sync Services Enables synchronization between databases, collaboration, and offline scenarios. Change tracking ability needs to be provided.
Synchronization can execute in the background.
Provides a hub-and-spoke type of architecture for collaboration between databases. 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 Representational State Transfer (REST)-like scheme. Is only applicable to service-oriented scenarios.
Data can be returned in either Atom or JSON formats.
Includes a lightweight versioning scheme to simplify the release of new service interfaces.
.NET, Microsoft Silverlight®, and Asynchronous JavaScript and XML (AJAX) client libraries allow developers to work directly with objects and provide strongly typed LINQ access to ADO.NET Data Services.
.NET, Silverlight, and AJAX client libraries provide a familiar API surface to Windows Azure Tables, SQL Data Services, and other Microsoft services.
LINQ to Data Services Allows you to create LINQ-based queries against client-side data returned from ADO.NET Data Services. Can only be used with the ADO.NET Data Services client-side framework.
Supports LINQ-based queries against REST data.

N-Tier

Technology Benefits Considerations
ADO.NET Core Includes .NET managed code providers for connected access to a wide range of data stores. Code is written directly against specific providers, thereby reducing reusability.
Provides facilities for disconnected data storage and manipulation. The relational database structure may not match the object model, requiring you to write a data mapping layer by hand.
ADO.NET Data Services Framework Is a simple out-of-box solution with the ADO.NET Entity Framework. Is only applicable to service-oriented scenarios.
Data can be addressed directly via a URI using a REST-like scheme. 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.
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.
.NET, Silverlight, and AJAX client libraries provide a familiar API surface to Windows Azure Tables, SQL Data Services, and other Microsoft services.
ADO.NET Entity Framework Separates metadata into well-defined architectural layers. Requires you to change the design of your entities and queries if you are coming from a more traditional data access method.
Supports LINQ to Entities, for querying complex object models. Entity objects can be shipped across the wire, or you can use the Data Mapper pattern to transform entities into objects that are more generalized DataContract types. Planned addition of POCO support will eliminate the need to transform objects when shipping them across the wire.
Provider model allows it to be mapped to many database types Building service endpoints that receive generalized graph of entities is less “service oriented” than endpoints that enforce stricter contracts on the types of payload that might be accepted
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 the 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 (POCO) objects to be shipped across service boundaries
LINQ to Objects Allows you to create LINQ-based queries against objects in memory. Will only work with objects that implement the IEnumerable interface.
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.
LINQ to XML Allows you to create LINQ-based queries against XML data. Relies heavily on generic classes.
Comparable to the Document Object Model (DOM), which brings an XML document into memory, but is much easier to use. Is not optimized to work with untrusted XML documents, which require different mitigation techniques for security.
Query results can be used as parameters to XElement and XAttribute object constructors.
LINQ to SQL Is a simple way to get objects in and out of the database 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:
  • 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 Data Access Application Block.
  • Object relational mapping (ORM). If you are looking for an ORM-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 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 / 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 Windows Mobile devices, with their limited memory capabilities. Primarily utilize SQL Server Compact Edition and ADO.NET Sync Services to maintain data on a mobile device and synchronize it with a larger database system. Features such as merge replication can also assist in Windows Mobile scenarios.

Note: You might need to** mix and match the data-access technology options for your scenario. Start with what you need.

Common Scenarios and Solutions

ADO.NET Core

Consider using ADO.NET Core if you:
  • Need to use low-level APIs for full control over data access in your application.
  • Want to leverage the existing investment in ADO.NET providers.
  • Are using traditional data-access logic against the database.
  • Do not need the additional functionality offered by the other data-access technologies.
  • Are building an application that needs to support a disconnected data-access experience.

ADO.NET Data Services Framework

Consider using the ADO.NET Data Services Framework if you:
  • Are developing a Silverlight application and want to access data through a data-centric service interface.
  • Are developing a rich client application and want to access data through a data-centric service interface.
  • Are developing an N-tier application and want to access data through a data-centric service interface.

ADO.NET Entity Framework

Consider using the ADO.NET Entity Framework if you:
  • Need to share a conceptual model across applications and services.
  • Need to map a single class to multiple tables via inheritance.
  • Need to query relational stores other than the Microsoft SQL Server family of products.
  • Have an object model that you must map to a relational model using a flexible schema.
  • Need the flexibility of separating the mapping schema from the object model.

ADO.NET Sync Services

Consider using ADO.NET Sync Services if you:
  • Need to build an application that supports occasionally connected scenarios.
  • Need collaboration between databases.
  • Are using Windows Mobile and want to sync with a central database server.

LINQ to Data Services

Consider using LINQ to Data Services if you:
  • Are using data returned from ADO.NET Data Services in a client.
  • Want to execute queries against client-side data using LINQ syntax.
  • Want to execute queries against REST data using LINQ syntax.

LINQ to DataSets

Consider using LINQ to DataSets if you:
  • Want to execute queries against a Dataset, including queries that join tables.
  • Want to use a common query language instead of writing iterative code.

LINQ to Entities

Consider using LINQ to Entities if you:
  • Are using the ADO.NET Entity Framework.
  • Need to execute queries over strongly typed entities.
  • Want to execute queries against relational data using the LINQ syntax.

LINQ to Objects

Consider using LINQ to Objects if you:
  • Need to execute queries against a collection.
  • Want to execute queries against file directories.
  • Want to execute queries against in-memory objects using the LINQ syntax.

LINQ to XML

Consider using LINQ to XML if you:
  • Are using XML data in your application.
  • 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. At the time of this writing, this is the product group position:

“We will continue make some investments in LINQ to SQL based on customer feedback. This post was about making our intentions for future innovation clear and to call out the fact that as of .NET 4.0, LINQ to Entities will be the recommended data access solution for LINQ to relational scenarios.”

For more information, see the ADO.NET team blog at http://blogs.msdn.com/adonet/archive/2008/10/31/clarifying-the-message-on-l2s-futures.aspx

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:

Last edited Dec 19, 2008 at 4:40 AM by prashantbansode, version 4

Comments

190 Jan 12, 2013 at 3:53 AM 
Attention, please! Good coast are here. Get your <a href="http://www.womencoastdress.com/"><strong>coast clothing</strong></a>,<a href="http://www.womencoastdress.com/"><strong>coast dresses</strong></a> and <a href="http://www.womencoastdress.com/"><strong>coast dress</strong></a> from the coast online. There are <a href="http://www.womencoastdress.com/"><strong>occasion wear dresses</strong></a>, <a href="http://www.womencoastdress.com/"><strong>cheap bridesmaid dresses uk</strong></a> and <a href="http://www.womencoastdress.com/"><strong>Coast Bridal Dresses</strong></a> for you. The <a href="http://www.womencoastdress.com/"><strong>occassion dresses</strong></a> and <a href="http://www.womencoastdress.com/"><strong>Coast One Shoulder Dress</strong></a> are good, too. If you like the <a href="http://www.womencoastdress.com/"><strong>coast dress sale</strong></a> here, you should hurry. The <a href="http://www.womencoastdress.com/"><strong>coast perla dress</strong></a> maybe your favourite. Just go!
http://www.womencoastdress.com/