Reputation: 2610
I have an Order class with properties "ID as Int64, Description as String, Details as List(Of OrderDetail)"
There is also a OrderDetail class with properties "ID as Int64, Description as String, Amount as Decimal"
Public Class Order
Property ID As Int64
Property Description As String
Property Details As List(Of OrderDetail)
End Class
Public Class OrderDetail
Property ID As Int64
Property Description As String
Property Amount As Decimal
End Class
Then I created a static / shared function to call Entity Framework using LINQ and would like to create an Order list along with OrderDetails on each order.
Public Shared Function GetOrders() As List(Of Order)
Dim db = New OrderEntities()
Dim orders = (From O In db.OrderTable
Select New Order With {
.ID = O.OrderID,
.Description = O.OrderDescription,
.Details = (From OD In db.OrderDetailTable
Where OD.OrderID = O.OrderID
Select New OrderDetail With {
.ID = OD.OrderDetailID,
.Description = OD.OrderDetailDescription,
.Amount = OD.OrderDetailAmount})
}
).ToList()
Return orders
End Function
When executed I will get the following error.
Unable to cast the type 'System.Linq.IQueryable`1[[OrderDetails]]' to type
'System.Collections.Generic.List`1[[OrderDetails]]'.
LINQ to Entities only supports casting EDM primitive or enumeration types.
NOTE:
This is an Entity Framework problem and not a LINQ problem.
For example the following will work fine when using EF but not projecting into a new object list.
Public Shared Function GetOrders() As Object
Dim db = New OrderEntities()
Dim orders = (From O In db.OrderTable
Select New With {
.ID = O.OrderID,
.Description = O.OrderDescription,
.Details = (From OD In db.OrderDetailTable
Where OD.OrderID = O.OrderID
Select New With {
.ID = OD.OrderDetailID,
.Description = OD.OrderDetailDescription,
.Amount = OD.OrderDetailAmount})
}
).ToList()
Return orders
End Function
EDIT:
Well this is crazy. The problem seems to be a VB.NET problem with EF.
This C# version works.
var data = db.OrderTable
.Select(O => new Order { ID = O.OrderID,
Description = O.OrderDescription,
Details = db.OrderDetailTable.Where(D => D.OrderID == O.OrderID)
.Select(OD => new OrderDetail { Description = OD.OrderDetailDescription})})
.ToList();
The same does not work in VB.NET.
Dim data = db.OrderTable
.Select(Function(O) New Order With {.ID = O.OrderID,
.Description = O.OrderDescription,
.Details = db.OrderDetailTable.Where(Function(D) D.OrderID = O.OrderID)
.Select(Function(OD) New OrderDetail With {.Description = OD.OrderDetailDescription})})
.ToList()
The problem seems to be related to this work item.
https://entityframework.codeplex.com/workitem/808
EDIT:
Per UrbanEsc and jameslj I was able to get some working code.
It seems that if the Order.Details property is a "LIST" then the code will only work if using:
Dim orders = (From O In db.OrderTable
Select New With {
.ID = O.OrderID,
.Description = O.OrderDescription,
.Details = (From OD In db.OrderDetailTable.AsEnumerable()
Where OD.OrderID = O.OrderID
Select New With {
.ID = OD.OrderDetailID,
.Description = OD.OrderDetailDescription,
.Amount = OD.OrderDetailAmount}).ToList()
}
).ToList()
Notice the "AsEnumerable" along with the "ToList()" on the details. Both are required.
But if I change the Order.Details property to "IQueryable" or "IEnumerable" then I can remove the extra "ToList" and use AsQueryable or AsEnumerable.
Property Details As IQueryable(Of OrderDetail)
Dim orders = (From O In db.OrderTable
Select New With {
.ID = O.OrderID,
.Description = O.OrderDescription,
.Details = (From OD In db.OrderDetailTable.AsQueryable()
Where OD.OrderID = O.OrderID
Select New With {
.ID = OD.OrderDetailID,
.Description = OD.OrderDetailDescription,
.Amount = OD.OrderDetailAmount})
}
).ToList()
Upvotes: 1
Views: 1797
Reputation: 36
orders = db.OrderTable.Select(Function(x) New Order() With
{.Id = x.OrderID,
.Description = x.OrderDescription,
.Details = db.OrderDetailTable.Where(
Function(y) y.OrderId = x.OrderID).ToList().Select(
Function(z) New OrderDetail() With
{.Id = z.OrderDetailId,
.Description = z.OrderDetailDescription,
.Amount = z.OrderDetailAmount}).ToList()}).ToList()
Upvotes: 0
Reputation:
As per my comment, you will need to add a .ToList() for your SELECT on Details.
Public Shared Function GetOrders() As List(Of Order)
Dim db = New OrderEntities()
Dim orders = (From O In db.OrderTable
Select New Order With {
.ID = O.OrderID,
.Description = O.OrderDescription,
.Details = (From OD In db.OrderDetailTable
Where OD.OrderID = O.OrderID
Select New OrderDetail With {
.ID = OD.OrderDetailID,
.Description = OD.OrderDetailDescription,
.Amount = OD.OrderDetailAmount})
.ToList()
}
).ToList()
Return orders
End Function
Upvotes: 1