best way to extract a one to many relationship with dapper dot net orm?

Question

I have two classes 'Product' and 'Seller'.

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

    public Seller Seller { get; set; }
    public int? SellerId { get; set; }
}
public class Seller
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Product> Products { get; set; }
}

I want to extract a list of Sellers with all of their products using dapper.

Currently i'm doing it like this :

Dictionary<int, Seller> dic = new Dictionary<int, Seller>();
        Conn.Query<Seller, Product, int>
            (@"select s.*,p.* from Sellers s Join Products p 
                on p.SellerId = s.Id",
            (s, p) => {
                if (dic.ContainsKey(s.Id))
                    dic[s.Id].Products.Add(p);
                else
                {
                    s.Products = new List<Product>();
                    s.Products.Add(p);
                    dic.Add(s.Id, s);
                }
                return s.Id; 
            });
        var sellers = dic.Select(pair => pair.Value);

Is there any better way?

Answer 1

I think you are mixing how you want to store the data with how you want to use it. I would suggest normalizing your database.

//Normalized Database classes
public class Seller
{
    public int Id { get; set; }  // primary key
    public string Name { get; set; }
}

public class Product
{
    public int Id { get; set; }  // primary key
    public int SellerId { get; set; } // foreign key
    public string Name { get; set; }
    public decimal Price { get; set; }
}

Then you can query the Seller and product tables directly.

var Sellers = connection.Query<Seller>("Select * from Seller");
var Products = connection.Query<Product>("Select * from Product");

Then use linq "group by" to throw Product into a dictionary

var SellerWithProductsDict = 
        (from prod
        in Products 
        group prod by prod.SellerId
        into groupedProducts
        select groupedProducts)
        .ToDictionary(gp => gp.SellerId, gp => gp.ToList());

Then you can loop through the SellerWithProductsDict to see all the seller products, and if you need the seller name just get it by index from the Sellers query result

**************************************

That's the end of the answer, but if you really need the products mixed in with the sellers, you could use the same database structure above and do something like:

var qry = @"Select s.Id as SellerId,
                   s.Name as SellerName,
                   p.Id as ProductId,
                   p.Name as ProductName,
                   p.Price as ProductPrice
            From Seller as s, Product as p
            Where s.Id = p.id"

var SellerWithProducts = connection.Query(qry)

Then use linq "group by" functions to throw that into a dictionary. I would suggest looking at this "Linq group-by multiple fields" post for help with the group by linq