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:
Entity SQL with ObjectQuery<T>
Query builder methods of ObjectQuery<T>
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 advWorksContext As New AdventureWorksEntities
Try
' Create a query that takes two parameters.
Dim queryString As String = "SELECT VALUE Contact FROM AdventureWorksEntities.Contact " & _
"AS Contact WHERE Contact.LastName = @ln AND " & _
"Contact.FirstName = @fn"
' Add parameters to the collection.
Dim contactQuery As New ObjectQuery(Of Contact)(queryString, advWorksContext, MergeOption.NoTracking)
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
Catch ex As EntityException
Console.WriteLine(ex.ToString)
Catch ex As InvalidOperationException
Console.WriteLine(ex.ToString())
End Try
End Using
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.
Dim firstName = "Frances"
Dim lastName = "Adams"
Using context As New AdventureWorksEntities
Try
' Get the contacts with the specified name.
Dim contactQuery As ObjectQuery(Of Contact) = _
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.
For Each result As Contact In contactQuery
Console.WriteLine("Last Name:{0}First Name: {1}", _
result.LastName, result.FirstName)
Next
Catch ex As EntitySqlException
Console.WriteLine(ex.ToString())
End Try
End Using
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
Tasks
How to: Execute a Query that Returns an Entity Type (Entity Framework)
How to: Execute a Query that Returns an Anonymous Type (Entity Framework)
How to: Execute a Query that Returns a Primitive Type (Entity Framework)