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. 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.
-
Right-click on your project in the Solution Explorer window and select the menu option Add -> New Item.
-
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.
-
In the Choose Model Contents step, choose the EF Designer from Database option and click the Next button.
-
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.
-
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
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
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
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
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
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()