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）：842725037private 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.htmlvar 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.其他基本功能少花点精力优化，重点优化其他组件没有的功能