FreeSql

官网  
实体特性 
Ado 它包括所有对 SQL 操作的封装，提供 ExecuteReader、ExecuteDataSet、ExecuteDataTable、ExecuteNonQuery、ExecuteScalar 等方法，使用起来和传统 SqlHelper 一样。

1、安装包

dotnet add package FreeSql
dotnet add package FreeSql.Provider.SqlServer

-- 仓储
dotnet add package FreeSql.DbContext

2、Program.cs 文件

using FreeSql;
using Microsoft.OpenApi.Models;
using System.Configuration;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();

#region FreeSql

Func<IServiceProvider, IFreeSql> fsqlFactory = r =>
{
    IFreeSql fsql = new FreeSqlBuilder()
    .UseConnectionString(DataType.SqlServer, builder.Configuration.GetConnectionString("SqlServerDefault"))
    //.UseConnectionString(FreeSql.DataType.SqlServer, r.GetService<IConfiguration>()["ConnectionStrings:Default"])
    .UseMonitorCommand(cmd => Console.WriteLine($"Sql：{cmd.CommandText}"))//监听SQL语句
    //.UseAutoSyncStructure(true) //自动同步实体结构到数据库，FreeSql不会扫描程序集，只有CRUD时才会生成表。
    .UseLazyLoading(false)
    .UseNoneCommandParameter(true)
    .Build();
    return fsql;
};
/// 仓储
builder.Services.AddFreeRepository();
builder.Services.AddScoped<UnitOfWorkManager>();

builder.Services.AddSingleton(fsqlFactory);

#endregion

var app = builder.Build();

// Configure the HTTP request pipeline.

app.UseAuthorization();

//在项目启动时，从容器中获取IFreeSql实例，并执行一些操作：同步表，种子数据,FluentAPI等
using (IServiceScope serviceScope = app.Services.CreateScope())
{
    var fsql = serviceScope.ServiceProvider.GetRequiredService<IFreeSql>();
    //fsql.CodeFirst.SyncStructure(typeof(Topic));//Topic 为要同步的实体类//同步实体类到数据库
}

app.MapControllers();

app.Run();

3、appsettings.json 文件

{
  "ConnectionStrings": {
    "SqlServerDefault": "data source=.;initial catalog=dbTest;uid=sa;password=000000;TrustServerCertificate=True;",
    "Default": "Data Source=localhost;Port=3306;Database=dbTest;uid=root;pwd=1q2w3E*;charset=utf8mb4;Allow User Variables=true;AllowLoadLocalInfile=true;MinimumPoolSize=50;MaximumPoolSize=1000",
    "DbType": "MySql"
  },
  "AllowedHosts": "*"
}

4、FreeSqlController.cs 文件

using CodeFirst.Entity;
using Microsoft.AspNetCore.Mvc;

namespace Trial.WebAPI.Controllers
{
    [Route("api/FreeSql/[action]")]
    [ApiController]
    public class FreeSqlController : ControllerBase
    {
        private readonly IFreeSql _fsql;

        public FreeSqlController(IFreeSql freeSql)
        {
            _fsql = freeSql;
        }

        /// <summary>
        /// 单条插入
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public IActionResult Insert()
        {
            var model = new Transfer_Amount_Relation_copy2_FreeSql
            {
                UserName = "UserName",
                ActualPaymentFee = 100,
                Recipient = "Recipient",
                BankCardNumber = "BankCardNumber",
                IDNumber = "IDNumber",
                PaymentTime = DateTime.Now,
                OrderNo = "OrderNo",
                InputDetailId = 1,
                BigAmountId = 1,
                UsageAmount = 10
            };

            /// 返回即将执行的 SQL 语句
            string sqlString = _fsql.Insert(model).ToSql();

            /// 返回插入后的记录
            List<Transfer_Amount_Relation_copy2_FreeSql> t1 = _fsql.Insert(model).ExecuteInserted();

            /// 返回影响的行数
            int t2 = _fsql.Insert(model).ExecuteAffrows();

            /// 返回自增
            /// 表有自增列，插入数据后应该要返回 id
            long id = _fsql.Insert(model).ExecuteIdentity();

            return Ok(new { data = model });
        }

        /// <summary>
        /// 批量插入
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public IActionResult BulkCopy()
        {
            var items = new List<Transfer_Amount_Relation_copy2_FreeSql>();
            for (int i = 0; i < 10; i++)
            {
                items.Add(new Transfer_Amount_Relation_copy2_FreeSql
                {
                    UserName = "UserName" + i.ToString(),
                    ActualPaymentFee = 100,
                    Recipient = "Recipient" + i.ToString(),
                    BankCardNumber = "BankCardNumber" + i.ToString(),
                    IDNumber = "IDNumber" + i.ToString(),
                    PaymentTime = DateTime.Now,
                    OrderNo = "OrderNo",
                    InputDetailId = 1,
                    BigAmountId = 1,
                    UsageAmount = 10
                });
            }

            /// 批量插入 Affrows
            int t = _fsql.Insert(items).ExecuteAffrows();

            /// 批量插入 SqlBulkCopy
            _fsql.Insert(items).ExecuteSqlBulkCopy();

            return Ok();
        }

        [HttpGet]
        public IActionResult SelectTest()
        {
            // 如果查询结果为空，ToOne方法会返回null，而不是抛出异常‌
            var toOne = _fsql.Select<Transfer_Amount_Relation_copy2_FreeSql>().Where(u => u.Id == 1000).ToOne();

            // 如果查询结果为空，First方法会抛出异常(经过测试，不会抛异常)
            var first = _fsql.Select<Transfer_Amount_Relation_copy2_FreeSql>().Where(u => u.Id == 1000).First();

            return Ok(new { data = toOne });
        }

        [HttpGet]
        public IActionResult PageTest()
        {
            long total = 0;
            decimal actualPaymentFee = 100;

            #region 分页1
            var page1 = _fsql.Select<Transfer_Amount_Relation_copy2_FreeSql>()
                .WhereIf(actualPaymentFee > 0, x => x.ActualPaymentFee > 0)
                .OrderBy(b => b.Id)
                .Skip(0)
                .Limit(10) //第100行-110行的记录
                .ToList();
            #endregion

            #region 分页2
            var page2 = _fsql.Select<Transfer_Amount_Relation_copy2_FreeSql>()
                .Where(u => u.ActualPaymentFee > 0)
                .OrderBy(u => u.ActualPaymentFee)
                .Count(out total) //总记录数量
                .Page(1, 20)
                .ToList();
            #endregion

            #region 分页3
            /// 数据量大一般不建议查 Count/CountAsync，而应该采用流式分页（上一页、下一页、不返回总数量）

            total = 0;
            var page3 = _fsql.Select<Transfer_Amount_Relation_copy2_FreeSql>()
                .Where(u => u.ActualPaymentFee > 0)
                .OrderBy(u => u.ActualPaymentFee);

            total = page3.Count();

            var list = page3.Page(1, 30).ToList();
            #endregion


            return Ok(new { data = list });
        }

        /// <summary>
        /// sql 语句
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public IActionResult WithSqlTest()
        {
            #region select
            var list = _fsql.Select<Transfer_Amount_Relation_copy2_FreeSql>()
                .WithSql("select * from Transfer_Amount_Relation_copy2 where ActualPaymentFee > @val", new { val = 0 })
                .Page(1, 10)
                .ToList();
            #endregion

            #region select
            var sql = "select top 10 * from Transfer_Amount_Relation_copy2";
            var list1 = _fsql.Ado.Query<Transfer_Amount_Relation_copy2_FreeSql>(sql).ToList();
            #endregion

            #region INSERT
            var insertSql = "INSERT INTO [Transfer_Amount_Relation_copy2]" +
                "([UserName], [ActualPaymentFee], [Recipient], [BankCardNumber], [IDNumber], [OrderNo], [PaymentTime], [BigAmountId], [InputDetailId], [UsageAmount]) " +
                "VALUES" +
                "(@UserName, @ActualPaymentFee, @Recipient, @BankCardNumber, @IDNumber, @OrderNo, @PaymentTime, @BigAmountId, @InputDetailId, @UsageAmount)";

            var affectedRows = _fsql.Ado.ExecuteNonQuery(insertSql, new
            {

                UserName = "UserName100",
                ActualPaymentFee = 100,
                Recipient = "Recipient",
                BankCardNumber = "BankCardNumber",
                IDNumber = "IDNumber",
                OrderNo = "OrderNo",
                PaymentTime = DateTime.Now,
                BigAmountId = 1,
                InputDetailId = 1,
                UsageAmount = 10
            });
            #endregion

            return Ok(new { data = list });
        }

        /// <summary>
        /// sql 语句
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public IActionResult AdoTest()
        {
            #region 多条记录
            var list = _fsql.Ado.Query<Transfer_Amount_Relation_copy2>("select top 10 * from Transfer_Amount_Relation_copy2").ToList();
            #endregion

            #region 单条记录
            var model = _fsql.Ado.QuerySingle<Transfer_Amount_Relation_copy2>("select * from Transfer_Amount_Relation_copy2 where Id = @Id", new { Id = 50 });
            #endregion

            #region 多个结果集
            var sql1 = "select top 10 * from Transfer_Amount_Relation_copy2";
            var sql2 = "select top 20 * from Transfer_Amount_Relation_copy2";
            var result = _fsql.Ado.Query<Transfer_Amount_Relation_copy2, Transfer_Amount_Relation_copy2>($"{sql1};{sql2}");
            List<Transfer_Amount_Relation_copy2> list1 = result.Item1;
            List<Transfer_Amount_Relation_copy2> list2 = result.Item2;
            #endregion

            #region INSERT
            var insertSql = "INSERT INTO [Transfer_Amount_Relation_copy2]" +
                "([UserName], [ActualPaymentFee], [Recipient], [BankCardNumber], [IDNumber], [OrderNo], [PaymentTime], [BigAmountId], [InputDetailId], [UsageAmount]) " +
                "VALUES" +
                "(@UserName, @ActualPaymentFee, @Recipient, @BankCardNumber, @IDNumber, @OrderNo, @PaymentTime, @BigAmountId, @InputDetailId, @UsageAmount)";

            var affectedRows = _fsql.Ado.ExecuteNonQuery(insertSql, new
            {

                UserName = "UserName100",
                ActualPaymentFee = 100,
                Recipient = "Recipient",
                BankCardNumber = "BankCardNumber",
                IDNumber = "IDNumber",
                OrderNo = "OrderNo",
                PaymentTime = DateTime.Now,
                BigAmountId = 1,
                InputDetailId = 1,
                UsageAmount = 10
            });
            #endregion

            return Ok(new { data = list });
        }
    }
}

================ 仓储 ================
5、SimpleFreeSqlRepository.cs 文件

using CodeFirst.Entity;
using FreeSql;

namespace Trial.Repository.AmountRelation
{
    public interface ISimpleFreeSqlRepository
    {
        Task<long> Create(Transfer_Amount_Relation_copy2_FreeSql input);

        Task<long> Modify(Transfer_Amount_Relation_copy2_FreeSql input);

        Task<Transfer_Amount_Relation_copy2_FreeSql> GetAsync(long id);

        Task<List<Transfer_Amount_Relation_copy2_FreeSql>> GetPageList();

        /// <summary>
        /// WithSql
        /// </summary>
        /// <returns></returns>
        Task<List<Transfer_Amount_Relation_copy2_FreeSql>> GetPageListWithSql();
    }

    public class SimpleFreeSqlRepository : BaseRepository<Transfer_Amount_Relation_copy2_FreeSql>, ISimpleFreeSqlRepository
    {
        public SimpleFreeSqlRepository(IFreeSql fsql) : base(fsql) { }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<long> Create(Transfer_Amount_Relation_copy2_FreeSql input)
        {
            Transfer_Amount_Relation_copy2_FreeSql model = await InsertAsync(input);
            return model.Id;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<long> Modify(Transfer_Amount_Relation_copy2_FreeSql input)
        {
            int count = await UpdateAsync(input);
            return count;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<Transfer_Amount_Relation_copy2_FreeSql> GetAsync(long id)
        {
            var model = await Select.WhereDynamic(id).ToOneAsync<Transfer_Amount_Relation_copy2_FreeSql>();

            var model1 = Select.Where(x => x.Id == id).First();

            return model;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public async Task<List<Transfer_Amount_Relation_copy2_FreeSql>> GetPageList()
        {
            var list = await Select.Where(u => u.ActualPaymentFee > 0)
                .Where(x => x.Id > 0)
                .OrderBy(x => x.Id)
                .Skip(1)
                .Take(100)
                .ToListAsync();
            return list;
        }

        public async Task<List<Transfer_Amount_Relation_copy2_FreeSql>> GetPageListWithSql()
        {
            string sql = "select * from Transfer_Amount_Relation_copy2";
            var list = await Select.WithSql(sql)
                .Where(x => x.Id > 0)
                .OrderBy(x => x.Id)
                .Skip(1)
                .Take(100)
                .ToListAsync();
            return list;
        }
    }
}

6、FreeSqlSimpleController.cs 仓储 

using CodeFirst.Entity;
using Microsoft.AspNetCore.Mvc;
using Trial.Repository.AmountRelation;

namespace Trial.WebAPI.Controllers
{
    [Route("api/FreeSqlSimple/[action]")]
    [ApiController]
    public class FreeSqlSimpleController : ControllerBase
    {
        private readonly ISimpleFreeSqlRepository _repository;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="repository"></param>
        public FreeSqlSimpleController(ISimpleFreeSqlRepository repository)
        {
            _repository = repository;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<ActionResult> Get()
        {
            /// 插入
            long id = await _repository.Create(new Transfer_Amount_Relation_copy2_FreeSql
            {
                UserName = "UserName-FreeSql",
                ActualPaymentFee = 100,
                Recipient = "Recipient-FreeSql",
                BankCardNumber = "BankCardNumber-FreeSql",
                IDNumber = "IDNumber-FreeSql",
                PaymentTime = DateTime.Now,
                OrderNo = "OrderNo-FreeSql",
                InputDetailId = 1,
                BigAmountId = 1,
                UsageAmount = 10
            });

            /// 查询单条
            var model = await _repository.GetAsync(50);

            /// 分页
            var list = await _repository.GetPageList();

            /// 分页
            var withSqlList = await _repository.GetPageListWithSql();

            return Ok();
        }
    }
}

7、实体类   实体特性  

using FreeSql.DataAnnotations;

namespace CodeFirst.Entity
{
    /// <summary>
    /// FreeSql
    /// </summary>
    [Table(Name = "Transfer_Amount_Relation_copy2")]
    public class Transfer_Amount_Relation_copy2_FreeSql
    {
        [Column(IsIdentity = true, IsPrimary = true)]
        public long Id { get; set; }

        /// <summary>
        /// 用户名
        /// </summary>
        public string UserName { get; set; }

        /// <summary>
        /// 实付费用
        /// </summary>
        public decimal ActualPaymentFee { get; set; }

        /// <summary>
        /// 收款人
        /// </summary>
        public string Recipient { get; set; }

        /// <summary>
        /// 银行卡号
        /// </summary>
        public string BankCardNumber { get; set; }

        /// <summary>
        /// 身份证号
        /// </summary>
        public string IDNumber { get; set; }

        /// <summary>
        /// 订单号
        /// </summary>
        public string OrderNo { get; set; }

        /// <summary>
        /// 支付时间
        /// </summary>
        public DateTime PaymentTime { get; set; }

        /// <summary>
        /// 
        /// </summary>
        public int BigAmountId { get; set; }

        /// <summary>
        /// 
        /// </summary>
        public int InputDetailId { get; set; }

        /// <summary>
        /// 使用金额
        /// </summary>
        public decimal UsageAmount { get; set; }
    }
}

==================== 仓储 abstract 抽象类 ====================
用于 仓储 扩展方法，自定义方法
创建自定义 IRepositoryBase.cs 接口文件，并且继承 FreeSql框架本身的 IBaseRepository 接口

using System;
using System.Linq.Expressions;
using FreeSql;

namespace Trial.Repository.FreeSql
{
    public interface IRepositoryBase<TEntity, TKey> : IBaseRepository<TEntity, TKey> where TEntity : class
    {
        /// <summary>
        /// 获得Dto
        /// </summary>
        /// <typeparam name="TDto"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        Task<TDto> GetAsync<TDto>(TKey id);

        /// <summary>
        /// 根据条件获取实体
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        Task<TEntity> GetAsync(Expression<Func<TEntity, bool>> exp);

        /// <summary>
        /// 根据条件获取Dto
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        Task<TDto> GetAsync<TDto>(Expression<Func<TEntity, bool>> exp);

        /// <summary>
        /// 软删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        Task<bool> SoftDeleteAsync(TKey id);

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        Task<bool> SoftDeleteAsync(TKey[] id);
    }

    public interface IRepositoryBase<TEntity> : IRepositoryBase<TEntity, long> where TEntity : class
    {
    }
}

创建自定义 RepositoryBase.cs 类文件，并且继承 FreeSql框架本身的 BaseRepository 类

using FreeSql;
using System.Linq.Expressions;

namespace Trial.Repository.FreeSql
{
    public abstract class RepositoryBase<TEntity, TKey> : BaseRepository<TEntity, TKey> where TEntity : class, new()
    {
        protected RepositoryBase(UnitOfWorkManager uowm) : base(uowm.Orm)
        {
            uowm.Binding(this);
        }

        /// <summary>
        /// 查询单条
        /// </summary>
        /// <typeparam name="TDto"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public virtual Task<TDto> GetAsync<TDto>(TKey id)
        {
            return Select.WhereDynamic(id).ToOneAsync<TDto>();
        }

        /// <summary>
        /// 根据条件获取实体
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public virtual Task<TEntity> GetAsync(Expression<Func<TEntity, bool>> exp)
        {
            return Select.Where(exp).ToOneAsync();
        }

        /// <summary>
        /// 根据条件获取Dto
        /// </summary>
        /// <typeparam name="TDto"></typeparam>
        /// <param name="exp"></param>
        /// <returns></returns>
        public virtual Task<TDto> GetAsync<TDto>(Expression<Func<TEntity, bool>> exp)
        {
            return Select.Where(exp).ToOneAsync<TDto>();
        }

        public async Task<bool> SoftDeleteAsync(TKey id)
        {
            await UpdateDiy.SetDto(new { IsDeleted = true }).WhereDynamic(id).ExecuteAffrowsAsync();
            return true;
        }

        public async Task<bool> SoftDeleteAsync(TKey[] ids)
        {
            await UpdateDiy.SetDto(new { IsDeleted = true }).WhereDynamic(ids).ExecuteAffrowsAsync();
            return true;
        }

    }

    public abstract class RepositoryBase<TEntity> : RepositoryBase<TEntity, long> where TEntity : class, new()
    {
        protected RepositoryBase(UnitOfWorkManager uowm) : base(uowm)
        {
        }
    }
}

*、AmountRelationFreeSqlRepository.cs 文件
因为继承了 FreeSql框架本身的 IBaseRepository 接口，可以直接使用InsertAsync，UpdateAsync，DeleteAsync 等方法，所以不用再自定义 增删改查 方法 

using CodeFirst.Entity;
using FreeSql;
using Trial.Repository.FreeSql;

namespace Trial.Repository.AmountRelation
{
    /// <summary>
    /// 继承了基类
    /// </summary>
    public interface IAmountRelationFreeSqlRepository : IRepositoryBase<Transfer_Amount_Relation_copy2_FreeSql>
    {
    }

    public class AmountRelationFreeSqlRepository : RepositoryBase<Transfer_Amount_Relation_copy2_FreeSql>, IAmountRelationFreeSqlRepository
    {
        public AmountRelationFreeSqlRepository(UnitOfWorkManager uowm) : base(uowm)
        {
        }
    }
}

*、FreeSqlAmountRelationController.cs 文件 

using CodeFirst.Entity;
using Microsoft.AspNetCore.Mvc;
using Trial.Repository.AmountRelation;

namespace Trial.WebAPI.Controllers
{
    [Route("api/AmountRelationFreeSql/[action]")]
    [ApiController]
    public class FreeSqlAmountRelationController : ControllerBase
    {
        private readonly IAmountRelationFreeSqlRepository _repository;

        public FreeSqlAmountRelationController(IAmountRelationFreeSqlRepository repository)
        {
            _repository = repository;
        }

        [HttpGet]
        public async Task<ActionResult> Get()
        {
            var info = await _repository.InsertAsync(new Transfer_Amount_Relation_copy2_FreeSql
            {
                UserName = "UserName-FreeSql",
                ActualPaymentFee = 100,
                Recipient = "Recipient-FreeSql",
                BankCardNumber = "BankCardNumber-FreeSql",
                IDNumber = "IDNumber-FreeSql",
                PaymentTime = DateTime.Now,
                OrderNo = "OrderNo-FreeSql",
                InputDetailId = 1,
                BigAmountId = 1,
                UsageAmount = 10

            });

            int count = await _repository.UpdateAsync(new Transfer_Amount_Relation_copy2_FreeSql
            {
                Id = info.Id,
                UserName = "UserName-FreeSql-修改",
                ActualPaymentFee = 100,
                Recipient = "Recipient-FreeSql",
                BankCardNumber = "BankCardNumber-FreeSql",
                IDNumber = "IDNumber-FreeSql",
                PaymentTime = DateTime.Now,
                OrderNo = "OrderNo-FreeSql",
                InputDetailId = 1,
                BigAmountId = 1,
                UsageAmount = 10

            });

            /// 框架方法
            var result0 = await _repository.GetAsync(50);

            /// 自定义方法
            var result1 = await _repository.GetAsync<Transfer_Amount_Relation_copy2_FreeSql>(51);

            /// 自定义方法
            var result2 = await _repository.GetAsync(x => x.Id == 52);

            return Ok();
        }
    }
}

*
*
*
*
*
*