A comparison on SQL Server data access methods

Type providers + Query Expressions

F# Query Expressions (aka LINQ 2.0) is lovely and useful technology. It makes writing data access code a pleasant exercise. There are several different implementations available: SQLProvider (recent community effort), SqlDataConnection/DbmlFile and SqlEntityConnection/EdmxFile.

But I would like to point out some reasons you might prefer SqlCommandProvider over those. Take a look at this StackOverflow issue. There are dozens of similar questions (hundreds if you include C# LINQ which is essentially the same issue).

So, what's a deal? The thing is, you can have perfectly valid F# code that compiles but still fails at run-time because of unsupported F#-to-SQL translation semantics. Contrary, SqlCommandProvider offers "What You See Is What You Get" approach. Once F# code involving SqlCommandProvider passes compilation stage you are guaranteed to have valid executable (both F# code and T-SQL).

Lack of control and opaqueness of F#-to-SQL conversion spells performance problems too. At times developers are really frustrated by ineffective T-SQL code generated by type providers (most of EF users are aware of this issue). Evolution and changes in T-SQL code generated by type provider may present certain problems too. A data access code that is performing well today can become suboptimal tomorrow. SqlCommandProvider gives you direct access to full power of T-SQL scripting including the latest features which are often unavailable in type providers because they target older or different versions of the system.

Common for all TypeProviders

You write logic, not POCO-objects and mappings
If your database changes, your code breaks at compile time
No SQL-injection vulnerabilities
Commit to existing transactions so you can mix with other technologies
You can call stored procedures if needed
Joins and nested in-queries are supported so you can avoid "n+1 database hits" problems

FSharp.Data.SqlClient

Good:

Supports .NET Standard and .NET Core
User has full control of SQL
Which allows full utilization of database indexes with complex queries
SQL syntax is already familiar for many
Exposes database objects directly in code
Supports async database operations
Used in production for enterprise development for years
Open source

Not so good:

By default supports only Microsoft SQLServer and T-SQL
Code overhead if you have a large amount of different small operations
Doesn't generate domain model for work with C#

SQLProvider

Good:

Supports .NET Standard and .NET Core
Supports any database (MSSQL, MySQL, PostgreSQL, Oracle, Odbc, ...)
Changing the database is not actually huge work
Makes effective simple SQL
Supports async database operations
Used in production for enterprise development for years
Simplifies user code
Open source

Not so good:

No Manual control over generated SQL
FSharp/LINQ query-syntax is a learning curve
Doesn't generate domain model for work with C#
Most LINQ-operations supported, but not yet complex group-by scenarios
Updates in complex scenarios can be a pain

SqlDataConnection

Good:

You can use generated DB-model from C#-projects
Supports even complex LINQ-operations
Used in production for enterprise development for years

Not so good:

Generates EF-models in background
Not dynamic
Doesn't scale to large databases
FSharp/LINQ query-syntax is a learning curve
Closed source
Supports only Microsoft SQLServer
No support for .NET Standard / .NET Core

Rezoom.SQL

Good:

User has full control of SQL
Which allows full utilization of database indexes with complex queries
SQL syntax is already familiar for many
Supports SQLite, SQL Server, and PostgreSQL
Open source

Not so good:

No support for .NET Standard / .NET Core
No support for MySQL, Oracle, ...
Code overhead if you have a large amount of different small operations
Doesn't generate domain model for work with C#

Dapper

FSharp.Data.SqlClient is much closer to the family of micro-ORMs rather than complete solutions like EntityFramework and NHibernate, hence comparison with the best of the breed - Dapper.

Dapper is a micro-ORM by StackOverflow with a main goal of being extremely fast. Here is a description from StackOverflow itself:

dapper is a micro-ORM, offering core parameterization and materialization services, but (by design) not the full breadth of services that you might expect in a full ORM such as LINQ-to-SQL or Entity Framework. Instead, it focuses on making the materialization as fast as possible, with no overheads from things like identity managers - just "run this query and give me the (typed) data".

Let's start with performance to get it out of the way.

Dapper comes with an excellent benchmark. The focus of the test is on deserialization. Here is how FSHarp.Data.SqlClient compares with all major .Net ORMs:

Tests were executed against SqlExpress 2012 so the numbers are a bit higher than what you can see on Dapper page. A test retrieves single record with 13 properties by random id 500 times and deserializes it. Runs for different ORMs are mixed up randomly. All executions are synchronous.

Note that we didn't put any specific effort into improving FSharp.Data.SqlClient performance for this test. The very nature of type providers helps to produce the simplest run-time implementation and hence be as close as possible to hand-coded ADO.NET code.

Now, to the usage. Line-by-line comparison is probably unfair as I'll end up comparing C# with F#, and C# is going to lose. Keeping in mind that FSharp.Data.SqlClient is not strictly an ORM in commonly understood sense of the term, here are the some pros and cons:

Because result types are auto-generated, FSharp.Data.SqlClient doesn't support so-called multi-mapping
As FSharp.Data.SqlClient is based on features specific for Sql Server 2012; Dapper provides better range of supported scenarios, including Mono
Other side of this is that FSharp.Data.SqlClient fully supports SqlServer-specific types like hierarchyId and spatial types, which Dapper does not
FSharp.Data.SqlClient fully supports User-Defined Table Types for input parameters with no additional coding required, as opposed to Dapper
Dapper intentionally has no support for SqlConnection management; FSharp.Data.SqlClient encapsulates SqlConnection life-cycle including asynchronous scenarios while optionally accepting external SqlTransaction.

Following FSharp.Data.SqlClient features are unique:

Reasonable auto-generated result type definition so there is no need to define it in code
This type also comes with IDictionary<string,obj> and DynamicObject implementation
Sql command is just a string for other ORMs, while FSHarp.Data.SqlClient verifies it and figures out input parameters and output types so design-time experience is simply unparalleled
Design-time verification means less run-time tests, less yak shaving synchronizing database schema with code definitions, and earliest possible identification of bugs and mismatches
SqlProgrammabilityProvider lets user to explore stored procedures and user-defined functions right from the code with IntelliSense

It is my believe that FSharp.Data.SqlClient comes closest to the mission of micro-ORM - to make conversion between data stored in database and .Net run-time types as painless as possible while keeping away from object-relational impedance mismatch as described in famous blog The Vietnam of Computer Science by Ted Neward.

Generating strongly typed DataSets

Using strongly typed DataSets requires creation of XSD schema which then used to generate C# code. Dubious pleasure of hand-crafting XSD aside, this traditional technique suffers from the same limitation as Entity Framework and such when compared to FSharp.Data.SqlClient: no design-time interaction with model database - it is up to a user to keep definitions and database schema in sync.

At the same time, FSharp.Data.SqlClient supports strongly typed data table as one of the result types. No XSD required.