CODEWITHSUNDEEP
c#sql-serverdapper
UCProgrammer
UCProgrammer

Reputation: 557

Dapper sql select query with join statement, mutli mapping error

New to dapper and having a difficult time figuring out how to complete this query. I've looked at other examples on their site and SO, but I'm still having a difficult time putting things together.

I have the following stored procedure in my sql db. 

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SELECT tests.id AS tests_id, tester.name AS tester_name, product.name AS prod_name, product.version 
    FROM tests JOIN tester ON tests.tester_id =  tester.id 
    JOIN product ON tests.product_id = product.id;
END

Here is my Test class along with my Product and Tester classes. The properties for the Tester and Product class match up the same naming wise with the column names in tester and product tables. The Tests table just contains an id, tester_id(fk) and product_id(fk).

public class Test
{
    public int Id { get; set; }
    public Tester Tester { get; set; }
    public Product Product { get; set; }
}

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

public class Tester
{
    public int Id { get; set; }
    public string Name { get; set; }
}

I'm trying to run this stored procedure and populate my Test object. 

public List<Test> GetTests()
{
    using (IDbConnection connection = new SqlConnection(Helper.CnnVal("pqual_db")))
    {
        connection.Open();
        var output = connection.Query<Test, Product, Tester, Test>("pqual_db.sptests_GetTests", (tests, products, tester) => { tests.Product = products; tests.Tester = tester; return tests; }).ToList();
        return output;
    }
}

I'm currently getting an ArgumentException, "When using the multi-mapping API, ensure that you set the splitOn param if you have keys other than id Parameter name: splitOn." I'm thinking that the splitOn param should be used here twice, once for the join statement regarding tester id and once for the other join for product id, however, I have these columns named differently as you can see. Wouldn't that cause issues for dapper? Do I have to go into my db and rename my PK's for tables like tester.id to tester.tester_id in order for these queries to work? I'm thinking I don't need to go that far but I'm really stumped on how to fix this. Any help would be appreciated.

Upvotes: 0

Views: 397

Answers (1)

HariHaran
HariHaran

Reputation: 4119

Can you try specifying the column names in the splitOn property like this 

public List<Test> GetTests()
{
    using (IDbConnection connection = new SqlConnection(Helper.CnnVal("pqual_db")))
    {
        connection.Open();
        var output = connection.Query<Test, Product, Tester, Test>("pqual_db.sptests_GetTests", (tests, products, tester) => 
    { 
      tests.Product = products; 
      tests.Tester = tester; 
      return tests; 
    }, splitOn:"tester_id,product_id ").ToList();
     return output;
    }
}

Upvotes: 1

Related Questions