goroth
goroth

Reputation: 2610

Entity Framework linq master detail projection into custom classes

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

Answers (2)

jameslj
jameslj

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

user604613
user604613

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

Related Questions