Note: Most of my examples are from "ScottGu's Blog", I love to read them always :)
What Is LINQ to SQL?
LINQ to SQL is an O/RM (object relational mapping) implementation that ships in the .NET Framework"Orcas" release, and which allows you to model a relational database using .NET classes. You can thenquery the database using LINQ, as well as update/insert/delete data from it.LINQ to SQL fully supports transactions, views, and stored procedures. It also provides an easy way tointegrate data validation and business logic rules into your data model.
Understanding the DataContext Class:
hen you press the "save" button within the LINQ to SQL designer surface, Visual Studio will persistout .NET classes that represent the entities and database relationships that we modeled. For each LINQto SQL designer file added to our solution, a custom DataContext class will also be generated. This DataContext class is the main conduit by which we'll query entities from the database as well as apply changes. The DataContext class created will have properties that represent each Table we modeled within the database, as well as methods for each Stored Procedure we added.
LINQ to SQL Code Examples
Once we've modeled our database using the LINQ to SQL designer, we can then easily write code to work
against it. Below are a few code examples that show off common data tasks:
1) Query Products From the Database (Northwind database)
MyDataContext db = new MyDataContext();
var products = from p in db.products where p.Category.categoryName =="Beverages" select p;
2) Update a Product in the Database (Northwind database)
MyDataContext db = new MyDataContext();
var product = db.products.singleorDefault(p=> p.ProductName == "Toy1");
product.UnitPrice = 100;
product.UnitsInStock = 40;db.SubmitChanges();
3)Insert a New Category and Two New Products into the Northwind Database
The code below demonstrates how to create a new category, and then create two new products and
associate them with the category. All three are then saved into the database.
Note below how I don't need to manually manage the primary key/foreign key relationships. Instead, just
by adding the Product objects into the category's "Products" collection, and then by adding the Category
object into the DataContext's "Categories" collection, LINQ to SQL will know to automatically persist the
appropriate PK/FK relationships for me.
MyDataContext db = new MyDataContext();
//Create new category and products
var category = new Category();
category.CategoryName = "Chaitanya's toys";
var product1 = new Product();
product1.ProductName="toy1";
var prosduct2 = new Product();
product2.ProductName="toy2";
//Associate products with Category
category.Products.Add(product1);
category.Products.Add(product2);
//Add Category to database
db.Categories.Add(category);
db.SubmitChanges();
