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. Before trying out any of the examples below you need to create a new ADO.NET Data Model. The topic is described in more details in MSDN here.

Add a new ADO.NET Data Model.

Figure 1: Add ADO.NET Entity Data Model
Common Linq To Ado Net 010

Using the ADO.NET Entity Framework wizard you can easily create a representation of the sample Northwind database like the one show in Figure 2:

Figure 2: Nortwind in Entity Designer
Common Linq To Ado Net 020

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.ProductSet
            where p.Categories.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.ProductSet _
    Where p.Categories.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.ProductSet.Single( p => p.ProductName == "Aniseed Syrup" );
product.UnitPrice = 1000;
dbContext.SaveChanges();

[VB] Example 2: Update UnitPrice of Product

Dim dbContext As New NorthwindEntities()
Dim query = From p In dbContext.ProductSet_
            Where p.ProductName = "Aniseed Syrup" _
            Select p
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.AddToCategorySet( 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.AddToCategorySet(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.ProductSet
            where p.ProductName.Contains( "Test" )
            select p;
foreach ( Product p in query )
    dbContext.DeleteObject( p );
dbContext.SaveChanges();

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

Dim dbContext As New NorthwindEntities()
Dim query = From p In dbContext.ProductSet _
    Where p.ProductName.Contains("Test") _
    Select p
For Each p As Product In query
    dbContext.DeleteObject(p)
Next
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.ProductSet
              where p.Categories.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.ProductSet _
    Where p.Categories.CategoryName = "Aniseed Syrup" _
    Select p).Skip(300).Take(15)
Dim products As IEnumerable(Of Product) = query.ToList()

See Also