These examples aim to show the simplest possible ways to get started with the LINQ to SQL in common scenarios.
- Create Database Context for LINQ to SQL
- Get a Single Row by ID with LINQ
- Get All Rows
- Insert a Row and Return The Generated ID
- Update a Row
- Delete a Row
1. Create Database Context for LINQ to SQL
Before any LINQ to SQL -code can be written, you need to create necessary classes and a database connection.
First, add Linq to SQL classes by right-clicking on the project and selecting Add / New Item… Select Linq to SQL Classes and give a good name. For example, AdventureWorksDB.dbml.
Second, add a connection to the database using Server Explorer’s Data Connection. From there, you can drag and drop the tables onto the design surface and classes will be automatically generated.
For more detailed instructions, be sure to read ScottGu’s blog entry about this topic.
2. Get a Single Row by ID with LINQ
When coding typical data driven applications, you need all the time to get single rows. Typically this is accomplished by selecting a row from the table by their primary key.
There are many ways to accomplish with LINQ.
Example 1a:
//Returns a single row from the database
private Product GetSingleRowExample1(int productID)
{
Product product = (from p in ctx.Products
where p.ProductID == productID
select p).First();
return product;
}
With the new lambda expressions, there is a bit shorter alternative:
Example 1b:
//Returns a single row from the database (lambda)
private Product GetSingleRowExample2(int productID)
{
return ctx.Products.Single(p => p.ProductID == productID);
}
3. Get All Rows
The simplest way is to bind the collection directly to the data control.
//Retrieves all product categories
private void GetAllProductCategories()
{
gridResult.DataSource = ctx.ProductCategories;
gridResult.DataBind();
}
4. Insert a Row and Return The Generated ID
When inserting a new row, the generated ID is saved into the instance of the object after the item has been inserted successfully.
protected void btnInsertProductCategory_Click(object sender,
EventArgs e)
{
ProductCategory productCategory = new ProductCategory();
productCategory.Name = “Sample Category”;
productCategory.ModifiedDate = DateTime.Now;
productCategory.rowguid = Guid.NewGuid();
int id = InsertProductCategory(productCategory);
lblResult.Text = id.ToString();
}
//Insert a new product category and return the generated ID (identity value)
private int InsertProductCategory(ProductCategory productCategory)
{
//ctx.ProductCategories.Add(productCategory); **Doesn’t work in RTM!**
ctx.ProductCategories.InsertOnSubmit(productCategory);
ctx.SubmitChanges();
return productCategory.ProductCategoryID;
}
5. Update a Row
The built-in change tracking in LINQ automatically determines, if connecting to the database is necessary. If an object is modified, LINQ then generates the necessary UPDATE-statemens automatically.
//Retrieves and updates product category with ID of 4
protected void btnUpdateProductCategory_Click(object sender, EventArgs e)
{
ProductCategory productCategory =
ctx.ProductCategories.Single(pc => pc.ProductCategoryID == 4);
UpdateProductCategory(productCategory);
}
//Updates the given product category
private void UpdateProductCategory(ProductCategory productCategory)
{
productCategory.ModifiedDate = DateTime.Now;
productCategory.Name = productCategory.Name + ” test”;
ctx.SubmitChanges();
}
6. Delete a Row
Deleting a row is simple. You could also remove several rows with the DeleteAllOnSubmit-method.
protected void btnDeleteProductCategory_Click(object sender,
EventArgs e)
{
ProductCategory productCategory = ctx.ProductCategories
.Single(pc => pc.ProductCategoryID == 5);
DeleteProductCategory(productCategory);
}
//Deletes the given product category
private void DeleteProductCategory(ProductCategory productCategory)
{
//ctx.ProductCategories.Remove(productCategory);
//** “Remove” doesn’t work in the RTM!**
ctx.ProductCategories.DeleteOnSubmit(productCategory);
ctx.SubmitChanges();
}
For more examples about modifying data with LINQ, read ScottGu’s post.
January 18, 2008 at 6:43 pm
I think there’s something wrong with you Insert operation.
You can not get the ID of the object has been inserted to database in this way.
Could you please consider it again and confirm it for me by writting another reply or email to me ?
I really need a way to get ID of the object after insert it datatabase without using store procedure.
Thank you
January 18, 2008 at 10:22 pm
Quan: You are correct, that the previous sample didn’t work due to the changes between the Beta 2 and RTM. I fixed the examples and now the insertion of object should work as described.
Note that the insertion sample expects that the primary key column is an identity column that automatically generates the primary key in the SQL Server.
Thank you for commenting and notifying me about the error
January 28, 2008 at 2:26 am
A very helpful post! I’m just using LINQ to Sql in commercial code for the first time, so these kind of summaries are extremely usful. Thanks a lot.
January 28, 2008 at 9:01 am
Matthew:
Thanks for your feedback and good luck with the project!
When I began to work with LINQ I had needed the same info and blogged it as a reference for myself thinking it could be useful for others, too.
LINQ makes it fun to code the previously repetitive parts. It’s nice that LINQ to SQL is just one way to use the powerful language feature. Once it is familiar, it’ll be easy to use LINQ to XML, LINQ to objects etc and there are even LINQ providers for the Amazon and Terraserver!
February 1, 2008 at 9:49 am
Exactly the quick ref page I needed. Thanks mate.
February 4, 2008 at 8:38 am
[...] http://blog.jemm.net/articles/databases/how-to-common-data-patterns-with-linq-to-sql/ [...]
March 23, 2008 at 4:58 pm
Hello everybody, my name is Damion, and I’m glad to join your conmunity,
and wish to assit as far as possible.
May 2, 2008 at 5:34 pm
That was really a quick ref - Thanks