Skip to main content

Untyped Mapping

nicyeAbout 4 minAbout 1066 words

Untyped Mapping

This document follows the “Dynamic Operations” and is designed specifically for low-code solutions.

FreeSql defaults to using entity types. Although types can be dynamically created at runtime, frequent dynamic compilation can lead to memory leaks and difficulties in managing type versions.

This feature operates independently and performs CRUD operations using pure dictionaries (without entity types), supporting features such as navigation properties and cascading operations.

Note: This feature operates independently; do not confuse it with the cascading mechanism in other documents.

Dictionary CUD (Single Table)

var dic = new Dictionary<string, object>();
dic.Add("id", 1);
dic.Add("name", "xxxx");

fsql.InsertDict(dic).AsTable("table1").ExecuteAffrows();
fsql.UpdateDict(dic).AsTable("table1").WherePrimary("id").ExecuteAffrows();
fsql.DeleteDict(dic).AsTable("table1").ExecuteAffrows();
fsql.InsertOrUpdateDict(dic).AsTable("table1").WherePrimary("id").ExecuteAffrows();

InsertDict/UpdateDict/DeleteDict/InsertOrUpdateDict all support batch operations, corresponding to the type List<Dictionary<string, object>>.

Untyped CRUD (Advanced)

No dependence on entity types, no dynamic compilation needed, pure dictionary operations, supports navigation properties, cascading saves, and AOT compilation.

NuGet installation:

dotnet add package FreeSql.Extensions.ZeroEntity

var ctx = new ZeroDbContext(fsql, JsonConvert.DeserializeObject<TableDescriptor[]>(json)); // see the end of the document

var item = JsonConvert.DeserializeObject<Dictionary<string, object>>(@"
{
  ""Name"":""user1"",
  ""Ext"":
  {
    ""Remarks"":[{""Remark"":""remark1""},{""Remark"":""remark2""}]
  },
  ""Claims"":[{""ClaimName"":""claim1""},{""ClaimName"":""claim2""},{""ClaimName"":""claim3""}],
  ""Roles"":[{""Name"":""role1""},{""Name"":""role2""}]
}");
ctx.Insert(item);

var item2 = JsonConvert.DeserializeObject<Dictionary<string, object>>(@"
{
  ""Id"":1,
  ""Name"":""user1111"",
  ""Ext"":{},
  ""Claims"":[{""Id"":1,""ClaimName"":""claim1111""},{""Id"":""3"",""ClaimName"":""claim3222222""},{""ClaimName"":""claim0000""}],
  ""Roles"":[{""Name"":""role111100001""},{""Id"":2,""Name"":""role2""}]
}");
ctx.Update(item2);
ctx.Delete(item2);
INSERT INTO [Role]([Name]) OUTPUT INSERTED.[Id] as [Id], INSERTED.[Name] as [Name] VALUES(N'role1'), (N'role2')
INSERT INTO [User]([Name]) OUTPUT INSERTED.[Id] as [Id], INSERTED.[Name] as [Name] VALUES(N'user1')
INSERT INTO [UserExt]([UserId]) VALUES(1)
INSERT INTO [UserExtRemarks]([RemarkId], [UserId], [Remark]) VALUES('6570e3f8-a226-c3ac-00d1-a3dd18b30339', 1, N'remark1'), ('6570e3f8-a226-c3ac-00d1-a3de16d9aa68', 1, N'remark2')
INSERT INTO [UserClaim]([UserId], [ClaimName]) OUTPUT INSERTED.[Id] as [Id], INSERTED.[UserId] as [UserId], INSERTED.[ClaimName] as [ClaimName] VALUES(1, N'claim1'), (1, N'claim2'), (1, N'claim3')
INSERT INTO [UserRole]([UserId], [RoleId]) VALUES(1, 5), (1, 6)

INSERT INTO [Role]([Name]) OUTPUT INSERTED.[Id] as [Id], INSERTED.[Name] as [Name] VALUES(N'role111100001'), (N'role2')
INSERT INTO [UserClaim]([UserId], [ClaimName]) OUTPUT INSERTED.[Id] as [Id], INSERTED.[UserId] as [UserId], INSERTED.[ClaimName] as [ClaimName] VALUES(1, N'claim0000')
INSERT INTO [UserRole]([UserId], [RoleId]) VALUES(1, 7), (1, 8)
DELETE FROM [UserRole] WHERE ([UserId] = 1 AND [RoleId] = 6)
DELETE FROM [UserRole] WHERE ([UserId] = 1 AND [RoleId] = 5)
DELETE FROM [UserClaim] WHERE ([Id] = 2)
UPDATE [User] SET [Name] = N'user1111'
WHERE ([Id] = 1)
UPDATE [UserClaim] SET [ClaimName] = CASE [Id]
WHEN 1 THEN N'claim1111'
WHEN 3 THEN N'claim3222222' END
WHERE ([Id] IN (1,3))

DELETE FROM [UserRole] WHERE (([UserId] = 1 AND [RoleId] = 7) OR ([UserId] = 1 AND [RoleId] = 8))
DELETE FROM [UserClaim] WHERE ([Id] IN (1,3,4))
DELETE FROM [UserExt] WHERE ([UserId] = 1)
DELETE FROM [User] WHERE ([Id] = 1)

Queries return results as dictionaries Dictionary<string, object>:

// Automatic cascading
ctx.Select.Where("id", 1).ToList();

// Separate query
ctx.SelectNoTracking("User")
  //.IncludeAll()
  .Include("Ext.Remarks", then => then.Where("remark", "like", "error"))
  .Include("Roles", then => then.Include("Users",
    then => then.Include("Ext.Remarks")))
  .ToList();

// Regular multi-table query
ctx.SelectNoTracking("User")
  .LeftJoin("UserExt", "UserId", "User.Id")
  .ToList();
//[{id:1, UserExt:{} },..]

The ctx object depends on the JSON configuration as follows:

[
  {
    "Name":"User",
    "Comment":"User Table",
    "Columns": [
      {"Name":"Id","IsPrimary":true,"IsIdentity":true,"MapType":"System.Int32"},
      {"Name":"Name","MapType":"System.String"}
    ],
    "Navigates":[
      {"Name":"Ext","Type":"OneToOne","RelTable":"UserExt"},
      {"Name":"Claims","Type":"OneToMany","RelTable":"UserClaim","Bind":"UserId"},
      {"Name":"Roles","Type":"ManyToMany","RelTable":"Role","ManyToMany":"UserRole"}
    ],
    "Indexes":[]
  },
  {
    "Name":"UserExt",
    "Comment":"User Extension Information Table",
    "Columns":[
      {"Name":"UserId","IsPrimary":true,"MapType":"System.Int32"},
    ],
    "Navigates":[
      {"Name":"Remarks","Type":"OneToMany","RelTable":"UserExtRemarks","Bind":"UserId"},
    ],
  },
  {
    "Name":"UserExtRemarks",
    "Comment":"User Extension Information Table - Sub Table",
    "Columns":[
      {"Name":"RemarkId","IsPrimary":true,"MapType":"System.Guid"},
      {"Name":"UserId","MapType":"System.Int32"},
      {"Name":"Remark","MapType":"System.String"},
    ],
  },
  {
    "Name":"UserClaim",
    "Comment":"One-to-Many Test Table",
    "Columns":[
      {"Name":"Id","IsPrimary":true,"IsIdentity":true,"MapType":"System.Int32"},
      {"Name":"UserId","MapType":"System.Int32"},
      {"Name":"ClaimName","MapType":"System.String"},
    ],
  },
  {
    "Name":"Role",
    "Comment":"Permission Table",
    "Columns":[
      {"Name":"Id","IsPrimary":true,"IsIdentity":true,"MapType":"System.Int32"},
      {"Name":"Name","MapType":"System.String"}
    ],
    "Navigates":[
      {"Name":"Users","Type":"ManyToMany","RelTable":"User","ManyToMany":"UserRole"}
    ],
    "Indexes":[]
  },
  {
    "Name":"UserRole",
    "Comment":"Many-to-Many Intermediate Table",
    "Columns":[
      {"Name":"UserId","IsPrimary":true,"MapType":"System.Int32"},
      {"Name":"RoleId","IsPrimary":true,"MapType":"System.Int32"}
    ],
    "Navigates":[
      {"Name":"User","Type":"ManyToOne","RelTable":"User","Bind":"UserId"},
      {"Name":"Role","Type":"ManyToOne","RelTable":"Role","Bind":"RoleId"}
    ]
  }
]

Cascading Mechanism

Before understanding this mechanism, please forget about the cascading mechanisms of Repository/DbContext and other prior methods; they are unrelated.

schemas[] is a set of table mapping information definitions, including table names, column names, navigation properties, indexes, etc.

  • Navigation Properties: OneToOne/OneToMany/ManyToOne/ManyToMany
  • Aggregate Root: OneToOne/OneToMany/Many-to-Many Intermediate Tables, treated as a whole
  • External Root: ManyToOne/ManyToMany External

For example:

  • User is the aggregate root.
  • UserExt/UserClaim/UserRole are child members of the User table, stored/deleted together.
  • Role is an external root (relative to User, it is an independent aggregate root).

CRUD operations are based on the aggregate root of schemas[0].

  • Query: Eagerly load all child members, the external root, and the external root's external roots (recursively).
  • State Management: Snapshot of the aggregate root (since external roots are also aggregate roots, they are stored in parallel to the aggregate root).

Comparing and Saving:

Compare the current operation's aggregate root with the snapshot copy to calculate the changes in columns.

Navigation PropertySnapshotLatestAction
OneToOneNULLObjectAdd new record
OneToOneObjectNULLDelete snapshot record
OneToOneObjectObjectUpdate if changed, otherwise ignore
OneToManyNULL/EmptyListAdd latest List records
OneToManyListNULLIgnore
OneToManyListEmptyDelete snapshot List records
OneToManyListListCompare and save, calculate add/update/delete actions
Many-to-Many Intermediate TableSame as OneToMany

Insert:

  • OneToOne: Cascading insert
  • OneToMany: Cascading insert
  • ManyToOne: Compare and save external root first, associate external root ID, then insert the aggregate root
  • ManyToMany: Compare and save external root first, insert aggregate root, then insert into the intermediate table

Update:

  • OneToOne: Cascading compare and save
  • OneToMany: Cascading compare and save
  • ManyToOne: Compare and save external root first, associate external root ID, then update the aggregate root
  • ManyToMany: Compare and save external root first, update the aggregate root, then compare and save the intermediate table

Delete:

  • OneToOne: Cascading delete
  • OneToMany: Cascading delete
  • ManyToOne: Ignore
  • ManyToMany: Cascading delete from the intermediate table (note: do not delete the external root)