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:

• Create the ADO.NET Entity Data Model

• Query an entity from the database

• Update an entity in the database

• Insert a new record(s) in the database

• Delete a record from the database

• Retrieve a record with Server Side Paging

This tutorial uses the Northwind database. You can get a SQL Query for the whole database from here. You can use Microsoft SQL Management Studio to execute the query.

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.

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. 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.

   

3. In the Choose Model Contents step, choose the EF Designer from Database option and click the Next button.

   

4. 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.

   

5. 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.

Example 1: Query Product by CategoryName

• C#
• VB

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

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.

Example 2: Update UnitPrice of Product

• C#
• VB

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

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.

Example 3: Insert Products with new Category

• C#
• VB

public partial class MainWindow : Window 
{ 
    public MainWindow() 
    { 
        InitializeComponent(); 
        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(); 
    } 
} 

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.

Example 4: Delete a record based on a condition

• C#
• VB

public MainWindow() 
{ 
    InitializeComponent(); 
    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(); 
} 

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.

Example 5:Server-side Paging

• C#
• VB

public MainWindow() 
{ 
    InitializeComponent(); 
    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(); 
} 

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