How to use Dapper to write raw SQL and map to a entity class using C#

Question

I want to write raw SQL to get a result sets from a set of tables where there is 1-to-M relationship.

I am using Dapper for this, but not getting the expected results.

The first table is Supplier:

    public class Supplier: IId
    {
        public int Id { get; set; }
        public string Ref{ get; set; }
        public DateTime CreatedOn { get; set; }
        public List<Category> Categories{ get; set; }
        public int? Ticket { get; set; }
    }

Then there's Category:

    public class Category: IId
    {
        public int Id { get; set; }
        public int SupplierId{ get; set; }
        [JsonIgnore] public Supplier Supplier{ get; set; }
        public Type Type { get; set; }
        public Status Status { get; private set; }
        public string CompletedBy { get; private set; }
        public string CreatedBy { get; private set; }
        public DateTime? CreatedOn { get; private set; }
        public int? Ticket { get; set; }
    }

Here is my SQL:

var reference = 5055;

var sql = "SELECT S.Ref,S.CreatedOn,S.Ticket" +
          "       C.TaskId AS 'Id', C.SupplierId, C.Type, C.Status, C.CompletedBy, C.CreatedBy, C.CreatedOn, C.Ticket " + 
          "FROM Category C " +
          "INNER JOIN Supplier S ON S.SupplierId = C.SupplierId " +
          "WHERE S.Ref = @reference ";

 var parameters = new {reference};

 var suppliers = connection.QueryAsync<Supplier, List<Category>, Supplier>(sql, (supplier,category)   => {
                    supplier.Categories = category;
                    return supplier;
                }, parameters, splitOn: "Id" );

var result = suppliers.Result.OrderByDescending(x => x.CreatedOn).FirstOrDefault();

What I want is categories should be included in supplier entity, I read the documentation for Dapper and from that I tried the code shown here, but it's not working out.

Can anybody help me with this?

Can anybody tell me another approach for this, for example with a SQL view or a stored procedure?

Answer 1

how i found a way to solve this is

var reference = 5055;

var sql = "SELECT S.SupplierId As 'Id',S.Ref,S.CreatedOn,S.Ticket " +
          "FROM Supplier S " +
          "WHERE S.Ref = @reference ";

 var parameters = new {reference};

 var suppliers = connection.QueryFirstOrDefault<Supplier>(sql, parameters);

 suppliers.Category =  (List<Category>) connection.Query<Category>("SELECT C.CategoryId As 'Id',C.SupplierId,C.Type,C.Status,C.CompletedBy,C.CompletedOn,C.CreatedBy,C.CreatedOn,C.Ticket FROM Category C WHERE C.SupplierId= @supplierId", new { supplierId = suppliers.Id});
            return suppliers;

Answer 2

Here is the code that was outlined in the comments. The basic idea is that since the query will return multiple rows for the same "Supplier"-instance the Dictionary will make sure that only one instance is actually created and will be reused in subsequent result rows.

 Dictionary<int, Supplier> dict = new Dictionary<int, Supplier>();

 var suppliers = connection.QueryAsync<Supplier, Category, Supplier>(sql,
    (s, c) =>
    {
        if (!dict.TryGetValue(s.Id, out var supplier))
        {
            // Haven't seen this supplier before -> add to dictionary and initialize Categories list
            dict[s.Id] = s;
            s.Categories = new List<Category>();
            supplier = s;
        }

        supplier.Categories.Add(c);
        return supplier;
    },
 parameters, splitOn: "Id");

Answer 3

//try this using look-up dictionary.

var lookup = new Dictionary<int, Supplier>();
connection.Query<Supplier, Category, Supplier>(sql, (s, c) =>
   {
       Supplier supplier;
       if (!lookup.TryGetValue(c.Id, out supplier))
           lookup.Add(c.Id, supplier = s);
       if (supplier.Categories == null)
           supplier.Categories = new List<Category>();
       supplier.Categories.Add(c);
       return supplier;
   }, parameters).AsQueryable();
   var resultList = lookup.Values;