What You Can Do With LINQ to SQL

LINQ to SQL supports all the key capabilities you would expect as a SQL developer. You can query for information, and insert, update, and delete information from tables.

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,

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

Visual Basic

 
' Northwnd inherits from System.Data.Linq.DataContext.
Dim nw As New Northwnd("c:\northwnd.mdf")

Dim companyNameQuery = _
 From cust In nw.Customers _
 Where cust.City = "London" _
 Select cust.CompanyName

For Each customer In companyNameQuery
 Console.WriteLine(customer)
Next

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

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.
Dim nw As New Northwnd("c:\northwnd.mdf")

Dim cust As New Customer With {.CompanyName = "SomeCompany", _
 .City = "London", _
 .CustomerID = 98128, _
 .PostalCode = 55555, .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()

C# 
// 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.

Visual Basic

 
Dim nw As New Northwnd("c:\northwnd.mdf")
Dim cityNameQuery = _
 From cust In nw.Customers _
 Where cust.City.Contains("London") _
 Select cust

For Each customer In cityNameQuery
 If customer.City = "London" Then
     customer.City = "London - Metro"
 End If
Next
nw.SubmitChanges()

C#

 
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();

Deleting

To Delete an item, remove the item from the collection to which it belongs, and then call SubmitChanges on the DataContext to commit the change.

Note:

LINQ to SQL does not recognize cascade-delete operations. If you want to delete a row in a table that has constraints against it.

In the following example, the customer who has CustomerID of 98128 is retrieved from the database. Then, after confirming that the customer row was retrieved, DeleteOnSubmit is called to remove that object from the collection. Finally, SubmitChanges is called to forward the deletion to the database.

Visual Basic 
Dim nw As New Northwnd("c:\northwnd.mdf")
Dim deleteIndivCust = _
 From cust In nw.Customers _
 Where cust.CustomerID = 98128 _
 Select cust

If deleteIndivCust.Count > 0 Then
 nw.Customers.DeleteOnSubmit(deleteIndivCust.First)
 nw.SubmitChanges()
End If


 
Northwnd nw = new Northwnd(@"northwnd.mdf");
var deleteIndivCust =
 from cust in nw.Customers
 where cust.CustomerID == "98128"
 select cust;

if (deleteIndivCust.Count() > 0)
{
 nw.Customers.DeleteOnSubmit(deleteIndivCust.First());
 nw.SubmitChanges();
}

Comments

Post a Comment

Popular posts from this blog

JavaScript Interview Questions

What are the possible ways to create objects in JavaScript There are many ways to create objects in javascript as mentioned below: Object literal syntax: The object literal syntax (or object initializer), is a comma-separated set of name-value pairs wrapped in curly braces. var object = { name : "Amit" , age : 40 } ; Object literal property values can be of any data type, including array, function, and nested object. Note:  This is one of the easiest ways to create an object. Object constructor: The simplest way to create an empty object is using the  Object  constructor. Currently this approach is not recommended. var object = new Object ( ) ; The  Object()  is a built-in constructor function so "new" keyword is not required. The above code snippet can be re-written as: var object = Object ( ) ; Object's create method: The  create  method of Object is used to create a new object by passing the specificied prototype object and p...
Read more

WCF Overview

Image
WCF is a .NET platform for developing services .Service is any functionality that can be consumed by the outside world. A service defines well defined communication protocols to communicate with it. A service can be located anywhere , it could be on local machine on the intranet or even internet.A client consumes the functionality exposed by the service.A service can be anything it can be an independent application like asp.net or it can be another service. Client and Service communicate with each other by sending messages. SOAP Message SOAP Message Client HTTP/TCP /MSMQ HTTP/TCP/MSMQ Server Figure: Basic Client Server Communication Since the functionality of the service is hidden to the outside world. Service exposes metadata that client uses to communicate with the service. Metadata can be in any technology neutral format like wsdl. Client always uses proxy to communicate with the service irrespective of where service is located. In WCF every service has endpoints and the cli...
Read more