v1.4.0+ automatically recognizes EF attributes such as
Key/Required/NotMapped/MaxLength/StringLength/DatabaseGenerated/Table/Column.
[Table(Name = "tb_topic")]
class Topic { }
Architecture:
[Table(Name = "dbo.tb_topic")]
Note: For table names with dots, use
[Table(Name = "
sys.config
")]
to resolve.
Methods for table name mapping, from lowest to highest priority:
-
-
Entity class name
-
-
Aop
fsql.Aop.ConfigEntity += (_, e) => e.ModifyResult.Name = "public.tabname"
-
-
FluentApi
fsql.CodeFirst.ConfigEntity(a => a.Name("public.tabname"))
-
-
[Table(Name = "public.tabname")]
-
-
AsTable
fsql.Select<T>().AsTable((_, old) => "public.tabname").ToList()
v3.2.660 allows adjusting priority with
UseMappingPriority
.
Property:
[Column(Name = "xxx")]
class Topic
[Column(IsPrimary = true)]
public int Id { get; set; }
}
-
If no primary key is specified, a field named
id
will be treated as the primary key (case insensitive).
-
When the primary key is of type Guid, a value will be automatically created (ordered, unique) upon insertion, so you don't need to assign it yourself (supports distributed systems).
Composite primary keys use attributes on multiple properties.
For Oracle, if the primary key name length exceeds 30, use
[OraclePrimaryKeyName(name)]
.
class Topic
[Column(IsIdentity = true)]
public int Id { get; set; }
}
-
If no primary key is specified, the auto-incremented member will be used as the primary key.
-
For DbFirst mode sequences:
[Column(IsIdentity = true, InsertValueSql = "seqname.nextval")]
[Index("uk_phone", "Phone", true)]
[Index("uk_group_index", "Group,Index11", true)]
[Index("uk_group_index22", "Group,Index22 desc", true)]
class Topic
public int Id { get; set; }
public string Phone { get; set; }
public string Group { get; set; }
public int Index11 { get; set; }
public string Index22 { get; set; }
}
-
The third parameter
true
indicates a unique key, while
false
indicates a regular index.
-
For sharding scenarios:
[Index("{tablename}_idx_01", "phone")]
Note: Generally, use .NET common types (e.g., int/string/DateTime) for mapping. There is no need to set
DbType
explicitly.
class Topic
[Column(DbType = "varchar(128) NOT NULL default....")]
public string Title { get; set; }
}
You can specify
NOT NULL
directly on the type or use
[Column(IsNullable = false)]
.
class Topic
[Column(Precision = 10, Scale = 2)]
public decimal Amount { get; set; }
}
class Topic
[Column(StringLength = 128)]
// Or [MaxLength(128)]
public string Title {
get; set; }
}
When the length is -1, the mapping is as follows:
|
MySql
|
PostgreSQL
|
SqlServer
|
Oracle
|
Sqlite
|
Firebird
|
DuckDB
|
MsAccess
|
DM
|
Kingbase
|
Shentong
|
Gbase
|
|
text
|
text
|
nvarchar(max)
|
nclob
|
text
|
blob sub_type 1
|
text
|
longtext
|
text
|
text
|
text
|
text
|
Note: MySql
[MaxLength(-2)]
or
[Column(StringLength = -2)]
maps to
longtext
. Other databases follow the same mapping rules as -1.
class Topic
[Column(IsNullable = false)]
public string Title { get; set; }
}
If
DbType
and
IsNullable
are not specified, FreeSql provides default settings, such as:
-
int
-> not null
-
int?
-> nullable
Typically, only specify
IsNullable
for
string
types (string is nullable by default).
class Topic
[Column(ServerTime = DateTimeKind.Utc, CanUpdate = false)]
public DateTime CreateTime { get; set; }
[Column(ServerTime = DateTimeKind.Utc)]
public DateTime UpdateTime
{ get; set; }
}
When using database time to insert data:
-
Setting the entity's value is ineffective upon insertion.
-
The value of the entity will still be the C# time after the successful insert.
class Topic
[Column(IsIgnore = true)]
public string Title { get; set; }
}
If the property is not mappable,
IsIgnore
does not need to be specified. For example, the following is unnecessary:
class Topic
[Column(IsIgnore = true)]
public Topic Parent { get; set; }
}
class Topic
public Guid Id { get; set; }
public string Title { get; set; }
[Column(IsVersion = true)]
public int Version { get; set; }
}
The principle of optimistic locking is to use a field in the entity, such as
long version
. Before updating, query the data; if
version
is 1, the generated SQL will include
where version = 1
. If the modification fails (i.e.,
Affrows == 0
), an exception (
DbUpdateVersionException
) is thrown.
Each entity supports only one optimistic lock property and supports
int
,
long
,
string
,
Guid
.
Applicable to
SetSource
for updating data. Regardless of how the version value is updated, it will be incremented by 1.
Using MapType Enum -> string/int etc.:
class Table
[Column(MapType = typeof(string))]
public PeopleType t1 { get; set; }
[Column(MapType = typeof(int))]
public PeopleType t2 { get; set; }
[Column(MapType = typeof(string))]
public BigInteger t3 { get; set; }
public enum PeopleType { TaiWan, abc, HongKong }
TypeHandlers (Custom):
FreeSql.Internal.Utils.TypeHandlers.TryAdd(typeof(JsonPoco), new JsonPocoTypeHandler());
FreeSql.Internal.Utils.TypeHandlers.TryAdd(typeof(DateOnly), new DateOnlyTypeHandler());
FreeSql.Internal.Utils.TypeHandlers.TryAdd(typeof(DateTimeOffset), new DateTimeOffsetTypeHandler());
class Product
public JsonPoco json { get; set; }
public DateOnly date { get; set; }
public DateTimeOffset dateTimeOffset {
get; set; }
class JsonPoco
public int a { get; set; }
public int b { get; set; }
class JsonPocoTypeHandler : TypeHandler<JsonPoco>
public override object Serialize(JsonPoco value) => JsonConvert.SerializeObject(value);
public override JsonPoco Deserialize(object value) => JsonConvert.DeserializeObject<JsonPoco>((string)value);
public override void FluentApi(FluentColumn col) => col.MapType(typeof(string)).StringLength(-1);
class DateOnlyTypeHandler : TypeHandler<DateOnly>
public override object Serialize(DateOnly value) => value.ToString("yyyy-MM-dd");
public override DateOnly Deserialize(object value) => DateOnly.TryParse(string.Concat(value), out var trydo) ? trydo : DateOnly.MinValue;
public override void FluentApi(FluentColumn col) => col.MapType(typeof(string)).StringLength(12);
class DateTimeOffsetTypeHandler : TypeHandler<DateTimeOffset>
public override object Serialize(DateTimeOffset value) => value.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss");
public override DateTimeOffset Deserialize(object value) => DateTimeOffset.TryParse((string)value, out var dts) ? dts : DateTimeOffset.MinValue;
public override void FluentApi(FluentColumn col) => col.MapType(typeof(string)).DbType("datetime");
}
JsonMap:
dotnet add package FreeSql.Extensions.JsonMap
fsql.UseJsonMap(); //Open
class Table
public int Id { get; set; }
[JsonMap]
public TableOptions Options { get; set; }
class TableOptions
public int Value1 { get; set; }
public string Value2 { get; set; }
fsql.Select<Table>().Where(a => a.Options.Value1 == 100 && a.Options.Value2 == "xx").ToList();
//WHERE json_extract(a."Options",'$.Value1') = 100 AND json_extract(a."Options",'$.Value2') = 'xx'
Applicable scenario: When inheriting entity classes, the order of fields created by CodeFirst might not be desired. Use this attribute to set the order.
Specify field positions when creating tables, e.g.,
[Column(Position = 1)]
, negative values are for reverse positions.
Indicates whether the field can be inserted or updated, default is
true
. If set to
false
, the field will be ignored during insert or update operations.
When specifying methods like
InsertColumn
/
UpdateColumns
, this attribute may become ineffective.
Use this value when executing the Insert method; its syntax is SQL.
Note: For functions like
getdate()
, consider using
ServerTime
as it is adapted for all databases.
class Topic
[Column(InsertValueSql = "'xxx'")]
public string Name { get; set; }
fsql.Insert(new Type()).ExecuteAffrows();
// INSERT INTO `type`(`Name`) VALUES('xxx')
// v3.2.700 Ignore InsertValueSql
fsql.Insert(new Type()).IgnoreInsertValueSql(a => a.Name).ExecuteAffrows();
// INSERT INTO `type`(`Name`) VALUES('')
Rewrite SQL for insertion and read SQL for querying. For example, handling geography type read and write scenarios:
class Topic
[Column(DbType = "geography",
RewriteSql = "geography::STGeomFromText({0}, 4236)",
RereadSql = "{0}.STAsText()")]
public string Geo { get; set; }
// Insert: INSERT INTO [ts_geocrud01]([id], [geo])
// VALUES(@id_0, geography::STGeomFromText(@geo_0, 4236))
// Query: SELECT TOP 1 a.[id], a.[geo].STAsText()
// FROM [ts_geocrud01] a
// WHERE (a.[id] = 'c7227d5e-0bcf-4b71-8f0f-d69a552fe84e')
fsql.CodeFirst.IsAutoSyncStructure
sets global automatic structure migration, or use
FreeSqlBuilder.UseAutoSyncStructure(true)
when creating
IFreeSql
.
When an [entity class] corresponds to a database [view] or other objects, you can disable migration for specific entities.
[Table(DisableSyncStructure = true)]
class Topic2
