How to join multiple tables in entity framework

asp.net-mvc c# entity-framework

Question

I have below code where I am using entity framework,

public IEnumerable<Product> GetProducts()
{
    var query = (from p in db.Products
                     join pd in db.ProductDetails
                         on p.ProductID equals pd.ProductID
                     select new
                     {
                         p.ProductName,
                         pd.IsEnabled
                     }).ToList();

        IEnumerable<Product> products =
            db.Products.Include(x => x.ProductDetail).ToList();

        return products;
}


public partial class Product
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public System.DateTime CreatedDate { get; set; }

    public virtual ProductDetail ProductDetail { get; set; }
}

Instead of returning data from only Products table, I want to return data from ProductDetails table as well.

Also, if I want to add where clause here, how would I do that?

I tried Something like this but it doesn't work,

public IEnumerable<ProductEnableInfo> GetProducts(int pid) 
{ 
   return db.Products.Select( x => new ProductEnableInfo 
                                 { ProductId = x.ProductID,
                                   ProductName = x.ProductName, 
                                   IsEnabled = x.ProductDetail.IsEnabled 
                                 }).Where(x => x.ProductId == pid) .ToList(); 
}

How can I do that?

1
0
11/6/2015 9:32:14 PM

Accepted Answer

You can create a custom class to hold only ProductName and IsEnabled like this:

public class ProductEnableInfo
{
    public string ProductName { get; set; }
    public bool IsEnabled { get; set; }
}

Then you can change your method to this:

public IEnumerable<ProductEnableInfo> GetProducts()
{
    return db.Products.Select(
        x => new ProductEnableInfo
        {
            ProductName = x.ProductName,
            IsEnabled = x.ProductDetail.IsEnabled
        })
        .ToList();
}

UPDATE:

You can filter by ProductID like this:

public IEnumerable<ProductEnableInfo> GetProducts(int product_id)
{
    return db.Products
        .Where(x => x.ProductID == product_id)
        .Select(
        x => new ProductEnableInfo
        {
            ProductName = x.ProductName,
            IsEnabled = x.ProductDetail.IsEnabled
        })
        .ToList();
}
2
11/6/2015 9:41:22 PM

Popular Answer

this should be giving you what you need.

public IEnumerable<Product> GetProducts()
{
    IEnumerable<Product> products =
        db.Products.Include(x => x.ProductDetail).ToList();

    return products;
}

to access the IsEnabled property you just reference the ProductDetail property

var products = GetProducts();
foreach (var product in products)
{
    bool enabled = product.ProductDetail.IsEnabled;
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow