CODEWITHSUNDEEP
linqentity-framework
user1165359
user1165359

Reputation: 11

Creating object with nested object list using LINQ to Entities

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.

Upvotes: 1

Views: 6738

Answers (3)

Martin D
Martin D

Reputation: 170

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();
}

Upvotes: 2

Slauma
Slauma

Reputation: 177133

You cannot instantiate an entity of your model in a Select statement. Select is for creating objects with "selected" properties/columns - anonymous objects or (for example) View Models or Data Transfer Objects.

What you are trying to load (an entity including navigation properties) is usually done with eager loading:

var customer = db.Customer.Include("Purchases")
    .SingleOrDefault(c => c.CustID == 1);

You generally also cannot use your custom methods (like GetPurchases()) in a LINQ to Entities query. There are only few exceptions - like extension methods of IQueryable<T> or methods which return an Expression<Func<T>>. In most cases Entity Framework cannot convert such a custom method into a "Store Expression" in a language your database can understand (= SQL for example).

Upvotes: 0

Saeed Amiri
Saeed Amiri

Reputation: 22555

It can't convert:

.Select ( p=> new Purchase()
            {
                PurchaseID=p.purchaseid
})

to proper sql statement, in fact it will makes expression tree, but can't convert it to sql command, you can do something 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=> p.purchaseid)
        .ToList() // here you should fetch your data from DB

        // now convert fetched data via linq2object to what you want:
        .Select( p => new Purchase()
            {
                PurchaseID=p.purchaseid
            })
        .ToList();

Edit: Sorry I didn't see your first select statement:

Select( t=> new Customer()
{
 CustName = t.name ,
 Purchases = t.Customer.Purchase...
}

linq2entity Also can't handle this (in fact any complex initialization)

But as I can see, Seems you just have one customer, so why you didn't get it totally? in fact doing so:

var customer = db.Customer.FirstOrDefault(x=>x.ID == 1)

?

Upvotes: 0

Related Questions