ORM for .NET 5 Records
Tags: net5, orm, c, dotnet
Published: 2020-11-21 22:42:38

---
title: ORM for .NET 5 Records
published: true
description: 
tags: NET5, ORM, C#, dotnet
---

Object-Relational Mapping (ORM, O/R mapping tool) is a tool for converting data between object-oriented languages and relational databases. When programming using C#, an ORM tool helps to reduce the tedious field to field mapping work. The most famous one is the Entity Framework.

## DragonCore ORM

Entity Framework and other ORM tools usually have a load of features, which makes them complicated and hard to control. I feel I need a very simple ORM tool that just can load data from the SQL server into C# objects and execute stored procedures. Therefore, I have created a simple ORM tool, call [Dragon](https://www.nuget.org/packages/dragon) since .NET framework 1.1 and used it in many production projects. It has been updated to leverage new C# features along with the major C# version releases, such as generics, auto-properties, anonymous types, tuple destruction, and etc.

[DragonCore](https://www.nuget.org/packages/dragoncore) is a port of Dragon to .NET 5 to support the latest and greatest C# 9 features, in particular the record types. 

To use the DragonCore, install the NuGet package.

```
dotnet add package DragonCore
```

## From Class To Record

The recent .NET 5 release announced C# 9 [record type](https://docs.microsoft.com/en-us/dotnet/csharp/whats-new/csharp-9).

> C# 9.0 introduces record types, which are a reference type that provides synthesized methods to provide value semantics for equality. Records are immutable by default.

Before the .NET 5 release, we use regular C# class.

```C#
public class User
{
  public int UserId { get; set; }
  public string Email { get; set; }
}
```
Now, we can use C# 9 _record type_.

```C#
public record User
{
  public int UserId { get; init; }
  public string Email { get; init; }
}
```
Although changing the keywords from _class_ to _record_ and from _set_ to _init_ does not look like big changes, we actually have implemented the concept of _immutable reference types_. No need to understand the in-depth theory, you only need to know the _immutable reference types_ is better than regular _class_ for loading data from the database and passing data to other layers of applications. Of course, you can review all the benefits from the [record type document](https://docs.microsoft.com/en-us/dotnet/csharp/whats-new/csharp-9#record-types). 

There is a short-hand syntax to create a _record type_, which is also known as a [_positional record_](https://docs.microsoft.com/en-us/dotnet/csharp/tutorials/exploration/records). We can re-write the above _User_ record as _positional record_.

```C#
public record User(int UserId, string Email);
```

The C# compiler creates the constructor, properties, and destructors out of the _positional record_. DragonCore supports the _positional record_ syntax.


Strongly Typed Mapping
======================
DragonCore can execute SQL queries and create C# strongly typed objects.

```C#
Database database = Database.Open(/*connection string*/);

public record User(int UserId, string Email);

var users = database.Query<User>(@"select UserId, Email from UserProfile");
```

The class/record property names don't need to match the table/view column names. The class/record property names match the field names in the SQL select statements. Therefore, we can use SQL select to define column alias to match the property names.

```C#
public record User(int Id, string Email);

var users = database.Query<User>(@"select UserId as Id, Email from UserProfile");
```

Also, we can have more class properties than the columns returned from the query. Extra columns are ignored, but the query will still return all the columns from the SQL server. Be aware to always be specific on the columns in production code and not to use "SELECT *".   

The _Query_ method also supports calling stored procedures for creating objects. 

```C# 
var (users, members, roles) = database.Query<TestUser, TestMemberShip, TestUserRole>("GetAllUserInfo");
```

There is also the _QueryValue_ method that creates a SQL command and runs ExecuteScalar to return a single value from the SQL server.

Query Parameters
================
Query parameters are passed in as anonymous types. 

```C#
var users = database.Query<User>(@"
    select UserId, Email from UserProfile 
    where UserId=@UserId", new { UserId = 2 });
```
This allows you to name parameters and pass data through the parameters easily.
	 

Multiple Recordsets
===================
The _Query_ method supports up to 3 recordsets. The result objects are returned in a Tuple. We can use [_tuple destruction_](https://docs.microsoft.com/en-us/dotnet/csharp/deconstruct) to unpack variables from the tuple.

```C#
var (users, members, roles) = database.Query<TestUser, TestMemberShip, TestUserRole>(@"
  select * from dbo.UserProfile
  Select * from dbo.webpages_Membership
  select * from dbo.webpages_Roles");
```

Execute Stored Procedure
========================
The _Execute_ method is for executing stored procedures. It creates a SQL command and runs ExecuteNonQuery.

Manage Transactions
===================

Use the [TransactionScope](https://docs.microsoft.com/en-us/dotnet/api/system.transactions.transactionscope?view=net-5.0) guaranteeing that database queries can commit or rollback as a single unit of work.

```C#
try
{
    Database database = Database.Open(/*connection string*/);
    using (TransactionScope scope = new TransactionScope())
    {
        database.execute(...);
        database.execute(...);
        database.execute(...);

        // The Complete method commits the transaction. If an exception has been thrown, the transaction is rolled back.
        scope.Complete();
    }
}
catch (TransactionAbortedException ex)
{
    writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message);
}
```

Conclusion
==========
I only want a simple tool to map SQL recordsets to C# objects. No [repository pattern](https://docs.microsoft.com/en-us/dotnet/architecture/microservices/microservice-ddd-cqrs-patterns/infrastructure-persistence-layer-design#:~:text=of%20Work%20patterns.-,The%20Repository%20pattern,from%20the%20domain%20model%20layer.) or [unit of work pattern](https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions/getting-started-with-ef-5-using-mvc-4/implementing-the-repository-and-unit-of-work-patterns-in-an-asp-net-mvc-application). I can handle transactions with stored procedures or [TransactionScope](https://docs.microsoft.com/en-us/dotnet/api/system.transactions.transactionscope?view=net-5.0). If you like me, give DragonCore ORM a try.

DragonCore ORM has only one class, called Database, which has four methods: _Open_, _Execute_, _QueryValue_, and _Query_.

* **Open**: opens database connection using a connection string.
* **Execute**: creates a command and runs ExecuteNonQuery.
* **QueryValue**: creates a command and runs ExecuteScalar.
* **Query**: creates a command with SQL statement(s) or stored procedure name and creates objects. The Query method supports up to 3 multiple recordsets.


Source Code
===========
https://github.com/yysun/dragon-core

Pull Requests are welcome. Have fun coding.

(C) Copyright 2020, Yiyi Sun