WCF Multi-layer Services Development with Entity Framework Fourth Edition (2014)
Chapter 7. LINQ to Entities – Basic Concepts and Features
In the previous chapters, we learned how to create a three-layer WCF service. In this and the following chapters, we will learn how to use LINQ to query a database, or in other words, how to use LINQ to Entities in C#. After reading these two chapters, we will have a good understanding of LINQ to Entities so that we can rewrite the data access layer of our WCF service with LINQ to Entities to securely and reliably communicate with the underlying database.
In this chapter, we will cover the following topics:
· LINQ to Entities
· Creating a LINQ to Entities test application
· Creating the data model
· Querying and updating a database table
· Viewing generated SQL statements
· Deferred execution
· Deferred loading versus eager loading
· Joining two tables
· Querying a view
In the next chapter, we will cover the advanced concepts and features of LINQ to Entities such as stored procedure support, simultaneous updating, and transaction processing.
LINQ to Entities
LINQ to Entities provides the LINQ support that enables developers to write queries against Entity Framework's conceptual model using Visual Basic or Visual C#. Queries against Entity Framework are represented by command-tree queries, which execute against the object context. LINQ to Entities converts the LINQ queries to the command-tree queries, executes the queries against Entity Framework, and returns objects that can be used by both Entity Framework and LINQ.
LINQ to Entities allows developers to create flexible, strongly typed queries against the conceptual data model, Entity Data Model (EDM), by using the LINQ expressions and standard LINQ query operators.
Creating a LINQ to Entities test application
Now, let's start exploring LINQ to Entities with some examples. We will apply the skills we are going to learn in this chapter and in Chapter 8, LINQ to Entities – Advanced Concepts and Features, to the data access layer of our WCF service, so that from the WCF service we can communicate with the database using LINQ to Entities instead of the raw ADO.NET data adapter.
First, we need to create a new project to test LINQ to Entities. Just follow these steps to create this test application:
1. Start Visual Studio, select the menu options FILE | New | Project…, and you will see the New Project dialog box. Do not open the LayerNorthwind solution (from the previous chapter), as in this chapter we will create a completely new solution and save it in a different location.
2. In the New Project window, specify Visual C# | Console Application as the project template, TestLINQToEntities as the project name, and C:\SOAwithWCFandEF\Projects\ as the location. Make sure that the Create directory for solution checkbox is selected.
3. Click on the OK button to create the project.
Creating the data model
To use LINQ to Entities, we need to add a conceptual data model, EDM, to the project. There are two ways to create an EDM: create it from a database or create it manually. Here, we will create the EDM from the Northwind database. We will add two tables and one view from theNorthwind database into our project, so that later on we can use them to demonstrate LINQ to Entities.
Installing Entity Framework
To utilize LINQ to Entities, we first need to install Entity Framework to the project. Follow these steps to do this:
1. From the Solution Explorer, right-click on the project item and then select Manage NuGet Packages.....
2. On the Manage NuGet Packages window, select Online | nuget.org | EntityFramework.
3. Click on the Install button to install Entity Framework to the project. This will add three references to the project, change the config file to include nodes for Entity Framework, add a folder called packages for the Entity Framework package, and add a packages.config file for the package to the project.
Note
As you can see, Entity Framework 6.1.1 was installed when this book was written. You might have a newer version to install when you are reading this section.
Adding a LINQ to Entities item to the project
Once Entity Framework is installed to the project, let's add a new item to our test project TestLINQToEntities. The new item added should be the ADO.NET Entity Data Model type and named Northwind.edmx, as shown in the following Add New Item - TestLINQToEntities dialog window:
After you click on the Add button, the Entity Data Model Wizard window will pop up. Follow these steps to finish this wizard:
1. On the Choose Model Contents page, select EF Designer from database. Later, we will connect to the Northwind database and let Visual Studio generate the conceptual data model for us. If you choose the Empty EF Designer model option here, you will have to manually create the data model, which might be applicable in certain circumstances such as the absence of a physical database while you do the modeling. You can even create your physical database from your model later if you have chosen this option and have finished your model.
2. Click on the Next button in this window. The Choose Your Data Connection window should be displayed.
3. As this is our first LINQ to Entities application, there is no existing data connection to choose from, so let's click on the New Connection… button. The Choose Data Source window should be displayed on the screen.
We will now set up a new data connection with the following steps:
1. Select Microsoft SQL Server as the data source and leave .NET Framework Data Provider for SQL Server as the data provider. Click on the Continue button to close this window.
2. Now the Connection Properties window should pop up on your screen. In this window, enter your database server name as Server name or localhost if the database is on your local machine.
3. Then, specify the login details to your database.
4. Click on the Test Connection button to test your database connection settings. You should get the Test connection succeeded message. If not, modify your server name or login details and make sure that your SQL Server service is started.
5. Now, select Northwind as the database name. If you don't see Northwind in the database list, you need to install it to your SQL Server (refer to the previous chapter for installation details). Now, the Connection Properties window should appear as shown in the following screenshot:
6. Click on the OK button in the Connection Properties window to go back to the Entity Data Model Wizard window. The Choose Your Data Connection page in the Entity Data Model Wizard window should now look as shown in the following screenshot:
7. Click on the Next button on this window to go to the next page.
8. On the Choose Your Database Objects and Settings page, select the tables Products and Categories, the Current Product List view, and then click on the Finish button:
After you click on the Finish button, you might get a security warning dialog box. This is because Visual Studio will now run a custom template to generate the Plain Old CLR/C# Object (POCO), which means that classes for your data model are just made up strictly of data properties rather than functionality and that they are not tied to Entity Framework in any way. Just click on OK to dismiss this dialog box (you can check the Do not show this message again checkbox to dismiss it forever).
At this point, the Visual Studio LINQ to Entities designer should be open, as shown in the following screenshot:
The generated LINQ to Entities classes
If you browse in the Solution Explorer, you will find that the following classes have been generated for the project:
public partial class NorthwindEntities : DbContext
public partial class Product
public partial class Category
public partial class Current_Product_List
The NorthwindEntities class, which is under the Northwind.Context.cs file, is the main conduit through which we'll query entities from the database as well as apply changes back to it. It contains three properties of the DbSet<> type, one for each table/view that we will be working with. It inherits from the DbContext class, which represents the main entry point for the LINQ to Entities framework.
The next two classes, Product and Category, are for the two tables that we are interested in. They are all POCO classes with a few properties. Note that Product has a navigation property called Category, and Category has a collection property called Products. We can use these properties to get the category of a product or the product list of a category.
The last class, Current_Product_List, is for the view. This is a simple class with only two property members.
Note
These four classes are generated through the T4 template system and should not be manually altered in any way. If any of them is modified, the changes will be overwritten when the files are regenerated. If you do need to customize them, you can modify the template or put your changes in a partial class.
Querying and updating a database table
Now that we have the entity classes created, we will use them to interact with the database. We will first work with the Products table to query and update records as well as to insert and delete records.
We will put our code in the Program.cs file. To make it easier to maintain, we will create a method, TestTables, put the code inside this method, and then call this method from the Main method.
Querying records
First, we will query the database to get some products. To query a database by using LINQ to Entities, we first need to construct a DbContext object as follows:
var NWEntities = new NorthwindEntities();
We can then use the LINQ query syntax to retrieve records from the database using the following code:
IEnumerable<Product> beverages = from p in NWEntities.Products
where p.Category.CategoryName == "Beverages"
orderby p.ProductName
select p;
The preceding code will retrieve all of the products in the Beverages category sorted by the product name.
You can use the following statement to print out the total number of beverage products in the Northwind database:
Console.WriteLine("There are {0} Beverages", beverages.Count());
After we have finished working with this context object, we need to dispose of it as follows:
NWEntities.Dispose();
As a best practice, we should wrap all the preceding code in a using statement, so the code will be as follows:
using (var NWEntities = new NorthwindEntities())
{
// retrieve all Beverages products
IEnumerable<Product> beverages =
from p in NWEntities.Products
where p.Category.CategoryName == "Beverages"
orderby p.ProductName
select p;
Console.WriteLine("There are {0} Beverages",
beverages.Count());
}
Updating records
We can update any of the products that we have just retrieved from the database as follows:
// update a product
var bev1 = beverages.ElementAtOrDefault(10);
if (bev1 != null)
{
var newPrice = (decimal)bev1.UnitPrice + 10.00m;
Console.WriteLine("The price of {0} is {1}. Update to {2}", bev1.ProductName, bev1.UnitPrice, newPrice);
bev1.UnitPrice = newPrice;
// submit the change to database
NWEntities.SaveChanges();
}
We used the ElementAtOrDefault method, not the ElementAt method, just in case there was no Beverages product at element number 10 (ElementAt will throw an exception if the index is out of range while ElementAtOrDefault will return the default value of the list type, which is nullin this example). We know that there are 12 beverage products in the sample database, so we increased the eleventh product's price by 10.00 and called NWEntities.SaveChanges() to update the record in the database. After you run the program, if you query the database, you will findthat the eleventh beverage's price is increased by 10.00.
Inserting records
We can also create a new product and then insert this new product into the database by using the following code:
// add a product
var newProduct = new Product {ProductName="new test product" };
NWEntities.Products.Add(newProduct);
NWEntities.SaveChanges();
Console.WriteLine("Added a new product with name 'new test product'");
Deleting records
To delete a product, we first need to retrieve it from the database and then call the Remove method, as shown in the following code snippet:
// delete a product
var productsToDelete =
from p in NWEntities.Products
where p.ProductName == "new test product"
select p;
if (productsToDelete.Count() > 0)
{
foreach (var p in productsToDelete)
{
NWEntities.Products.Remove(p);
Console.WriteLine("Deleted product {0}", p.ProductID);
}
NWEntities.SaveChanges();
}
Note
You can also use the following statement to delete a product, say p, from the database:
NWEntities.Entry(p).State = EntityState.Deleted;
Running the program
The following is the content of the Program.cs file now:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace TestLINQToEntities
{
class Program
{
static void Main(string[] args)
{
// CRUD operations on tables
TestTables();
Console.WriteLine("Press any key to continue ...");
Console.ReadKey();
}
static void TestTables()
{
using(NorthwindEntities NWEntities =
new NorthwindEntities())
{
// retrieve all Beverages
IEnumerable<Product> beverages =
from p in NWEntities.Products
where p.Category.CategoryName == "Beverages"
orderby p.ProductName
select p;
Console.WriteLine("There are {0} Beverages",
beverages.Count());
// update one product
var bev1 = beverages.ElementAtOrDefault(10);
if (bev1 != null)
{
var newPrice = (decimal)bev1.UnitPrice + 10.00m;
Console.WriteLine("The price of {0} is {1}. Update to {2}",
bev1.ProductName, bev1.UnitPrice, newPrice);
bev1.UnitPrice = newPrice;
}
// submit the change to database
NWEntities.SaveChanges();
// insert a product
var newProduct = new Product { ProductName =
"new test product" };
NWEntities.Products.Add(newProduct);
NWEntities.SaveChanges();
Console.WriteLine("Added a new product");
// delete a product
var productsToDelete =
from p in NWEntities.Products
where p.ProductName == "new test product"
select p;
if (productsToDelete.Count() > 0)
{
foreach (var p in productsToDelete)
{
NWEntities.Products.Remove(p);
Console.WriteLine("Deleted product {0}",
p.ProductID);
}
NWEntities.SaveChanges();
}
}
}
}
}
If you run the program now, the output will be as shown in the following screenshot:
Viewing the generated SQL statements
You might wonder which SQL statements are used by LINQ to Entities to interact with the databases. In this section, we will use two ways to view the generated SQL statements used by LINQ to Entities queries. The first one is to use the ToString method and the second one is to use SQL Profiler.
Viewing the SQL statements using ToString
First, let's write a new test method to contain one LINQ to Entities query:
static void ViewGeneratedSQL()
{
using(var NWEntities =
new NorthwindEntities())
{
var beverages =
from p in NWEntities.Products
where p.Category.CategoryName == "Beverages"
orderby p.ProductName
select p;
}
}
Now, we can print out the SQL statement of the LINQ to Entities query using the following statement:
// view SQL using ToString method
Console.WriteLine("The SQL statement is:" +
beverages.ToString());
Note
In Entity Framework 4 or earlier, if you apply .ToString() to a LINQ to Entities query variable, you will get the type of the variable, which is System.Data.Objects.ObjectQuery.
The Program.cs file should now be as follows:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace TestLINQToEntities
{
class Program
{
static void Main(string[] args)
{
// CRUD operations on tables
//TestTables();
ViewGeneratedSQL();
Console.WriteLine("Press any key to continue ...");
Console.ReadKey();
}
static void TestTables()
{
// the body of this method is omitted to save space
}
static void ViewGeneratedSQL()
{
using(var NWEntities =
new NorthwindEntities())
{
var beverages =
from p in NWEntities.Products
where p.Category.CategoryName == "Beverages"
orderby p.ProductName
select p;
// view SQL using ToString method
Console.WriteLine("The SQL statement is:\n" +
beverages.ToString());
}
}
}
}
Run this program and you will see the output as shown in the following screenshot:
Viewing the SQL statements using SQL Profiler
With the ToString method, we can view the generated SQL statements for some LINQ to Entities expressions, but not all of them. For example, when we add a new product to the database, or when we execute a stored procedure in the database, there is no IQueryable object for us to use to view the generated SQL statements. In this case, we can use SQL Profiler to view the SQL statements.
SQL Server Profiler is a tool from Microsoft that can be used to create and manage traces and analyze and replay trace results. With SQL Profiler, you can capture any and all events that are happening to a database engine in real time, including the SQL statements that are being executed at that moment.
To view the SQL statements for LINQ to Entities queries by using SQL Profiler, we need to run some LINQ to Entities queries. So, we will keep this in mind for now and try this in the next section while we are learning about another important feature of LINQ to Entities, that is, deferred execution.
Deferred execution
One important thing to remember when working with LINQ to Entities is the deferred execution of LINQ.
Standard query operators differ in the timing of their execution depending on whether they return an aggregation value or a sequence of values. Those methods that return an aggregation value (for example, Average and Sum) execute immediately. Methods that return a sequence defer the query execution and return an enumerable object. These methods do not consume the target data until the query object is enumerated. This is known as deferred execution.
Checking deferred execution with SQL Profiler
To test the deferred execution of LINQ to Entities, let's first add the following method to our Program.cs file:
static void TestDeferredExecution()
{
using(var NWEntities =
new NorthwindEntities())
{
// SQL is not yet executed
var beverages =
from p in NWEntities.Products
where p.Category.CategoryName == "Beverages"
orderby p.ProductName
select p;
// SQL is executed on this statement
Console.WriteLine("There are {0} Beverages",
beverages.Count());
}
}
Call the preceding method from the Main method of the program and comment out the calls to the two previous test methods (TestTables and ViewGeneratedSQL). Then, perform the following steps:
1. Open Profiler (All Programs\Microsoft SQL Server\Performance Tools\SQL Server Profiler).
2. Start a new trace on the Northwind database engine. You can refer to MSDN's SQL Server Profiler documentation to learn how to start a new trace on a database engine.
3. Go back to Visual Studio and set a break point on the first line of the TestDeferredExecution method.
4. Press F5 to start debugging the program.
The program is now running and the cursor should be stopped on the first line of the method. Press F10 to move to the next line of code and press F10 again to step over the following line of code:
var beverages =
from p in NWEntities.Products
where p.Category.CategoryName == "Beverages"
orderby p.ProductName
select p;
Switch to Profiler and you will find that there is nothing in there.
However, when you press F10 in Visual Studio and when the following statement is executed, you will see (in Profiler) that a query has been executed in the database:
Console.WriteLine("There are {0} Beverages", beverages.Count());
The query executed in the database is as follows:
SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[Products] AS [Extent1]
INNER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID]
WHERE N'Beverages' = [Extent2].[CategoryName]
) AS [GroupBy1]
The SQL Server Profiler window should look as shown in the following screenshot:
From Profiler, we know that, under the hood, LINQ actually first created a subquery to get the total beverage products' count, and then got this count from the subquery result. It also used an inner join to get the categories of products.
Deferred execution for aggregation methods
If the query expression returns an aggregation value, the query will be executed as soon as it is defined. For example, we can add the following statement to our test deferred execution method to get the average price of all products:
// SQL is executed on this statement
var averagePrice = (from p in NWEntities.Products
select p.UnitPrice).Average();
Console.WriteLine("The average price is {0}", averagePrice);
Start SQL Profiler and then press F5 to start debugging the program. When the cursor is stopped on the line to print out the average price (in the SQL Server Profiler window), we see that a query has been executed to get the average price, and when the printing statement is being executed, no further query is executed in the database.
The SQL Server Profiler window is shown in the following screenshot:
Deferred execution for aggregation methods within sequence expressions
However, just because a query is using one of the aggregation methods such as sum, average, or count, this doesn't mean that the query will be executed as soon as it is defined. If the query result is a sequence, the execution will still be deferred. The following is an example of this kind of query:
// SQL is not executed even though there is a singleton method
var cheapestProductsByCategory =
from p in NWEntities.Products
group p by p.CategoryID into g
select new
{
CategoryID = g.Key,
CheapestProduct =
(from p2 in g
where p2.UnitPrice == g.Min(p3 => p3.UnitPrice)
select p2).FirstOrDefault()
};
Console.WriteLine("Cheapest products by category:");
// SQL is executed on this statement
foreach (var p in cheapestProductsByCategory)
{
if (p.CategoryID == null || p.CheapestProduct == null)
continue;
Console.WriteLine("category {0}: product name: {1} price: {2}",
p.CategoryID, p.CheapestProduct.ProductName,
p.CheapestProduct.UnitPrice);
}
Start SQL Profiler and then press F5 to start debugging the program. When the cursor is stopped at the beginning of the foreach line, in Profiler we don't see the query statement to get the minimum price for any product. When we press F10 again, the cursor is stopped on thecheapestProductsByCategory variable, in the foreach line of code, but we still don't see the query statement to get the cheapest products.
Then, after we press F10 twice, the cursor is stopped on the var p keyword in the foreach line of code, and this time in Profiler, we see that the query is executed.
The actual SQL statements for this LINQ to Entities expression are as follows:
SELECT
1 AS [C1],
[GroupBy1].[K1] AS [CategoryID],
[Limit1].[ProductID] AS [ProductID],
[Limit1].[ProductName] AS [ProductName],
[Limit1].[SupplierID] AS [SupplierID],
[Limit1].[CategoryID] AS [CategoryID1],
[Limit1].[QuantityPerUnit] AS [QuantityPerUnit],
[Limit1].[UnitPrice] AS [UnitPrice],
[Limit1].[UnitsInStock] AS [UnitsInStock],
[Limit1].[UnitsOnOrder] AS [UnitsOnOrder],
[Limit1].[ReorderLevel] AS [ReorderLevel],
[Limit1].[Discontinued] AS [Discontinued]
FROM (SELECT
[Extent1].[CategoryID] AS [K1],
MIN([Extent1].[UnitPrice]) AS [A1]
FROM [dbo].[Products] AS [Extent1]
GROUP BY [Extent1].[CategoryID] ) AS [GroupBy1]
OUTER APPLY (SELECT TOP (1)
[Extent2].[ProductID] AS [ProductID],
[Extent2].[ProductName] AS [ProductName],
[Extent2].[SupplierID] AS [SupplierID],
[Extent2].[CategoryID] AS [CategoryID],
[Extent2].[QuantityPerUnit] AS [QuantityPerUnit],
[Extent2].[UnitPrice] AS [UnitPrice],
[Extent2].[UnitsInStock] AS [UnitsInStock],
[Extent2].[UnitsOnOrder] AS [UnitsOnOrder],
[Extent2].[ReorderLevel] AS [ReorderLevel],
[Extent2].[Discontinued] AS [Discontinued]
FROM [dbo].[Products] AS [Extent2]
WHERE (([GroupBy1].[K1] = [Extent2].[CategoryID]) OR (([GroupBy1].[K1] IS NULL) AND ([Extent2].[CategoryID] IS NULL))) AND (([Extent2].[UnitPrice] = [GroupBy1].[A1]) OR (([Extent2].[UnitPrice] IS NULL) AND ([GroupBy1].[A1] IS NULL))) ) AS [Limit1]
From this output, you can see that when the cheapestProductsByCategory variable is accessed, it first calculates the minimum price for each category. Then, for each category it returns the first product with that price. In a real application, you probably wouldn't want to write such a complex query in your code. Instead, you might want to put it in a stored procedure, which we will discuss in the next chapter.
The test method is as follows:
static void TestDeferredExecution()
{
using(var NWEntities =
new NorthwindEntities())
{
// SQL is not executed
var beverages =
from p in NWEntities.Products
where p.Category.CategoryName == "Beverages"
orderby p.ProductName
select p;
// SQL is executed on this statement
Console.WriteLine("There are {0} Beverages",
beverages.Count());
// SQL is executed on this statement
var averagePrice = (from p in NWEntities.Products
select p.UnitPrice).Average();
Console.WriteLine("The average price is {0}", averagePrice);
// SQL is not executed even there is a singleton method
var cheapestProductsByCategory =
from p in NWEntities.Products
group p by p.CategoryID into g
select new
{
CategoryID = g.Key,
CheapestProduct =
(from p2 in g
where p2.UnitPrice == g.Min(p3 => p3.UnitPrice)
select p2).FirstOrDefault()
};
// SQL is executed on this statement
Console.WriteLine("Cheapest products by category:");
foreach (var p in cheapestProductsByCategory)
{
if (p.CategoryID == null || p.CheapestProduct == null)
continue;
Console.WriteLine(
"category {0}: product name: {1} price: {2}",
p.CategoryID, p.CheapestProduct.ProductName,
p.CheapestProduct.UnitPrice);
}
}
}
If you comment out all other test methods (TestTables and ViewGeneratedSQL) and run the program, you should get an output similar to the one shown in the following screenshot:
Deferred execution – lazy loading versus eager loading
In one of the preceding examples, we retrieved the category name of a product using the following expression:
p.Category.CategoryName == "Beverages"
Even though there is no field called categoryname in the Products table, we can still get the category name of a product because there is an association between the Products and Category tables. In the Northwind.edmx design pane, click on the line that connects the Products andCategories tables and you will see all of the properties of the association. Note that its Referential Constraint property is Category.CategoryID -> Product.CategoryID, meaning that category ID is the key field to link these two tables.
Because of this association, we can retrieve the category for each product and also retrieve products for each category.
Lazy loading by default
Even with an association, the associated data is not loaded when the query is executed. For example, suppose we use the following test method to retrieve all of the categories and then access the products for each category:
static void TestAssociation()
{
using (var NWEntities = new NorthwindEntities())
{
var categories = from c in NWEntities.Categories select c;
foreach (var category in categories)
{
Console.WriteLine("There are {0} products in category {1}",
category.Products.Count(), category.CategoryName);
}
}
}
Start SQL Profiler and then press F5 to start debugging the program. When the cursor is stopped on the foreach line (after you press F10 three times to move the cursor to the var category keyword), in Profiler we see the following SQL statement:
SELECT
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[CategoryName] AS [CategoryName],
[Extent1].[Description] AS [Description],
[Extent1].[Picture] AS [Picture]
FROM [dbo].[Categories] AS [Extent1]
When you press F10 to execute the printout line, in Profiler we see the following SQL statement:
exec sp_executesql N'SELECT
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductName] AS [ProductName],
[Extent1].[SupplierID] AS [SupplierID],
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit],
[Extent1].[UnitPrice] AS [UnitPrice],
[Extent1].[UnitsInStock] AS [UnitsInStock],
[Extent1].[UnitsOnOrder] AS [UnitsOnOrder],
[Extent1].[ReorderLevel] AS [ReorderLevel],
[Extent1].[Discontinued] AS [Discontinued]
FROM [dbo].[Products] AS [Extent1]
WHERE [Extent1].[CategoryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1
From these SQL statements, we know that Entity Framework first goes to the database to query all of the categories. Then, for each category, when we need to get the total count of products, it goes to the database again to query all of the products for that category. Because there are eight categories in the database, it goes to the database nine times in total (including the first one to retrieve all categories).
This is because, by default, lazy loading is set to true, meaning that the loading of all associated data (children) is deferred until the data is needed.
Eager loading with the Include method
To change the behavior seen in the preceding section, we can use the Include method to tell DbContext to automatically load the specified children during the initial query:
static void TestEagerLoading()
{
using(var NWEntities = new NorthwindEntities())
{
// eager loading products of categories
var categories = from c
in NWEntities.Categories.Include(c=>c.Products)
select c;
foreach (var category in categories)
{
Console.WriteLine("There are {0} products in category {1}",
category.Products.Count(), category.CategoryName);
}
}
}
Inside this test method, when constructing the LINQ to Entities query, we added an Include clause to tell the framework to load all products when loading the categories.
Note
To use Include with a lambda expression, you need to add the following using statement to the class:
using System.Data.Entity;
To test it, start SQL Profiler and then press F5 to start debugging the program. When the cursor is stopped on the foreach line (at the var category keyword), in Profiler you will see the following SQL statement:
SELECT
[Project1].[CategoryID] AS [CategoryID],
[Project1].[CategoryName] AS [CategoryName],
[Project1].[Description] AS [Description],
[Project1].[Picture] AS [Picture],
[Project1].[C1] AS [C1],
[Project1].[ProductID] AS [ProductID],
[Project1].[ProductName] AS [ProductName],
[Project1].[SupplierID] AS [SupplierID],
[Project1].[CategoryID1] AS [CategoryID1],
[Project1].[QuantityPerUnit] AS [QuantityPerUnit],
[Project1].[UnitPrice] AS [UnitPrice],
[Project1].[UnitsInStock] AS [UnitsInStock],
[Project1].[UnitsOnOrder] AS [UnitsOnOrder],
[Project1].[ReorderLevel] AS [ReorderLevel],
[Project1].[Discontinued] AS [Discontinued]
FROM ( SELECT
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[CategoryName] AS [CategoryName],
[Extent1].[Description] AS [Description],
[Extent1].[Picture] AS [Picture],
[Extent2].[ProductID] AS [ProductID],
[Extent2].[ProductName] AS [ProductName],
[Extent2].[SupplierID] AS [SupplierID],
[Extent2].[CategoryID] AS [CategoryID1],
[Extent2].[QuantityPerUnit] AS [QuantityPerUnit],
[Extent2].[UnitPrice] AS [UnitPrice],
[Extent2].[UnitsInStock] AS [UnitsInStock],
[Extent2].[UnitsOnOrder] AS [UnitsOnOrder],
[Extent2].[ReorderLevel] AS [ReorderLevel],
[Extent2].[Discontinued] AS [Discontinued],
CASE WHEN ([Extent2].[ProductID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
FROM [dbo].[Categories] AS [Extent1]
LEFT OUTER JOIN [dbo].[Products] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID]
) AS [Project1]
ORDER BY [Project1].[CategoryID] ASC, [Project1].[C1] ASC
As you can see from the preceding SQL statement, all products for all categories are loaded during the first query.
Comparing lazy loading and eager loading
As you have learned in the previous sections, lazy loading and eager loading are two very different options for the loading-related objects. With lazy loading, the first query will return only the main objects, and every time a related object is needed, another query has to be executed. Each query will have a smaller payload, but there will be multiple queries to the database. With eager loading, the first query will return all objects, including any related objects. When a related object is needed, it will be retrieved right from the object model, not from the database. There will be only one database trip, but the payload will be larger. You should weigh the pros and cons of each option and choose one appropriately.
Joining two tables
Although an association is a kind of join in LINQ, we can also explicitly join two tables using the Join keyword, as shown in the following code snippet:
static void TestJoin()
{
using(var NWEntities = new NorthwindEntities())
{
var categoryProducts =
from c in NWEntities.Categories
join p in NWEntities.Products
on c.CategoryID equals p.CategoryID
into productsByCategory
select new {
c.CategoryName,
productCount = productsByCategory.Count()
};
foreach (var cp in categoryProducts)
{
Console.WriteLine("There are {0} products in category {1}",
cp.productCount, cp.CategoryName);
}
}
}
This was not so useful in the previous example because the Products and Categories tables are associated with a foreign key relationship. If there is no foreign key association between two tables, or if we have not added the associations between these two tables, this will be particularly useful.
From the following SQL statement, we can see that only one query is executed to get the results:
SELECT
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[CategoryName] AS [CategoryName],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Products] AS [Extent2]
WHERE [Extent1].[CategoryID] = [Extent2].[CategoryID]) AS [C1]
FROM [dbo].[Categories] AS [Extent1]
In addition to joining two tables, you can also:
· Join three or more tables
· Join a table to itself
· Create left, right, and full outer joins
· Join using composite keys
Querying a view
Querying a view is the same as querying a table (the view needs to have a unique key). For example, you can query the view "current product lists" as follows:
static void TestView()
{
using(var NWEntities = new NorthwindEntities())
{
var currentProducts = from p
in NWEntities.Current_Product_Lists
select p;
foreach (var p in currentProducts)
{
Console.WriteLine("Product ID: {0} Product Name: {1}",
p.ProductID, p.ProductName);
}
}
}
This will get and print all of the current products using the view.
Summary
In this chapter, we learned the basic concepts and features of LINQ to Entities. We learned how to query a database with LINQ to Entities, update a database with LINQ to Entities, and we also learned how to change loading behaviors with LINQ to Entities.
In the next chapter, we will cover the advanced concepts and features of LINQ to Entities such as stored procedure support, simultaneous updating, and transaction processing.