Creating object with nested object list using LINQ to Entities

Question

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.

I was getting error on this - Purchases = t.Customer.Purchase .Select ( p=> new Purchase() { PurchaseID=p.purchaseid }).ToList() — user1165359
– user1165359, Commented Jan 24, 2012 at 14:27

Martin D · Accepted Answer · 2016-05-26 23:59:52Z

2

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

answered May 26, 2016 at 23:59

Martin D

1801 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cdlm Over a year ago

This works but I'm finding it to be tremendously slow in generating the SQL.

Saeed Amiri · Accepted Answer · 2012-01-25 18:02:33Z

0

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)

?

edited Jan 25, 2012 at 18:02

answered Jan 24, 2012 at 11:07

Saeed Amiri

22.6k5 gold badges49 silver badges86 bronze badges

7 Comments

Saeed Amiri Over a year ago

@user1165359, After you test it, and you see this works for you, you can mark it as answer by toggling tic mark in left hand side of my answer to green.

user1165359 Over a year ago

Hi Saeed , I am getting the same error - can not convert to store expression , NotSupportedException.

Saeed Amiri Over a year ago

Is your purchaceid complex property?

user1165359 Over a year ago

No its not a complex property.I found this article - mosesofegypt.net/post/… on internet about this . I was just wondering if there are some other solutions for this issue . Thanks for responding to my post and you suggestion.I appreciate it.

Saeed Amiri Over a year ago

@user1165359, I didn't see your first Select statement, If you have just one customer you can do what I suggest but in too many customer it's better to write your own sp.

|

Slauma · Accepted Answer · 2012-01-25 22:18:39Z

0

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).

answered Jan 25, 2012 at 22:18

Slauma

177k60 gold badges407 silver badges424 bronze badges

4 Comments

user1165359 Over a year ago

Slauma , There are two reasons for which I am not using Include.1) Lets say I have Customer connected to Order which is connect to Status.I can not use this - db.Customer.Include("Order").Include("Status") as this requires both the two tables - Order and Status should be connected to Customer table .Correct me if I am wrong but I think Include will not work If I have relation like this - tables 1-->2-->3 , it works for relation like 1--> 2 and 1--> 3 .2) With Include I think I am sending the complete object set to client and when the client will use it then only the query will be executed..

user1165359 Over a year ago

I want the query to be executed from DAL and not from the client.

Slauma Over a year ago

@user1165359: For point 1 you can use Include("Order.Status") to fetch deeper levels in the hierarchy. This works with eager loading. For point 2: Include (=eager loading) will load the related objects together with the base object in one DB roundtrip. The query is executed by ToList or SingleOrDefault. You can do this in the DAL and the whole object graph gets materialized.

user1165359 Over a year ago

I think in that case I can use Include .Thanks for clearing up the Include.

Collectives™ on Stack Overflow

Creating object with nested object list using LINQ to Entities

3 Answers 3

1 Comment

7 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related