Export (0) Print
Expand All

Known Issues and Considerations in LINQ to Entities

This section provides information about known issues with LINQ to Entities queries.

Ordering Information Lost

If any additional operations are performed after an ordering operation, there is no assurance that the ordering will be preserved in those additional operations. This includes operations such as Select or Where, as shown in the following example:

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

}

Projecting columns into an anonymous type will cause ordering information to be lost in some queries that are executed against a SQL Server 2005 database set to a compatibility level of "80". This occurs when a column name in the order-by list matches a column name in the selector, as shown in the following example:

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

}

The First and FirstOrDefault methods, which take an expression as an input parameter, do not preserve order.

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

Nested Queries

Ordering in nested queries is not preserved. In the following example, the ordering by last name is lost when the second Select method is called:

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

In the following example, calling the OrderBy method before the Where method produces a nested statement when the query is translated into canonical command trees, and the ordering is lost:

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

}

Unsigned Integers Not Supported

Specifying an unsigned integer type in a LINQ to Entities query is not supported because the Entity Framework does not support unsigned integers. If you specify an unsigned integer, an ArgumentException exception will be thrown during the query expression translation, as shown in the following example. This example queries for an order with ID 48000.

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

Type Conversion Errors

In Visual Basic, when a property is mapped to a column of SQL Server bit type with a value of 1 using the CByte function, a SqlException is thrown with an "Arithmetic overflow error" message. The following example queries the Product.MakeFlag column in the AdventureWorks sample database and an exception is thrown when the query results are iterated over.

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

Referencing Non-Scalar Closures Not Supported

Referencing a non-scalar closure, such as an entity, in a query is not supported. When such a query executes, a NotSupportedException exception is thrown with a message that states "Unable to create a constant value of type 'Closure type'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."

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

Nested Queries May Fail with SQL Server 2000

With SQL Server 2000, LINQ to Entities queries may fail if they produce nested Transact-SQL queries that are three or more levels deep.

See Also

Community Additions

Show:
© 2014 Microsoft