What is the standard pattern to retrieve JOINed data from normalized tables?

Question

I have the following simple "textbook" classes defined:

    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Category { get; set; }
    }

    public class Category
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

The Category field in Product is an integer referencing the Id field in the Category class. In the database, it would be a foreign key relationship between the Product and Category tables.

But my application would need to show the friendly name of the Category of a product.

Q1: Is it correct practice to define a new class as follows:

    public class ProductJ : Product
    {
        public string CategoryName { get; set; }
    }

And then, if I now have a method getProductsJ as follows:

    public class Test
    {
        public List<Category> Categories = new List<Category>() { ... };
        public List<Product> Products = new List<Product>() { ... };

        public List<ProductJ> getProductsJ()
        {
            var products = from p in Products
               join c in Categories on p.Category equals c.Id
               select new ProductJ { Id = p.Id, , Name = p.Name, CategoryName = c.Name }; //!
            return products.ToList();
        }
    }

Q2: Is the above the best way to retrieve a list of Products with Category names?

Q3: In the select statement (//!) is there a faster way to populate the fields of the base class Product linqwithout having to enter them one by one?

Thanks.

Answer 1

You could project the result of the join into an object that contains a reference to both the product and category.

public class Product
{
  public int Id { get; set; }
  public string Name { get; set; }
  public int Category { get; set; }
}

public class Category
{
  public int Id { get; set; }
  public string Name { get; set; }
}

public class ProductCategory
{
  public Product Product { get; set; }
  public Category Category { get; set; }
}

class Program
{
  static void Main(string[] args)
  {
    List<Category> Categories = new List<Category>();
    List<Product> Products = new List<Product>();

    var products = from p in Products
                   join c in Categories on p.Category equals c.Id
                   select new ProductCategory { Product = p, Category = c };

    var list = products.ToList();
  }
}