Dynamic Query with Entity Framework 4

Question

Okay I'm new to EF and I'm having issues grasping on filtering results...

I'd like to emulate the ef code to do something like:

select * 
from order o  
    inner join orderdetail d on (o.orderid = d.orderid)
where d.amount > 20.00

just not sure how this would be done in EF (linq to entities syntax)

Craig Stuntz · Accepted Answer · 2010-07-15 12:47:13Z

1

Your SQL gives multiple results per order if there's multiple details > 20.00. That seems wrong to me. I think you want:

var q = from o in Context.Orders
        where o.OrderDetails.Any(d => d.Amount > 20.00)
        select o;

answered Jul 15, 2010 at 12:47

Craig Stuntz

127k12 gold badges257 silver badges275 bronze badges

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

Comments

Craig Stuntz · Accepted Answer · 2010-07-15 12:42:41Z

0

I would do it like that:

context.OrderDetails.Where(od => od.Amount > 20).Include("Order").ToList().Select(od => od.Order).Distinct();

We are taking details first, include orders, and take distinct orders.

Craig Stuntz

127k12 gold badges257 silver badges275 bronze badges

answered Jul 15, 2010 at 11:37

LukLed

31.9k17 gold badges87 silver badges108 bronze badges

That gives different results than his SQL, but I think his SQL is buggy, so it's hard to tell.

That gives the same result. It return only details with amount > 20 and adds their orders.