Problemas conocidos y consideraciones en LINQ to Entities
En esta sección se ofrece información sobre los problemas conocidos relacionados con consultas de LINQ to Entities.
Pérdida de información de ordenación
Consultas anidadas
Enteros sin signo no admitidos
Errores de la conversión de tipos
Referencia a cierres no escalares no admitidos
Pérdida de información de ordenación
Si se realizan operaciones adicionales después de una operación de ordenación, no hay ninguna garantía de que la ordenación se conserve en dichas operaciones adicionales. Entre estas se incluyen operaciones como Select o Where, como se muestra en el ejemplo siguiente:
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);
}
}
La proyección de columnas en un tipo anónimo provocará la pérdida de información de ordenación en algunas consultas que se ejecuten en un conjunto de base de datos de SQL Server 2005 con un nivel de la compatibilidad de "80". Esto se produce cuando un nombre de columna en la lista ORDER BY coincide con un nombre de columna en el selector, como se muestra en el ejemplo siguiente:
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);
}
Los métodos FirstOrDefault y First, que toman una expresión como parámetro de entrada, no conservan el orden.
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);
}
}
Consultas anidadas
No se conserva la ordenación en las consultas anidadas. En el ejemplo siguiente, la ordenación por apellido se pierde cuando se llama al segundo método 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);
}
}
En el ejemplo siguiente, la llamada al método OrderBy antes de llamar al método Where genera una instrucción anidada cuando la consulta se convierte en árboles de comandos canónicos, y se pierde la ordenación:
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);
}
}
Enteros sin signo no admitidos
La especificación de un tipo entero sin signo en una consulta de LINQ to Entities no se admite porque Entity Framework no admite enteros sin signo. Si se especifica un entero sin signo, se producirá una excepción ArgumentException se durante la conversión de la expresión de consulta, como se muestra en el ejemplo siguiente. En este ejemplo se consulta un pedido cuyo número de identificación (Id.) es 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);
}
}
Errores de la conversión de tipos
En Visual Basic, cuando se asigna una propiedad a una columna de tipo bit de SQL Server con un valor de 1 utilizando la función CByte, se produce una excepción SqlException con el mensaje "Error de desbordamiento aritmético". En el ejemplo siguiente se consulta la columna Product.MakeFlag en la base de datos de ejemplo AdventureWorks y se produce una excepción cuando tiene lugar una iteración en los resultados de la consulta.
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
Referencia a cierres no escalares no admitidos
En una consulta no se puede hacer referencia a un cierre no escalar, por ejemplo una entidad. Cuando este tipo de consulta se ejecuta, se lanza una excepción NotSupportedException con un mensaje que indica "No se puede crear un valor constante de tipo 'tipo Closure'. Sólo los tipos primitivos ('como Int32, String y Guid') se admiten en este contexto.".
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);
}
}