HOW TO：執行參數化查詢 (Entity Framework)

本主題示範如何使用 ObjectQuery 來執行含有參數的 Entity SQL 查詢。 此範例會將兩個參數傳遞給 ObjectQuery、執行查詢，然後逐一查看 Contact 項目的集合。 相同範例會使用下列每個 Entity Framework 查詢技術顯示：

• LINQ to Entities

• Entity SQL 與 ObjectQuery<T>

• ObjectQuery<T> 的查詢產生器方法

本主題的範例根據 Adventure Works Sales Model。若要執行此主題中的程式碼，您必須已經將 Adventure Works Sales Model 加入到專案中，並設定您的專案使用 Entity Framework。如需詳細資訊，請參閱 HOW TO：使用實體資料模型精靈 (Entity Framework) 或 HOW TO：手動設定 Entity Framework 專案及 HOW TO：手動設定 Entity Framework 專案。

範例

以下是 LINQ to Entities 範例。

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

以下是 Entity SQL 範例。

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

這是查詢產生器方法範例。

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：執行傳回實體類型物件的查詢 (Entity Framework)
HOW TO：執行傳回匿名型別集合的查詢 (Entity Framework)
HOW TO：執行傳回基本型別集合的查詢

概念

Entity SQL 語言