CODEWITHSUNDEEP
c#linq-to-entities
Villager
Villager

Reputation: 6689

Lookup / Join with LINQ-to-Entities in C#

I have a database that has two tables: Order and Product. This database is exposed via an Entity Data Model and LINQ-to-Entities.

I have a product ID and I want to get all of the Order objects that reference the Product. I would like to learn how to do this with LINQ. I know I can query the products 

int productID = GetProductID();
using (DatabaseContext database = new DatabaseContext())
{
  var products = from product in database.Products
               where product.ProductID = productID
               select product;

}

I know this LINQ query get me all of the products with a specific product ID. However, I want the Order objects. I was trying to figure out to do a Join and get the just the Order objects. I do not care about the products as I have the product id. Is there a way to do this?

Thank you!

Upvotes: 0

Views: 1268

Answers (4)

Danny Varod
Danny Varod

Reputation: 18068

Something like (depending on properties you have):

context.Orders.Where(order => order.ProductId == myProduct.Id);

Upvotes: 0

Bala R
Bala R

Reputation: 108957

 var orders = from product in database.Products
            join order in database.Orders on product.Key equals order.KeyField 
           where product.ProductID == productID
           select order;

Upvotes: 1

Maciek
Maciek

Reputation: 19893

Typically, this would be done like this :

For the sake of simplicity : [TABLE] <key/relation>

[Products]-<productId>-[OrderedProducts]-<orderId>-[Orders]

var result = 
from op in OrderedProducts
join o in Orders on op.OrderId equals o.Id
join p in Products on op.ProductId equals p.Id
select p

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1500675

Normally an Order would have a relationship to the products, allowing you to write:

var orders = from order in database.Orders
             where order.Products.Any(p => p.ProductID == productID)
             select order;

Upvotes: 1

Related Questions