/FreeSql

FreeSql is the most convenient ORM in dotnet. It supports Mysql, Postgresql, SqlServer, Oracle and Sqlite.

Primary LanguageC#MIT LicenseMIT

FreeSql 是功能强大的对象关系映射技术(O/RM),支持 .NETCore 2.1+ 或 .NETFramework 4.0+ 或 Xamarin

扶摇直上,至强ORM只为自由编码;鹏程万里,至简Linq可使保留黑发;横批:FreeSql(诗人:Coder)

Features

  • 支持 CodeFirst 迁移,哪怕使用 Access 数据库也支持;
  • 支持 DbFirst 从数据库导入实体类,安装实体类生成工具
  • 支持 深入的类型映射,比如pgsql的数组类型;
  • 支持 丰富的表达式函数,以及灵活的自定义解析;
  • 支持 导航属性一对多、多对多贪婪加载,以及延时加载;
  • 支持 读写分离、分表分库,租户设计,过滤器,乐观锁,悲观锁;
  • 支持 MySql/SqlServer/PostgreSQL/Oracle/Sqlite/达梦数据库/Access;
《新人学习指引》 | 《Select》 | 《Update》 | 《Insert》 | 《Delete》
《表达式函数》 | 《CodeFirst》 | 《DbFirst》 | 《BaseEntity》
《Repository》 | 《UnitOfWork》 | 《过滤器》 | 《乐观锁》 | 《DbContext》
《读写分离》 | 《分区分表》 | 《租户》 | 《AOP》 | 《黑科技》 | 更新日志

Packages

Package Name NuGet Downloads
FreeSql nuget stats
FreeSql.Repository nuget stats
FreeSql.DbContext nuget stats
FreeSql.AdminLTE nuget stats

FreeSql 提供了五种使用习惯,请根据实际情况选择团队合适的一种:

FluentApi 与 EfCore 90% 相似的扩展包

学习项目

欢迎更多使用 FreeSql 的开源项目加入目录

Providers

Package Name Version
FreeSql.Provider.MySql NETStandard2.0、net45、net40
FreeSql.Provider.MySqlConnector NETStandard2.0、net45
FreeSql.Provider.PostgreSQL NETStandard2.0、net45
FreeSql.Provider.SqlServer NETStandard2.0、net45、net40
FreeSql.Provider.Sqlite NETStandard2.0、net45、net40
FreeSql.Provider.Oracle NETStandard2.0、net45、net40
FreeSql.Provider.Odbc NETStandard2.0、net45、net40
FreeSql.Extensions.LazyLoading NETStandard2.0、net45、net40
FreeSql.Extensions.JsonMap NETStandard2.0、net45、net40
FreeSql.Extensions.BaseEntity NETStandard2.0

ConnectionStrings

DataType ConnectionString
DataType.MySql Data Source=127.0.0.1;Port=3306;User ID=root;Password=root; Initial Catalog=cccddd;Charset=utf8; SslMode=none;Min pool size=1
DataType.PostgreSQL Host=192.168.164.10;Port=5432;Username=postgres;Password=123456; Database=tedb;Pooling=true;Minimum Pool Size=1
DataType.SqlServer Data Source=.;Integrated Security=True;Initial Catalog=freesqlTest;Pooling=true;Min Pool Size=1
DataType.Oracle user id=user1;password=123456; data source=//127.0.0.1:1521/XE;Pooling=true;Min Pool Size=1
DataType.Sqlite Data Source=|DataDirectory|\document.db; Attachs=xxxtb.db; Pooling=true;Min Pool Size=1
DataType.OdbcMySql Driver={MySQL ODBC 8.0 Unicode Driver}; Server=127.0.0.1;Persist Security Info=False; Trusted_Connection=Yes;UID=root;PWD=root; DATABASE=cccddd_odbc;Charset=utf8; SslMode=none;Min Pool Size=1
DataType.OdbcSqlServer Driver={SQL Server};Server=.;Persist Security Info=False; Trusted_Connection=Yes;Integrated Security=True; DATABASE=freesqlTest_odbc; Pooling=true;Min Pool Size=1
DataType.OdbcOracle Driver={Oracle in XE};Server=//127.0.0.1:1521/XE; Persist Security Info=False; Trusted_Connection=Yes;UID=odbc1;PWD=123456; Min Pool Size=1
DataType.OdbcPostgreSQL Driver={PostgreSQL Unicode(x64)};Server=192.168.164.10; Port=5432;UID=postgres;PWD=123456; Database=tedb_odbc;Pooling=true;Min Pool Size=1
DataType.OdbcDameng (达梦) Driver={DM8 ODBC DRIVER};Server=127.0.0.1:5236; Persist Security Info=False; Trusted_Connection=Yes; UID=USER1;PWD=123456789
DataType.Odbc Driver={SQL Server};Server=.;Persist Security Info=False; Trusted_Connection=Yes;Integrated Security=True; DATABASE=freesqlTest_odbc; Pooling=true;Min pool size=1

Quick start

dotnet add package FreeSql.Provider.Sqlite

static IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.Sqlite, 
        @"Data Source=|DataDirectory|\document.db;Pooling=true;Max Pool Size=10")
    .UseAutoSyncStructure(true) //自动同步实体结构到数据库
    .Build(); //请务必定义成 Singleton 单例模式

class Song {
    [Column(IsIdentity = true)]
    public int Id { get; set; }
    public string Title { get; set; }
    public string Url { get; set; }
    public DateTime CreateTime { get; set; }

    public virtual ICollection<Tag> Tags { get; set; }
}
class Song_tag {
    public int Song_id { get; set; }
    public virtual Song Song { get; set; }

    public int Tag_id { get; set; }
    public virtual Tag Tag { get; set; }
}
class Tag {
    [Column(IsIdentity = true)]
    public int Id { get; set; }
    public string Name { get; set; }

    public int? Parent_id { get; set; }
    public virtual Tag Parent { get; set; }

    public virtual ICollection<Song> Songs { get; set; }
    public virtual ICollection<Tag> Tags { get; set; }
}

Query

//OneToOne、ManyToOne
var t0 = fsql.Select<Tag>()
    .Where(a => a.Parent.Parent.Name == "粤语")
    .IncludeMany(a => a.Tags, then => then.Where(sub => sub.Name == "xxx"))
    .ToList();

//OneToMany
var t1 = fsql.Select<Tag>()
    .Where(a => a.Tags.AsSelect().Any(t => t.Parent.Id == 10))
    .ToList();

//ManyToMany
var t2 = fsql.Select<Song>()
    .Where(s => s.Tags.AsSelect().Any(t => t.Name == "国语"))
    .IncludeMany(a => a.Tags, then => then.Where(sub => sub.Name == "xxx"))
    .ToList();

//Other
var t3 = fsql.Select<Xxx>()
    .Where(a => a.IsDelete == 0)
    .WhereIf(keyword != null, a => a.UserName.Contains(keyword))
    .WhereIf(role_id > 0, a => a.RoleId == role_id)
    .Where(a => a.Nodes.AsSelect().Any(t => t.Parent.Id == t.UserId))
    .Count(out var total)
    .Page(page, size)
    .OrderByDescending(a => a.Id)
    .ToList()

更多前往Wiki:《Select 查询数据文档》

var t3 = fsql.Select<Song>()
    .Where(a => new[] { 1, 2, 3 }.Contains(a.Id))
    .ToList();
var t4 = fsql.Select<Song>()
    .Where(a => a.CreateTime.Date == DateTime.Now.Date)
    .ToList();
var t5 = fsql.Select<Song>()
    .OrderBy(a => Guid.NewGuid())
    .Limit(1)
    .ToList();

更多前往Wiki:《表达式函数》

Repository & UnitOfWork

dotnet add package FreeSql.Repository

using (var uow = fsql.CreateUnitOfWork()) {
    var repo1 = uow.GetRepository<Song, int>();
    var repo2 = uow.GetRepository<Tag, int>();

    await repo1.InsertAsync(new Song());
    await repo2.InsertAsync(new Tag());
    uow.Commit();
}

DbContext & DbSet

dotnet add package FreeSql.DbContext

using (var ctx = new fsql.CreateDbContext()) {
    var songs = ctx.Set<Song>();
    var tags = ctx.Set<Tag>();

    var tag = new Tag {
        Name = "testaddsublist",
        Tags = new[] {
            new Tag { Name = "sub1" },
            new Tag { Name = "sub2" },
            new Tag {
                Name = "sub3",
                Tags = new[] {
                    new Tag { Name = "sub3_01" }
                }
            }
        }
    };
    //tags.Add(tag);
    ctx.Add(tag);
    await ctx.SaveChangesAsync();
}

Performance

FreeSql Query & Dapper Query

Elapsed: 00:00:00.6733199; Query Entity Counts: 131072; ORM: Dapper

Elapsed: 00:00:00.4554230; Query Tuple Counts: 131072; ORM: Dapper

Elapsed: 00:00:00.6846146; Query Dynamic Counts: 131072; ORM: Dapper

Elapsed: 00:00:00.6818111; Query Entity Counts: 131072; ORM: FreeSql*

Elapsed: 00:00:00.6060042; Query Tuple Counts: 131072; ORM: FreeSql*

Elapsed: 00:00:00.4211323; Query ToList<Tuple> Counts: 131072; ORM: FreeSql*

Elapsed: 00:00:01.0236285; Query Dynamic Counts: 131072; ORM: FreeSql*

FreeSql ToList & Dapper Query

Elapsed: 00:00:00.6707125; ToList Entity Counts: 131072; ORM: FreeSql*

Elapsed: 00:00:00.6495301; Query Entity Counts: 131072; ORM: Dapper

Test codeMore

Contributors

systemhejiyongLambertWmypeng1985stulzqmovingsamALer-Rzouql、 深圳|凉茶、 densen2014LiaoLiaoWuJuhd2ytky753

(QQ群:4336577)

Donation

L*y 58元、花花 88元、麦兜很乖 50元、网络来者 2000元、John 99.99元、alex 666元

Thank you for your donation