ORM for .NET 5 Records

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

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.

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

Now, we can use C# 9 record type.

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.

There is a short-hand syntax to create a record type, which is also known as a positional record. We can re-write the above User record as positional record.

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.

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.

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.

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.

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 to unpack variables from the tuple.

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 guaranteeing that database queries can commit or rollback as a single unit of work.

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 or unit of work pattern. I can handle transactions with stored procedures or TransactionScope. 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