Geek Answers Handbook

Creating an object with a nested list of objects using LINQ to Entities

I am trying to create an object containing a list of objects using the Linq to Entity Framework. But I get a NotSupported exception. My db structure is very similar to the db that comes with LinqPad. Take this, for example, I have a customer domain object and a Purchase domain object (or for ex-Order).

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

I am trying to populate a Customer domain object in a DAL using 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 a NotSpported exception for this

I also tried to create a method for purchases – GetPurchases() . It returns a shopping list and assigns it to the customer object that I create. But I still get the same exception. I get a NotSuuported exception with an error message -

cannot convert method to store expression

. I searched, and it looks like it is supported in linq for sql, but not in linq for ef. I'm trying to do the same thing as this post - Using linq to return an object with a list of <object> member Is it possible to populate a domain object as I do. Are there any known solutions or work for this.

+1
source share
3 answers

Here is a working example of something similar. In this case, the collection of invested taxes is completed.

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

It cannot convert:

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

for the correct sql statement, in fact it will create an expression tree, but cannot 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 did not see your first select statement:

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

linq2entity also cannot handle this (actually any complicated initialization)

But, as I see it, it seems that you have only one client, so why didn’t you fully understand this? in fact it does:

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

?

0
source

You cannot create an instance of your model in the Select statement. Select designed to create objects with "selected" properties / columns - anonymous objects or (for example). View models or data transfer objects.

What you are trying to load (an object, including navigation properties) is usually done with active loading:

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

You also cannot use your own methods (e.g. GetPurchases() ) in a LINQ to Entities query. There are only a few exceptions - for example, IQueryable<T> extension methods or methods that return Expression<Func<T>> . In most cases, the Entity Framework cannot convert such a custom method to "Store Expression" in a language that your database (for example, SQL) can understand.

0
source

More articles: