This article goes on to the previous article: query to SQL and add to and update to SQL. Taking northwind as an example, we will introduce how to delete data using LINQ to SQL.
First, take a row of data in the MERs table as an example to delete the data:
NorthwindDataContext ctx = new NorthwindDataContext(); Customer test1 = ctx.Customers.Single(c => c.CustomerID == "TEST1"); ctx.Customers.Remove(test1); ctx.SubmitChanges();
By viewing the MERs table in the database, you can find that the data has been deleted. Next, let's look at the following code:
NorthwindDataContext ctx = new NorthwindDataContext(); Customer test1 = ctx.Customers.Single(c => c.CustomerID == "TEST1"); ctx.Customers.Remove(test1); ctx.SubmitChanges(); test1 = ctx.Customers.Single(c => c.CustomerID == "TEST1"); Console.WriteLine(test1.CustomerID);
Delete a row of data whose customerid is "test1" and then query the data in the database. Theoretically, the data in the database does not exist and there should be no results. However, the screen output is "test1", which is the customerid of the deleted customer. Is it strange that the data in the database does not exist, but the query can still get the correct result. In fact, the reason is also very simple. Although the data in the database has been deleted, the identity cache in datacontext also stores references to this object (what is identity cache, as explained above), the query results are the objects cached in datacontext rather than in the database. You can know that if you query the data in another datacontext, it is certainly not found.
The following section describes how to perform cascading deletion in LINQ to SQL. Take MERs and orders as an example:
NorthwindDataContext ctx = new NorthwindDataContext(); Customer test1 = ctx.Customers.Single(c => c.CustomerID == "TEST1"); Order order1 = test1.Orders.Single(o => o.ShipCity == "Shanghai"); test1.Orders.Remove(order1); ctx.SubmitChanges();
In this example, you want to delete the order in which shipcity is Shanghai in the order of the customer whose customerid is "test1. Run this code. You can see that the SQL profile does not run Delete from orders... the SQL statement is update, but the customerid of the record in the orders table is set to null. The relationship between the record and the customer is deleted, but the record is not actually deleted. To delete a record, you must use datacontext:
ctx.Orders.Remove(order1); ctx.SubmitChanges();
This is a noteworthy issue during the deletion process.
To delete the customer and the related order, perform this operation (you can also set cascade deletion in the database ):
NorthwindDataContext ctx = new NorthwindDataContext(); Customer test1 = ctx.Customers.Single(c => c.CustomerID == "TEST1"); foreach (Order o in test1.Orders) { test1.Orders.Remove(o); ctx.Orders.Remove(o); } ctx.Customers.Remove(test1); ctx.SubmitChanges();
When you delete data, you will also encounter conflicts like data updates. The solution is basically the same.