LINQ to Entities の既知の問題および注意点

[アーティクル]
09/17/2008

ここでは、LINQ to Entities クエリの既知の問題について説明します。

失われた情報の収集の順序付け
入れ子になったクエリ
サポートされていない符号なし整数
型変換エラー
非スカラクロージャの参照はサポート非対象

失われた情報の収集の順序付け

順序付け操作の後に、追加操作を行う場合は、追加操作で順序が保持される保証はありません。次の例に示すように、Select や Where などの操作がこれに該当します。

Using AWEntities As New AdventureWorksEntities()

    ' In this query, the ordering is not preserved because Distinct
    ' is called after OrderByDescending.
    Dim productsList = _
    From product In AWEntities.Product _
    Order By product.Name Descending _
    Select product.Name _
    Distinct

    Console.WriteLine("The list of products:")
    For Each productName In productsList
        'Console.WriteLine(productName)
    Next

    ' In this query, the ordering is preserved because 
    ' OrderByDescending is called after Distinct.
    Dim productsList2 = _
    From product In AWEntities.Product _
    Select product.Name _
    Distinct _
    Order By Name Descending

    Console.WriteLine("The list of products:")
    For Each productName In productsList2
        Console.WriteLine(productName)
    Next

End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    // In this query, the ordering is not preserved because Distinct
    // is called after OrderByDescending.
    IQueryable<string> productsList = AWEntities.Product
        .OrderByDescending(p => p.Name)
        .Select(p => p.Name)
        .Distinct();

    Console.WriteLine("The list of products:");
    foreach (string productName in productsList)
    {
        Console.WriteLine(productName);
    }

    // In this query, the ordering is preserved because 
    // OrderByDescending is called after Distinct.
    IQueryable<string> productsList2 = AWEntities.Product
        .Select(p => p.Name)
        .Distinct()
        .OrderByDescending(p => p);

    Console.WriteLine("The list of products:");
    foreach (string productName in productsList2)
    {
        Console.WriteLine(productName);
    }

}

匿名型に列を投影すると、互換性レベルが "80" に設定された SQL Server 2005 データベースに対して実行されるクエリの一部で順序付けが失われます。この状況は、次の例に示すように、ORDER BY リストの列名がセレクタの列名と一致する場合に発生します。

Using AWEntities As New AdventureWorksEntities()
    ' Ordering information is lost when executed against a SQL Server 2005
    ' database running with a compatibility level of "80".
    Dim results = AWEntities.Contact.SelectMany(Function(c) c.SalesOrderHeader) _
        .OrderBy(Function(c) c.SalesOrderDetail.Count) _
        .Select(Function(c) New With {c.SalesOrderDetail.Count})

    For Each result In results
        Console.WriteLine(result.Count)
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    // Ordering information is lost when executed against a SQL Server 2005
    // database running with a compatibility level of "80".
    var results = AWEntities.Contact.SelectMany(c => c.SalesOrderHeader)
        .OrderBy(c => c.SalesOrderDetail.Count)
        .Select(c => new { c.SalesOrderDetail.Count });

    foreach (var result in results)
        Console.WriteLine(result.Count);

}

入力パラメータとして式を指定する First および FirstOrDefault メソッドでは、順序付けが保持されません。

Using AWEntities As New AdventureWorksEntities()
    ' The First() and FirstOrDefault() methods which take expressions
    ' as input parameters do not preserve order.
    Dim orders = AWEntities.SalesOrderHeader _
            .Where(Function(c) c.TotalDue = 11.039) _
            .OrderByDescending(Function(c) c.SalesOrderID) _
            .Select(Function(c) c)

    Console.WriteLine("The ordered results:")
    For Each order As SalesOrderHeader In orders
        Console.WriteLine("ID: {0}  Total due: {1}", order.SalesOrderID, order.TotalDue)
    Next

    Dim result As SalesOrderHeader = AWEntities.SalesOrderHeader _
            .Where(Function(c) c.TotalDue = 11.039) _
            .OrderByDescending(Function(c) c.SalesOrderID) _
            .First(Function(c) c.SalesOrderID > 500)

    Console.WriteLine("")
    Console.WriteLine("The result returned is not the first result from the ordered list.")
    Console.WriteLine("ID: {0}  Total due: {1}", result.SalesOrderID, result.TotalDue)
End Using

    using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
    {
        // The First() and FirstOrDefault() methods which take expressions
        // as input parameters do not preserve order.
        var orders = AWEntities.SalesOrderHeader
            .Where(c => c.TotalDue == 11.039M)
            .OrderByDescending(c => c.SalesOrderID)
            .Select(c => c);

        Console.WriteLine("The ordered results:");
        foreach (SalesOrderHeader order in orders)
            Console.WriteLine("ID: {0} \t Total due: {1}", order.SalesOrderID, order.TotalDue);

        SalesOrderHeader result = AWEntities.SalesOrderHeader
            .Where(c => c.TotalDue == 11.039M)
            .OrderByDescending(c => c.SalesOrderID)
            .First(c => c.SalesOrderID > 500);

        Console.WriteLine("");
        Console.WriteLine("The result returned is not the first result from the ordered list.");
        Console.WriteLine("ID: {0} \t Total due: {1}", result.SalesOrderID, result.TotalDue);
    }
}

入れ子になったクエリ

入れ子になったクエリ内の順序付けは保持されません。次の例で、姓による順序付けは、2 つ目の Select メソッドが呼び出されるときに失われます。

Using AWEntities As New AdventureWorksEntities()
    Dim contacts = AWEntities.Contact _
            .OrderBy(Function(x) x.LastName) _
            .Select(Function(x) x) _
            .Select(Function(x) x.LastName)

    For Each contact In contacts
        Console.WriteLine(contact)
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    // Return all contacts, ordered by last name.
    IQueryable<string> contacts = AWEntities.Contact
        .OrderBy(x => x.LastName)
        .Select(x => x)
        .Select(x => x.LastName);

    foreach (var c in contacts)
    {
        Console.WriteLine(c);
    }
}

次の例では、クエリが正規コマンドツリーに変換され、順序付けが失われるときに、Where メソッドの前に OrderBy メソッドを呼び出して、入れ子になったステートメントを生成します。

Using AWEntities As New AdventureWorksEntities()
    ' Return all contacts, ordered by last name. The OrderBy before
    ' the Where produces a nested query when translated to 
    ' canonical command trees and the ordering by last name is lost.
    Dim contacts = AWEntities.Contact _
            .OrderBy(Function(x) x.LastName) _
            .Where(Function(x) x.FirstName = "John") _
            .Select(Function(x) x)

    For Each c In contacts
        Console.WriteLine(c.LastName & ", " & c.FirstName)
    Next

    ' Return all contacts, ordered by last name.
    Dim contacts2 = AWEntities.Contact _
            .Where(Function(x) x.FirstName = "John") _
            .OrderBy(Function(x) x.LastName) _
            .Select(Function(x) x)

    For Each c In contacts2
        Console.WriteLine(c.LastName & ", " & c.FirstName)
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    // Return all contacts, ordered by last name. The OrderBy before
    // the Where produces a nested query when translated to 
    // canonical command trees and the ordering by last name is lost.
    IQueryable<Contact> contacts = AWEntities.Contact
        .OrderBy(x => x.LastName)
        .Where(x => x.FirstName == "John")
        .Select(x => x);

    foreach (var c in contacts)
    {
        Console.WriteLine(c.LastName + ", " + c.FirstName);
    }

    // Return all contacts, ordered by last name.
    IQueryable<Contact> contacts2 = AWEntities.Contact
        .Where(x => x.FirstName == "John")
        .OrderBy(x => x.LastName)
        .Select(x => x);

    foreach (var c in contacts2)
    {
        Console.WriteLine(c.LastName + ", " + c.FirstName);
    }

}

サポートされていない符号なし整数

エンティティフレームワークによって符号なし整数がサポートされていないため、LINQ to Entities クエリでの符号なし整数型の指定はサポートされていません。次の例に示すように、符号なし整数を指定すると、クエリ式の変換時に ArgumentException 例外がスローされます。この例では、ID 48000 を持つ注文に対してクエリを実行します。

Using AWEntities As New AdventureWorksEntities()
    Dim saleId As UInteger = UInt32.Parse("48000")

    Dim sales As ObjectQuery(Of SalesOrderDetail) = AWEntities.SalesOrderDetail
    Dim query = _
        From sale In sales _
        Where sale.SalesOrderID = saleId _
        Select sale

    Try
        ' NotSupportedException exception is thrown here.
        For Each order As SalesOrderDetail In query
            Console.WriteLine("SalesOrderID: " & order.SalesOrderID)
        Next
    Catch ex As NotSupportedException
        Console.WriteLine("Exception: " + ex.Message)
    End Try
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    uint s = UInt32.Parse("48000");

    ObjectQuery<SalesOrderDetail> sales = AWEntities.SalesOrderDetail;

    IQueryable<SalesOrderDetail> query = from sale in sales
                                         where sale.SalesOrderID == s
                                         select sale;

    // NotSupportedException exception is thrown here.
    try
    {
        foreach (SalesOrderDetail order in query)
            Console.WriteLine("SalesOrderID: " + order.SalesOrderID);
    }
    catch (NotSupportedException ex)
    {
        Console.WriteLine("Exception: {0}", ex.Message);
    }
}

型変換エラー

Visual Basic では、CByte 関数を使用して 1 の値を持つ SQL Server の bit 型の列にプロパティがマップされると、"算術オーバーフローエラー" というメッセージと共に SqlException がスローされます。次の例では、AdventureWorks サンプルデータベースの Product.MakeFlag 列をクエリし、クエリ結果が反復処理されると、例外がスローされます。

Using AWEntities As New AdventureWorksEntities()
    Dim productsList = _
        From product In AWEntities.Product _
        Select CByte(product.MakeFlag)

    ' Throws an SqlException exception with a "Arithmetic overflow error 
    ' for data type tinyint" message when a value of 1 is iterated over.
    For Each makeFlag In productsList
        Console.WriteLine(makeFlag)
    Next
End Using

非スカラクロージャの参照はサポート非対象

クエリでの非スカラクロージャ (エンティティなど) の参照はサポートされていません。そのようなクエリを実行すると、NotSupportedException 例外がスローされ、"型 'クロージャ型' の定数値を作成できません。このコンテキストでサポートされるのはプリミティブ型 ('Int32、String、Guid など') だけです" というメッセージが表示されます。

Using AWEntities As New AdventureWorksEntities()

    Dim contact As Contact = AWEntities.Contact.FirstOrDefault()

    ' Referencing a non-scalar closure in a query will
    ' throw an exception when the query is executed.
    Dim contacts = From c In AWEntities.Contact _
                   Where c.Equals(contact) _
                   Select c.LastName

    Try
        For Each name As String In contacts
            Console.WriteLine("Name: ", name)
        Next

    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try

End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    Contact contact = AWEntities.Contact.FirstOrDefault();

    // Referencing a non-scalar closure in a query will
    // throw an exception when the query is executed.
    IQueryable<string> contacts = from c in AWEntities.Contact
        where c == contact
        select c.LastName;

    try
    {
        foreach (string name in contacts)
        {
            Console.WriteLine("Name: ", name);
        }
    }
    catch (NotSupportedException ex)
    {
        Console.WriteLine(ex.Message);
    }
}

参照

その他のリソース

LINQ to Entities を使用したクエリ

次の方法で共有

LINQ to Entities の既知の問題および注意点

失われた情報の収集の順序付け

入れ子になったクエリ

サポートされていない符号なし整数

型変換エラー

非スカラ クロージャの参照はサポート非対象

参照

その他のリソース

その他のリソース

非スカラクロージャの参照はサポート非対象