Deleting Entities

In This Topic

This topic describes the ways one can use to delete rows in the database, the related model configurations and the actions that implicitly lead to deleting data.

DeleteOnSubmit and DeleteAllOnSubmit

As all CUD operations in LinqConnect are performed during calls to the SubmitChanges method of DataContext, a developer should mark an entity object for deletion so that the context delete the corresponding row from the database. The main (and the only explicit) way this is done is to call the DeleteOnSubmit or DeleteAllOnSubmit method.

These methods take an entity object or a collection of entities respectively. Evidently, this means that an entity (or entities) has to be materialized to be deleted. For example, we can fetch it from the database:

C#csharp	Copy Code
CrmDemoDataContext context = new CrmDemoDataContext() { Log = Console.Out }; Order order = context.Orders .Where(o => o.OrderID == 10) .SingleOrDefault(); context.Orders.DeleteOnSubmit(order); context.SubmitChanges();

C#csharp

Copy Code

CrmDemoDataContext context =
    new CrmDemoDataContext() { Log = Console.Out };
 
Order order = context.Orders
    .Where(o => o.OrderID == 10)
    .SingleOrDefault();
context.Orders.DeleteOnSubmit(order);
context.SubmitChanges();

Visual Basic	Copy Code
Dim context As New CrmDemoDataContext() With { _ .Log = Console.Out _ } Dim order As Order = context.Orders _ .Where(Function(o) o.OrderID = 10) _ .SingleOrDefault() context.Orders.DeleteOnSubmit(order) context.SubmitChanges()

Visual Basic

Copy Code

Dim context As New CrmDemoDataContext() With { _
    .Log = Console.Out _
}
 
Dim order As Order = context.Orders _
    .Where(Function(o) o.OrderID = 10) _
    .SingleOrDefault()
context.Orders.DeleteOnSubmit(order)
context.SubmitChanges()

In the sample, we

get an order with a particular ID from the database;
mark it for deletion via the DeleteOnSubmit method;
execute the SubmitChanges method.

An actual SQL Delete command is executed at the latter step; we enable logging in the sample so that you can check this command.

Only entities that are either attached or marked for insertion can be passed to the DeleteOnSubmit and DeleteAllOnSubmit; otherwise, InvalidOperationException is thrown. The requirement of the entity being attached is set as a part of the object tracking approach, and the second option is allowed as a way to cancel the insertion (i.e., no actual delete is executed in this case).

Thus, if you've got an entity object in some other way than by querying for it, you should attach it to the context before deleting. For more information about this, refer to the Deleting a Detached Entity paragraph in this article.

The DeleteAllOnSubmit method works in the same way as DeleteOnSubmit except that a collection should be passed instead of a single entity. For example, you can clear the whole table in the following way:

C#csharp	Copy Code
CrmDemoDataContext context = new CrmDemoDataContext() { Log = Console.Out }; context.Orderdetails.DeleteAllOnSubmit(context.Orderdetails); context.SubmitChanges();

Visual Basic	Copy Code
Dim context As New CrmDemoDataContext() With { _ .Log = Console.Out _ } context.Orderdetails.DeleteAllOnSubmit(context.Orderdetails) context.SubmitChanges()

As you can see from the generated SQL, all entities are deleted one-by-one. This is a part of the object-oriented approach implemented in LinqConnect: the runtime should be informed about the rows affected during the update operations to do necessary modifications to the corresponding objects (it wouldn't happen if the condition used in the delete command was different). To make executing multiple separated deletions faster, LinqConnect unites them in batches. And if you do want to delete multiple rows with a single command, or delete a row by just a primary key (to avoid getting the object from the database), you can use the ExecuteCommand method of DataContext.

Deleting a Detached Entity

As it was said, the DeleteOnSubmit and DeleteAllOnSubmit methods take entities that are attached to the current context as arguments. However, it is possible that you get an entity object that should be deleted in some other way than by querying for it via the same context. For example, you could get it from a data service, or created it manually to avoid unnecessary round-trip to the server.

In this case, you have to Attach this object to the context before calling the DeleteOnSubmit method:

C#csharp	Copy Code
CrmDemoDataContext context = new CrmDemoDataContext() { Log = Console.Out }; Order orderToDelete = someOrder; // Here we get an Order object in any way. context.Orders.Attach(orderToDelete); context.Orders.DeleteOnSubmit(orderToDelete); context.SubmitChanges();

C#csharp

Copy Code

CrmDemoDataContext context =
    new CrmDemoDataContext() { Log = Console.Out };
 
Order orderToDelete = someOrder; // Here we get an Order object in any way.
 
context.Orders.Attach(orderToDelete);
context.Orders.DeleteOnSubmit(orderToDelete);
context.SubmitChanges();

Visual Basic	Copy Code
Dim context As New CrmDemoDataContext() With { _ .Log = Console.Out _ } Dim orderToDelete As Order = someOrder ' Here we get an Order object in any way. context.Orders.Attach(orderToDelete) context.Orders.DeleteOnSubmit(orderToDelete) context.SubmitChanges()

Visual Basic

Copy Code

Dim context As New CrmDemoDataContext() With { _
    .Log = Console.Out _
}
 
Dim orderToDelete As Order = someOrder
' Here we get an Order object in any way.
context.Orders.Attach(orderToDelete)
context.Orders.DeleteOnSubmit(orderToDelete)
context.SubmitChanges()

In this sample, we get an order in whatever way. For example, we can just create it from scratch and set its fields to the values currently available in the database (or we can omit some of the fields, if their UpdateCheck properties are not set to 'Always').

After that, we attach the Order object to the context. At this moment the context checks that there is no other Order with the same entity key attached, and enables change tracking for orderToDelete.

After attaching, this Order is deleted in the general way.

Deleting Dependent Entities

Delete on null

It is a common situation for one-to-many and one-to-one associations that, from the conceptual point of view, there is no need in 'dependent' entities having no 'master'. For example, this is the case for the 'Order Details' > Order relationship in the CRM Demo sample database: if an OrderDetail object doesn't point to an Order one, it makes no sense. Thus, the data corresponding to such entities should be wiped from the database. To ensure that all 'orphan' entities are deleted whenever they are detached from the 'parent' ones, you can set the 'Delete On Null' association property to true:

For example, in the following code we remove the OrderDetail items that point to a particular product from the corresponding collection of a certain order:

C#csharp	Copy Code
var order = context.Orders .Where(o => o.OrderID == 10) .SingleOrDefault(); if (order != null) { var orderDetails = order.Orderdetails .Where(od => od.ProductID == 7319); foreach (Orderdetail detail in orderDetails.ToList()) order.Orderdetails.Remove(detail); context.SubmitChanges(); }

C#csharp

Copy Code

var order = context.Orders
    .Where(o => o.OrderID == 10)
    .SingleOrDefault();
if (order != null)
{
    var orderDetails = order.Orderdetails
        .Where(od => od.ProductID == 7319);
    foreach (Orderdetail detail in orderDetails.ToList())
        order.Orderdetails.Remove(detail);
    context.SubmitChanges();
}

Visual Basic	Copy Code
Dim order = context.Orders _ .Where(Function(o) o.OrderID = 10) _ .SingleOrDefault() If order IsNot Nothing Then Dim orderDetails = order.Orderdetails _ .Where(Function(od) od.ProductID = 7319) For Each detail As Orderdetail In orderDetails.ToList() order.Orderdetails.Remove(detail) Next context.SubmitChanges() End If

Visual Basic

Copy Code

Dim order = context.Orders _
    .Where(Function(o) o.OrderID = 10) _
    .SingleOrDefault()
If order IsNot Nothing Then
    Dim orderDetails = order.Orderdetails _
        .Where(Function(od) od.ProductID = 7319)
    For Each detail As Orderdetail In orderDetails.ToList()
        order.Orderdetails.Remove(detail)
    Next
    context.SubmitChanges()
End If

Here we use the ToList() method on orderDetails to work with a copy instead of the original collection; this is done to avoid the 'Collection was modified during enumeration.' exception. If the DeleteOnNull option is enabled, the sample code will result in deleting all the details being removed from the OrderDetails collection.

The DeleteOnNull option can be enabled only for one-to-one and one-to-many associations with the non-nullable foreign key field(s). E.g., the OrderId field of the OrderDetail entity type is non-nullable, as it is a part of the entity key.

[email protected]