using Dapper MultiMapper with an outer join query

c# dapper

Question

This is my first time using Dapper and i am trying to achieve the following

We have our domain objects defined as

public class Company
{
    public Int64 company_id { get; set; }

    public ICollection<Brand> Brands { get; set; }

    public ICollection<CompanyConnector> CompanyConnectors { get; set; }
}

public class Brand
{
    public Int64 brand_id { get; set; }

    public Int64 company_id { get; set; }

    public string brand_name { get; set; }
}

public class CompanyConnector
{
    public Int64 company_connector_id { get; set; }

    public Int64 company_id { get; set; }
}

I am attempting to use the multi mapper in Dapper to get a company, all its brands (this is optional) and all the connectors (optional too).

I have the query with a left outer join

select company.*, brand.* , company_connector.*
from company 
left outer join brand on company.company_id = brand.company_id
left outer join company_connector on company.company_id=        company_connector.company_id

The code to get the data is

var lookup = new Dictionary<Int64, Company>();
connection.Query<Company, Brand, CompanyConnector, Company>(strQuery,
(c, b, cc) =>
{
    Company cm;
    if (!lookup.TryGetValue(c.company_id, out cm))
    {
        lookup.Add(c.company_id, cm = c);
    }

    if (null != b)
    {
        if (null == cm.Brands)
            cm.Brands = new List<Brand>();
        cm.Brands.Add(b);
    }

    if (null != cc)
    {
        if (null == cm.CompanyConnectors)
            cm.CompanyConnectors = new List<CompanyConnector>();
        cm.CompanyConnectors.Add(cc);
    }

    return cm;
}, splitOn: "brand_id, company_connector_id"
).AsQueryable();

Since a company has 2 brands and 2 connectors - the outer join results in 4 rows - hence the result objects has 4 brand entries and 4 connector entries

I can add the line of code to check if the object exists before adding to the list

if(!cm.Brands.Any(item => item.brand_id == b.brand_id))
cm.Brands.Add(b);

Question - am i using this the right way? What is the solution for me to get the correct number of brand records and connector records

Popular Answer

From what I gather, Dapper doesn't directly support loading child collections which is why you have the code to do the lookup, check for null, add values...

I just added a check in there to see if the value was already added to the collection.

var lookup = new Dictionary<Int64, Company>();
connection.Query<Company, Brand, CompanyConnector, Company>(strQuery,
(c, b, cc) =>
{
    Company cm;
    if (!lookup.TryGetValue(c.company_id, out cm))
    {
        lookup.Add(c.company_id, cm = c);
    }

    if (null != b)
    {
        if (null == cm.Brands)
            cm.Brands = new List<Brand>();
        if(!cm.Brands.Any(r=>r.brand_id == b.brand_id ))
            cm.Brands.Add(b);
    }

    if (null != cc)
    {
        if (null == cm.CompanyConnectors)
            cm.CompanyConnectors = new List<CompanyConnector>();
        if(!cm.CompanyConnectors.Any(r=>r.company_connector_id  == b.company_connector_id  ))
            cm.CompanyConnectors.Add(cc);
    }

    return cm;
}, splitOn: "company_id, brand_id, company_connector_id"
).AsQueryable();

Another thing that could be done is to load the 3 collections separably and map them outside of the dapper function.

select * from company
select * from brand b join company c on c.company_id = b.company_id
select * from company_connector b join company c on c.company_id = b.company_id


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why