Share your own written dapper-based lightweight ORM Framework ~

1. Description

This project is a lightweight, Dapper-based ORM framework, developed using. NET Standard 2.0, that includes a basic crud and a way to do something based on an expression that is currently only for a single table and does not include multiple table join operations.
Github:https://github.com/iamoldli/netsql

2, the use of method 2.2, installation

Install-Package NetSql

2.2. Create an entity

Create an Article entity class, InheritEntityBase

public class Article : EntityBase{    [Column("Title")]    public string Title1 { get; set; }    public string Summary { get; set; }    public string Body { get; set; }    public Category Category { get; set; }    public int ReadCount { get; set; }    public bool IsDeleted { get; set; }    public DateTime CreatedTime { get; set; }}public enum Category{    Blog,    Movie}

EntityBaseis a well-defined entity base class that contains a generic primary key identifier, which is type Int by default, or a long or string type

 public class Article : EntityBase<string>

2.3. Defining the database context (DbContext)

Database context I am a simulation of the EF, IDbContextOptions is the database context configuration item interface, the default contains SQL Server implementation DbContextOptions , if you are using MYSQL or SQLite, need to install the corresponding expansion pack

Install-Package NetSql.MySql //MySql

Install-Package NetSql.SQLite //SQLite

Here I define a BlogDbContext context that contains a Articles set of data

public class BlogDbContext : DbContext{    public BlogDbContext(IDbContextOptions options) : base(options)    {    }    public IDbSet<Article> Articles { get; set; }}

2.4. Data set (DbSet) using instructions 2.4.1, creating a DB context instance

private readonly BlogDbContext _dbContext;private readonly IDbSet<Article> _dbSet;public DbSetTests(){    _dbContext = new BlogDbContext(new SQLiteDbContextOptions("Filename=./Database/Test.db"));    _dbSet = _dbContext.Set<Article>();    //预热    _dbSet.Find().First();}

2.4.2, inserting

[Fact]public async void InsertTest(){    var article = new Article    {        Title1 = "test",        Category = Category.Blog,        Summary = "这是一篇测试文章",        Body = "这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章",        ReadCount = 10,        IsDeleted = true,        CreatedTime = DateTime.Now    };    await _dbSet.InsertAsync(article);    Assert.True(article.Id > 0);}

2.4.3, BULK Insert

[Fact]public void BatchInsertTest(){    var sw = new Stopwatch();    sw.Start();    var tran = _dbContext.BeginTransaction();    for (var i = 0; i < 10000; i++)    {        var article = new Article        {            Title1 = "test" + i,            Category = i % 3 == 1 ? Category.Blog : Category.Movie,            Summary = "这是一篇测试文章",            Body = "这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章",            ReadCount = 10,            IsDeleted = i % 2 == 0,            CreatedTime = DateTime.Now        };        _dbSet.InsertAsync(article, tran);    }    tran.Commit();    sw.Stop();    var s = sw.ElapsedMilliseconds;    Assert.True(s > 0);}

2.4.4, delete based on primary key

[Fact]public void DeleteTest(){    var b = _dbSet.DeleteAsync(3).Result;    Assert.True(b);}

2.4.5, deleting by expression

[Fact]public async void DeleteWhereTest(){    var b = await _dbSet.Find(m => m.Id > 10).Delete();    Assert.True(b);}[Fact]public async void DeleteWhereTest(){    var b = await _dbSet.Find(m => m.Id > 10)        .Where(m => m.CreatedTime > DateTime.Now).Delete();    Assert.True(b);}

2.4.6, modification

[Fact]public async void UpdateTest(){    var article = await _dbSet.Find().First();    article.Title1 = "修改测试";    var b = await _dbSet.UpdateAsync(article);    Assert.True(b);}

2.4.7, modifying an entity part property based on an expression

[Fact]public async void UpdateWhereTest(){    var b = await _dbSet.Find(m => m.Id == 1000).Update(m => new Article    {        Title1 = "hahahaah",        ReadCount = 1000    });    Assert.True(b);}

2.4.8, querying a single entity based on the primary key

[Fact]public void GetTest(){    var article = _dbSet.GetAsync(100).Result;    Assert.NotNull(article);}

2.4.9, querying a single data based on an expression

The method returns the first piece of data in the result set

[Fact]public async void GetWehreTest(){    var article = await _dbSet.Find(m => m.Id > 100).First();    Assert.NotNull(article);}

2.4.10, using an expression

IDbSetFindmethod returns an object that INetSqlQueryable mimics the EF inside IQueryable , though somewhat nondescript, but is designed in the way that suits you.

INetSqlQueryableCurrently, the following methods are included:

• Where: For adding filter conditions

var query =  _dbSet.Find().Where(m => m.Id > 1);

• WhereIf: Add filter criteria according to specified criteria

var query = _dbSet.Find().WhereIf(id > 1, m => m.Id > 200);

• OrderBy: Used to add collations

var query = _dbSet.Find(m => m.Id > 200 && m.Id < 1000).OrderBy(m => m.Id, SortType.Desc);

• Limit: This method contains two parameters skip and take identifies skipping skip bar data, taking take bar data

var query = _dbSet.Find(m => m.Id > 100 && m.Id < 120).Limit(5, 10);

• Select: Select the columns to return

var query = _dbSet.Find().Select(m => new { m.Id, m.Title1 }).Limit(0, 10);

All of these methods are used for construction INetSqlQueryable , and the following methods are executed:

• Max: Query Maximum Value

var maxReadCount = _dbSet.Find().Max(m => m.ReadCount).Result;

• Min: Query Minimum value

var maxReadCount = _dbSet.Find().Min(m => m.ReadCount).Result;

• Count: Number of queries

var count = _dbSet.Find(m => m.Id > 1000).Count().Result;

• Exists: Determine if there is

var b = _dbSet.Find(m => m.Id > 1000).Exists().Result;

• First: Gets the first piece of data

var article = _dbSet.Find(m => m.Id > 100 && m.Id < 120).First().Result;

• Delete: Delete Data

var b = _dbSet.Find(m => m.Id > 1000).Delete().Result;

• Update: Update Data

var b = await _dbSet.Find(m => m.Id == 1000).Update(m => new Article{    Title1 = "hahahaah",    ReadCount = 1000});

• ToList: Get result set

var list = await _dbSet.Find(m => m.Id > 100 && m.Id < 120).ToList();

3. Attribute table aliases and column names

[Table("blog_article")]public class Article : EntityBase{    [Column("Title")]    public string Title1 { get; set; }    public string Summary { get; set; }    public string Body { get; set; }    public Category Category { get; set; }    public int ReadCount { get; set; }    public bool IsDeleted { get; set; }    public DateTime CreatedTime { get; set; }}

Specify primary key

You can KeyAttribute specify a field to be the primary key by

4. Generic Warehousing (Repository)

At ordinary times, the use of pseudo-DDD is more, so the framework provides a generic warehousing interface IRepository and an abstract implementationRepositoryAbstract

<summary>///to determine the presence///</summary>///<param name= "where" ></param>///<param name= " Transaction "></param>///<returns></returns>Task<bool> Existsasync (expression<func <tentity, bool>> where, idbtransaction transaction = NULL);///<summary>///Add//</summary>/// <param name= "Entity" > Entities </param>///<param name= "Transaction" > Transaction </param>///<returns> </returns>Task<bool> Addasync (TEntity entity, idbtransaction transaction = NULL);///<summary>/// Batch new///</summary>///<param name= "list" ></param>///<param name= "Transaction" ></param >///<returns></returns>Task<bool> Addasync (list<tentity> List, idbtransaction transaction = NULL);///<summary>///Delete//</summary>///<param name= "id" ></param>///< param name= "Transaction" ></param>///<returns></returns>Task<bool> DEleteasync (Dynamic ID, idbtransaction transaction = NULL);///<summary>///Update///</summary>///<param Name= "entity" > Entity </param>///<param name= "Transaction" > Transaction </param>///<returns></ Returns>task<bool> Updateasync (TEntity entity, idbtransaction transaction = NULL);///<summary>/// Query by primary key///</summary>///<param name= "id" ></param>///<param name= "Transaction" ></param >///<returns></returns>Task<TEntity> getasync (Dynamic ID, idbtransaction transaction = NULL);// /<summary>///query a single record based on an expression//</summary>///<param name= "where" ></param>///<param name= " Transaction "></param>///<returns></returns>Task<TEntity> Getasync (expression<func <TEntity,bool>> where, idbtransaction transaction = NULL);///<summary>///page Search///</summary>///& Lt;param name= "Paging" > Pagination </param>///<param name= "where" > Filter conditions &LT;/PAram>///<param name= "Transaction" > Transaction </param>///<returns></returns>Task<List< Tentity>> Paginationasync (Paging Paging = null, expression<func<tentity, bool>> where = null, IDbTransaction transaction = null);

RepositoryAbstractContains the data set for the entity IDbSet and the upper bound of the dataIDbContext

protected readonly IDbSet<TEntity> Db;protected readonly IDbContext DbContext;protected RepositoryAbstract(IDbContext dbContext){    DbContext = dbContext;    Db = dbContext.Set<TEntity>();}

For transactions, it is recommended that you use work cellsIUnitOfWork

public interface IUnitOfWork{    /// <summary>    /// 打开一个事务    /// </summary>    /// <returns></returns>    IDbTransaction BeginTransaction();    /// <summary>    /// 提交    /// </summary>    /// <returns></returns>    void Commit();    /// <summary>    /// 回滚    /// </summary>    void Rollback();}

The project already contains an implementationUnitOfWork

6, Warehousing Use method 6.1, the definition of warehousing

public interface IArticleRepository : IRepository<Article>{}

6.2. Create Warehousing instances

private readonly IArticleRepository _repository;public RepositoryTest(){    var dbContext = new BlogDbContext(new SQLiteDbContextOptions("Filename=./Database/Test.db"));    _repository = new ArticleRepository(dbContext);}

6.3. New

[Fact]public async void AddTest(){    var article = new Article    {        Title1 = "test",        Category = Category.Blog,        Summary = "这是一篇测试文章",        Body = "这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章",        ReadCount = 10,        IsDeleted = true,        CreatedTime = DateTime.Now    };    await _repository.AddAsync(article);    Assert.True(article.Id > 0);}

6.4, Batch Increase

[Fact]public void BatchInsertTest(){    var list = new List<Article>();    for (var i = 0; i < 10000; i++)    {        var article = new Article        {            Title1 = "test" + i,            Category = i % 3 == 1 ? Category.Blog : Category.Movie,            Summary = "这是一篇测试文章",            Body = "这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章这是一篇测试文章",            ReadCount = 10,            IsDeleted = i % 2 == 0,            CreatedTime = DateTime.Now        };        list.Add(article);    }    var sw = new Stopwatch();    sw.Start();    _repository.AddAsync(list);    sw.Stop();    var s = sw.ElapsedMilliseconds;    Assert.True(s > 0);}

6.5. Delete

[Fact]public async void DeleteTest(){    var b = await _repository.DeleteAsync(2);    Assert.True(b);}

6.6. Modification

[Fact]public async void UpdateTest(){    var article = await _repository.GetAsync(2);    article.Title1 = "修改测试";    var b = await _repository.UpdateAsync(article);    Assert.True(b);}

6.7, paging query

[Fact]public async void PaginationTest(){    var paging = new Paging(1, 20);    var list = await _repository.PaginationAsync(paging, m => m.Id > 1000);    Assert.True(paging.TotalCount > 0);}

Not to be continued ~

Share your own written dapper-based lightweight ORM Framework ~