Edit this page

Using LINQ to ADO.NET Entity Data Model

The purpose of this article is to show you how to use LINQ against ADO.NET Entity Data Model. It will cover how to:

This tutorial uses the Northwind database, which can be downloaded from here.

Create the ADO.NET Entity Data Model

You can take advantage of the Visual Studio Entity Data Model Wizard to generate an Entity Data Model from the Northwind database automatically.

To do so, follow these steps:

  1. Right-click on your project in the Solution Explorer window and select the menu option Add -> New Item.
  2. In the Add New Item dialog, select the Data category.
  3. Select the ADO.NET Entity Data Model template, give the Entity Data Model the name Northwind.edmx, and click the Add button. Clicking the Add button launches the Data Model Wizard.
  4. In the Choose Model Contents step, choose the EF Designer from Database option and click the Next button.
  5. In the Choose Your Data Connection step, select the Northwind.mdf database connection, enter the entities connection settings name NorthwindEntities, and click the Next button.
  6. In the Choose Your Database Objects and Settings step, select the desired database tables, specify the model namespace and click the Finish button.

Query an Entity from the Database

The code in Example 1 shows how to use LINQ query syntax to retrieve an IEnumerable sequence of Product objects.

[C#] Example 1: Query Product by CategoryName

NorthwindEntities dbContext = new NorthwindEntities();
var query = from p in dbContext.Products
            where p.Category.CategoryName == "Seafood"
            select p;
IEnumerable<Product> products = query.ToList();

[VB] Example 1: Query Product by CategoryName

Dim dbContext As New NorthwindEntities()
Dim query = From p In dbContext.Products
    Where p.Category.CategoryName = "Seafood"
    Select p
Dim products As IEnumerable(Of Product) = query.ToList()

Update an Entity in the Database

The code in Example 2 demonstrates how to grab a single Product object from the database, update its price, and then save the changes back to the database.

[C#] Example 2: Update UnitPrice of Product

NorthwindEntities dbContext = new NorthwindEntities();
Product product = dbContext.Products.First(p => p.ProductName == "Aniseed Syrup");
product.UnitPrice = 1000;
dbContext.SaveChanges();

[VB] Example 2: Update UnitPrice of Product

Dim dbContext As New NorthwindEntities()
Dim product As Product = dbContext.Products.First(Function(p) p.ProductName = "Aniseed Syrup")
product.UnitPrice = 1000
dbContext.SaveChanges()

Insert a New Record(s) in the Database

The code in Example 3 shows you how to create a new Category object. Then, it shows how to create two new Products and associate them with the Category. Finally, all three objects are saved in the database.

[C#] Example 3: Insert Products with new Category

NorthwindEntities dbContext = new NorthwindEntities();

Category category = new Category();
category.CategoryName = "Test Category";

Product firstProduct = new Product();
firstProduct.ProductName = "Test Product 1";

Product secondProduct = new Product();
secondProduct.ProductName = "Test Product 2";

category.Products.Add( firstProduct );
category.Products.Add( secondProduct );

dbContext.Categories.Add(category);
dbContext.SaveChanges();

[VB] Example 3: Insert Products with new Category

Dim dbContext As New NorthwindEntities()

Dim category As New Category()
category.CategoryName = "Test Category"

Dim firstProduct As New Product()
firstProduct.ProductName = "Test Product 1"

Dim secondProduct As New Product()
secondProduct.ProductName = "Test Product 2"

category.Products.Add(firstProduct)
category.Products.Add(secondProduct)

dbContext.Categories.Add(category)
dbContext.SaveChanges()

Delete a Record from the Database

Example 4 demonstrates you how to delete all 'Test' products from the database.

[C#] Example 4: Delete a record based on a condition

NorthwindEntities dbContext = new NorthwindEntities();
var query = from p in dbContext.Products
            where p.ProductName.Contains("Test")
            select p;
foreach (Product p in query)
{
    dbContext.Products.Remove(p);
}
dbContext.SaveChanges();

[VB] Example 4: Delete a record based on a condition

Dim dbContext As New NorthwindEntities()
Dim query = From p In dbContext.Products
            Where p.ProductName.Contains("Test")
            Select p
For Each p As Product In query
    dbContext.Products.Remove(p)
Next p
dbContext.SaveChanges()

Retrieve a Record with Server-side Paging

Example 5 shows you how to implement efficient server-side database paging. By using the Skip() and Take() methods, you will return 15 rows from the database - starting with row 300.

[C#] Example 5:Server-side Paging

NorthwindEntities dbContext = new NorthwindEntities();
var query = (from p in dbContext.Products
            where p.Category.CategoryName == "Aniseed Syrup"
            select p).Skip(300).Take(15);
IEnumerable<Product> products = query.ToList();

[VB] Example 5:Server-side Paging

Dim dbContext As New NorthwindEntities()
Dim query = (From p In dbContext.Products
            Where p.Category.CategoryName = "Aniseed Syrup"
            Select p).Skip(300).Take(15)
Dim products As IEnumerable(Of Product) = query.ToList()

See Also