Entity-framework – Creating object with nested object list using LINQ to Entities

entity-frameworklinq

I am trying to create an object that contains the list of objects using Linq to Entity Framework. But I am getting NotSupported exception. My db structure is very similar to the db that comes with LinqPad. Let’s take that for example – I have a customer domain object and Purchase (or for ex – Order) domain object.

Public Class Customer (){ long CustID ,string CustName , List<Purchase> purchases }
Public Class Purchase () { long PurchaseID}

I am trying to populate the Customer domain object in DAL using the navigation properties like this – 

db.Customer
.Where( t=> t.CustID==1)  //hard coding for 1
.Select( t=> new Customer()
{
CustName = t.name ,
Purchases = t.Customer.Purchase
        .Select ( p=> new Purchase()
            {
                PurchaseID=p.purchaseid
}).ToList()
});

I got the NotSpported exception for this

I also tried creating a method for purchases – GetPurchases(). It returns the list of purchases and assigned it to the customer object that I am creating up. But I am still getting the same exception.
I am getting NotSuuported exception with error message –

cannot convert method to store expression

. I searched and it seems like its supported in linq to sql but not in linq to ef. I am trying to do the same thing like this post – Using linq to return an object with a list<object> member
Is it possible to populate the domain object like I am doing. Are there any known solutions or work around for this.

Best Answer

Here is a working example of something similar. In this case it is the nested Taxes collection that is being populated.

public async Task<IEnumerable<ServiceLayer.DTO.Vendor>> GetAllVendors()
{
    return await (

        from vendor in _db.Vendors

        select new ServiceLayer.DTO.Vendor()
        {
            Id = vendor.Id,
            Name = vendor.Name,
            Taxes = (from tax in _db.VendorTaxes
                     where tax.VendorId.Equals(vendor.Id)
                     select new DTO.VendorTax() { Id = tax.Id, Rate = tax.Rate })
        })

    .ToListAsync();
}
Related Topic