Export (0) Print
Expand All

How to: Execute a Parameterized Query (Entity Framework)

This topic shows how to execute an Entity SQL query with parameters using ObjectQuery. The example passes two parameters to the ObjectQuery, executes the query, and iterates through the collection of Contact items. The same example is shown using each of the following Entity Framework query technologies:

The example in this topic is based on the AdventureWorks Sales Model (EDM). 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 Entity SQL example.

using (AdventureWorksEntities advWorksContext =
    new AdventureWorksEntities())
{
    try
    {
        // Create a query that takes two parameters.
        string queryString =
            @"SELECT VALUE Contact FROM AdventureWorksEntities.Contact 
                    AS Contact WHERE Contact.LastName = @ln AND
                    Contact.FirstName = @fn";

        ObjectQuery<Contact> contactQuery =
            new ObjectQuery<Contact>(queryString, advWorksContext);

        // Add parameters to the collection.
        contactQuery.Parameters.Add(new ObjectParameter("ln", "Adams"));
        contactQuery.Parameters.Add(new ObjectParameter("fn", "Frances"));

        // Iterate through the collection of Contact items.
        foreach (Contact result in contactQuery)
            Console.WriteLine("Last Name: {0}; First Name: {1}",
            result.LastName, result.FirstName);
    }
    catch (EntityException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    catch (InvalidOperationException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

This is the query builder method example.

string firstName = @"Frances";
string lastName = @"Adams";

using (AdventureWorksEntities context =
    new AdventureWorksEntities())
{
    try
    {
        // Get the contacts with the specified name.
        ObjectQuery<Contact> contactQuery = context.Contact
            .Where("it.LastName = @ln AND it.FirstName = @fn",
            new ObjectParameter("ln", lastName), 
            new ObjectParameter("fn", firstName));

        // Iterate through the collection of Contact items.
        foreach (Contact result in contactQuery)
            Console.WriteLine("Last Name: {0}; First Name: {1}",
            result.LastName, result.FirstName);
    }
    catch (EntitySqlException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

See Also

Community Additions

Show:
© 2014 Microsoft