.netcore下使用Chloe.ORM框架

一、引言

Chloe.ORM是一款轻量、高效的类EF数据库访问框架(ORM)。查询接口借鉴 Linq(不支持 Linq)，之前使用过EF很容易上手。Chloe目前支持四种主流数据库：SqlServer、MySQL、Oracle、Sqlite。

二、项目搭建

1.搭建项目架构

Test.Web：.netcore webapi项目

Test.Model：.netcore类库项目，存放api接口输入输出实体类、以及数据库表映射的实体类型

Test.DB：.netcore类库项目，实现对数据库访问的封装

在Test.Web添加对Test.DB、Test.Model项目的引用

2.Nuget安装Chloe

(1)在Test.DB项目下使用nuget安装Chloe和Chloe.SqlServer

(2)在Test.Model子项目下使用Nuget安装Chloe

3.在Test.DB子项目下新建基础操作类

using Chloe;
using System;
using System.Collections.Generic;
using System.Linq.Expressions;

namespace Test.DB
{
    public class DB
    {
        private IDbContext NewDB()
        {
            var sqlserver_conn_str = "database=Archives;server=.;User=test;password=123;";
            return new Chloe.SqlServer.MsSqlContext(sqlserver_conn_str);
        }
        #region 基础操作
        /// <summary>
        /// 新增,如果一个实体存在自增列，会自动将自增列设置到相应的属性上
        /// </summary>
        /// <param name="entity">实体</param>
        public T Insert<T>(T entity) where T : class, new()
        {
            using (var db = NewDB())
            {
                return db.Insert(entity);
            }
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="entity">实体</param>
        public int Update<T>(T entity) where T : class, new()
        {
            using (var db = NewDB())
            {
                return db.Update(entity);
            }
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="expWhere"></param>
        public int Delete<T>(Expression<Func<T, bool>> expWhere) where T : class, new()
        {
            using (var db = NewDB())
            {
                return db.Delete<T>(expWhere);
            }
        }
        /// <summary>
        /// 获取一个实体
        /// </summary>
        /// <param name="expWhere">查询条件</param>
        /// <returns></returns>
        public T Find<T>(Expression<Func<T, bool>> expWhere) where T : class, new()
        {
            using (var db = NewDB())
            {
                IQuery<T> q = db.Query<T>();
                var entity = q.Where(expWhere).FirstOrDefault();
                return entity;
            }
        }
        /// <summary>
        /// 单表查询
        /// </summary>
        /// <param name="expWhere">查询条件,可空</param>
        /// <param name="expOrder">排序字段,可空</param>
        /// <param name="isAsc">是否升序，expOrder不为空时有效</param>
        /// <returns></returns>
        public IEnumerable<T> FindList<T>(Expression<Func<T, bool>> expWhere = null, Expression<Func<T, object>> expOrder = null, bool isAsc = false) where T : class, new()
        {
            using (var db = NewDB())
            {
                IQuery<T> q = db.Query<T>();
                if (expWhere != null)
                {
                    q = q.Where(expWhere);
                }
                if (expOrder != null)
                {
                    if (isAsc)
                    {
                        q = q.OrderBy(expOrder);
                    }
                    else
                    {
                        q = q.OrderByDesc(expOrder);
                    }
                }
                var list = q.ToList();
                return list;
            }
        }
        /// <summary>
        /// 根据sql语句查询
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public IEnumerable<T> DtoQuery<T>(string strSql, params DbParam[] parameters) where T : class, new()
        {
            using (IDbContext db = NewDB())
            {
                return db.SqlQuery<T>(strSql, parameters);
            }
        }
        #endregion
    }
}

4.在Test.Model下添加数据库表UserConfig映射的实体类

using System;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using Chloe.Annotations;
using Chloe.Entity;

namespace Test.Model
{
    /// <summary>
    /// UserConfig(用户配置表)实体类
    ///</summary>
    [Table("UserConfig")]
    public class UserConfig
    {
        #region 获取/设置 字段值
        /// <summary>
        /// 唯一标识
        /// </summary>
        /// <returns></returns>
        [Column(IsPrimaryKey = true)]
        [DisplayName("唯一标识")]
        public string Id { get; set; }
        /// <summary>
        /// 用户ID
        /// </summary>
        /// <returns></returns>
        [DisplayName("用户ID")]
        public string UserId { get; set; }
        /// <summary>
        /// 配置key
        /// </summary>
        /// <returns></returns>
        [DisplayName("配置key")]
        public string ConfigKey { get; set; }
        /// <summary>
        /// 配置value
        /// </summary>
        /// <returns></returns>
        [DisplayName("配置value")]
        public string ConfigValue { get; set; }
        /// <summary>
        /// 项目编码
        /// </summary>
        /// <returns></returns>
        [DisplayName("项目编码")]
        public string ProjectCode { get; set; }
        /// <summary>
        /// 备注
        /// </summary>
        /// <returns></returns>
        [DisplayName("备注")]
        public string Memo { get; set; }
        /// <summary>
        /// 创建时间
        /// </summary>
        /// <returns></returns>
        [DisplayName("创建时间")]
        public DateTime? CreateTime { get; set; }
        /// <summary>
        /// 修改时间
        /// </summary>
        /// <returns></returns>
        [DisplayName("修改时间")]
        public DateTime? UpdateTime { get; set; }
        #endregion
    }
}

5.在Test.Web中添加控制器调用

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Test.Model;
using Test.Model.InputParam;
using Test.Model.OutputParam;

namespace Test.Web.Controllers
{
    /// <summary>
    /// Home控制器
    /// </summary>
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class HomeController : ControllerBase
    {
        /// <summary>
        /// 查询用户
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [HttpGet]
        public IEnumerable<UserConfig> UserSearch()
        {
            var list = new Test.DB.DB().FindList<UserConfig>();
            return list;
        }

    }
}

因为Chloe没有将数据库表自动生成为Model实体类的工具，自行编写一个代码生成器是个比较好的选择。

值的一提的是，==和Equals是有区别的，这个之前在EF中就有发现：

/* 使用此方法与双等号（==）的区别是：a => Sql.Equals(a.Name, a.XName) 会被翻译成 a.Name == a.XName，
       而 a => a.Name == a.XName 则会被翻译成 a.Name == a.XName or (a.Name is null and a.XName is null) */
    public static bool Equals<T>(T value1, T value2);

Chole官方网站文档参考：https://github.com/shuxinqin/Chloe