LINQ Tutorial for Beginners

LINQ

LINQ is Microsoft’s technology to provide a language-level support mechanism for querying data of all types. These types include in memory arrays and collections, databases, XML documents and more, since version 3.5 and Visual Studio 2008. (.NET 4 and Visual Studio 2010 added support for the Parallel LINQ features).

Need for LINQ

Here is the issue that we cannot programmatically interact with a database at the native language level. This means syntax errors often go undetected until runtime.
Differing data types utilized by a particular data domain, such as database or XML data types versus the native language
XML parsing, iterating, and manipulation can be quite tedious. an XmlDocument must be created just to perform various operations on the XML fragment

Advantages of LINQ

Query is integrated into the language. Gone are the days of writing a SQL query into a string and not detecting a syntax error until runtime, for example we forget the name of a field of the table (or reference) or it has been changed in DB.
In addition to query features, LINQ to XML provides a more powerful and easier-to-use interface for working with XML data, as though it were a database.

Example:

XElement books = XElement.Parse(

       @"<books>

            <book>

            <title>Pro LINQ: Language Integrated Query in C#2010</title>

            <author>Joe Rattz</author>

            </book>

            <book>

            <title>Pro .NET 4.0 Parallel Programming in C#</title>

            <author>Adam Freeman</author>

            </book>

            <book>

            <title>Pro VB 2010 and the .NET 4.0 Platform</title>

            <author>Andrew Troelsen</author>

            </book>

            </books>");

//XElement Xbooks = XElement.Parse(@"XMLFile.xml");

var titles =

from book in books.Elements("book")

where (string)book.Element("author") == "Joe Rattz"

select book.Element("title");

foreach (var title in titles)

    Console.WriteLine(title.Value);

Not only for querying data but for formatting, validating, and even getting data into the necessary format : example string array to integer array and sort. can also use select new for complex classes

Example:

string[] numbers = { "0042", "010", "9", "27" };

int[] nums = numbers.Select(

s => Int32.Parse(s)).OrderBy(s => ).ToArray();

foreach (int num in nums)

Console.WriteLine(num);

Syntax of LINQ

Two syntaxes are available for LINQ queries:

Query expression syntax

from str in strings where str.Length==3 select str;

Standard dot notation syntax

stringList.Where(s => s.Length == 3).Select(s=>s);

Type of LINQ

LINQ to Objects
LINQ to XML
LINQ to DataSet
LINQ to SQL
LINQ to Entities.

More about LINQ

They are queries returning a set of matching objects, a single object, or a subset of fields from an object or set of objects. In LINQ, this returned set of objects is called a sequence, and most LINQ sequences are of type IEnumerable<T>.
We can use the "Cast" or "OfType" operators for Legacy Collections to convert them to IEnumerable to use LINQ.
The LINQ query actually take place the first time a result from it is needed. This is typically when the query results variable is enumerated.
And this may lead to the situation: deferred query is passed undetected with error.--> deferred query example (disadvantage)

Example:

string[] strings = { "one", "two", null, "three" };

Console.WriteLine("Before Where() is called.");

IEnumerable<string> ieStrings = strings.Where(s => s.Length == 3);

Console.WriteLine("After Where() is called.");

foreach (string s in ieStrings)

  Console.WriteLine("Processing " + s);

Calling the actual query each time is needless work. It might make more sense to have a query initialization method that gets called once for the lifetime of the scope and to construct all the queries there (advantage).

List<string> strings = new List<string>();

strings.Add("one");

strings.Add("two");

strings.Add("three");

IEnumerable<string> ieStrings = strings.Where(s => s.Length == 3);

foreach (string s in ieStrings)

  Console.WriteLine("Processing " + s);

Console.ReadKey();

strings.Add("six");

Console.WriteLine("source enumerable changed but query is not invoked again");

//query is not invoked explicitly, ieStrings is not changes

foreach (string s in ieStrings)

  Console.WriteLine("Processing " + s);

In Linq To Entity, string can be passed in where clause as a condition using “it” as variable refenrence.

Example:

VCAB_CMS_DBEntities context = new VCAB_CMS_DBEntities();

string condition = "it.DocName=='Shakuntala Devi'";

int pageCount= context.EDOC.Where(condition).Select(c => c.DocPageCount).First();

Language additions with LINQ

Lambda expressions

i => ((i & 1) == 1)

This expression refers to an anonymous method with left side as input to the method and right side as the output.

Expression trees

IEnumerable<int> numsLessThanFour = nums.Where(i => i < 4).OrderBy(i => i);

The Where operator is called first, followed by the OrderBy operator. But an expression tree allows the simultaneous evaluation and execution of all operators in a query, a single query can be made instead of a separate query for each operator.

The keyword var, object and collection initialization, and anonymous types

var address = new { address = "105 Elm Street",

  city = "Atlanta", state = "GA", postalCode = "30339" };

Compiler generated anonymous class name looks like: <>f__AnonymousType5`4[System.String,System.String,System.String,System.String].
Collection initialization allows you to specify the initialization values for a collection, just like you would for an object:

List<string> presidents = new List<string> { "Adams", "Arthur", "Buchanan" };

Extension methods

Used to extend a sealed class like adding factorial method in int class or adding todouble method in string class.
Extension methods are methods that, although static, can be called on an instance (object) of a class rather than on the class itself.
Specifying a method’s first argument with the “this” keyword modifier will make that method an extension method.
Extension methods can be declared only in static classes.

public static class ExtendsStringClass{

    public static double ToDouble(this string s){

        return Double.Parse(s);

class Program{

static void Main(string[] args){

        double pi = "3.1415926535".ToDouble();

        Console.WriteLine(pi);

        MyWidget myWidget = new MyWidget();

        Console.ReadKey();

Partial methods

A partial method can exist only in a partial class
A partial method must return void.
Partial methods are private but must not specify the private modifier

Example: To make the generated entity classes more usable, partial methods have been added to them. You can add another module that declares the same entity class, implement these partial methods, and be notified every time a property is about to be changed and after it is changed

Query expressions

Query expressions allow LINQ queries to be expressed in nearly SQL form, with just a few minor deviations.
The “from” statement precedes the select statement, hence intelliSense has the scope of what variables to offer you for selection.

LINQ to SQL: Basic Concepts and Features

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

Starting last month, I have started to write a few 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 (last article)
LINQ to SQL: Basic Concepts and Features (this article)
LINQ to SQL: Advanced Concepts and Features (next article)
LINQ to Entities: Basic Concepts and Features (future article)
LINQ to Entities: Advanced Concepts and Features (future article)

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, we learned a few new features of C# 3.0 for LINQ. In this article and the next, we will see how to use LINQ to interact with a SQL Server database, or in other words, how to use LINQ to SQL in C#.
In this article, we will cover the basic concepts and features of LINQ to SQL, which include:

What is ORM
What is LINQ to SQL
What is LINQ to Entities
Comparing LINQ to SQL with LINQ to Objects and LINQ to Entities
Modeling the Northwind database in LINQ to SQL
Querying 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 SQL, such as Stored Procedure support, inheritance, simultaneous updating, and transaction processing.

ORM—Object-Relational Mapping

LINQ to SQL is considered to be one of Microsoft's new ORM products. So before we start explaining LINQ to SQL, 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, an application could 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 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 databases-related activities are handled by the ORM.
The following diagram shows the three different ways of accessing a database from an application. There are some other mechanisms to access a database from an application, such as JDBC and ADO.NET. However, to keep the diagram simple, they have not been shown here.

LINQ to SQL

LINQ to SQL is a component of the .NET Framework version 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 the 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 them back to objects that you can work with in your own programming language.
LINQ to SQL fully supports transactions, views, Stored Procedures, and user-defined functions. It also provides an easy way to integrate data validation and business logic rules into your data model, and supports single table inheritance in the object model.
LINQ to SQL is one of Microsoft's new ORM products to compete with many existing ORM products for the .NET platform on the market, like the Open Source products NHibernate, NPersist, and commercial products LLBLGen and WilsonORMapper. LINQ to SQL has many overlaps with other ORM products, but because it is designed and built specifically for .NET and SQL Server, it has many advantages over other ORM products. For example, it takes the advantages of all the LINQ features and it fully supports SQL Server Stored Procedures. You get all the relationships (foreign keys) for all tables, and the fields of each table just become properties of its corresponding object. You have even the intellisense popup when you type in an entity (table) name, which will list all of its fields in the database. Also, all of the fields and the query results are strongly typed, which means you will get a compiling error instead of a runtime error if you miss spell the query statement or cast the query result to a wrong type. In addition, because it is part of the .NET Framework, you don’t need to install and maintain any third party ORM product in your production and development environments.
Under the hood of LINQ to SQL, ADO.NET SqlClient adapters are used to communicate with real SQL Server databases. We will see how to capture the generated SQL statements at runtime later in this article.
Below is a diagram showing the usage of LINQ to SQL in a .NET application:

We will explore LINQ to SQL features in detail in this article and the following article.

Comparing LINQ to SQL with LINQ to Objects

In the previous article, we used LINQ to query in-memory objects. Before we dive further to the world of LINQ to SQL, we will first look at the relationships between LINQ to SQL and LINQ to Objects.
Followings are some key differences between LINQ to SQL and LINQ to Objects:

LINQ to SQL needs a Data Context object. The Data Context object is the bridge between LINQ and the database. LINQ to Objects doesn’t need any intermediate LINQ provider or API.
LINQ to SQL returns data of type IQueryable<T> while LINQ to Objects returns data of type IEnumerable<T>.
LINQ to SQL is translated to SQL by way of Expression Trees, which allow them to be evaluated as a single unit and translated to the appropriate and optimal SQL statements. LINQ to Objects does not need to be translated.
LINQ to SQL is translated to SQL calls and executed on the specified database while LINQ to Objects is executed in the local machine memory.

The similarities shared between 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 a language syntax point, working with a database is the same as working with in-memory objects.

LINQ to Entities

For LINQ to SQL, another product that you will want to compare with is the .NET Entity Framework. Before comparing LINQ to SQL with the Entity Framework, let’s first see what Entity Framework is.
ADO.NET Entity Framework (EF) was first released with Visual Studio 2008 and .NET Framework 3.5 Service Pack 1. So far, many people view EF as just another ORM product from Microsoft, though by design it is supposed to be much more powerful than just an ORM tool.
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, which has been taken from the July 2008 issue of the MSDN Magazine, shows the architectures of the Entity Framework.

From the diagram, you can see that LINQ is one of the query languages that can be used to query against Entity Framework Entities. LINQ to Entities allows developers to create flexible, strongly typed queries against the Entity Data Model (EDM) by using LINQ expressions and the LINQ standard query operators. It is the same as what LINQ to SQL can do, though LINQ to Entities supports more features than LINQ to SQL, like multiple-table inheritance, and it supports many other mainstream RDBMS databases besides Microsoft SQL Server, like Oracle, DB2, and MySQL.

Comparing LINQ to SQL with LINQ to Entities

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 construct; the entire application operates in terms of the higher-level EDM model.
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, etc.
On the other hand, for developers that do not require mapping to a conceptual model, LINQ to SQL enables developers to experience the LINQ programming model directly over an existing database schema.
LINQ to SQL allows developers to generate .NET classes that represent data. Rather than mapping 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 previously described for in-memory collections, Entities, or the DataSet, as well as 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 underlying database schema changes might require significant client application changes.
So in 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 table below lists some supported features by these two data access methodologies:

Features	LINQ to SQL	LINQ to Entities
Conceptual Data Model	No	Yes
Storage Schema	No	Yes
Mapping Schema	No	Yes
New Data Access Provider	No	Yes
Non-SQL Server Database Support	No	Yes
Direct Database Connection	Yes	No
Language Extensions Support	Yes	Yes
Stored Procedures	Yes	Yes
Single-table Inheritance	Yes	Yes
Multiple-table Inheritance	No	Yes
Single Entity from Multiple Tables	No	Yes
Lazy Loading Support	Yes	Yes

We will use LINQ to SQL in this article, because we will use it in the data access layer, and the data access layer is only one of the three layers for a WCF service. LINQ to SQL is much less complex than LINQ to Entities, so we can still cover it in the same article with WCF. However, once you have learned how to develop WCF services with LINQ to SQL through this article, and you have learned how to use LINQ to Entities through some other means, you can easily migrate your data access layer to using LINQ to Entities.

Creating a LINQtoSQL Test Application

Now that we have learned some basic concepts of LINQ to SQL, next let’s start exploring LINQ to SQL with real examples.
First, we need to create a new project to test LINQ to SQL. We will reuse the solution we have created in the previous article (Introducing LINQ—Language Integrated Query). If you haven't read that article, you can just download the source file from that article, or create a new solution TestLINQ.
You will also need to have a SQL Server database with the sample database Northwind installed. You can just search "Northwind dample 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".
Now follow these steps to add a new application to the solution:

Open (or create) the solution TestLINQ.
From Solution Explorer, right click on the solution item and select Add | New Project … from the context menu.
Select Visual C# | Windows as the project type, and Console Application as the project template, enter TestLINQToSQLApp as the (project) name, and D:\SOAwithWCFandLINQ\Projects\TestLINQ\TestLINQToSQLApp as the location.
Click OK.

Modeling the Northwind Database

The next thing to do is to model the Northwind database. We will now drag and drop two tables and one view from the Northwind database to our project, so later on we can use them to demonstrate LINQ to SQL.

Adding a LINQ to SQL Item to the Project

To start with, let’s add a new item to our project TestLINQToSQLApp. The new item added should be of type LINQ to SQL Classes, and named Northwind, like in the Add New Item dialog window shown below.

After you click the button Add, the following three files will be added to the project: Northwind.dbml, Northwind.dbml.layout, and Northwind.designer.cs. The first file holds the design interface for the database model, while the second one is the XML format of the model. Only one of them can remain open inside the Visual Studio IDE. The third one is the code-behind for the model which defines the DataContext of the model.
At this point, the Visual Studio LINQ to SQL designer should be open and empty, like the following diagram:

Connecting to the Northwind Database

Now we need to connect to our Northwind sample database in order to drag and drop objects from the database.

Open the Server Explorer window from the left most side of the IDE. You can hover your mouse over Server Explorer and wait for a second, or click on the Server Explorer to open it. If it is not visible in your IDE, select the menu View | Server Explorer, or press Ctrl+Alt+S to open it.
From Server Explorer, right click on Data Connections and select Add Connection to bring the add connection window. In this window, specify your server name (including your instance name if it is not a default installation), logon information, and choose Northwind as the database. You can click the button Test Connection to make sure everything is set correctly.

Click OK to add this connection. From now on, Visual Studio will use this database as the default database for your project. You can look at the new file Properties\Settings.Designer.cs for more information.

Adding Tables and Views to the Design Surface

The new connection Northwind.dbo should appear in the Server Explorer now. Next, we will drag and drop two tables and one view to the LINQ to SQL design surface.

Expand the connection until all the tables are listed, and drag Products to the Northwind.dbml design surface. You should have a screen like in this diagram:

Then drag the Categories table from Server Explorer to the Northwind.dbml design surface.
We will also need to query data using a view, so drag the view Current Product List from Server Explorer to the Northwind.dbml design surface.

The Northwind.dbml design surface on your screen should look like this:

Generated LINQ to SQL Classes

If you open the file Northwind.Designer.cs, you will find following classes are generated for the project:

Collapse | Copy Code

public partial class NorthwindDataContext : System.Data.Linq.DataContext

public partial class Product : INotifyPropertyChanging, INotifyPropertyChanged

public partial class Category : INotifyPropertyChanging, INotifyPropertyChanged

public partial class Current_Product_List

Among the above four classes, the DataContext class is the main conduit by 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 the included tables. It inherits from the System.Data.Linq.DataContext class which represents the main entry point for the LINQ to SQL framework.
The next two classes are for those two tables we are interested in. They all implement the INotifyPropertyChanging and INotifyPropertyChanged interfaces. These two interfaces define all 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. It is a simple class with only two property members. Since we are not going to update the database through this view, it doesn’t define any property changing 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, 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 SQL, we first need to construct a DataContext object, like this:

Collapse | Copy Code

NorthwindDataContext db = new NorthwindDataContext();

Then we can use this LINQ query syntax to retrieve records from the database:

Collapse | Copy Code

IEnumerable<Product> beverages = from p in db.Products

                     where p.Category.CategoryName == "Beverages"

                     orderby p.ProductName

                     select p;

The preceding code will retrieve all products in the Beverages category sorted by product name.

Updating Records

We can update any of the products that we have just retrieved from the database, like this:

Collapse | Copy Code

// update one product

Product bev1 = beverages.ElementAtOrDefault(10);

if (bev1 != null)

    Console.WriteLine("The price of {0} is {1}. Update to 20.0",

                      bev1.ProductName, bev1.UnitPrice);

    bev1.UnitPrice = (decimal)20.00;

// submit the change to database

db.SubmitChanges();

We used ElementAtOrDefault, not the ElementAt method, just in case there is no product at element 10. Though, in the sample database, there are 12 beverage products, and the 11th (element 10 starting from index 0) is Steeleye Stout, whose unit price is 18.00. We change its price to 20.00, and called db.SubmitChanges() to update the record in the database. After you run the program, if you query the product with ProductID 35, you will find its price is now 20.00.

Inserting Records

We can also create a new product, then insert this new product into the database, like in the following code:

Collapse | Copy Code

Product newProduct = new Product {ProductName="new test product" };

db.Products.InsertOnSubmit(newProduct);

db.SubmitChanges();

Deleting Records

To delete a product, we first need to retrieve it from the database, then just call the DeleteOnSubmit method, like in the following code:

Collapse | Copy Code

// delete a product

Product delProduct = (from p in db.Products

                     where p.ProductName == "new test product"

                     select p).FirstOrDefault();

if(delProduct != null)

    db.Products.DeleteOnSubmit(delProduct);

db.SubmitChanges();

Running the Program

The file Program.cs so far is followed. Note that we declared db as a class member, and added a method to contain all the test cases for the table operations. We will add more methods to test other LINQ to SQL functionalities.

Collapse | Copy Code

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.Linq;

namespace TestLINQToSQLApp

    class Program

        // create data context

        static NorthwindDataContext db = new NorthwindDataContext();

        static void Main(string[] args)

            // CRUD operations on tables

            TestTables();

            Console.ReadLine();

        static void TestTables()

            // retrieve all Beverages

            IEnumerable<Product> beverages = from p in db.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)

                Console.WriteLine("The price of {0} is {1}. Update to 20.0",

                                  bev1.ProductName, bev1.UnitPrice);

                bev1.UnitPrice = (decimal)20.0;

            // submit the change to database

            db.SubmitChanges();

            // insert a product

            Product newProduct = new Product { ProductName = "new test product" };

            db.Products.InsertOnSubmit(newProduct);

            db.SubmitChanges();

            Product newProduct2 = (from p in db.Products

                                   where p.ProductName == "new test product"

                                   select p).SingleOrDefault();

            if (newProduct2 != null)

                Console.WriteLine("new product inserted with product ID {0}",

                                  newProduct2.ProductID);

            // delete a product

            Product delProduct = (from p in db.Products

                                  where p.ProductName == "new test product"

                                  select p).FirstOrDefault();

            if (delProduct != null)

                db.Products.DeleteOnSubmit(delProduct);

            db.SubmitChanges();

If you run the program, the output will be:

Deferred Execution

One important thing to remember when working with LINQ to SQL 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. Those methods do not consume the target data until the query object is enumerated. This is known as deferred execution.
In the case of methods that operate on in-memory collections, that is, those methods that extend

IEnumerable<(Of
<(T>)>)

, the returned enumerable object captures 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

There are two ways to see when the query is executed. The first is Open Profiler (All Programs\Microsoft SQL Server 2005(or 2008)\Performance Tools\SQL 2005(or 2008) Profiler); start a new trace to the Northwind database engine, then debug the program. For example, when the following statement is executed, there is nothing in the profiler:

Collapse | Copy Code

IEnumerable<Product> beverages = from p in db.Products

                    where p.Category.CategoryName == "Beverages"

                    orderby p.ProductName

                    select p;

However, when the following statement is being executed, from the profiler, you will see a query is executed in the database:

Collapse | Copy Code

Console.WriteLine("There are {0} Beverages", beverages.Count());

The query executed in the database is like this:

Collapse | Copy Code

exec sp_executesql N'SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID],

    [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice],

    [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]

FROM [dbo].[Products] AS [t0]

LEFT OUTER JOIN [dbo].[Categories] AS [t1] ON [t1].[CategoryID] = [t0].[CategoryID]

WHERE [t1].[CategoryName] = @p0

ORDER BY [t0].[ProductName]',N'@p0 nvarchar(9)',@p0=N'Beverages'

The profiler window should be like this diagram:

From the profiler, we know under the hood that LINQ actually called sp_executesql, and it also used a left outer join to get the categories of products.

Checking Deferred Execution with SQL Logs

Another way to trace the execution time of a LINQ statement is using logs. The DataContext class provides a method to log every SQL statement it executes. To see the logs, we can first add this statement to the program in the beginning, right after Main:

Collapse | Copy Code

db.Log = Console.Out;

Then we can add this statement right after the variable beverages is defined, but before its Count is referenced:

Collapse | Copy Code

Console.WriteLine("After query syntax is defined, before it is referenced.");

So the first few lines of statements are now like this:

Collapse | Copy Code

static void Main(string[] args)

    // log database query statements to stand out

    db.Log = Console.Out;

    // CRUD operations on tables

    TestTables();

    Console.ReadLine();

static void TestTables()

    // retrieve all Beverages

    IEnumerable<Product> beverages = from p in db.Products

                where p.Category.CategoryName == "Beverages"

                orderby p.ProductName

                select p;

    Console.WriteLine("After query syntax beverages is defined, " +

                      "before it is referenced.");

    Console.WriteLine("There are {0} Beverages", beverages.Count());

// rest of the file

Now if you run the program, the output will be like this:

From the logs, we see the query is not executed when the query syntax is defined. Instead, it is executed when beverages.Count() is being called.

Deferred Execution for Singleton Methods

But if the query expression will return a singleton value, the query will be executed immediately while it is defined. For example, we can add this statement to get the average price of all products:

Collapse | Copy Code

decimal? averagePrice = (from p in db.Products

                         select p.UnitPrice).Average();

Console.WriteLine("After query syntax averagePrice is defined, before it is referenced.");

Console.WriteLine("The average price is {0}", averagePrice);

The output is like this:

From this output, we know the query is executed at the same time when the query syntax is defined.

Deferred Execution for Singleton Methods Within Sequence Expressions

However, just because a query is using one of those singleton methods like Sum, Average, or Count, it doesn’t mean the query will be executed when it is defined. If the query result is a sequence, the execution will still be deferred. Following is an example of this kind of query:

Collapse | Copy Code

// deferred execution2

var cheapestProductsByCategory =

    from p in db.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:");

foreach (var p in cheapestProductsByCategory)

    Console.WriteLine("categery {0}: product name: {1} price: {2}",

            p.CategoryID, p.CheapestProduct.ProductName, p.CheapestProduct.UnitPrice);

If you run the above query, you will see it is executed when the result is being printed, not when the query is being defined. Part of the result is like this:

From this output, you can see when the result is being printed, it first goes to the database to get the minimum price for each category, then for each category, it goes to the database again to get the first product with that price. Though in a real product, you probably don’t want to write so complex a query in your application code, but put it in a Stored Procedure.

Deferred (Lazy) Loading Versus Eager Loading

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

Collapse | Copy Code

p.Category.CategoryName == "Beverages"

Even though there is no such field called category name in the Products table, we can still get the category name of a product because there is an association between the Products and Category table. On the Northwind.dbml design surface, click on the line between the Products and Categories tables and you will see all the properties of the association. Note, its participating properties are Category.CategoryID -> Product.CategoryID, meaning 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 the products for each category.

Lazy Loading by Default

However, even with the association, the associated data is not loaded when the query is executed. For example, if we retrieve all categories like this:

Collapse | Copy Code

var categories = from c in db.Categories select c;

And later on we need to get the products for each category, the database has to be queried again. This diagram shows the execution result of the query:

From this diagram, we know that LINQ first goes to the database to query all categories, then for each category, when we need to get the total count of products, it goes to the database again to query all the products for that category.
This is because by default, lazy loading is set to true, meaning all associated data (children) are deferred loaded until needed.

Eager Loading With Load Options

To change this behavior, we can use the LoadWith method to tell the DataContext to automatically load the specified children in the initial query, like this:

Collapse | Copy Code

// eager loading products of categories

DataLoadOptions dlo2 = new DataLoadOptions();

dlo2.LoadWith<Category>(c => c.Products);

// create another data context, because we can't change LoadOptions of db

// once a query has been executed against it

NorthwindDataContext db2 = new NorthwindDataContext();

db2.Log = Console.Out;

db2.LoadOptions = dlo2;

var categories2 = from c in db2.Categories select c;

foreach (var category2 in categories2)

    Console.WriteLine("There are {0} products in category {1}",

                      category2.Products.Count(), category2.CategoryName);

db2.Dispose();

Note: DataLoadOptions is in the namespace System.Data.Linq, so you have to add a using statement to the program:

Collapse | Copy Code

using System.Data.Linq;

Also, we have to create a new DataContext instance for this test, because we have ran some queries again the original db DataContext, and it is no longer possible to change its LoadOptions.
Now after the category is loaded, all its children (products) will be loaded too. This can be proved from this diagram:

As you can see from this diagram, all products for all categories are loaded in the first query.

Filtered Loading With Load Options

While LoadWith is used to eager load all children, AssociateWith can be used to filter which children to load with. For example, if we only want to load products for categories 1 and 2, we can write this query:

Collapse | Copy Code

// eager loading only certain children

DataLoadOptions dlo3 = new DataLoadOptions();

dlo3.AssociateWith<Category>(

     c => c.Products.Where(p => p.CategoryID == 1 || p.CategoryID == 2));

// create another data context, because we can't change LoadOptions of db

// once query has been executed against it

NorthwindDataContext db3 = new NorthwindDataContext();

db3.LoadOptions = dlo3;

db3.Log = Console.Out;

var categories3 = from c in db3.Categories select c;

foreach (var category3 in categories3)

    Console.WriteLine("There are {0} products in category {1}",

                      category3.Products.Count(), category3.CategoryName);

db3.Dispose();

Now if we query all categories and print out the products count for each category, we will find that only the first two categories contain products, all other categories have no product at all, like in this diagram:

Combining Eager Loading and Filtered Loading

However, from the output above, you can see it is lazy loading. If you want eager loading products with some filters, you can combine LoadWith and AssociateWith, like in the following code:

Collapse | Copy Code

DataLoadOptions dlo4 = new DataLoadOptions();

dlo4.LoadWith<Category>(c => c.Products);

dlo4.AssociateWith<Category>(c => c.Products.Where(

     p => p.CategoryID == 1 || p.CategoryID == 2));

// create another data context, because we can't change LoadOptions of db

// once q query has been executed

NorthwindDataContext db4 = new NorthwindDataContext();

db4.Log = Console.Out;

db4.LoadOptions = dlo4;

var categories4 = from c in db4.Categories select c;

foreach (var category4 in categories4)

    Console.WriteLine("There are {0} products in category {1}",

                      category4.Products.Count(), category4.CategoryName);

db4.Dispose();

The output is like this diagram:

Note for each field of an entity, you can also set its Delay Loaded property to change its loading behavior. This is different from the children lazy/eager loading, as it only affects one property of that particular entity.

Joining Two Tables

While associations are kinds of joins, in LINQ, we can also explicitly join two tables using the keyword Join, like in the following code:

Collapse | Copy Code

var categoryProducts =

    from c in db.Categories

    join p in db.Products on c.CategoryID equals p.CategoryID into products

    select new {c.CategoryName, productCount = products.Count()};

foreach (var cp in categoryProducts)

    Console.WriteLine("There are {0} products in category {1}",

                      cp.CategoryName, cp.productCount);

It is not so useful in the above example because the tables Products and Categories are associated with a foreign key relationship. When there is no foreign key association between two tables, this will be particularly useful.
From the output, we can see only one query is executed to get the results:

Besides joining two tables, you can also join three or more tables, join self, create left /right outer join, or join using composite keys.

Querying With a View

Querying with a view is the same as with a table. For example, you can call the view “current product lists” like this:

Collapse | Copy Code

var currentProducts = from p in db.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 all 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 SQL is. We also compared LINQ to SQL with LINQ to Entities and explored some basic features of LINQ to SQL.
The key points in this article include:

An ORM product can greatly ease data access layer development.
LINQ to SQL is one of Microsoft’s ORM products to use LINQ against SQL Server databases.
The built-in LINQ to SQL designer in Visual Studio 2008 can be used to model databases.
You can connect to a database in Visual Studio 2008 Server Explorer then drag and drop database items to the LINQ to SQL design surface.
The class System.Data.Linq.DataContext is the main class for LINQ to SQL applications.
LINQ methods that return a sequence defer the query execution and you can check the execution timing with Profiler, or SQL logs.
LINQ query expressions that return a singleton value will be executed immediately while they are defined.
By default, associated data is deferred (lazy) loaded. You can change this behavior with the LoadWith option.
Associated data results can be filtered with the AssociateWith option.
Options LoadWith and AssociateWith can be combined together to eager load associated data with filters.
The Join operator can be used to join multiple tables and views.
Views can be used to query a database in LINQ to SQL just like tables.

Note: this article is based on chapter 10 of my old book "WCF Multi-tier Services Development with LINQ" (ISBN 1847196624). Since LINQ to SQL is now not preferred by Microsoft, this book has been upgraded to using LINQ to Entities in my new book "WCF 4.0 Multi-tier Services Development with LINQ to Entities" (ISBN 1849681147). Both books are hands-on guides to learn how to build SOA applications on the Microsoft platform, with the old one using WCF and LINQ to SQL in Visual Studio 2008 and the new one using WCF and LINQ to Entities in Visual Studio 2010.
With either book, you can learn how to master WCF and LINQ to SQL/LINQ to Entities concepts by completing practical examples and applying them to your real-world assignments. They are among the first of few books to combine WCF and LINQ to SQL/LINQ to Entities in a multi-tier real-world WCF Service. They are ideal for beginners who want to learn how to build scalable, powerful, easy-to-maintain WCF Services. Both books are rich with example code, clear explanations, interesting examples, and practical advice. They are truly hands-on books for C++ and C# developers.
You don't need to have any experience in WCF or LINQ to SQL/LINQ to Entities to read either book. Detailed instructions and precise screenshots will guide you through the whole process of exploring the new worlds of WCF and LINQ to SQL/LINQ to Entities. These two books are distinguished from other WCF and LINQ to SQL/LINQ to Entities books by that, they focus 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 SQL/LINQ to Entities. Once you have finished one of the books, you will be proud that you have been working with WCF and LINQ to SQL/LINQ to Entities in the most straightforward way.
You can buy either book from Amazon (search WCF and LINQ), or from the publisher's website at https://www.packtpub.com/wcf-4-0-multi-tier-services-development-with-linq-to-entities/book.

LINQ to Entities: Basic Concepts and Features

Introduction

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

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:

Features	LINQ to SQL	LINQ to Entities
Conceptual Data Model	No	Yes
Storage Schema	No	Yes
Mapping Schema	No	Yes
New Data Access Provider	No	Yes
Non-SQL Server Database Support	No	Yes
Direct Database Connection	Yes	No
Language Extensions Support	Yes	Yes
Stored Procedures	Yes	Yes
Single-table Inheritance	Yes	Yes
Multiple-table Inheritance	No	Yes
Single Entity from Multiple Tables	No	Yes
Lazy Loading Support	Yes	Yes

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:

Open the solution TestLINQ
From Solution Explorer, right-click on the Solution item and select Add | New Project… from the context menu
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
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:

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.
Click Next on this window.

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.

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.

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.
Then specify the logon to your database.
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.
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:

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:

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:

Collapse | Copy Code

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:

Collapse | Copy Code

NorthwindEntities NWEntities = new NorthwindEntities();

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

Collapse | Copy 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 product name.
You can use this statement to print out the total number of beverage products in the Northwind database:

Collapse | Copy Code

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:

Collapse | Copy Code

// 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:

Collapse | Copy 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:

Collapse | Copy 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.

Collapse | Copy Code

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:

Collapse | Copy Code

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:

Collapse | Copy Code

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:

Collapse | Copy Code

// 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:

Collapse | Copy Code

using System.Data.Objects;

The file Program.cs now should be like this:

Collapse | Copy Code

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:

Collapse | Copy Code

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:

Open Profiler (All Programs\Microsoft SQL Server 2005(or 2008)\Performance Tools\SQL 2005(or 2008) Profiler).
Start a new trace on the Northwind database engine.
Go back to Visual Studio, set a break point on the first line of the TestDeferredExecution method.
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:

Collapse | Copy 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:

Collapse | Copy Code

Console.WriteLine("There are {0} Beverages", beverages.Count());

The query executed in the database is like this:

Collapse | Copy Code

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:

Collapse | Copy Code

// 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:

Collapse | Copy Code

// 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:

Collapse | Copy Code

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:

Collapse | Copy Code

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:

Collapse | Copy Code

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:

Collapse | Copy Code

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:

Collapse | Copy Code

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:

Collapse | Copy Code

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:

Collapse | Copy Code

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:

Collapse | Copy Code

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:

Collapse | Copy 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:

Collapse | Copy Code

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:

Collapse | Copy Code

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.

Pages

Thursday, July 11, 2013

LINQ Complete Tutorials

LINQ Tutorial for Beginners

LINQ

Need for LINQ

Advantages of LINQ

Syntax of LINQ

Type of LINQ

More about LINQ

Language additions with LINQ

Lambda expressions

Expression trees

The keyword var, object and collection initialization, and anonymous types

Extension methods

Partial methods

Query expressions

LINQ to SQL: Basic Concepts and Features

Overview

ORM—Object-Relational Mapping

LINQ to SQL

Comparing LINQ to SQL with LINQ to Objects

LINQ to Entities

Comparing LINQ to SQL with LINQ to Entities

Creating a LINQtoSQL Test Application

Modeling the Northwind Database

Adding a LINQ to SQL Item to the Project

Connecting to the Northwind Database

Adding Tables and Views to the Design Surface

Generated LINQ to SQL Classes

Querying and Updating the Database with a Table

Querying Records

Updating Records

Inserting Records

Deleting Records

Running the Program

Deferred Execution

Checking Deferred Execution with SQL Profiler

Checking Deferred Execution with SQL Logs

Deferred Execution for Singleton Methods

Deferred Execution for Singleton Methods Within Sequence Expressions

Deferred (Lazy) Loading Versus Eager Loading

Lazy Loading by Default

Eager Loading With Load Options

Filtered Loading With Load Options

Combining Eager Loading and Filtered Loading

Joining Two Tables

Querying With a View

Summary

Introduction

Overview

ORM—Object-Relational Mapping

Entity Framework

LINQ to Entities

Comparing LINQ to Entities with LINQ to Objects

LINQ to SQL

Comparing LINQ to SQL with LINQ to Entities

Creating a LINQ to Entities Test Application

Creating the Data Model

Preparing the Northwind Database

Adding a LINQ to Entities Item to the Project

Generated LINQ to Entities Classes

Querying and Updating the Database with a Table

Querying Records

Updating Records

Inserting Records

Deleting Records

Running the Program

View Generated SQL Statements

View SQL Statements Using ToTraceString

View SQL Statements Using Profiler

Deferred Execution

Checking Deferred Execution With SQL Profiler

Deferred Execution for Singleton Methods

Deferred Execution for Singleton Methods Within Sequence Expressions

Deferred (Lazy) Loading Versus Eager Loading

Lazy Loading by Default

Eager Loading With Include Method

Joining Two Tables

Querying a View