Getting Started

FreeSql is a powerful .NET ORM that supports all .NET Standard runtime platforms like .NET Framework 4.0+, .NET Core 2.1+ and Xamarin, etc.

FreeSql supports MySql, SqlServer, PostgreSQL, Oracle, Sqlite, Firebird, Dameng, Shentong Database, Kingbase ES, Hangao Database, ClickHouse, GBase and MsAccess.

QQ Groups:4336577(full)、8578575(full)、52508226(available)

Models

FreeSql uses a model to perform data access. The model is represented by an entity class to represent a database table or view for querying and saving data.

The entity model can be generated from an existing database, and FreeSql provides the IDbFirst interface to generate the entity model.

Or you can create the model manually, and then create or modify the database based on the model. FreeSql provides an API for the ICodeFirst synchronization structure (it can even be synchronized automatically during the development phase).

using FreeSql.DataAnnotations;
using System;

public class Blog {
    [Column(IsIdentity = true, IsPrimary = true)]
    public int BlogId { get; set; }
    public string Url { get; set; }
    public int Rating { get; set; }
}
1
2
3
4
5
6
7
8
9

Packages

FreeSql.Provider.XXX (Optional Providers)

dotnet add packages FreeSql
dotnet add packages FreeSql.Provider.Sqlite
1
2

or

Install-Package FreeSql
Install-Package FreeSql.Provider.Sqlite
1
2

Declaring

IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=db1.db")
     //Automatically synchronize the entity structure to the database.
     //FreeSql will not scan the assembly, and will generate a table if and only when the CRUD instruction is executed.
    .UseAutoSyncStructure(true) 
     //Be sure to define as singleton mode
    .Build(); 
    
//Note: IFreeSql should be declared as a singleton in the project, not created every time it is used.
1
2
3
4
5
6
7
8
9
  • .NET Core Singleton Startup.cs
public void ConfigureServices(IServiceCollection services)
{
    IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=db1.db")
    //Automatically synchronize the entity structure to the database.
    //FreeSql will not scan the assembly, and will generate a table if and only when the CRUD instruction is executed.
    .UseAutoSyncStructure(true)
    .Build();
    services.AddSingleton<IFreeSql>(fsql);
}
1
2
3
4
5
6
7
8
9
10
public class DB
{
   static Lazy<IFreeSql>sqliteLazy = new Lazy<IFreeSql>(() => new FreeSql.FreeSqlBuilder()
        .UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=db1.db")
        //Automatically synchronize the entity structure to the database.
        //FreeSql will not scan the assembly, and will generate a table if and only when the CRUD instruction is executed.
        .UseAutoSyncStructure(true)
        .Build());

    public static IFreeSql Sqlite => sqliteLazy.Value;
}
1
2
3
4
5
6
7
8
9
10
11

Then when using it, use fsql directly through IFreeSql fsql = DB.Sqlite;.

IFreeSql is the top-level object of ORM, and all operations use its methods or properties:


fsql.Select<T>(); //Query
fsql.Insert<T>(); //Insert
fsql.Update<T>(); //Update
fsql.Delete<T>(); //Delete
fsql.InsertOrUpdate<T>()// Insert or Update
fsql.Transaction(..); //Transaction

fsql.CodeFirst; //CodeFirst Object
fsql.DbFirst; //DbFirst Object
fsql.Ado; //Ado Object
fsql.Aop; //Aop Object
fsql.GlobalFilter; //Gloabl Filter Object
1
2
3
4
5
6
7
8
9
10
11
12
13

Migration

When the program is running, FreeSql will check the AutoSyncStructure parameter, and use this condition to determine whether to compare the changes between the entity and the database structure to achieve the purpose of automatic migration. For more information, please refer to the CodeFirst Documentation.

Note: Use this feature in a production environment with caution.

Note: Use this feature in a production environment with caution.

Note: Use this feature in a production environment with caution.

Query Data

var blogs = fsql.Select<Blog>()
    .Where(b => b.Rating > 3)
    .OrderBy(b => b.Url)
    .Skip(100)
    .Limit(10) //Query the record from line 100 to line 110
    .ToList();
1
2
3
4
5
6

Insert Data

var blog = new Blog { Url = "http://sample.com" };
blog.BlogId = (int)fsql.Insert<Blog>()
    .AppendData(blog)
    .ExecuteIdentity();
1
2
3
4

Update Data

fsql.Update<Blog>()
    .Set(b => b.Url, "http://sample2222.com")
    .Where(b => b.Url == "http://sample.com")
    .ExecuteAffrows();
1
2
3
4

Delete Data

fsql.Delete<Blog>()
    .Where(b => b.Url == "http://sample.com")
    .ExecuteAffrows();
1
2
3

FreeSqlBuilder

MethodsReturnDescription
UseConnectionStringthisConnection string
UseSlavethisSet up the slave database, support multipleslace databases.
UseConnectionFactorythisSet up a custom database connection object (abandon the built-in object connection pool technology)
UseAutoSyncStructurethis[Recommended development environment] Automatically synchronize the entity structure to the database, and check entity creation or modification table structure during program operation
UseNoneCommandParameterthisDo not use command parameterized execution. for Insert/Update, you can also temporarily use IInsert/IUpdate.NoneParameter()
UseGenerateCommandParameterWithLambdathisFor lambda expression analysis, generate command parameterized execution
UseLazyLoadingthisTurn on the lazy loading function,
UseMonitorCommandthisMonitor before and after global SQL execution.
UseNameConvertthisAutomatic name conversion Entity -> Db
UseExitAutoDisposePoolthisListen to the AppDomain.CurrentDomain.ProcessExit/Console.CancelKeyPress event to automatically release the connection pool (default true)
Build<T>IFreeSql<T>Create an IFreeSql object. Note: Singleton design, don’t repeat creation

ConnectionStrings

DataTypeConnectionString
DataType.MySqlData Source=127.0.0.1;Port=3306;User ID=root;Password=root; Initial Catalog=cccddd;Charset=utf8; SslMode=none;Min pool size=1
DataType.PostgreSQLHost=192.168.164.10;Port=5432;Username=postgres;Password=123456; Database=tedb;Pooling=true;Minimum Pool Size=1
DataType.SqlServerData Source=.;User Id=sa;Password=123456;Initial Catalog=freesqlTest;TrustServerCertificate=true;Pooling=true;Min Pool Size=1
DataType.Oracleuser id=user1;password=123456; data source=//127.0.0.1:1521/XE;Pooling=true;Min Pool Size=1
DataType.SqliteData Source=|DataDirectory|\document.db; Attachs=xxxtb.db; Pooling=true;Min Pool Size=1
DataType.Firebirddatabase=localhost:D:\fbdata\EXAMPLES.fdb;user=sysdba;password=123456
DataType.MsAccessProvider=Microsoft.Jet.OleDb.4.0;Data Source=d:/accdb/2003.mdb
DataType.Dameng(达梦)server=127.0.0.1;port=5236;user id=2user;password=123456789;database=2user;poolsize=5
DataType.ShenTong(神通)HOST=192.168.164.10;PORT=2003;DATABASE=OSRDB;USERNAME=SYSDBA;PASSWORD=szoscar55;MAXPOOLSIZE=2
DataType.KingbaseES(人大金仓)Server=127.0.0.1;Port=54321;UID=USER2;PWD=123456789;database=TEST;MAXPOOLSIZE=2
DataType.OdbcMySqlDriver={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.OdbcSqlServerDriver={SQL Server};Server=.;Persist Security Info=False; Trusted_Connection=Yes;Integrated Security=True; DATABASE=freesqlTest_odbc; Pooling=true;Min Pool Size=1
DataType.OdbcOracleDriver={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.OdbcPostgreSQLDriver={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.OdbcKingbaseES (人大金仓)Driver={KingbaseES 8.2 ODBC Driver ANSI};Server=127.0.0.1;Port=54321;UID=USER2;PWD=123456789;database=TEST
DataType.OdbcDriver={SQL Server};Server=.;Persist Security Info=False; Trusted_Connection=Yes;Integrated Security=True; DATABASE=freesqlTest_odbc; Pooling=true;Min pool size=1
DataType.Customopen in new windowCustom connection string, access any database

Reference