Linq to Sql Delete

LINQ to SQL
How to: Delete Rows From the Database (LINQ to SQL)

You can delete rows in a database by removing the corresponding LINQ to SQL objects from their table-related collection. LINQ to SQL translates your changes to the appropriate SQL DELETE commands.

LINQ to SQL does not support or recognize cascade-delete operations. If you want to delete a row in a table that has constraints against it, you must complete either of the following tasks:

•Set the ON DELETE CASCADE rule in the foreign-key constraint in the database.

•Use your own code to first delete the child objects that prevent the parent object from being deleted.

Otherwise, an exception is thrown. See the second code example later in this topic.

Note
You can override LINQ to SQL default methods for Insert, Update, and Delete database operations. For more information, see Customizing Insert, Update, and Delete Operations (LINQ to SQL).

Developers using Visual Studio can use the Object Relational Designer to develop stored procedures for the same purpose. For more information, see Object Relational Designer (O/R Designer) and Object Relational Designer (O/R Designer).


The following steps assume that a valid DataContext connects you to the Northwind database. For more information, see How to: Connect to a Database (LINQ to SQL).

To delete a row in the database
1.Query the database for the row to be deleted.

2.Call the DeleteOnSubmit method.

3.Submit the change to the database.

This first code example queries the database for order details that belong to Order #11000, marks these order details for deletion, and submits these changes to the database.

// Query the database for the rows to be deleted.
var deleteOrderDetails =
from details in db.OrderDetails
where details.OrderID == 11000
select details;

foreach (var detail in deleteOrderDetails)
{
db.OrderDetails.DeleteOnSubmit(detail);
}

try
{
db.SubmitChanges();
}
catch (Exception e)
{
Console.WriteLine(e);
// Provide for exceptions.
}


In this second example, the objective is to remove an order (#10250). The code first examines the OrderDetails table to see whether the order to be removed has children there. If the order has children, first the children and then the order are marked for removal. The DataContext puts the actual deletes in correct order so that delete commands sent to the database abide by the database constraints.


Northwnd db = new Northwnd(@"c:\northwnd.mdf");

db.Log = Console.Out;

// Specify order to be removed from database
int reqOrder = 10250;

// Fetch OrderDetails for requested order.
var ordDetailQuery =
from odq in db.OrderDetails
where odq.OrderID == reqOrder
select odq;

foreach (var selectedDetail in ordDetailQuery)
{
Console.WriteLine(selectedDetail.Product.ProductID);
db.OrderDetails.DeleteOnSubmit(selectedDetail);
}

// Display progress.
Console.WriteLine("detail section finished.");
Console.ReadLine();

// Determine from Detail collection whether parent exists.
if (ordDetailQuery.Any())
{
Console.WriteLine("The parent is presesnt in the Orders collection.");
// Fetch Order.
try
{
var ordFetch =
(from ofetch in db.Orders
where ofetch.OrderID == reqOrder
select ofetch).First();
db.Orders.DeleteOnSubmit(ordFetch);
Console.WriteLine("{0} OrderID is marked for deletion.", ordFetch.OrderID);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.ReadLine();
}
}
else
{
Console.WriteLine("There was no parent in the Orders collection.");
}


// Display progress.
Console.WriteLine("Order section finished.");
Console.ReadLine();

try
{
db.SubmitChanges();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.ReadLine();
}

// Display progress.
Console.WriteLine("Submit finished.");
Console.ReadLine();

LINQ to SQL Select ,Insert,Update,Delete Query

Selecting
--------------------------------------------------------------------------------

Selecting (projection) is achieved by just writing a LINQ query in your own programming language, and then executing that query to retrieve the results. LINQ to SQL itself translates all the necessary operations into the necessary SQL operations that you are familiar with. For more information, see LINQ to SQL.

In the following example, the company names of customers from London are retrieved and displayed in the console window.


// Northwnd inherits from System.Data.Linq.DataContext.
Northwnd nw = new Northwnd(@"northwnd.mdf");
// or, if you are not using SQL Server Express
// Northwnd nw = new Northwnd("Database=Northwind;Server=server_name;Integrated Security=SSPI");

var companyNameQuery =
from cust in nw.Customers
where cust.City == "London"
select cust.CompanyName;

foreach (var customer in companyNameQuery)
{
Console.WriteLine(customer);
}

Inserting
--------------------------------------------------------------------------------

To execute a SQL Insert, just add objects to the object model you have created, and call SubmitChanges on the DataContext.

In the following example, a new customer and information about the customer is added to the Customers table by using InsertOnSubmit.

// Northwnd inherits from System.Data.Linq.DataContext.
Northwnd nw = new Northwnd(@"northwnd.mdf");

Customer cust = new Customer();
cust.CompanyName = "SomeCompany";
cust.City = "London";
cust.CustomerID = "98128";
cust.PostalCode = "55555";
cust.Phone = "555-555-5555";
nw.Customers.InsertOnSubmit(cust);

// At this point, the new Customer object is added in the object model.
// In LINQ to SQL, the change is not sent to the database until
// SubmitChanges is called.
nw.SubmitChanges();

Updating
--------------------------------------------------------------------------------

To Update a database entry, first retrieve the item and edit it directly in the object model. After you have modified the object, call SubmitChanges on the DataContext to update the database.

In the following example, all customers who are from London are retrieved. Then the name of the city is changed from "London" to "London - Metro". Finally, SubmitChanges is called to send the changes to the database.


Northwnd nw = new Northwnd(@"northwnd.mdf");

var cityNameQuery =
from cust in nw.Customers
where cust.City.Contains("London")
select cust;

foreach (var customer in cityNameQuery)
{
if (customer.City == "London")
{
customer.City = "London - Metro";
}
}
nw.SubmitChanges();