LINQ to Entities: Basic Concepts and Features

• Download source code - 45.3 KB

Introduction

In my first three articles on CodeProject.com, I have explained the fundamentals of Windows Communication Foundation (WCF), including:

• Implementing a Basic Hello World WCF Service
• Implementing a WCF Service with Entity Framework
• Concurrency Control of a WCF Service with Entity Framework

From early this year, I have started to write a series of articles to explain LINQ, LINQ to SQL, Entity Framework, and LINQ to Entities. Followings are the articles I wrote or plan to write for LINQ, LINQ to SQL, and LINQ to Entities:

• Introducing LINQ—Language Integrated Query
• LINQ to SQL: Basic Concepts and Features
• LINQ to SQL: Advanced Concepts and Features (last article)
• LINQ to Entities: Basic Concepts and Features (this article)
• LINQ to Entities: Advanced Concepts and Features (next article)

And as I said, after finishing these five articles, I will come back to write some more articles on WCF from my real work experience, which will be definitely helpful to your real world work, if you are using WCF right now.

Overview

In the previous article (Introducing LINQ—Language Integrated Query), we learned the new features of C# 3.0 including LINQ. In this article and the next, we will see how to use LINQ to query a database, or in other words, how to use LINQ to Entities in C#. After reading these two articles, you will have a good understanding of LINQ to Entities, so that you can write the data access layer of your WCF service with LINQ to Entities, to securely, and reliably communicate with the underlying database.

In this article, we will cover the basic concepts and features of LINQ to Entities, which include:

• What ORM is
• What LINQ to Entities is
• What LINQ to SQL is
• Comparing LINQ to Entities with LINQ to Objects and LINQ to SQL
• Modeling the Northwind database with LINQ to EntitiesQuerying and updating a database with a table
• Deferred execution
• Lazy loading and eager loading
• Joining two tables
• Querying with a view

In the next article, we will cover the advanced concepts and features of LINQ to Entities, such as Stored Procedure support, inheritance, simultaneous updating, and transaction processing.

ORM—Object-Relational Mapping

LINQ to Entities is considered to be one of Microsoft's new ORM products. So before we start explaining LINQ to Entities, let us first understand what ORM is.

ORM stands for Object-Relational Mapping. Sometimes it is called O/RM, or O/R mapping. It is a programming technique that contains a set of classes that map relational database entities to objects in a specific programming language.

Initially, applications could call specified native database APIs to communicate with a database. For example, Oracle Pro*C is a set of APIs supplied by Oracle to query, insert, update, or delete records in an Oracle database from C applications. The Pro*C pre-compiler translates embedded SQL into calls to the Oracle runtime library (SQLLIB).

Then, ODBC (Open Database Connectivity) was developed to unify all of the communication protocols for various RDBMSs. ODBC was designed to be independent of programming languages, database systems, and Operating Systems. So with ODBC, one application can communicate with different RDBMSs by using the same code, simply by replacing the underlying ODBC drivers.

No matter which method is used to connect to a database, the data returned from a database has to be presented in some format in the application. For example, if an Order record is returned from the database, there has to be a variable to hold the Order number, and a set of variables to hold the Order details. Alternatively, the application may create a class for the Orders, and another class for Order details. When another application is developed, the same set of classes may have to be created again, or if it is designed well, they can be put into a library, and re-used by various applications.

This is exactly where ORM fits in. With ORM, each database is represented by an ORM context object in the specific programming language, and database entities such as tables are represented by classes, with relationships between these classes. For example, the ORM may create an Order class to represent the Order table, and an OrderDetail class to represent the Order Details table. The Order class will contain a collection member to hold all of its details. The ORM is responsible for the mappings and the connections between these classes and the database. So, to the application, the database is now fully-represented by these classes. The application only needs to deal with these classes, instead of with the physical database. The application does not need to worry about how to connect to the database, how to construct the SQL statements, how to use the proper locking mechanism to ensure concurrency, or how to handle distributed transactions. These database-related activities are handled by the ORM.

Entity Framework

Since LINQ to Entities is based on the Entity Framework, let’s explain what Entity Framework is now.

ADO.NET Entity Framework (EF) is a new addition to the Microsoft ADO.NET family. It enables developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications. Entity Framework applications provide the following benefits:

• Applications can work in terms of a more application-centric conceptual model, including types with inheritance, complex members, and relationships.
• Applications are freed from hard-coded dependencies on a particular data engine or storage schema.
• Mappings between the conceptual model and the storage-specific schema can change without changing the application code.
• Developers can work with a consistent application object model that can be mapped to various storage schemas, possibly implemented in different database management systems.
• Multiple conceptual models can be mapped to a single storage schema.
• Language-integrated query (LINQ) support provides compile-time syntax validation for queries against a conceptual model.

With Entity Framework, developers work with a conceptual data model, an Entity Data Model, or EDM, instead of the underlying databases. The conceptual data model schema is expressed in the Conceptual Schema Definition Language (CSDL), the actual storage model is expressed in the Storage Schema Definition Language (SSDL), and the mapping in between is expressed in the Mapping Schema Language (MSL). A new data-access provider, EntityClient, is created for this new framework but under the hood, the ADO.NET data providers are still being used to communicate with the databases. The diagram below shows the high level architectures of Entity Framework.

LINQ to Entities

Now let’s have a look at what LINQ to Entities is.

LINQ to Entities provides Language-Integrated Query (LINQ) support that enables developers to write queries against the Entity Framework conceptual model using Visual Basic or Visual C#. Queries against the Entity Framework are represented by command tree queries, which execute against the object context. LINQ to Entities converts Language-Integrated Queries (LINQ) queries to command tree queries, executes the queries against the Entity Framework, and returns objects that can be used by both the Entity Framework and LINQ.

LINQ to Entities allows developers to create flexible, strongly-typed queries against the Entity Data Model (EDM) by using LINQ expressions and standard LINQ query operators. To certain degrees, LINQ to Entities is similar to LINQ to SQL, but LINQ to Entities is a true ORM product from Microsoft, and it supports more features than LINQ to SQL, such as multiple-table inheritance. LINQ to Entities also supports many other mainstream RDBMSs such as Oracle, DB2, and MySQL in addition to Microsoft SQL Server.

Comparing LINQ to Entities with LINQ to Objects

In the previous article, we used LINQ to query in-memory objects. Before we dive further into the world of LINQ to Entities, we first need to look at the relationship between LINQ to Entities and LINQ to Objects.

Some key differences between LINQ to Entities and LINQ to Objects are:

• LINQ to Entities needs an Object Context object. The ObjectContext object is the bridge between LINQ and the database (we will explain more about ObjectContext later). LINQ to Objects don't need any intermediate LINQ provider or API.
• LINQ to Entities returns data of type IQueryable<T> whereas LINQ to Objects returns data of type IEnumerable<T>.
• LINQ to Entities queries are translated to SQL by way of Expression Trees, which allow them to be evaluated as a single unit, and translated to appropriate and optimal SQL Statements. LINQ to Objects queries do not need to be translated.
• LINQ to Entities queries are translated to SQL calls and executed on the specified database while LINQ to Objects queries are executed in the local machine memory.

The similarities shared by all aspects of LINQ are the syntax. They all use the same SQL-like syntax and share the same groups of standard query operators. From the language syntax perspective, working with a database is the same as working with in-memory objects.

LINQ to SQL

Before LINQ to Entities, Microsoft released another ORM product, which is LINQ to SQL. Both LINQ to SQL and LINQ to Entities can be used in the data access layer to interact with databases, but they are quite different. In this section, we will explain what LINQ to SQL is, and in the next section, we will compare these two technologies.

In short, LINQ to SQL is a component of .NET Framework 3.5 that provides a run-time infrastructure for managing relational data as objects.

In LINQ to SQL, the data model of a relational database is mapped to an object model expressed in the programming language of the developer. When the application runs, LINQ to SQL translates language-integrated queries in the object model into SQL, and sends them to the database for execution. When the database returns the results, LINQ to SQL translates the results back to objects that you can work with in your own programming language.

Unlike LINQ to Entities, with LINQ to SQL, developers don’t need to create an extra data model between their applications and the underlying database. Under the hood of LINQ to SQL, ADO.NET SqlClient adapters are used to communicate with the actual SQL Server databases.

The following diagram shows the use of LINQ to SQL in a .NET application:

Comparing LINQ to SQL with LINQ to Entities

Now we know what LINQ to Entities is, and what LINQ to SQL is. In this section, let’s compare these two technologies.

As described earlier, LINQ to Entities applications work against a conceptual data model (EDM). All mappings between the languages and the databases go through the new EntityClient mapping provider. The application no longer connects directly to a database, or sees any database-specific constructs. The entire application operates in terms of the higher-level EDM.

This means that you can no longer use the native database query language. Not only will the database not understand the EDM model, but also current database query languages do not have the constructs required to deal with the elements introduced by the EDM, such as inheritance, relationships, complex-types, and so on.

On the other hand, for developers who do not require mapping to a conceptual model, LINQ to SQL enables developers to experience the LINQ programming model directly over the existing database schema.

LINQ to SQL allows developers to generate .NET classes that represent data. Rather than map to a conceptual data model, these generated classes map directly to database tables, views, Stored Procedures, and user defined functions. Using LINQ to SQL, developers can write code directly against the storage schema using the same LINQ programming pattern as was previously described for in-memory collections, Entities, or the Data Set, as well as for other data sources such as XML.

Compared to LINQ to Entities, LINQ to SQL has some limitations, mainly because of its direct mapping against the physical relational storage schema. For example, you can't map two different database entities into one single C# or VB object, and if the underlying database schema changes, this might require significant client application changes.

So, in a summary, if you want to work against a conceptual data model, use LINQ to Entities. If you want to have a direct mapping to the database from your programming languages, use LINQ to SQL.

The following table lists some of the features supported by these two data access methodologies:

Interestingly, some say LINQ to SQL was an intermediate solution. Fact is that LINQ to SQL was made by the C# team, instead of the ADO.NET team. It was of great importance for the C# team to release an O/RM mapper together with their new LINQ technology. Without a LINQ to databases implementation, the C# team would have a hard time evangelizing LINQ.

In November 2008, the ADO.NET team announced that Microsoft will continue to make some investments in LINQ to SQL, but they also made it pretty clear that LINQ to Entities is the recommended data access solution in future frameworks. Microsoft will invest heavily in the Entity Framework. So in this book, we will use LINQ to Entities in our data access layer.

Creating a LINQ to Entities Test Application

Now that we have learned some of the basic concepts of LINQ to Entities, let us start exploring LINQ to Entities with some real examples. We will apply the skills we are going to learn in the following two articles 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 add this test application to the solution:

1. Open the solution TestLINQ
2. From Solution Explorer, right-click on the Solution item and select Add | New Project… from the context menu
3. Select Visual C# | Console Application as the project template, enter TestLINQToEntitiesApp as the (project) Name, and leave the default value C:\SOAwithWCFandLINQ\Projects\TestLINQ as the Location
4. Click OK

Creating the Data Model

To use LINQ to Entities, we need to add a conceptual data model—an Entity Data Model, or EDM – to the project. There are two ways to create the EDM, create from a database, or create manually. Here we will create the EDM from the Northwind database. We will add two tables and one view from the Northwind database into our project, so that later on we can use them to demonstrate LINQ to Entities.

Preparing the Northwind Database

Before you can create the EDM, you need to have a SQL Server database with the sample database Northwind installed. You can just search "Northwind sample database download", then download and install the sample database. If you need detailed instructions as how to download/install the sample database, you can refer to the section "Preparing the Database" in one of my previous articles, Implementing a WCF Service with Entity Framework".

Adding a LINQ to Entities Item to the Project

To start with, let us add a new item to our project TestLINQToEntitiesApp. The new item added should be of type ADO.NET Entity Data Model, and named Northwind.edmx, as shown in the following Add New Item dialog window:

After you click 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 Generate 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 model option here, you will have to manually create the data model, which may be applicable sometimes, like you may not have a physical database when you do the modeling. You may even create your physical database from your model later if you choose this option and have finished your model.
2. Click Next on this window.



3. Now the Choose Your Data Connection window should be displayed. Since this is our first LINQ to Entities application, there is no existing data connection to choose from, so let’s click button New Connection … and set up a new data connection.
1. First choose Microsoft SQL Server as the data source, and leave .NET Framework Data Provider for SQL Server as the data provider. Click OK to close this window.



2. The Connection Properties window should be displayed on the screen. On this window, enter your database server name, together with your database instance name if your database instance is not the default one on your server. If it is on your machine, you can enter localhost as the server name.
3. Then specify the logon to your database.
4. Click Test Connection to test your database connection settings. You should get a “Test connection succeeded” message. If not, modify your server name or logon details, and make sure your SQL Server service is started. If your SQL Server is on another computer and your firewall is turned on, remember to enable the SQL Server port on the SQL Server machine.
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 article for installation details).

   The Connection Properties window should be like this now:



6. Click OK on the Connection Properties window to go back to the Entity Data Model Wizard.

The Entity Data Model Wizard should be like this now:



4. On the Choose Your Database Objects page, select table Products, Categories, and view Current Product List, then click Finish:



After you click Finish, the following two files will be added to the project: Northwind.edmx and Northwind.designer.cs. The first file holds the model of the entities, including the entity sets, entity types, conceptual models, and the mappings. The second one is the code for the model, which defines the ObjectContext of the model.

At this point, the Visual Studio LINQ to Entities designer should be open and as shown in the following image:

Generated LINQ to Entities Classes

If you open the file Northwind.Designer.cs (you need to switch from the Model Browser to the Solution Explorer to open this file), you will find that the following classes have been generated for the project:

public partial class NorthwindEntities : ObjectContext
public partial class Product : EntityObject
public partial class Category : EntityObject
public partial class Current_Product_List : EntityObject

In the above four classes, the NorthwindEntities class is the main conduit through which we'll query entities from the database, as well as apply changes back to it. It contains various flavors of types and constructors, partial validation methods, and property members for all of the included tables. It inherits from the ObjectContext class, which represents the main entry point for the LINQ to Entities framework.

The next two classes are for the two tables that we are interested in. They implement the EntityObject interface. This interface defines all of the related property changing, and property changed event methods, which we can extend to validate properties before and after the change.

The last class is for the view. This is a simple class with only two property members. Because we are not going to update the database through this view, it doesn't define any property change or changed event method.

Querying and Updating the Database with a 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.

Querying Records

First, we will query the database to get some products.

To query a database using LINQ to Entities, we first need to construct an ObjectContext object, like this:

NorthwindEntities NWEntities = new NorthwindEntities();

We can then use LINQ query syntax to retrieve records from the database:

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

You can use this statement to print out the total number of beverage products in the Northwind database:

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, like this:

// update a product
Product bev1 = beverages.ElementAtOrDefault(10);

if (bev1 != null)
{
    decimal 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 is no product at element 10. We know that there are 12 beverage products in the sample database, so we increase the 11^th product’s price by 10.00 and call NWEntities.SaveChanges() to update the record in the database. After you run the program, if you query the database, you will find that the 11^th 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
Product newProduct = new Product {ProductName="new test product" };
NWEntities.Products.AddObject(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 DeleteObject method, as shown in the following code:

// delete a product
IQueryable<Product> productsToDelete = 
    from p in NWEntities.Products
    where p.ProductName == "new test product"
    select p;

if (productsToDelete.Count() > 0)
{
    foreach (var p in productsToDelete)
    {
        NWEntities.DeleteObject(p);
        Console.WriteLine("Deleted product {0}", p.ProductID);
    }
    NWEntities.SaveChanges();
}

Note here that we used a variable of type IQueryable<Product>, instead of IEnumerable<Product>, to hold the result of the LINQ to Entities query. Since IQueryable extends the interface IEnumerable, we can use either one of them, though with IQueryable, we can do much more as we will see in the next section.

Running the Program

The file Program.cs has been used so far. Note that we added one method to contain all of the test cases for table operations. We will add more methods later to test other LINQ to Entities functionalities. Following is the content of this file now.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace TestLINQToEntitiesApp
{
    class Program
    {
        static void Main(string[] args)
        {
            // CRUD operations on tables
            TestTables();
            Console.WriteLine("Press any key to continue ...");
            Console.ReadKey();
        }
        static void TestTables()
        {
            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
            Product bev1 = beverages.ElementAtOrDefault(10);
            if (bev1 != null)
            {
                decimal 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
            Product newProduct = new Product { ProductName = 
                                       "new test product" };
            NWEntities.Products.AddObject(newProduct);
            NWEntities.SaveChanges();
                Console.WriteLine("Added a new product");
                // delete a product
                IQueryable<Product> productsToDelete = 
                         from p in NWEntities.Products
                         where p.ProductName == "new test product"
                         select p;
                if (productsToDelete.Count() > 0)
                {
                    foreach (var p in productsToDelete)
                    {
                        NWEntities.DeleteObject(p);
                        Console.WriteLine("Deleted product {0}",
                               p.ProductID);
                    }
                    NWEntities.SaveChanges();
                }
                NWEntities.Dispose();
        }
    }
}

If you run the program now, the output will be:

View Generated SQL Statements

You may wonder what the actual SQL statements used by LINQ to Entities to interact with the databases are. In this section, we will explain two ways to view the generated SQL statements used by LINQ to Entities queries.

There are two ways to view the generated LINQ to Entities SQL statements. The first one is to use the ObjectQuery.ToTraceString method, and the second one is to use SQL Profiler.

View SQL Statements Using ToTraceString

First let’s write a new test method to contain LINQ to SQL queries:

static void ViewGeneratedSQL()
{
    NorthwindEntities NWEntities = new NorthwindEntities();
    IQueryable<Product> beverages = 
        from p in NWEntities.Products
        where p.Category.CategoryName == "Beverages"
        orderby p.ProductName
        select p;
    NWEntities.Dispose();
}

As we have learned from the previous section, the variable beverages is of type IQueryable<Product>, which is a derived class of type IEnumerable<Product>. Actually, this type is also a subtype of System.Data.Objects.ObjectQuery<Product>, which has a method ToTraceString we can use to view the generated SQL statements. To make it easier for us to call the ObjectQuery.ToTraceString method, we now define an extension method like this:

public static class MyExtensions
{
    public static string ToTraceString<T>(this IQueryable<T> t)
    {
        string sql = "";
        ObjectQuery<T> oqt = t as ObjectQuery<T>;
        if (oqt != null)
            sql = oqt.ToTraceString();
        return sql;
    }
}

Note that this extension method is inside a non-generic static class MyEntensions, and we put this class inside the namespace TestLINQToEntitiesApp, which is the same namespace of our test class, so we can use it inside our test method without worrying about importing its namespace.

Now we can print out the SQL statement of the LINQ to Entities query using this statement:

// view SQL using ToTraceString method
Console.WriteLine("The SQL statement is:" + beverages.ToTraceString());

and we also need to add a using statement to import the namespace for the QueryObject class:

using System.Data.Objects;

The file Program.cs now should be like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
namespace TestLINQToEntitiesApp
{
    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()
        {
            NorthwindEntities NWEntities = new NorthwindEntities();
            IQueryable<Product> beverages = 
                from p in NWEntities.Products
                where p.Category.CategoryName == "Beverages"
                orderby p.ProductName
                select p;
            // view SQL using ToTraceString method
            Console.WriteLine("The SQL statement is:\n" + 
                beverages.ToTraceString());
            NWEntities.Dispose();
        }
    }
    public static class MyExtensions
    {
        public static string ToTraceString<T>(this IQueryable<T> t)
        {
            string sql = "";
            ObjectQuery<T> oqt = t as ObjectQuery<T>;
            if (oqt != null)
                sql = oqt.ToTraceString();
            return sql;
        }
    }
}

Run this program, and you will see the following output:

View SQL Statements Using Profiler

With the ToTraceString 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 the SQL profiler to view the SQL statements. But if you go to view the generated SQL statements for the above query, you may be confused, as there is no SQL statement displayed in SQL profiler. So we will not explain the steps to view the SQL statements in the Profiler here, but we will explain it in the next section, together with the explanation of another important LINQ to Entities feature, deferred execution.

Deferred Execution

One important thing to remember when working with LINQ to Entities is the deferred execution of LINQ.

The standard query operators differ in the timing of their execution, depending on whether they return a singleton value or a sequence of values. Those methods that return a singleton 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.

In the case of the methods that operate on in-memory collections, that is, those methods that extend IEnumerable<(Of <(T>)>), the returned enumerable object captures all of the arguments that were passed to the method. When that object is enumerated, the logic of the query operator is employed, and the query results are returned.

In contrast, methods that extend IQueryable<(Of <(T>)>) do not implement any querying behavior, but build an expression tree that represents the query to be performed. The query processing is handled by the source IQueryable<(Of <(T>)>) object.

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()
{
    NorthwindEntities NWEntities = new NorthwindEntities();
    // SQL is not executed
    IQueryable<Product> 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());
    NWEntities.Dispose();
}

Call this method from the Main method of the program, and comment out the calls to the two previous test methods, then do the following:

1. Open Profiler (All Programs\Microsoft SQL Server 2005(or 2008)\Performance Tools\SQL 2005(or 2008) Profiler).
2. Start a new trace on the Northwind database engine.
3. Go back to Visual Studio, 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 this line of code:

IQueryable<Product> beverages =
         from p in NWEntities.Products
         where p.Category.CategoryName == "Beverages"
         orderby p.ProductName
         select p;

Switch to the Profiler, you will find that there is nothing in there.

However, when you press F10 in Visual Studio and before the following statement is executed, you will see from the 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 like this:

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 profiler window should look as shown in the following image:

From the Profiler, we know that, under the hood, LINQ actually first creates a sub query to get the total beverage products count, then gets this count from the sub query result. It also uses an inner join to get the categories of products.

Note that with LINQ to SQL, we can set the DataContext object’s Log property to Console.Out, then view the generated SQL statements from the standard output for all subsequent LINQ to SQL expressions. Unfortunately, with LINQ to Entities, the ObjectContext does not have such a property to let us view generated SQL statements. We have to use either ToTraceString or the Profiler to view the generated SQL statements.

Deferred Execution for Singleton Methods

If the query expression will return a singleton value, the query will be executed as soon as it is defined. For example, we can add this statement to our test deferred execution method to get the average price of all products:

// SQL is executed on this statement
decimal? averagePrice = (from p in NWEntities.Products
                         select p.UnitPrice).Average();
Console.WriteLine("The average price is {0}", averagePrice);

Start SQL Profiler, then press F5 to start debugging the program. When the cursor is stopped on the line to print out the average price, from the Profiler window, we see a query has been executed to get the average price, and when the printing statement is being executed, no more query is executed in the database.

The Profiler window is like this:

Deferred Execution for Singleton Methods Within Sequence Expressions

However, just because a query is using one of the singleton 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 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)
{
    Console.WriteLine("categery {0}: product name: {1} price: {2}",   
                      p.CategoryID, p.CheapestProduct.ProductName, 
                      p.CheapestProduct.UnitPrice);
}

Start SQL Profiler, then press F5 to start debugging the program. When the cursor is stopped on the beginning of the foreach line, from the 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 the variable cheapestProductsByCategory within the foreach line of code, but we still don’t see the query statement to get the cheapest products.

Then after we press F10 again, the cursor is stopped on the in keyword within the foreach line of code, and this time from the Profiler, we see the query is executed.

The actual SQL statements for this LINQ to Entities expression are like this:

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]) ) AS [Limit1]

From this output, you can see that when the variable cheapestProductsByCategory 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 may want to put it in a Stored Procedure, which we will discuss in the next article.

The test method is like this:

static void TestDeferredExecution()
{
  NorthwindEntities NWEntities = new NorthwindEntities();
  // SQL is not executed
  IQueryable<Product> 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
  decimal? 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)
  {
     Console.WriteLine(
              "categery {0}: product name: {1} price: {2}",
               p.CategoryID, p.CheapestProduct.ProductName,
               p.CheapestProduct.UnitPrice);
  }
  NWEntities.Dispose();
}

If you comment out all other test methods (TestTables and ViewGeneratedSQL) and run the program, you should get an output similar to the following image:

Deferred (Lazy) Loading Versus Eager Loading

In one of the above examples, we retrieved the category name of a product using this expression:

p.Category.CategoryName == "Beverages"

Even though there is no such 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 table and the Categories table and you will see all of the properties of the association. Note that its Referential Constraint properties are 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 on the other hand, we can also retrieve products for each category.

Lazy Loading by Default

However, 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, then access the products for each category:

static void TestAssociation()
{
    NorthwindEntities 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);
    }
    NWEntities.Dispose();
}

Start SQL Profiler then press F5 to start debugging the program. When the cursor is stopped on the foreach line (after you press F10 twice to move the cursor to the in keyword), from the Profiler, we see this 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, from the Profiler, we see this 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.

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 Include Method

To change this behavior, we can use the Include method to tell the ObjectContext to automatically load the specified children during the initial query:

static void TestEagerLazyLoading()
{
  NorthwindEntities NWEntities = new NorthwindEntities();
  // eager loading products of categories
  var categories = from c 
                   in NWEntities.Categories.Include("Products")
                   select c;
  foreach (var category in categories)
  {
      Console.WriteLine("There are {0} products in category {1}",
          category.Products.Count(), category.CategoryName);
  }
  NWEntities.Dispose();
}

As you can see, 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.

To test it, start SQL Profiler, then press F5 to start debugging the program. When the cursor is stopped on the foreach line (at the in keyword), from the Profiler, you will see this 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 this SQL statement, all products for all categories are loaded during the first query.

In addition to pre-loading one child entity, with the Include method, you can also traverse multiple child entities together. For example, you can use Include(“Products.Orders”) to preload products and orders for all categories, if Orders is also added as an Entity to the model. You can also chain multiple Includes to preload multiple child entities on the same level, like Customers.Include(“Orders”).Include(“Contacts”) if there is a Contacts table for customers, and customers, orders, and contacts are all added as entities to the model.

Note that with LINQ to SQL, you can set associations and eager loading configurations with DataLoadOptions, and you can even pre-load some objects with conditions, but with LINQ to Entities, you don’t have any other choice. You have to pre-load an entity entirely.

Another difference between LINQ to SQL and LINQ to Entities is, with LINQ to SQL you have strong typed load options for eager loading, like LoadWith<Category>, but with LINQ to Entities, you have to put the entity names within a string expression, which might cause a run time exception if you make a mistake in the entity names.

Joining Two Tables

Although associations are a kind of join in LINQ, we can also explicitly join two tables using the keyword Join, as shown in the following code:

static void TestJoin()
{
    NorthwindEntities 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);
    }
    NWEntities.Dispose();
}

This is not so useful in the above example, because the tables Products and Categories are associated with a foreign key relationship. If there is no foreign key association between the two tables, or if we hadn’t 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 outer joins
• Join using composite keys

Querying a View

Querying a View is the same as querying a table. For example, you can query the View "current product lists" like this:

static void TestView()
{
   NorthwindEntities 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);
   }
    NWEntities.Dispose();
}

This will get all of the current products, using the View.

Summary

In this article, we have learned what an ORM is, why we need an ORM, and what LINQ to Entities is. We also compared LINQ to SQL with LINQ to Entities, and explored some basic features of LINQ to Entities.

The key points covered in this article include:

• An ORM product can greatly ease data access layer development
• LINQ to Entities is one of Microsoft's ORM products that uses LINQ against a .NET Conceptual Entity Model
• The built-in LINQ to Entities designer in Visual Studio 2010 can be used to model the Conceptual Entity Model
• You can generate the Conceptual Entity Model from a physical database in Visual Studio 2010 Entity Model designer
• System.Data.Objects.ObjectContext is the main class for LINQ to Entities applications
• LINQ methods that return a sequence defer the query execution and you can check the timing of the execution of a query with Profiler
• LINQ query expressions that return a singleton value will be executed as soon as they are defined
• By default, the loading of associated data is deferred (lazy loading); you can change this behavior with the Include method
• The Join operator can be used to join multiple tables and Views
• Views can be used to query a database in LINQ to Entities in the same way as for tables

Note

This article is based on Chapter 7 of my book "WCF 4.0 Multi-tier Services Development with LINQ to Entities" (ISBN 1849681147). This book is a hands-on guide to learn how to build SOA applications on the Microsoft platform using WCF and LINQ to Entities. It is updated for VS2010 from my previous book: WCF Multi-tier Services Development with LINQ.

With this book, you can learn how to master WCF and LINQ to Entities concepts by completing practical examples and applying them to your real-world assignments. This is the first and only book to combine WCF and LINQ to Entities in a multi-tier real-world WCF Service. It is ideal for beginners who want to learn how to build scalable, powerful, easy-to-maintain WCF Services. This book is rich with example code, clear explanations, interesting examples, and practical advice. It is a truly hands-on book for C++ and C# developers.

You don't need to have any experience in WCF or LINQ to Entities to read this book. Detailed instructions and precise screenshots will guide you through the whole process of exploring the new worlds of WCF and LINQ to Entities. This book is distinguished from other WCF and LINQ to Entities books by that, this book focuses on how to do it, not why to do it in such a way, so you won't be overwhelmed by tons of information about WCF and LINQ to Entities. Once you have finished this book, you will be proud that you have been working with WCF and LINQ to Entities in the most straightforward way.

You can buy this book from Amazon, or from the publisher's website at https://www.packtpub.com/wcf-4-0-multi-tier-services-development-with-linq-to-entities/book.