.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.

ORM

EF Core

Entity Framework is Microsoft’s Object-Relational Mapper for .NET, and as such is one of the most-used data access technologies for .NET. EF Core, released simultaneously with .NET Core, is a lightweight and extensible version of Entity Framework that works on both .NET Core and .NET Framework. It has support for Microsoft SQL Server, SQLite, PostgreSQL, MySQL, Microsoft SQL Server Compact Edition, and IBM DB2 with more to come, such as Oracle.

What follows is an example of EF Core code accessing a blog’s database. The full tutorial can be found on the EF documentation site.

	using (var db = new BloggingContext())
	{
	db.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/adonet" });
	var count = db.SaveChanges();
	Console.WriteLine($"{count} records saved to database");

	Console.WriteLine();
	Console.WriteLine("All blogs in database:");
	foreach (var blog in db.Blogs)
	{
	Console.WriteLine($" - {blog.Url}");
	}
	}

view raw ef.cs hosted with

by GitHub

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.

	var sql = @"
	select * from Customers where CustomerId = @id
	select * from Orders where CustomerId = @id";

	using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
	{
	var customer = multi.Read<Customer>().Single();
	var orders = multi.Read<Order>().ToList();
	// ...
	}

view raw dapper.cs hosted with

by GitHub

NPoco

NPoco is a micro-ORM that also works on .NET Core.

	public class User
	{
	public int UserId { get;set; }
	public string Email { get;set; }
	}

	using (IDatabase db = new Database("connStringName"))
	{
	List<User> users = db.Fetch<User>("select userId, email from users");
	}

view raw npoco.cs hosted with

by GitHub

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.

	using (var connection = new SqlConnection("Server=tcp:YourServer,1433;Initial Catalog=YourDatabase;Persist Security Info=True;"))
	{
	var command = new SqlCommand("SELECT TOP 10 Id, Name, Price FROM Products ORDER BY Price", connection);
	connection.Open();
	using (var reader = command.ExecuteReader())
	{
	while (reader.Read())
	{
	Console.WriteLine($"{reader[0]}:{reader[1]} ${reader[2]}");
	}
	}
	}

view raw sqlserver.cs hosted with

by GitHub

PostgreSQL

PostgreSQL is an open source relational database with a devoted following. The Npgsql client library supports .NET Core.

	using (var conn = new NpgsqlConnection("Host=myserver;Username=mylogin;Password=******;Database=music"))
	{
	conn.Open();
	using (var cmd = new NpgsqlCommand())
	{
	cmd.Connection = conn;

	cmd.CommandText = "SELECT name FROM artists";
	using (var reader = cmd.ExecuteReader())
	{
	while (reader.Read())
	{
	Console.WriteLine(reader.GetString(0));
	}
	}
	}
	}

view raw postgresql.cs hosted with

by GitHub

Another interesting library for PostgreSQL that is compatible with .NET Core is Marten. Marten uses PostgreSQL storage to implements a document database.

MySQL

MySQL is one of the most commonly used relational databases on the market and is open source. Support for .NET Core is now available, both through EF Core and directly through the MySQL Connector for .NET Core.

	using (var connection = new MySqlConnection
	{
	ConnectionString = "server=localhost;user id=root;password=******;persistsecurityinfo=True;port=3305;database=music"
	}) {
	connection.Open();
	var command = new MySqlCommand("SELECT * FROM music.category;", connection);

	using (MySqlDataReader reader = command.ExecuteReader())
	{
	while (reader.Read())
	{
	Console.WriteLine(
	$"{reader["category_id"]}: {reader["name"]} {reader["last_update"]}");
	}
	}
	}

view raw mysql.cs hosted with

by GitHub

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.

You can use SQLite with EF Core, or you can talk to a SQLite database directly using the Microsoft.Data.Sqlite library that is maintained by the ASP.NET team.

	using (var connection = new SqliteConnection("Filename=" + path))
	{
	connection.Open();

	using (var reader = connection.ExecuteReader("SELECT Name FROM Person;"))
	{
	while (reader.Read())
	{
	Console.WriteLine($"Hello {reader.GetString(0)}!"));
	}
	}
	}

view raw sqlite.cs hosted with

by GitHub

There’s another SQLite package that’s compatible with .NET Core called SQLitePCL.raw.

Firebird

Firebird is a mature relational database with a small footprint. It now has a .NET Core compatible client library.

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.

	using (var client = new DocumentClient(
	new Uri("https://your-nosql-database.documents.azure.com:443/"),
	"******"))
	{
	var contentUri = UriFactory.CreateDocumentCollectionUri("cms", "content-items");
	var home = client.CreateDocumentQuery<ContentItem>(contentUri)
	.Where(item => item.Id == "home")
	.AsEnumerable()
	.FirstOrDefault();
	if (home != null)
	{
	Console.WriteLine($"{home.Id}: {home.Title}");
	Console.WriteLine(home.Body);
	}
	}

view raw documentdb.cs hosted with

by GitHub

MongoDB

MongoDB is a document database with an official .NET driver that supports .NET Core.

	var client = new MongoClient("mongodb://localhost:27017");
	var database = client.GetDatabase("commerce");
	BsonClassMap.RegisterClassMap<Person>();
	var customers = database.GetCollection<Person>("customer").AsQueryable();

	var query = from c in customers
	where c.Age > 21
	select c;

view raw mongodb.cs hosted with

by GitHub

RavenDB

RavenDB is a document database that is not only compatible with .NET Core, it’s also built with it.

	using (IDocumentStore store = new DocumentStore
	{
	Url = "http://localhost:8080/",
	DefaultDatabase = "Northwind"
	})
	{
	store.Initialize();

	using (IDocumentSession session = store.OpenSession())
	{
	IList<Product> results = session
	.Query<Product>()
	.Where(x => x.UnitsInStock > 10)
	.Skip(5)
	.Take(10)
	.ToList();
	}
	}

view raw ravendb.cs hosted with

by GitHub

Redis

Redis is one of the most popular key-value stores.

StackExchange.Redis is a high performance Redis client that is maintained by the StackExchange team.

	var redis = ConnectionMultiplexer.Connect("localhost");
	var db = redis.GetDatabase();
	var value = await db.StringGetAsync("mykey");
	Console.WriteLine($"mykey: {value}");

view raw redis.cs hosted with

by GitHub

ServiceStack has its own Redis client library, that is compatible with .NET Core, like the rest of ServiceStack.

	var clientsManager = container.Resolve<IRedisClientsManager>();
	using (IRedisClient redis = clientsManager.GetClient())
	{
	var redisTodos = redis.As<Todo>();
	var todo = redisTodos.GetById(1);
	Console.WriteLine($"Need to {todo.Content}.");
	}

view raw servicestackredis.cs hosted with

by GitHub

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.

	var cluster = Cluster.Builder()
	.AddContactPoint("127.0.0.1")
	.Build();
	using (var session = cluster.Connect())
	{
	session.UserDefinedTypes.Define(
	UdtMap.For<Address>()
	.Map(a => a.Street, "street")
	.Map(a => a.City, "city")
	.Map(a => a.ZipCode, "zip_code")
	.Map(a => a.Phones, "phones")
	);
	var query = new SimpleStatement("SELECT id, name, address FROM users where id = ?", userId);
	var rs = await session.ExecuteAsync(query);
	var row = rs.FirstOrDefault();
	if (row != null)
	{
	var userAddress = row.GetValue<Address>("address");
	Console.WriteLine("user lives on {0} Street", userAddress.Street);
	}
	}

view raw cassandra.cs hosted with

by GitHub

CouchBase

CouchBase is an open source document database that is popular in mobile applications. The offical Couchbase client library is compatible with .NET Core.

	using (var bucket = Cluster.OpenBucket())
	{
	var get = bucket.GetDocument<dynamic>(documentId);
	document = get.Document;
	Console.WriteLine($"{document.Id}: {document.Content.name}");
	}

view raw couchbase.cs hosted with

by GitHub

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.

	var albumId = "b08825e2a0303f5352e4840e1300167f";
	var url = $"http://localhost:5984/music/{albumId}";
	var request = WebRequest.Create(url);
	using (var response = await request.GetResponseAsync() as HttpWebResponse)
	{
	if (response.StatusCode != HttpStatusCode.OK) return;
	var json = await new StreamReader(response.GetResponseStream()).ReadToEndAsync();
	var album = JsonConvert.DeserializeObject<Album>(json);
	Console.WriteLine($"{album.Title} by {album.Artist} is a {album.Category} album.");
	}

view raw couchdb.cs hosted with

by GitHub

Neo4j

Neo4j is a graph database, which means that it establishes relationships not between tables but between data nodes, and treats these relationships as first class semantically rich entities that carry data and can be queried on. Readify has a .NET Core-compatible client for Neo4j called Neo4jClient.

	// query for 'What are the movies which employed all of their actors from Agency-A?'
	var results = client.Cypher
	.Match("(agency:Agency)-[:ACQUIRED]->(actor:Person)<-[:EMPLOYED]-(movie:Movie)")
	.Return((agency, actor, movie) => new
	{
	Agency = agency.As<Agency>(),
	Actor = actor.As<Person>(),
	Movie = movie.As<Movie>()
	}).Results;

view raw neo4j.cs hosted with

by GitHub

The full sample can be found in Tugberk Ugurlu’s blog post “Getting Started with Neo4j in .NET with Neo4jClient Library”

RethinkDB

RethinkDB is a document database that can stream real-time result updates to a query. RethinkDb.Driver is a .NET Core driver for RethinkDB that covers 100% of the features of the official Java driver. One should exercise caution for new project however, as the company behind RetinkDB is shutting down.

	var foo = R.Db("mydb").Table("mytable").Get("abc").Run<Foo>(conn);
	foo.Dump();

view raw rethinkdb.cs hosted with

by GitHub

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.

	var indexSearcher = new DirectoryIndexSearcher(new DirectoryInfo(indexPath));
	using (var searchService = new SearchService(indexSearcher))
	{
	var parser = new MultiFieldQueryParser(
	Lucene.Net.Util.Version.LUCENE_48,
	new[] { "Text" },
	new StandardAnalyzer(Lucene.Net.Util.Version.LUCENE_48));

	Query multiQuery = parser.Parse(QueryParser.Escape(query));

	var result = searchService.SearchIndex(multiQuery);
	return new SearchResults
	{
	Documents = result.Results
	.Skip(PageSize*(page - 1))
	.Take(PageSize)
	.Select(d => new SearchResult {
	Url = d.Get("Url"),
	Title = d.Get("Title"),
	Summary = d.Get("Summary")
	}),
	TotalCount = result.Results.Count()
	};
	}

view raw lucene.cs hosted with

by GitHub

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.

.NET Core Data Access

The week in .NET – Mitch Muenster – Stateless

Announcing Entity Framework Core 1.1

ORM

EF Core

Dapper

NPoco

Relational databases

SQL Server

PostgreSQL

MySQL

SQLite

Firebird

NoSQL

Azure DocumentDB

MongoDB

RavenDB

Redis

Cassandra

CouchBase

CouchDB

Neo4j

RethinkDB

YesSql

Lucene.NET

What about OLE DB?

Keeping track

Author

0 comments

ORM Copy link

EF Core Copy link

Dapper Copy link

NPoco Copy link

Relational databases Copy link

SQL Server Copy link

PostgreSQL Copy link

MySQL Copy link

SQLite Copy link

Firebird Copy link

NoSQL Copy link

Azure DocumentDB Copy link

MongoDB Copy link

RavenDB Copy link

Redis Copy link

Cassandra Copy link

CouchBase Copy link

CouchDB Copy link

Neo4j Copy link

RethinkDB Copy link

YesSql Copy link

Lucene.NET Copy link

What about OLE DB? Copy link

Keeping track Copy link

Author

0 comments

Stay informed

ORM

EF Core

Dapper

NPoco

Relational databases

SQL Server

PostgreSQL

MySQL

SQLite

Firebird

NoSQL

Azure DocumentDB

MongoDB

RavenDB

Redis

Cassandra

CouchBase

CouchDB

Neo4j

RethinkDB

YesSql

Lucene.NET

What about OLE DB?

Keeping track