How to execute a multi-table query in code-first approach of Entity Framework?

Question

If I want to execute a query that is based on joins between multiple tables, then how would this be coded in a code-first approach using Entity Framework 4.1? For example, I want to execute the following query:

SELECT p.ProductId
 , p.Name AS ProductName
 , c.Name AS CategoryName
FROM
Products p
INNER JOIN Categories c
    ON c.CategoryId = p.CategoryId

Answer 1

You will create classes like:

public class Product {
    public int ProductId { get; set; }
    public string Name { get; set; }
    public int CategoryId { get; set; }
    public virtual Category Category { get; set; }
}

public class Category {
    public int CategoryId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Product> Products { get; set; }
}

And you will use this query where join will be created automatically from navigation properties:

var query = from p in context.Products
            select new {
                ProductId = p.ProductId,
                ProductName = p.Name,
                CategoryName = p.Category.Name
            };