.NET Core Data Access

.NET Core was released a few months ago, and data access libraries for most databases, both relational and NoSQL are now available. In this post, I’ll detail what client libraries are available, as well as show code samples for each of them.

Dapper

Dapper is a micro-ORM built and maintained by StackExchange engineers. It focuses on performance, and can map the results of a query to a strongly-typed list, or to dynamic objects. .NET Core support is available.

NPoco

Relational databases

SQL Server

The Microsoft SQL Server client library is built into .NET Core. You don’t have to use an ORM, and can instead go directly to the metal and talk to a SQL Server instance or to an Azure SQL database using the same APIs from the System.Data.SqlClient package.

SQLite

SQLite is a self-contained, embedded relational database that is released in the public domain. SQLite is lightweight (less than 1MB), cross-platform, and is extremely easy to embed and deploy with an application, which explains how it quietly became the most widely deployed database in the world. It’s commonly used as an application file format.

Firebird

NoSQL

Azure DocumentDB

Azure DocumentDB is a NoSQL database service built for fast and predictable performance, high availability, automatic scaling, and ease of development. Its flexible data model, consistent low latencies, and rich query capabilities make it a great fit for web, mobile, gaming, IoT, and many other applications that need seamless scale. Read more in the DocumentDB introduction. DocumentDB databases can now be used as the data store for apps written for MongoDB. Using existing drivers for MongoDB, applications can easily and transparently communicate with DocumentDB, in many cases by simply changing a connection string. The next version of the DocumentDB client library, which will be available around the Connect event, supports .NET Core.The latest version of the DocumentDB SDK supports .NET Core.

Cassandra

Apache Cassandra is a highly scalable and fault-tolerant NoSQL database. DataStax is a C# driver for Cassandra with built-in support for mapping Cassandra data to CLR objects. The latest version is compatible with .NET Core.

CouchBase

CouchDB

CouchDB is a document database that I personally like a lot for its simplicity. It can scale from small devices such as a Raspberry Pi to cloud applications. It uses a very simple HTTP and JSON-based API, which limits the need for a client library. C# client libraries do exist, but none of them support .NET Core today as far as I can tell except for Kanapa which hasn’t been updated for a while. It’s very easy to interact with the database through its REST API nonetheless.

YesSql

YesSql is an interesting library that implements a transactional document database on top of relational stores such as SQL Server.

Lucene.NET

Finally, I want to mention Lucene.NET. It’s not technically a database, but it’s so useful in setting up full-text search on a data-driven project that a post on data access wouldn’t be complete without it. The team has put a lot of work into the new version of Lucene to implement new features and major improvements, and they also made it compatible with .NET Core. It’s still early, but prerelease packages will soon be available.

What about OLE DB?

OLE DB has been a great way to access various data sources in a uniform manner, but it was based on COM, which is a Windows-only technology, and as such was not the best fit for a cross-platform technology such as .NET Core. It is also unsupported in SQL Server versions 2014 and later. For those reasons, OLE DB won’t be supported by .NET Core.

Keeping track

More database support for .NET Core will no doubt become available in the future, and we’ll make sure to highlight new client libraries in our Week in .NET posts as they get announced. In the meantime, I hope this post helps get you started with .NET Core application development.

Tags

I can just imagine happiness of developers who REMOVE all MS-OLE-COM hooks from simple database library! Cannot believe MS sit on ugly COM so many years… or am I wrong and VS still full of that legacy sh…t?

Wow this is an incredibly impressive list, Bertrand. Great work. Unfortunately it’s TOO great as now I am confused. 😛 I seemed to have overlooked DocumentDB as a viable ORM-esque (or is that DRM Document-Relational-Model? 😛 ) offering. Is there a reason to use this over EF? I’ve always considered using EF as the defacto ORM, but it does look like DocumentDB is competitive. Are there any resources you can point to that do a better job with compare/contrast (pros/cons) between the two?

Well, the way I see it, what database engine you use should be determined by your application. For instance, Content Management Systems are heavily slanted towards document databases because content items are typically composite documents with a fluid schema. Then on top of that, you can choose to use something like an ORM to improve productivity.
There are of course many factors that enter such decisions, and the post wasn’t written with the goal of helping you make them (maybe that could be for another post, although there is plenty of existing literature). My goal in writing this post was to show what choices exist on .NET Core, and to point readers in the right direction for their specific choice. I hope this makes sense.

No it’s an excellent list and you did a fantastic job listing them out. It’s now in my world I have what looks like an ORM-ish alternative/competitor to what I’ve always used. Although it does look like DocumentDB is JS-based with its logic, so that makes it seem more web-centric for the database script kiddies. Also, the CMS-angle is good to know, too. I will do some research on this and post any links I find of value here. Thanks for the quick reply and solid content.

So for cross platform Oracle, you suggest just using Java instead? Because neither Oracle or MS seem in any hurry to bring .net core support for it.

2 years ago

Bertrand Le Roy

I’m not suggesting that at all, no. Each database’s community builds its own clients, and Oracle is no different. We are doing what we can to help these communities, and we have reached out to Oracle to offer to help them. They have delivered on MySql, but are not commenting on the Oracle database product at this moment.
For cross-platform ORacle development, you can still use Mono: http://www.mono-project.com/docs/database-access/providers/oracle/

2 years ago

bob

Not sufficient answer. Nor is Mono a sufficient answer. Oracle Managed Driver is needed in .NET Core. Many business applications that would like to use Core to say run in PCF (or other CloudFoundry, etc. platforms) use Oracle as a database (and no, switch is not an option). So swing the Microsoft big stick and get Oracle to play ball and quit being Oracle.

Well, @bob, I’m sorry you don’t find that answer satisfying, but that’s the way things are right now. I’m not sure what you mean by “swing the Microsoft big stick”. I honestly don’t know what that means.
There is something that can be done however, and that’s to contribute, for example by building an ODBC client, that would help with Oracle and other data stores that are also not currently accessible.

here is an unofficial oracle client for .net core based on mono’s System.Data.OracleClient it works. It is not ready for production use yet. i have no idea how to create a provider for EF Core though. Where would you start?

Yuri, please see the other comment threads that discuss Oracle support. Oracle has not announced .NET Core support for their client libraries. Barring that, your best chance for Oracle support may be to contribute an ODBC client.

Yes, even though no one can predict the future that looks like a big problem for .NET Core. There are many frameworks people actually use in .NET Framework and not easily portable to .NET Core. IMHO, this significantly jeopardize the future of .NET Core. While it will might be useful for fresh new code that really requires cross platformity. Many companies will not even consider .NET Core option due to this fact. If my boss asks me to run our services in Linux, I would just use mono. I do understand there is huge amount of engineering effort and perhaps multi billion budget behind. And .NET Core is actually trying to be something different than .net on *nix. But mono already achieved that too. Alas, I would even say, instead of reinventing something like .NET Core, killing .NET itself and embracing and fixing mono on every platform would have been a better choice (proven by Unity 3D). But I guess it is too late for that. Sorry for being too pessimistic but just my 2 cents topic. You will ignore these anyhow 🙂

2 years ago

Mike

Mono chosen to implement cross -platform on copy of full .net because that was available at the time… And even then not all pieces were ported over… If Unity were to make this choice today… Surly they would choose .NET core.

.NET Core is a fresh look and take on engineering .NET for the net 10+ years, taking into account lessons learned … New foundation for things to come…

“Unity were to make this choice today… Surly they would choose .NET core.”
Nope!

2 years ago

Bertrand Le Roy

“You will ignore these anyhow” <- like I'm in the habit of doing that… Anyways. This is why we're all hard at work on .NET Standard 2.0, which will have a much greater API surface and will make porting way easier.

What about ODBC? ODBC is a supported by SQL Server, and it’s a cross platform technology.

2 years ago

Ramon de Klein

ODBC is also a binary solution that is only compatible with Windows. I won’t expect any support for it in .NET Core.

2 years ago

Piers Williams

That’s not actually true. In recent years ODBC’s probably been *more* prevalent outside the Windows camp, to the point that Microsoft eventually gave up on OLEDB and went back to ODBC as the primary supported driver for SQL Server connectivity. And ship things like this: https://www.microsoft.com/en-au/download/details.aspx?id=28160 (Microsoft SQL Server ODBC driver for Linux)

Whilst originally created by Microsoft, ODBC is a defacto industry standard. Not supporting ODBC in core would seem like a bit of a limiter. But still early days I guess.

If someone has got to have ODBC support on .Net Core and it is not included as part of .NET Core, you could always take the source code for System.Data.Odbc from the Mono project and modify it for .NET Core.

I took Mono’s ODBC client System.Data.Odbc and ported it to .Net Core.

It works on .net core on Windows 10. It can connect to SQL Server 2016 via a DSN. It was a brute force port. Now, I need to work on cleaning it up, making sure it can work on Linux, Mac, etc.. too. This would require checking the operating system/platform and use the appropriate native library. Example: foo.dll on Windows vs. libfoo.so on Linux vs. libfoo.dynlib on macOS.

That is awesome. Thanks for the update. Would you mind adding a comment to https://github.com/dotnet/corefx/issues/13035 with a pointer to your work? Please do keep me updated on your progress, I’d love to point to it when it’s ready.

I entered another comment earlier about ODBC, but it is awaiting moderation. Not sure why.

Do an internet search for iodbc and unixodbc which are both open source ODBC managers for Linux/Unix-like platforms. They are both cross-platform so they should be able to work on non-Unix-like operating systems as well.

The Mono Project has an ODBC driver in System.Data.Odbc. I think it is based on Microsoft .NET Reference implementation. So, you could take mono’s version of Odbc and modify it for .net core. Mono does strive to be cross-platform including Linux, Mac, Windows, etc. If that does not work, you can look for an older version of Mono that pre-dates the inclusion of Microsoft .NET Reference implementation.

It’s more of a re-invention of EF than a lite version. It has some capabilities that EF6.x will never have. At the same time it’s a v1, so it doesn’t have all the features that EF6.x had. A much better and in-depth comparison is at https://docs.microsoft.com/en-us/ef/efcore-and-ef6/index.

As one of the committers on the Lucene.Net team I want to thank you for the shout-out. We’re about 90% of the way to a release, but could still use some good people to help us polish off the API and, more importantly, add the .NET touches to it to make it as great for the .NET world as it is in Java. For example, we could use some great tooling to make integration with SQL Server/Entity Framework simpler (such as a Directory that uses SQL Server as the back end), plus other tools and extension methods to help make the experience better. We need both people to come up with the ideas and people to help with implementing them. Not to mention, that antiquated page that was linked to here could use polishing as well.

Since we are getting close to having the .csproj tooling (Web Applications), we have two items left on our list, before we can fully support .NET Core. We have huge C/C++ libraries for which we have create Managed C++ wrappers. I am assuming the same is available with .NET Core, but I haven’t been able to find anything information on this.

And last, but certainly not lease, Application Domains. We need to support multiple versions of code at the same time, and we’ve been doing this by instancing each version in a different Application Domain. Is this available in .NET Core? The last I heard, it was partially working, but it wasn’t fully available yet.

2 years ago

Bertrand Le Roy

P/Invoke works exactly the same in .NET Core. The question about AppDomains would probably be better addressed somewhere else. Please contact me by email, and I’ll find you the specific answers you need. beleroy at Microsoft.

We’ve been told in no uncertain terms that this will not be in .Net Standard. Is this wrong?

2 years ago

Bertrand Le Roy

From the NetStandard blog post: “The AppDomain type has many APIs that aren’t tied to creating app domains, such as getting the list of loaded assemblies or registering an unhandled exception handler. These APIs are heavily used throughout the .NET library ecosystem. For this case, we decided it’s much better to add this type to .NET Standard and let the few APIs that deal with app domain creation throw exceptions on platforms that don’t support that, such as .NET Core.” – https://blogs.msdn.microsoft.com/dotnet/2016/09/26/introducing-net-standard/

Yeah. But I don’t think is clear that the only things in the AppDomain API that will be supported will have nothing to do with creating AppDomains! Only helper functions to do with Assemblies that were put in this API ‘by mistake’.

So when people ask ‘will AppDomains’ be supported, my understanding direct from MS is that the answer is ‘no’.

Hey Linq Dan, I’ve been trying to look for documentation on how to use this. I see how the default context works, but I am guessing that I need to create multiple contexts, 1 for each version. The link that you provided does not have any samples in this regard. Is there any documentation or samples that go into this? Is it as simple as creating multiple AppPathAssemblyLoadContext classes, one for each version?

2 years ago

Eduardo

This will sound as archaic as it is, yet, I need to ask, what can I use (advise) to manage a set of Access DBs I use for local repositories in my Apps? (ODBC still?)

For the rest, what amazing and exciting library we got with .NET Core!.

I wish Microsoft would do something to modernize ADO.NET Core. I.e. the low-level database API that was called “Core” since a long time ago before they started calling everything Core. It really seems like it needs an overhaul. It is way to imperative. You need like 20 statements to do anything, when they could have made it so that a lot of the methods had additional parameters that would have simplified things and cut down the number of necessary statements. The other thing that has always really annoyed me about ADO.NET Core is the fact that it uses concrete classes in a lot of the examples, which locks you into using a particular database. I.e. coding things with SqlConnection, MySqlConnection, etc. rather than using DbConnection. If I remember correctly, there are issues querying for database metadata in a DBMS independent fashion. I really wish Microsoft would have designed it with DBMS-independence in mind from the start. It seemed like they grafted that on after the fact. Personally, I find JDBC, while similar, is easier to work with. Less statements. EF has been getting a lot of attention, but, it seems like the low-level API needs some attention also. EF doesn’t cut it when it comes to things like bulk loading data.

I think we need a ADO.NET Core specification site similar to the ODBC Specification (https://github.com/Microsoft/ODBC-Specification) to improve and the ADO.NET and to create the next generation Data Access (not ORM) specification for .NET Core.

ODBC is a widely adopted, standard, client-side API that works well for relational (i.e., tabular) data. However, many types of today’s data may not fit well into a relational model. ODBC 4.0 defines extensions to support non-relational concepts such as:

In addition, general enhancements are added in order to improve interoperability of generic ODBC clients across diverse data sources, including enhanced discovery, authentication, syntax, and capability reporting.

Microsoft ODBC version 4.0 is a new version of ODBC for modern data sources that adds support for features such as:

Mh,
what i dont’t get so far is the session & transaction handling with EF.
So far, we have used NHibernate, and its best practices tell us, if we want session/transaction safety, we have to care about it on our own.
How is this done in EF?
Lets say a website, classic “session-per-request”-pattern (where static/etc. requests are excluded, sure) – how can i guarantee in EF that all the stuff is written/read correctly?
What about isolation modes of DB rows?

When you write to the database in EF (via DbContext.SaveChanges()) it is an atomic operation – i.e. it all succeeds, or all fails. You can of course manually control the transaction if you want to (for example – you want to have save and query operations run within the same transaction). More info on that at https://docs.microsoft.com/en-us/ef/core/saving/transactions.

2 years ago

BobT

Here are the minimum set of test cases for a micro ORM from our $2+ mm budget .net project:
– Select query into an .net object
– All members of the object
– subset of the members of the object
– Remap table column X to property Y
– Save object to database table – mapping the properties into table columns
– Ignore properties with an ignore attribute
– Remap property Y to table column X
– begin / rollback / commit transaction
– Select into a strongly typed list or enumeration
– Run a DBCC SQL command and get the output into a list of dyamic/expando objects
– Run a SQL batch text string and get back multiple result sets
– Run a stored procedure
– Run a parameterized sql statement
– Run a sql insert statement and return the identity column key generated
– connect to database
– disconnect from database (force connection closed for administration operations)
– use a different database on the same sql server
– schema operations
– determine if a table, database or view exists (for dynamically upgrading a database)
– determine if a table has a particular column
– rename table, rename table column, rename database
– drop table, remove table column, drop view, drop database, etc
– create table, create view, create database
– bulk inserts
– set transaction identifier number
– create temp table
– insert blob into table,
– sql query to return columns + a column stored as a blob in a single operation – no need to do a special extra call to get the blob
– get statistics for a query or database operation
– execute stored procedure
…….. and more

Our developer unit test:
– What is the time needed, number of code modifications, number of special build steps/code generation needed to run, for the simple operation of adding a new nvarchar column to an existing table
-> This should take well under 20 minutes
-> Old non-micro EF took considerably more than 20 minutes

We did not need the following (aka present and future maintenance problems)
– generate sql schema from our object structure
– generate c# objects from an existing sql database
– anything that added custom build steps or code generation steps to our Visual Studio project
– anything that added extra UI elements (commands, buttons, …) to Visual Studio
– having to manually run a C# code generator tool or a SQL generator tool to get started

We needed:
– A single DLL to provide the micro-orm to be manually added as a reference to our projects

In past large EF projects, we slowly replaced EF with straight SQL queries and then removed EF completely due to ongoing high cost of maintenance.

Or even try to porting System.Data.Odbc to .net core from the Microsoft .Net Reference implementation. This would be the preferred source for porting to .Net Core.

However, if you have to have something now, you could try this. I did a brute force port of Mono’s old implementation of Odbc to .net core. It works on Windows, but I have to modify it to make it work on other platforms – specifically using their library filenames. It needs to be cleaned up. And I have to compare it to other .net core data providers to make sure it was implemented correctly.

Depends on what kind of Sybase DBMS are you talking about?
There is ASE (Adapter Server Enterprise which is been called Sybase SQL Server in the past and uses the TDS protocol. I think SAP owns Sybase now.

Then Sybase SQL Anywhere (could also be called ASA). Sybase SQL Anywhere DBMS is un-related to ASE.

If you have an ODBC driver for your database, consider ODBC. Someone needs to port Microsoft’s System.Data.Odbc from the .NET Reference source to .net core.

Or you could port an older version of Mono’s Odbc driver.

I have done a brute force port of an old version of Mono’s System.Data.Odbc to .net core. And it works on Windows. However, there is a lot to do still to clean it up, make sure it uses similar things like .net core providers like SqlClient, NPgsql, etc..

Try contacting SAP and ask them have they considered a .NET Core driver for ASE and SQL Anywhere databases.

Mono has an old version of a Sybase ASE provider, but it was not fully completed. It can connect to Sybase databases and retrieve data You need to look for the source code of the 2 assemblies: Mono.Data.Tds and Mono.Data.SybaseClient and look at TDS Protocol version 5.0 support. Then you could port this to .net core.

I wanted to point out that there exist open source projects FreeTDS and jTDS which have an implementation for Sybase ASE (Adapative Server Enterprise) databases. FreeTDS being C libraries while jTDS is java. Sybase ASE is not to be confused with Sybase ASA (SQL Anywhere) databases.

So, any port of a .net driver to .NET core for Sybase ASE could look at those 2 open source projects on how they connect to Sybase ASE databases.

1 year ago

Kenz

Basically we use OLEDB provider to connect to Sybase database, and there it is failing for provider not supported by EF Core.

Sybase was bought by SAP. So, it is called SAP ASE and SAP SQL Anywhere now. ASE is the traditional SQL Server while SQL Anywhere is typically deployed with the app.

I do not think Microsoft will port OLEDB to .Net Core because OLEDB is not cross-platform. However, there is nothing stopping you from porting System.Data.OleDb from the .Net Reference source code to .NET Core. However, it would only be able to run on Windows though.

So you are options are contacting SAP and asking them if they plan on creating a driver for .Net Core apps to connect to ASE and/or SQL Anywhere databases. Or contact a commercial database driver vendor like Dev Art or Data Direct or OpenLinkSW or EasySoft and ask them. Or consider using ODBC when it becomes available. Or port the code yourself.

I ported an old Mono implementation of Odbc to .NET Core. I have used it to connect and read data from a Microsoft SQL Server database via a ODBC DSN. The port is not finished; it needs to be cleaned up. It is not ready for Production use. However, if you have to have it now to connect to a Sybase ASE or Sybase SQL Anywhere database that has an ODBC driver – you could use it for your needs. I have not made it work on Linux yet, but that will not be hard to do.

Thanks for the github link man!
I needed to change the bufsize in “case OdbcType.NVarChar” from OdbcDataReader.cs to a even number, otherwise i got the following error: “[HY090] [Sybase][ODBC Driver]Invalid string or buffer length”
Why would you need a buffer size *2+1? Wouldnt it be enough with MaxLength?

Define faster 😉 Faster at doing what, in what kind of environment? You can write fast EF code, and slow ADO.NET. In general however, the higher the abstraction level, the slower it’s likely to be all other things being equal. There are caveats of course, as there are examples of machine optimizations being better than typical human-written code. In general however, there’s no good substitute to benchmarking for your specific application and making an informed decision based on that.