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?

I don't know about "better", but an *alternative* way would be query-multiple with two separate grids... but what you have should work — Marc Gravell, Nov 05 '12 at 20:15

score 3 · Accepted Answer · edited May 23 '17 at 12:06

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

Never modify your domain classes to match your database schema that is what an ORM and the DAL layer are for. You classes should map to your business domain and your data not the normalized database schema. — eaglestorm, May 14 '15 at 00:34

AdamV · Answer 2 · 2012-11-09T22:05:20.630

My last post went crazy for some reason.

First off, it might be a bad idea to get everything from your database. You might want to consider limiting your query. Even though your table might be small now, you don't want to back yourself against a wall.

With that being said, I would recommend simplifying the code to get the Sellers and Products by adding static methods onto Seller and Product.

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

    public int? SellerId { get; set; }
    public Seller Seller { get; set; }

    public static Product GetProductById(int id)
    {
        Product product = null;
        using (SqlConnection connection = new SqlConnection("connectionString"))
        {
            product = connection.Query<Product>("select * from Product where Id = @Id", new { Id = id }).SingleOrDefault();
            product.Seller = connection.Query<Seller>("select * from Seller where Id = @Id", new { Id = product.SellerId }).SingleOrDefault();
        }

        return product;
    }
}

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

    public static Seller GetSellerById(int id)
    {
        Seller seller = null;
        using (SqlConnection connection = new SqlConnection("connectionString"))
        {
            seller = connection.Query<Seller>("select * from Seller where Id = @Id", new { Id = id }).SingleOrDefault();
            if(seller != null)
            {
                seller.Products = connection.Query<Product>("select * from Product where SellerId = @Id", new { Id = id }).ToList();
                seller.Products.ForEach(p => p.Seller = seller);
            }
        }

        return seller;
    }
}

Keep in mind, this is rough and doesn't represent everything you may need, but it is easier to use in the long run and follows Object Oriented Design better than making one of Queries to get your data.

You can further expand on this idea by adding methods that query for other things about the seller or product like GetSellersInZipCode(int zipCode) and return a List via that method.

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

2 Answers2

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