LINQ to Entities의 알려진 문제 및 고려 사항
이 단원에서는 LINQ to Entities 쿼리 관련 알려진 문제에 대한 정보를 제공합니다.
순서 정보 손실
중첩 쿼리
부호 없는 정수 지원되지 않음
형식 변환 오류
스칼라가 아닌 Closure 참조는 지원되지 않음
순서 정보 손실
정렬 연산 이후에 다른 연산을 추가로 수행하는 경우 순서가 해당 추가 연산에서 유지되지 않을 수 있습니다. 여기에는 다음 예제와 같이 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 데이터베이스에 대해 실행되는 일부 쿼리에서 순서 정보가 손실됩니다. 이런 현상은 다음 예제와 같이 정렬 순서 목록에 있는 열 이름이 선택기의 열 이름과 일치할 때 발생합니다.
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);
}
}
중첩 쿼리
중첩 쿼리 내의 순서는 유지되지 않습니다. 다음 예제에서 성을 기준으로 한 정렬이 두 번째 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 예외가 throw됩니다. 다음 예제에서는 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 비트 형식의 열에 속성이 매핑되면 "산술 오버플로 오류" 메시지와 함께 SqlException이 throw됩니다. 다음 예제는 AdventureWorks 샘플 데이터베이스의 Product.MakeFlag 열을 쿼리하며, 쿼리 결과가 반복되면 예외가 throw됩니다.
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
스칼라가 아닌 Closure 참조는 지원되지 않음
엔터티와 같은 스칼라가 아닌 Closure를 쿼리에서 참조하는 기능은 지원되지 않습니다. 이러한 쿼리가 실행되면 NotSupportedException 예외가 throw되고 "'Closure' 형식의 상수 값을 만들 수 없습니다. 이 컨텍스트에서는 기본 형식('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);
}
}