Export (0) Print
Expand All

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:

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 (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 (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 (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

Community Additions

Show:
© 2014 Microsoft