How to join multiple tables in 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?

Answer 1

You could create a DTO object and using Select() method, you could change the output of the query, for sample:

public class ProductDto
{
   public int Id { get; set; }
   public string Name { get; set; }
   public bool IsEnalbed { get; set; }
}

And execute a query like this;

IEnumerable<ProductDto> result = db.Products    
                                   .Where(x => x.ProductId == productId) // if you want to filter, just add .Where() method                                   
                                   .Select(x => new ProductDto()
                                          {
                                             Id = x.ProductID,
                                             Name = x.ProductName,
                                             IsEnabled = x.ProductDetail.IsEnabled
                                          })
                                   .ToList();

Answer 2

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();
}

Answer 3

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;
}