CODEWITHSUNDEEP
c#linqentity-framework
Mustang31
Mustang31

Reputation: 282

How to query entities from a collection without using foreach

I've been struggling and trying to get my mind wrapped around these concepts. Any help would be greatly appreciated. Given a collection of Orders in one table what is the best way to iterate over it, and perform queries against other tables.

This loop does what I want, but I've been told and shown how this causes performance problems. On other posts I've read where it is best to query the dbase outside of loops.

foreach (var item in myOrders)
{
var lookUpValues=(from f in ORDERS
where f.ORDERS ==item.ORDER_ID 
&& f.ORDERS_SUFFIX==item.LINE
select f.Something);
}

Rather than pulling down the entire Order table prior to iterating over I've tried something like this, but as you see it won't return the accurate rows. 

var orderIds=myOrders.Select (x =>x.ORDER_ID).Distinct().ToList();
var lineIds=myOrders.Select (x =>x.LINE).Distinct().ToList();

var query=(from f in ORDERS
where orderIds.Contains(f.ORDERS)
&& lineIds.Contains(f.ORDERS_SUFFIX)
select f).ToList();

The below would do what I think is needed. It would return the correct Orders that are required to iterate over. However, it throws an error, which I believe is because you can't query memory objects and entities together.

var ordersAndLine=(from f in myOrders select new{sId=f.ORDER_ID,line=f.LINE}).ToList();

var query=(from f in ORDERS
from s in ordersAndLine
where s.sId==f.ORDERS
&& s.line==f.ORDERS_SUFFIX
select f).ToList();

NotSupportedException: Unable to create a constant value of type 'Anonymous type'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

I really hope my question makes sense. I apologize in advance if I didn't explain my situation correctly. What I've been doing is pulling down much of the table to memory to iterate over, but I was concerned that this would cause performance issues and wanted to see if there is a better way to filter the result query.

Upvotes: 0

Views: 1673

Answers (3)

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28698

If you combine your first attempt and your last attempt you'd have it. The last attempt would be fine if you weren't creating an anonymous type.

var ordersAndLine=(from f in myOrders select f).ToList();

var query=(from f in ORDERS
from s in ordersAndLine
where s.ORDER_ID==f.ORDERS
&& s.LINE==f.ORDERS_SUFFIX
select f).ToList();

Upvotes: 1

Ivo
Ivo

Reputation: 8352

Try this:

var orders = myOrders.SelectMany(item => (from f in ORDERS
                                          where f.ORDERS ==item.ORDER_ID 
                                          && f.ORDERS_SUFFIX==item.LINE
                                          select f.Something));

Upvotes: 0

fenix2222
fenix2222

Reputation: 4730

Have a look at these:

http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/095745fe-dcf0-4142-b684-b7e4a1ab59f0/

Linq to Entities - SQL "IN" clause

Upvotes: 0

Related Questions