FreeSql使用

目的:

1.方库分表
2.主从分离

3.分布式事务

过程：

官网：指南 | FreeSql 官方文档

1.Startup.cs 添加配置（本地数据库MySql）

ConfigureServices：

           Func<IServiceProvider, IFreeSql> fsql = r =>
            {
                IFreeSql fsql = new FreeSql.FreeSqlBuilder()
                    .UseConnectionString(FreeSql.DataType.MySql, @"Data Source=localhost;Database=testfreesql;User ID=root;Password=123456;pooling=true;port=3306;sslmode=none;CharSet=utf8;")
                    .UseMonitorCommand(cmd => Console.WriteLine($"Sql：{cmd.CommandText}"))//监听SQL语句
                    // .UseSlave(@"Data Source = localhost; Database = testfreesql1; User ID = root; Password = 123456; pooling = true; port = 3306; sslmode = none; CharSet = utf8; ", @"Data Source=localhost;Database=testfreesql2;User ID=root;Password=123456;pooling=true;port=3306;sslmode=none;CharSet=utf8;")
                    .UseAutoSyncStructure(true) //自动同步实体结构到数据库，FreeSql不会扫描程序集，只有CRUD时才会生成表。
                    .Build();
                return fsql;
            };
            services.AddSingleton<IFreeSql>(fsql);

Configure：

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

2.引入组件

    <PackageReference Include="FreeSql" Version="3.2.690" />
    <PackageReference Include="FreeSql.Cloud" Version="1.6.3" />
    <PackageReference Include="FreeSql.DbContext" Version="3.2.690" />
    <PackageReference Include="FreeSql.Provider.MySql" Version="3.2.690" />
    <PackageReference Include="FreeSql.Provider.Sqlite" Version="3.2.690" />

 3.创建类（自动生成表）

 4.接口调用

using Db.Entities;
using FreeSql;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace TestFreeSql.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class FreeSqlController : ControllerBase
    {
        //官方文档：https://freesql.net/guide/getting-started.html
        //demo作者微信（qq）：842725037


        private readonly ILogger<FreeSqlController> _logger;
        private readonly IFreeSql _freeSql;

        private readonly DbContext  _dbContext;

        private IMemoryCache _cache;
        public FreeSqlController(ILogger<FreeSqlController> logger, IFreeSql freeSql, IMemoryCache cache)
        {
            _logger = logger;
            _freeSql = freeSql;
            _dbContext = freeSql.CreateDbContext();
            _cache = cache;
        }

        /// <summary>
        /// 基础使用(增加、查询、删除、)
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public  async Task<string> TestFreeSqlBase()
        {
            //插入单一数据
            var blog = new Blog() { Rating = 1, Url = DateTime.Now.ToString() };
            var save =await _freeSql.Insert<Blog>(blog).ExecuteAffrowsAsync();
            
            //查询
            var select1 =await _freeSql.Select<Blog>().Where(x => x.BlogId == 1).ToListAsync();
            var select2 = _freeSql.Select<Blog>().ToList();


            //分页查询
            var select4 = _freeSql.Select<Blog>()
               .Where(a => a.BlogId > 1);
            var sql = select4.ToSql();
            var total = await select4.CountAsync();
            var list = await select4.Page(1, 20).ToListAsync();


            //修改
            var select3 =await _freeSql.Select<Blog>().FirstAsync();
            select3.Url = DateTime.Now.ToString();
            var save2 = await _freeSql.InsertOrUpdate<Blog>().SetSource(select3).ExecuteAffrowsAsync();


            //删除
            var delete =await _freeSql.Delete<Blog>().Where(x => x.Url == blog.Url).ExecuteAffrowsAsync();


            return "123";
        }


        /// <summary>
        /// 事务使用
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<string> TestFreeSqlDbContext1()
        {
            //工作单元
            var ctx = _dbContext;

            var blog = new Blog() { Rating = 1, Url = DateTime.Now.ToString() };
            ctx.Set<Blog>().Add(blog);
   
            var user = new User() { Name = DateTime.Now.ToString(), Age=1 };
            ctx.Set<User>().Add(user);

           var save=await ctx.SaveChangesAsync();


            return "123";
        }

        /// <summary>
        /// 事务使用
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<string> TestFreeSqlDbContext2()
        {
            //工作单元
            var ctx = _freeSql.CreateDbContext();

            var blog = new Blog() { Rating = 1, Url = DateTime.Now.ToString() };
            ctx.Set<Blog>().Add(blog);

            var user = new User() { Name = DateTime.Now.ToString(), Age = 1 };
            ctx.Set<User>().Add(user);

            var save = await ctx.SaveChangesAsync();


            return "123";
        }




        /// <summary>
        /// 读从库、写主库,伪功能（需要自己实现数据库数据同步）
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<string> TestFreeSqlReadWrite()
        {
            //文档：https://freesql.net/guide/read-write-splitting.html


            var select2 = _freeSql.Select<Blog>().Where(x=>x.BlogId>0).ToList();//读取从库

            //插入单一数据
            var blog = new Blog() { Rating = 1, Url = DateTime.Now.ToString() };
            var saveSql =await _freeSql.Insert<Blog>(blog).ExecuteAffrowsAsync();//写入主库

            var select3 = _freeSql.Select<Blog>().ToList();//读取从库

            var select4 = _freeSql.Select<Blog>().Master().ToList();//读取主库


            return "123";
        }



        /// <summary>
        /// 分表（自动分表）
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<string> TestFreeSqlSeparate()
        {
            //文档：https://github.com/dotnetcore/FreeSql/discussions/1066

            //插入单一数据
            var asTableLog = new AsTableLog() { msg=DateTime.Now.ToString(), createtime=DateTime.Now};
            var saveSql =await _freeSql.Insert<AsTableLog>(asTableLog).ExecuteAffrowsAsync();

            //插入单一数据
            var asTableLog2 = new AsTableLog() { msg = DateTime.Now.ToString(), createtime = DateTime.Now.AddDays(10) };
            var saveSql2 =await _freeSql.Insert<AsTableLog>(asTableLog2).ExecuteAffrowsAsync();


            //查询
            var select = _freeSql.Select<AsTableLog>();
                    //.Where(a => a.createtime.Between(DateTime.Parse("2022-3-1"), DateTime.Parse("2022-5-1")));
            var sql = select.ToSql();
            var list = select.ToList();

            return "123";
        }




    }
}

5.项目地址：

https://github.com/zhihuixitong/TestFreeSql

总结：

1.使用上感觉一般

2.和EF相比唯一有用的分表使用，分库不支持

3.分布式事务看着复杂没有使用

4.读写分离还好，就是要自己实现数据同步

建议：

1.分库分表完善（分库支持）

2.读写分离完善（自动通过配置自己同步数据）

3.分布式事务使用简单化

4.其他基本功能少花点精力优化，重点优化其他组件没有的功能