Known Issues and Considerations in LINQ to Entities
This section provides information about known issues with LINQ to Entities queries.
Ordering Information Lost
Nested Queries
Unsigned Integers Not Supported
Type Conversion Errors
Referencing Non-Scalar Closures Not Supported
Nested Queries May Fail with SQL Server 2000
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 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);
}
}
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 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);
}
The First and FirstOrDefault methods, which take an expression as an input parameter, do not preserve order.
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);
}
}
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 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);
}
}
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 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);
}
}
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 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);
}
}
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 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);
}
}
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.