How to: Filter Data (Entity Framework)

This topic shows how to filter query results. The example returns a collection of SalesOrderHeader objects that represents all online orders more than $2,500. The same example is shown using the following Entity Framework query technologies:

• LINQ to Entities

• Entity SQL with ObjectQuery<T>

• Query builder methods of ObjectQuery<T>

The examples in this topic are based on the Adventure Works Sales Model. To run the code in this example, you must have already added the AdventureWorks Sales Model to your project and configured your project to use the Entity Framework. To do this, complete the procedures in How to: Manually Configure an Entity Framework Project and How to: Manually Define an Entity Data Model (Entity Framework). You can also use the Entity Data Model Wizard to define the AdventureWorks Sales Model. For more information, see How to: Use the Entity Data Model Wizard (Entity Framework).

Example

The following is the LINQ to Entities example.

Using context As AdventureWorksEntities = _
New AdventureWorksEntities()

    ' Specify the order amount.
    Dim orderCost = 2500

    Try
        ' Define a LINQ query that returns only online orders
        ' more than the specified amount.
        Dim onlineOrders = _
        From order As SalesOrderHeader In context.SalesOrderHeader() _
            Where order.OnlineOrderFlag = True And _
            order.TotalDue > orderCost _
            Select order

        ' Print order information.
        For Each onlineOrder In onlineOrders

            Console.WriteLine("Order ID:{0}Order date: " _
                & "{1}:d Order number: {2}", _
                onlineOrder.SalesOrderID, _
                onlineOrder.OrderDate, _
                onlineOrder.SalesOrderNumber)
        Next
    Catch ex As EntitySqlException
        Console.WriteLine(ex.ToString())
    End Try
End Using

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    // Specify the order amount.
    int orderCost = 2500;

    try
    {
        // Define a LINQ query that returns only online orders
        // more than the specified amount.
        var onlineOrders =
            from order in context.SalesOrderHeader
            where order.OnlineOrderFlag == true &&
            order.TotalDue > orderCost
            select order;

        // Print order information.
        foreach (var onlineOrder in onlineOrders)
        {
            Console.WriteLine("Order ID: {0} Order date: "
                + "{1:d} Order number: {2}",
                onlineOrder.SalesOrderID,
                onlineOrder.OrderDate,
                onlineOrder.SalesOrderNumber);
        }
    }
    catch (EntitySqlException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

The following is the Entity SQL example.

Using context As AdventureWorksEntities = _
New AdventureWorksEntities()

    ' Specify the order amount.
    Dim orderCost = 2500

    ' Specify the Entity SQL query that returns only online orders
    ' more than the specified amount.
    Dim queryString = "SELECT VALUE o FROM SalesOrderHeader AS o " _
    & "WHERE o.OnlineOrderFlag = TRUE AND o.TotalDue > @ordercost"

    Try
        ' Define an ObjectQuery and pass the maxOrderCost parameter.
        Dim onlineOrders As ObjectQuery(Of SalesOrderHeader) = _
            New ObjectQuery(Of SalesOrderHeader)(queryString, context)
        onlineOrders.Parameters.Add( _
            New ObjectParameter("ordercost", orderCost))

        ' Print order information.
        For Each onlineOrder In onlineOrders
            Console.WriteLine("Order ID:{0}Order date: " _
                & "{1}:d Order number: {2}", _
                onlineOrder.SalesOrderID, _
                onlineOrder.OrderDate, _
                onlineOrder.SalesOrderNumber)
        Next
    Catch ex As EntitySqlException
        Console.WriteLine(ex.ToString())
    End Try
End Using

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    // Specify the order amount.
    decimal orderCost = 2500;

    // Specify the Entity SQL query that returns only online orders
    // more than the specified amount.
    string queryString = @"SELECT VALUE o FROM SalesOrderHeader AS o 
        WHERE o.OnlineOrderFlag = TRUE AND o.TotalDue > @ordercost";

    try
    {
        // Define an ObjectQuery and pass the maxOrderCost parameter.
        ObjectQuery<SalesOrderHeader> onlineOrders =
            new ObjectQuery<SalesOrderHeader>(queryString, context);
        onlineOrders.Parameters.Add(
            new ObjectParameter("ordercost", orderCost));

        // Print order information.
        foreach (var onlineOrder in onlineOrders)
        {
            Console.WriteLine("Order ID: {0} Order date: "
                + "{1:d} Order number: {2}",
                onlineOrder.SalesOrderID,
                onlineOrder.OrderDate,
                onlineOrder.SalesOrderNumber);
        }
    }
    catch (EntitySqlException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

The following is the query builder method example.

Using context As AdventureWorksEntities = _
New AdventureWorksEntities()

    ' Specify the order amount.
    Dim orderCost = 2500

    Try

        ' Define an ObjectQuery that returns only online orders 
        ' more than the specified amount.
        Dim onlineOrders As ObjectQuery(Of SalesOrderHeader) = _
        context.SalesOrderHeader.Where("it.OnlineOrderFlag = TRUE") _
            .Where("it.TotalDue > @ordercost", _
            New ObjectParameter("ordercost", orderCost))

        ' Print order information.
        For Each onlineOrder In onlineOrders
            Console.WriteLine("Order ID:{0}Order date: " _
                & "{1}:d Order number: {2}", _
                onlineOrder.SalesOrderID, _
                onlineOrder.OrderDate, _
                onlineOrder.SalesOrderNumber)
        Next
    Catch ex As EntitySqlException
        Console.WriteLine(ex.ToString())
    End Try
End Using

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    // Specify the order amount.
    int orderCost = 2500;

   try
    {
        // Define an ObjectQuery that returns only online orders 
        // more than the specified amount.
        ObjectQuery<SalesOrderHeader> onlineOrders =
            context.SalesOrderHeader
            .Where("it.OnlineOrderFlag = TRUE AND it.TotalDue > @ordercost",
            new ObjectParameter("ordercost", orderCost));

        // Print order information.
        foreach (var onlineOrder in onlineOrders)
        {
            var items = (from item in context.SalesOrderDetail
                        where item.SalesOrderHeader == onlineOrder
                        select item).Take(3);
             Console.WriteLine("Order ID: {0} Order date: "
                + "{1:d} Order number: {2}",
                onlineOrder.SalesOrderID,
                onlineOrder.OrderDate,
                onlineOrder.SalesOrderNumber);
        }
    }
    catch (EntitySqlException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

See Also

Other Resources

Querying an Entity Data Model (Entity Framework Tasks)