How to pull back all parent/child data in complex object

Question

I have these two tables with a one (category) to many (product) relationship in the database:

Table Product
    Name
    Description
    ProductCategory

Table Category
    Category
    Description

And these classes:

public class Product
{
    public string Name { get; set; }
    public string Description { get; set; }
    public Category CategoryName { get; set; }    
}

public class Category
{
    public string CategoryName { get; set; }
    public string Description { get; set; }    
}

I want to get a list back with all product and category object data in a list.

I've read about multipleResults and queryMultiple but can't see how to tie the two together.

I know how to do it for a single product but what about all products with their individual category objects as well.

Answer 1

Assume you have your tables like this.

Product

ID
ProductName
ProductCategoryID

Category

ID
CategoryName

and your classes

public class Product
{
    public int ID { set; get; }
    public string ProductName { set; get; }
    public int ProductCategoryID  {set;get;}
    public Category Category { set; get; }
}
public class Category
{
    public int ID { set; get; }
    public string CategoryName { set; get; }
}

The below code should work fine for you to load a list of products with associated categories.

var conString="Replace your connection string here";
using (var conn =   new SqlConnection(conString))
{
    conn.Open();
    string qry = "SELECT P.ID,P.ProductName,P.ProductCategoryID,C.ID,
                  C.CategoryName from Product P  INNER JOIN   
                  Category C ON P.ProductCategoryID=C.ID";
    var products = conn.Query<Product, Category, Product>
                     (qry, (prod, cat) => { prod.Category = cat; return prod; });

    foreach (Product product in products)
    {
        //do something with the products now as you like.
    }
    conn.Close(); 
}

Note : Dapper assumes your Id columns are named "Id" or "id", if your primary key is different or you would like to split the wide row at point other than "Id", use the optional 'splitOn' parameter.

Answer 2

This should do what you wish:

var res = db.Query<Product, Category, Product>(
  @"select p.[Name], p.[Description], c.Category, c.Category as [CategoryName], c.Description
  from Product p
  inner join Category c on c.Category = p.ProductCategory",
  (prd, cat) => {
      prd.CategoryName = cat;
      return prd;
  },
  splitOn: "Category"
  ).AsQueryable();

Also, the CategoryName name you chose for one of the Product's properties is inconvenient.

I suggest you change you Product class like this:

public class Product
{
   public string Name { get; set; }
   public string Description { get; set; }
   public Category Category { get; set; }    
}

Then the query can become cleaner:

var res = db.Query<Product, Category, Product>(
    @"select p.[Name], p.[Description], c.Category as [CategoryName], c.Description
      from Product p
      inner join Category c on c.Category = p.ProductCategory",
      (prd, cat) => {
          prd.Category = cat;
          return prd;
      },
      splitOn: "CategoryName"
      ).AsQueryable();