How to: Execute a Parameterized Query

Article
08/29/2011

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:

LINQ to Entities
Entity SQL with ObjectQuery<T>
Query builder methods of ObjectQuery<T>

The example in this topic is based on the Adventure Works Sales Model. To run the code in this topic, you must have already added the Adventure Works Sales Model to your project and configured your project to use the Entity Framework. For more information, see How to: Use the Entity Data Model Wizard (Entity Framework) or How to: Manually Configure an Entity Framework Project and How to: Manually Define an Entity Data Model (Entity Framework).

Example

The following is the LINQ to Entities example.

Using context As New AdventureWorksEntities()

    Dim FirstName = "Frances"
    Dim LastName = "Adams"

    Dim contactQuery = From contact In context.Contacts _
        Where contact.LastName = LastName AndAlso contact.FirstName = FirstName _
            Select contact


    ' Iterate through the results of the parameterized query.
    For Each result In contactQuery
        Console.WriteLine("{0} {1}", result.FirstName, result.LastName)
    Next
End Using

using (AdventureWorksEntities context =
    new AdventureWorksEntities())
{
    string FirstName = "Frances";
    string LastName = "Adams";


    var contactQuery = from contact in context.Contacts
                       where contact.LastName == LastName && contact.FirstName == FirstName
                       select contact;

    // Iterate through the results of the parameterized query.
    foreach (var result in contactQuery)
    {
        Console.WriteLine("{0} {1} ", result.FirstName, result.LastName);
    }
}

The following is the Entity SQL example.

Using context As New AdventureWorksEntities()
    ' Create a query that takes two parameters. 
    Dim queryString As String = "SELECT VALUE Contact FROM AdventureWorksEntities.Contacts " & _
        " AS Contact WHERE Contact.LastName = @ln AND Contact.FirstName = @fn"

    Dim contactQuery As New ObjectQuery(Of Contact)(queryString, context)

    ' 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. 
    For Each result As Contact In contactQuery
        Console.WriteLine("Last Name: {0}; First Name: {1}", result.LastName, result.FirstName)
    Next
End Using

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

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

    // 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);
}

This is the query builder method example.

Dim firstName As String = "Frances"
Dim lastName As String = "Adams"

Using context As New AdventureWorksEntities()
    ' Get the contacts with the specified name. 
    Dim contactQuery As ObjectQuery(Of Contact) = context.Contacts.Where("it.LastName = @ln AND it.FirstName = @fn", _
                                                     New ObjectParameter("ln", lastName), New ObjectParameter("fn", firstName))

    ' Iterate through the collection of Contact items. 
    For Each result As Contact In contactQuery
        Console.WriteLine("Last Name: {0}; First Name: {1}", result.LastName, result.FirstName)
    Next
End Using

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

using (AdventureWorksEntities context =
    new AdventureWorksEntities())
{
    // Get the contacts with the specified name.
    ObjectQuery<Contact> contactQuery = context.Contacts
        .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);
}

How to: Execute a Parameterized Query

Example

See Also

Tasks

Concepts

Additional resources