Data Strategy based on ADO.NET 3.5

  • Article

Often most of my customer seek guidance on data strategy based on ADO.Net technologies stack such as ADO.NET DAL, LINQ and Entity Framework and the Data Services.

The choices for developers/architects are:

1) Traditional ADO.Net Provider Model

  • Reduces OR mapping impedance mismatch through (typed) dataset
  • Tightly coupled with store/RDBMS (Like name of table columns and SP Parameters)
  • Sweet Spot: Hand coded Data Access Layer (DAL)/ Folks who don’t like ORMs (or Dynamic SQL within App Tiers)

2) LINQ to SQL

  • Suitable for TPH model (Table per Hierarchy)
  • Provides abstraction and compile time checks
  • Sweet Spot: RAD/Prototype/Proof-of-Concept (PoC)
  • Limitation: No further advancement planned

3) Entity SQL

  • Dynamic query creation
  • Independence from storage/RDBMS
  • ESQL based on underlying Entity Data Model
  • Limits: Read Only, Text-Based, Reader based (EntityDataReader)
  • Sweet Spot – use it when you have multiple databases, leverage your SQL like skills

4) LINQ to Entities

  • Supports TPH, TPT (Table per Type), TPCT (Concrete Types) inheritance and O/R mappings
  • Strongly typed, compile time verification, Intelligence
  • Uses Entity Client Provider under the covers but returns collection (IEnumerable)
  • Powerful LINQ lambda expressions, operators and dynamic criteria
  • Sweet Spot: LINQ and Dynamic SQL lovers

5) Object Services (Entity Framework)

  • Full CRUD
  • Choice of Entity SQL, LINQ or EDMContext
  • Fully materialized and strongly typed objects (Entity Type and Entity Sets aka Containers)
  • Sweet Spot: No Data Access Layer required, First Class Entities, Heavy-Lifting by Entity Framework
  • Limits: Thin Object Layer required to process basic entities as per business logic, transaction and concurrency management challenges, SQL hints, Diagnostics is difficult (like source of SQL Query)

5) Data Services (Astoria)

  • Ability to consume EDM or LINQ-to-SQL models
  • Any Store + (interface to support update semantics for ADO.NET Data Services) implementation 
  • Provides platform for exposing data over HTTP as a service for various clients such as AJAX/Silverlight/.Net and non-MSFT applications
  • REST Style resource model for full CRUD
  • Uses minimalistic formats to represent data, and supports AtomPub/JSON format to accommodate as many clients.
  • Extensibility through pipeline (Query and Change Interceptors) and Service Operations
  • Granular security at entity level
  • IIS based security for service level such as SSL and Auth/Z

Store Procs can be imported as Functions and can be invoked over the model directly (both LINQ to SQL and EF supports it).

It’s perfectly fine to have a mixed strategy that addresses your needs in an existing project. When starting a new project, you can make clear choices such as discarding dataset/adapter based approach.

Hope that this brief summary is useful to you in order to quickly compare various options offered by ADO.NET for data access.