LinQ sample Queries to Database?

Query the Customers from Database
//creating the datacontext instance
NorthwindDataClassesDataContext dc = new NorthwindDataClassesDataContext();

//Sample 1 : query all customers
var customers =
from c in dc.Customers
select c;

//display query result in a dataGridView
dataGridResult.DataSource = customers.ToList();
Query the Customers with a Simple Statement
//Sample 2 : query customers which country is UK
//constructing the query
var customers_from_uk =
from c in dc.Customers
where c.Country == "UK"
select c;

Query Multiple Tables
//Sample 3 : query customers and products
//(it makes a cross product it do not represent anything else than a query
var customers_and_product =
from c in dc.Customers
from p in dc.Products
where c.ContactName.StartsWith("A") && p.ProductName.StartsWith("P")
select new { Name = c.ContactName, Product = p.ProductName };
The resulting collection is the cross product between all contact names starting with "A" and all products starting with "P."

Query with Tables Joined
//Sample 4 : query customers and orders
var customers_and_orders =
from c in dc.Customers
from p in dc.Orders
where c.CustomerID == p.CustomerID
select new { c.ContactName, p.OrderID};

Query in the Old Way: with SQL as String
As you may want to execute SQL that is not yet supported by LINQ to SQL, a way to execute SQL queries in the old way is available.

//Sample 5 : execute SQL queries
dc.ExecuteCommand("UPDATE Customers SET PostalCode='05024' where CustomerId='ALFKI' ");
Insert


Insert, Update and Delete Rows from Database
LINQ to SQL provides a new way of managing data into database. The three SQL statements INSERT, DELETE and UPDATE are implemented, but using them is not visible.

Update Statement
//Sample 6 : updating data
var customers_in_paris =
from c in dc.Customers
where c.City.StartsWith("Paris")
select c;

foreach (var cust in customers_in_paris)
cust.City = "PARIS";

//modification to database are applied when SubmitChanges is called.
dc.SubmitChanges();
To make modifications to a database, just modify any relevant object properties and call the method SubmitChanges().

Insert Statement
To insert a new entry into the database, you just have to create an instance of a C# class and Attach it to the associated table.

//Sample 7 : inserting data
Product newProduct = new Product();
newProduct.ProductName = "RC helicopter";

dc.Products.Attach(newProduct);

dc.SubmitChanges();
Delete Statement
Deleting data is quite easy. When requesting your database, give a collection of data. Then just call DeleteOnSubmit (or DeleteAllOnSubmit) to delete the specified items.

//Sample 8 : deleting data
var products_to_delete =
from p in dc.Products
where p.ProductName.Contains("helicopter")
select p;

dc.Products.DeleteAllOnSubmit(products_to_delete);

dc.SubmitChanges();

Order of Operations
To use LINQ to SQL, a developer must know exactly when a query is executed. Indeed, LINQ to SQL is very powerful because the query is executed when it's required, but not at definition! In the first sample, we have this code:

///constructing the query
var customers =
from c in dc.Customers
select c;
The query is not yet executed; it is just compiled and analysed. In fact, the query is run when the code makes an access to the customer variable, like here:

//display query result in a dataGridView
dataGridResult.DataSource = customers.ToList();