Delete Data (original) (raw)

中文 | English

Deleting data is a very dangerous operation. FreeSql does not support deletion very powerfully. By default, it only supports single-table and conditional deletion methods.

If it is executed when the Where condition is empty, only 0 or the default value is returned, and no real SQL delete operation is performed.

static IFreeSql fsql = new FreeSql.FreeSqlBuilder() .UseConnectionString(FreeSql.DataType.MySql, connectionString) .UseAutoSyncStructure(true) //Automatically synchronize the entity structure to the database .Build(); //Be sure to define as singleton mode

class Topic { [Column(IsIdentity = true, IsPrimary = true)] public int Id { get; set; } public int Clicks { get; set; } public string Title { get; set; } public DateTime CreateTime { get; set; } }

Dynamic Conditions

fsql.Delete(object dywhere).ExecuteAffrows()

dywhere supports:

Primary key value
new[] { PrimaryKey1, PrimaryKey2 }
Topic Object
new[] { TopicObject1, TopicObject2 }
new { id = 1 }

var t1 = fsql.Delete(new[] { 1, 2 }).ExecuteAffrows(); //DELETE FROM Topic WHERE (Id = 1 OR Id = 2)

var t2 = fsql.Delete(new Topic { Id = 1, Title = "test" }).ExecuteAffrows(); //DELETE FROM Topic WHERE (Id = 1)

var t3 = fsql.Delete(new[] { new Topic { Id = 1, Title = "test" }, new Topic { Id = 2, Title = "test" } }).ExecuteAffrows(); //DELETE FROM Topic WHERE (Id in (1, 2))

var t4 = fsql.Delete(new { id = 1 }).ExecuteAffrows(); //DELETE FROM Topic WHERE (Id = 1)

Dynamic tablename

fsql.Delete(1).AsTable("Topic_201903").ExecuteAffrows();

Delete Conditions

For safety reasons, when there are no conditions, the delete action will not be executed to avoid deleting the entire table data by mistake. Delete the entire table data: fsql.Delete<T>().Where("1=1").ExecuteAffrows()

var t5 = fsql.Delete().Where(a => a.Id == 1).ExecuteAffrows(); //DELETE FROM Topic WHERE (Id = 1)

var t6 = fsql.Delete().Where("id = @id", new { id = 1 }).ExecuteAffrows(); //DELETE FROM Topic WHERE (id = @id)

var item = new Topic { Id = 1, Title = "newtitle" }; var t7 = fsql.Delete().Where(item).ExecuteAffrows(); //DELETE FROM Topic WHERE (Id = 1)

var items = new List(); for (var a = 0; a < 10; a++) items.Add(new Topic { Id = a + 1, Title = $"newtitle{a}", Clicks = a * 100 }); var t8 = fsql.Delete().Where(items).ExecuteAffrows(); //DELETE FROM Topic WHERE (Id IN (1,2,3,4,5,6,7,8,9,10))

Dictionary Delete

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

fsql.DeleteDict(dic).AsTable("table1").ExecuteAffrows();

`ISelect.ToDelete` Advanced Delete

IDelete does not support navigation objects, multi-table association, etc. By default, ISelect.ToDelete can convert the query to IDelete so that the navigation object can be used to delete data:

fsql.Select().Where(a => a.Options.xxx == 1).ToDelete().ExecuteAffrows();

Note: This method is not to query the data to the memory and delete it cyclically. The above code generates and executes the following SQL:

DELETE FROM T1 WHERE id in (select a.id from T1 a left join Options b on b.t1id = a.id where b.xxx = 1)

The benefits of using this method for complex deletion:

Preview data before deleting to prevent mistaken deletion operations;
Support complex deletion operations, for example: Use Limit(10) on ISelect to delete the first 10 records that meet the conditions;

Cascade deletion of IBaseRepository

1、Cascade deletion based on [object]

var groups = repo.Select .IncludeMany(a => a.Users, then => then.Include(b => b.UserExt)) .ToList(); repo.Delete(groups); //Cascade deletion, recursively traversing the navigation properties of group OneToOne/OneToMany/ManyToMany //DELETE FROM "userext" WHERE ("userid" IN (3,4,5)) //DELETE FROM "user" WHERE ("id" IN (3,4,5)) //DELETE FROM "usergroup" WHERE ("id" = 1)

Cascade deletion based on [database]

According to the set navigation properties, recursively delete the corresponding data of OneToOne/OneToMany/ManyToMany, and return the deleted data. This feature does not rely on database foreign keys

var repo = fsql.GetRepository(); var ret = repo.DeleteCascadeByDatabase(a => a.Id == 1); //SELECT a."id", a."username", a."password", a."groupid" FROM "user" a WHERE (a."groupid" = 1) //SELECT a."userid", a."remark" FROM "userext" a WHERE (a."userid" IN (3,4,5)) //DELETE FROM "userext" WHERE ("userid" IN (3,4,5)) //DELETE FROM "user" WHERE ("id" IN (3,4,5)) //DELETE FROM "usergroup" WHERE ("id" = 1)

//ret Count = 7 System.Collections.Generic.List // [0] {UserExt} object {UserExt} // [1] {UserExt} object {UserExt} // [2] {UserExt} object {UserExt} // [3] {User} object {User} // [4] {User} object {User} // [5] {User} object {User} // [6] {UserGroup} object {UserGroup}

public class UserGroup { [Column(IsIdentity = true)] public int Id { get; set; } public string GroupName { get; set; }

[Navigate(nameof(User.GroupId))]
public List<User> Users { get; set; }

} public class User { [Column(IsIdentity = true)] public int Id { get; set; } public string Username { get; set; } public string Password { get; set; } public int GroupId { get; set; }

[Navigate(nameof(Id))]
public UserExt UserExt { get; set; }

} public class UserExt { [Column(IsPrimary = true)] public int UserId { get; set; } public string Remark { get; set; }

[Navigate(nameof(UserId))]
public User User { get; set; }

}

Reference

API

Methods	Return	Parameters	Description
Where		Lambda	Expression conditions, only support entity basic members (not including navigation objects)
Where		string, parms	Raw SQL syntax conditions, Where("id = @id", new { id = 1 })
Where		T1 \| IEnumerable	Pass in the entity or collection, and use its primary key as the condition
CommandTimeout		int	Command timeout setting (seconds)
WithTransaction		DbTransaction	Set transaction object
WithConnection		DbConnection	Set the connection object
ToSql	string	Returns the SQL statement to be executed.
ExecuteAffrows	long	Execute SQL and return the number of rows affected.
ExecuteDeleted	List	Execute SQL and return the deleted records.