Telerik UI for WPF

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

Tip

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.

Copy[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();
Copy[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.

Copy[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();
Copy[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.

Copy[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();
Copy[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.

Copy[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();
Copy[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.

Copy[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();
Copy[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