I'm getting data from a table using entity framework lambda query:

db.Orders.Where(x => x.OrderID == orderid)
         .Select(x => new JOrders 
         { 
             id = x.OrderID, 
             customer_id = x.CustomerID, 
             product_id = x.ProductID,  
             account_id = x.AccountID
         }).toList()

CustomerID, ProductID and AccountID are primary keys from separate tables i.e. Customers, Products, Accounts respectively. I would like to get Name column for these IDs from their respective tables and return that instead. I'm looking for an optimized lambda query since the number of records returned could be in thousands.

2 Answers

1
Asakuraa Ranger On

If you didn't make an relating in EF you can manual Join like this

db.Orders.Where(x => x.OrderID == orderid)
.Join(db.Customers,Order => Order.OrderID, Customer => Customer.CustomerID, (Order, Customer) => new {OrderID = Order.id, CutomerName = Customer.Name, more fields..}).ToList()

But if you did have an relate table you can just call it

db.Orders.Where(x => x.OrderID == orderid)
         .Select(x => new JOrders 
         { 
             id = x.OrderID, 
             customer_id = x.CustomerID, 
             product_id = x.ProductID,  
             account_id = x.AccountID,
             name = x.Customers.Name
         }).toList()
1
CoOl On

If you have set the navigation properties correctly in your Orders class. It's just a matter of including them in your query and then you can use those properties in your lambda. Something like this:

db.Orders
.Include(x => x.Customer)
.Include(x => x.Product)
.Include(x => x.Account)
.Where(x => x.OrderID == orderid)
         .Select(x => new JOrders 
         { 
             id = x.OrderID, 
             customer_name = x.Customer.Name, 
             product_name = x.Product.Name,  
             account_name = x.Account.Name
         }).toList()

But I believe you will have multiple Products in an Order, so you might have to change it accordingly.