Queries in LINQ to Entities

A query is an expression that retrieves data from a data source. Queries are usually expressed in a specialized query language, such as SQL for relational databases and XQuery for XML. Therefore, developers have had to learn a new query language for each type of data source or data format that they query. Language-Integrated Query (LINQ) offers a simpler, consistent model for working with data across various kinds of data sources and formats. In a LINQ query, you always work with programming objects.

A LINQ query operation consists of three actions: obtain the data source or sources, create the query, and execute the query.

Data sources that implement the IEnumerable generic interface or the IQueryable generic interface can be queried through LINQ. Instances of the generic ObjectQuery class, which implements the generic IQueryable interface, serve as the data source for LINQ to Entities queries. The ObjectQuery generic class represents a query that returns an instance or collection of typed entities. The developer constructs an instance from the ObjectContext, which is the primary class for interacting with an Entity Data Model (EDM) as CLR objects.

In the query, you specify exactly the information that you want to retrieve from the data source. A query can also specify how that information should be sorted, grouped, and shaped before it is returned. In LINQ, a query is stored in a variable. If the query returns a sequence of values, the query variable itself must be a queryable type. This query variable takes no action and returns no data; it only stores the query information. After you create a query you must execute that query to retrieve any data.

In a query that returns a sequence of values, the query variable itself never holds the query results and only stores the query commands. Execution of the query is deferred until the query variable is iterated over in a foreach or For Each loop. This is known as deferred execution; that is, query execution occurs some time after the query is constructed. This means that you can execute a query as frequently as you want to. This is useful when, for example, you have a database that is being updated by other applications. In your application, you can create a query to retrieve the latest information and repeatedly execute the query, returning the updated information every time.

In contrast to deferred queries, which return a sequence of values, queries that return a singleton value are executed immediately. Some examples of singleton queries are Count, Max, Average, and First. These execute immediately because the query results are required in order to calculate the singleton result. You can also use the ToList or ToArray methods on a query to force immediate execution of a query that does not produce a singleton value. These techniques to force immediate execution can be useful when you want to cache the results of a query. For more information about deferred and immediate query execution, see Introduction to LINQ.

Queries

LINQ to Entities queries can be composed in two different syntaxes: query expression syntax and method-based query syntax. Query expression syntax is new in C# 3.0 and Visual Basic 9.0, and it consists of a set of clauses written in a declarative syntax similar to Transact-SQL or XQuery. However, the .NET Framework common language runtime (CLR) cannot read the query expression syntax itself. Therefore, at compile time, query expressions are translated to something that the CLR does understand: method calls. These methods are known as the standard query operators. As a developer, you have the option of calling them directly by using method syntax, instead of using query syntax. For more information, see LINQ Query Syntax versus Method Syntax (C#). For more information about how to use the standard query operators, see LINQ General Programming Guide.

Query Expression Syntax

Query expressions are a declarative query syntax. This syntax enables a developer to write queries in a high-level language that is formatted similar to Transact-SQL. By using query expression syntax, you can perform even complex filtering, ordering, and grouping operations on data sources with minimal code. For more information, see LINQ Query Expressions (C# Programming Guide) and Basic Query Operations (Visual Basic).

The following example uses Select to return all the rows from Product and display the product names.

Using AWEntities As New AdventureWorksEntities
    Dim products As ObjectQuery(Of Product) = AWEntities.Product

    Dim productNames = _
       From p In products _
       Select p.Name

    Console.WriteLine("Product Names:")
    For Each productName In productNames
        Console.WriteLine(productName)
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Product> products = AWEntities.Product;

    IQueryable<string> productNames =
       from p in products
       select p.Name;

    Console.WriteLine("Product Names:");
    foreach (var productName in productNames)
    {
        Console.WriteLine(productName);
    }
}

Method-Based Query Syntax

Another way to compose LINQ to Entities queries is by using method-based queries. The method-based query syntax is a sequence of direct method calls to LINQ operator methods, passing lambda expressions as the parameters. For more information, see Lambda Expressions (C# Programming Guide).

This example uses Select to return all the rows from Product and display the product names.

Using AWEntities As New AdventureWorksEntities


    Dim productNames = AWEntities.Product.Select(Function(p) p.Name)


    Console.WriteLine("Product Names:")
    For Each productName In productNames
        Console.WriteLine(productName)
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Product> products = AWEntities.Product;

    IQueryable<string> productNames = products.Select(p => p.Name);


    Console.WriteLine("Product Names:");
    foreach (var productName in productNames)
    {
        Console.WriteLine(productName);
    }
}

Composing Queries

As mentioned earlier in this topic, the query variable itself only stores the query commands when the query is designed to return a sequence of values. If the query does not contain a method that will cause immediate execution, the actual execution of the query is deferred until you iterate over the query variable in a foreach or For Each loop. Deferred execution enables multiple queries to be combined or a query to be extended. When a query is extended, it is modified to include the new operations, and the eventual execution will reflect the changes. In the following example, the first query returns all the products. The second query extends the first by using Where to return all the products of size "L":

Using AWEntities As New AdventureWorksEntities()
    Dim products As ObjectQuery(Of Product) = AWEntities.Product

    Dim productsQuery = _
        From p In products _
        Select p

    Dim largeProducts = _
        productsQuery.Where(Function(p) p.Size = "L")

    Console.WriteLine("Products of size 'L':")
    For Each product In largeProducts
        Console.WriteLine(product.Name)
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{

    ObjectQuery<Product> products = AWEntities.Product;

    IQueryable<Product> productsQuery =
        from p in products
        select p;

    IQueryable<Product> largeProducts = productsQuery.Where(p => p.Size == "L");

    Console.WriteLine("Products of size 'L':");
    foreach (var product in largeProducts)
    {
        Console.WriteLine(product.Name);
    }
}

After a query has been executed all successive queries will use the in-memory LINQ operators. Iterating over the query variable by using a foreach or For Each statement or by calling one of the LINQ conversion operators will cause immediate execution. These conversion operators include the following: ToList, ToArray, ToLookup, and ToDictionary.

In the following example, the first query returns all the products. The second query extends the first after it has been executed to return the red products.

Using AWEntities As New AdventureWorksEntities()
    Dim products As ObjectQuery(Of Product) = AWEntities.Product

    Dim productsQuery = _
        From p In products _
        Select p

    Console.WriteLine("The list of products:")
    For Each Product As Product In productsQuery
        Console.WriteLine(Product.Name)
    Next

    Dim redProducts = productsQuery _
        .Where(Function(p) p.Color = "Red") _
        .Select(Function(p) p)

    Console.WriteLine("")
    Console.WriteLine("The list of red products:")
    For Each redProduct As Product In redProducts
        Console.WriteLine(redProduct.Name)
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{

    ObjectQuery<Product> products = AWEntities.Product;

    IQueryable<Product> productsQuery =
        from p in products
        select p;

    Console.WriteLine("The list of products:");
    foreach (Product product in productsQuery)
    {
        Console.WriteLine(product.Name);
    }

    IQueryable<Product> redProducts = productsQuery
        .Where(p => p.Color == "Red")
        .Select(p => p);

    Console.WriteLine("");
    Console.WriteLine("The list of red products:");
    foreach (Product redProduct in redProducts)
    {
        Console.WriteLine(redProduct.Name);
    }

}

See Also

Other Resources

LINQ to Entities
Getting Started with LINQ in C#
Getting Started with LINQ in Visual Basic