An Open Source ORM Framework - Light.Data

Introduction

Light.Data is a lightweight ORM framework which is based on dotnet standard 2.0, through to the entity model class Attribute or configuration files relate data table. Use core class DataContext was carried out on the table to CRUD operation.

PM> Install-Package Light.Data

Supported Database

Database	Introduce
SqlServer	Need to install nuget install Light.Data.Mssql library, Support SqlServer 2008 or above
Mysql	Need to install nuget install Light.Data.Mysql library, Support Mysql5.5 or above
Postgre	Need to install nuget install Light.Data.Postgre library, Support PostgreSQL9.3 or above

• Document: https://aquilahkj.github.io/Light.Data.Site/
• Github: https://github.com/aquilahkj/Light.Data2

How to Use

Database Configuration

{
  "lightData": {
      "connections": [
          {
             "name": "mssql_db",
             "connectionString": "...",
             "providerName": "Light.Data.Mssql.MssqlProvider, Light.Data.Mssql"
          },
          {
             "name": "mysql_db",
             "connectionString": "...",
             "providerName": "Light.Data.Mysql.MysqlProvider, Light.Data.Mysql"
           }
       ]
    }
}

Method of Use

// direct use
DataContext context = new DataContext("mssql");

// create subclass
public class MyDataContext : DataContext
{
    public MyDataContext() : base("mssql")
    {

    }
}

// create subclass with options
public class MyDataContext : DataContext
{
    public MyDataContext(DataContextOptions<MyDataContext> options) : base(options)
    {

    }
}

// direct config connect string and params （IServiceCollection）
service.AddDataContext<MyDataContext>(builder => {
    builder.UseMssql(connectionString);
    builder.SetTimeout(2000);
    builder.SetVersion("11.0");
}, ServiceLifetime.Transient);

// use default configuration file to config （IServiceCollection）
service.AddDataContext<MyDataContext>(DataContextConfiguration.Global, config => {
       config.ConfigName = "mssql";
}, ServiceLifetime.Transient);

Object Mapping

[DataTable("Te_User", IsEntityTable = true)]
public class TeUser
{
    /// <summary>
    /// Id
    /// </summary>
    /// <value></value>
    [DataField("Id", IsIdentity = true, IsPrimaryKey = true)]
    public int Id
    {
        get;
        set;
    }

    /// <summary>
    /// Account
    /// </summary>
    /// <value></value>
    [DataField("Account")]
    public string Account
    {
        get;
        set;
    }

    /// <summary>
    /// Telephone
    /// </summary>
    /// <value></value>
    [DataField("Telephone", IsNullable = true)]
    public string Telephone
    {
        get;
        set;
    }
    ....
}

Child Table Mapping

In the derived class TeUserAndExtend, add a public property Extend whose type is TeUserExtend, and add Attribute RelationFieldAttribute. In query TeUserAndExtend, the associated TeUserExtend data will be also detect, and also support One-to-Many:

[DataTable("Te_UserExtend", IsEntityTable = true)]
public class TeUserExtend
{
    [DataField("Id", IsIdentity = true, IsPrimaryKey = true)]
    public int Id
    {
        get;
        set;
    }

    [DataField("MainId")]
    public int MainId
    {
        get;
        set;
    }
    
    [DataField("Data", IsNullable = true)]
    public string Data
    {
        get;
        set;
    }
}

public class TeUserAndExtend : TeUser
{
    [RelationField("Id", "MainId")]
    public TeUserExtend Extend
    {
        get;
        set;
    }
}

Basic Operation

• Basic CRUD
• Batch CUD
• Supports transaction processing
• Supports data fields default value and automatic timestamp
• Supports data fields read-write control
• The Query results specify class or anonymous class output
• The query results insert into the data table directly

var context = new DataContext();
// query single data
var item = context.Query<TeUser>().Where(x => x.Id == 10).First();
// query collection datas
var list = context.Query<TeUser>().Where(x => x.Id > 10).ToList();
// create date
var user = new TeUser() {
  Account = "foo",
  Password = "bar"
};
context.Insert(user);
// update data
user.Password = "bar1";
context.Update(user);
// delete data
context.Delete(user); 

Data Aggregation

• Single-Column data directly aggregate
• Multi-Column data grouped aggregate
• Format the grouped field
• Aggregate data insert into the data table directly

// basic
var list = context.Query<TeUser> ()
         .Where (x => x.Id >= 5)
         .GroupBy (x => new LevelIdAgg () {
           LevelId = x.LevelId,
           Data = Function.Count ()
          })
         .ToList ();

// date format
var list = context.Query<TeUser> ()
         .GroupBy (x => new RegDateFormatAgg () {
           RegDateFormat = x.RegTime.ToString("yyyy-MM-dd"),
           Data = Function.Count ()
          })
         .ToList ();

Join-Table Query

• Multi-Table join, Support inner join, left join and right join
• Support query results and aggregate data join together
• Join query results specify class or anonymous class output
• Join query results insert into the data table directly

// inner join
var join = context.Query<TeUser> ()
                  .Join<TeUserExtend>((x,y) => x.Id == y.Id);

// aggregate data join entity table             
var join = context.Query<TeMainTable>()
                  .GroupBy(x => new {
                      MId = x.MId,
                      Count = Function.Count(),
                   })
                  .Join<TeSubTable>((x, y) => x.MId == y.Id);

Execute SQL

• Use SQL and Stored Procedures directly
• Supports object parameters
• Query results specify class or anonymous class output
• Stored Procedures support use output parameters

// basic parameter
var sql = "update Te_User set NickName=@P2 where Id=@P1";
var ps = new DataParameter[2];
ps[0] = new DataParameter("P1", 5);
ps[1] = new DataParameter("P2", "abc");
var executor = context.CreateSqlStringExecutor(sql, ps);
var ret = executor.ExecuteNonQuery();

// object parameter
var sql = "update Te_User set NickName={nickname} where Id={id}";
var executor = context.CreateSqlStringExecutor(sql, new { nickname = "abc", id = 5 });
var ret = executor.ExecuteNonQuery();

Unit Test

The project uses xUnit to do unit testing, the test code address is: https://github.com/aquilahkj/Light.Data2/tree/master/test

Each database has more than 300 sets of tests with 1,000 cases, covering most of the code.

Performance Test

At present, only use EF Core on the same computer Docker SQL Server 2017 for the Linux do simple CRUD performance tests the code address https://github.com/aquilahkj/OrmTest.

1000 times of CUD and single data query.

A total of 5 rounds, each round of 1000 times CUD and 1000 data query.

EF test result:

Light.Data test result:

From the comparison, the query performance is almost the same, the insert performance Light.Data is slightly better, and the batch update is also slightly better.

This article is only a brief introduction, and you can refer to the documentation and test cases for specifics on how to use.