Multi mapping in dapper.net

Question

I'm implementing my first Dapper.Net project. Now i'm wondering what's the easiest way to initialize an object that contains another object(multi mapping).

Here's my code:

public static IEnumerable<ShopPrefix> GetShopPrefixes(short fiSL)
{
    using (var con = new SqlConnection(Properties.Settings.Default.RM2Con))
    {
        const String sql = @"
            SELECT  locShopPrefix.idShopPrefix,
                    locShopPrefix.fiSL,
                    locShopPrefix.fiShop,
                    locShopPrefix.Prefix,
                    locShopPrefix.Active,
                    locShop.idShop,
                    locShop.ShopName,
                    locShop.ContactPerson,
                    locShop.Street,
                    locShop.ZIP,
                    locShop.City,
                    locShop.Telephone,
                    locShop.Telefax,
                    locShop.Email,
                    locShop.ShopKey
            FROM    locShopPrefix
                    INNER JOIN locShop
                        ON locShopPrefix.fiShop = locShop.idShop
            WHERE   (locShopPrefix.fiSL = @fiSL);";
        con.Open();
        IEnumerable<Tuple<ShopPrefix,Shop>> shops =
            con.Query<ShopPrefix, Shop, Tuple<ShopPrefix, Shop>>(
            sql
            , (shopPrefix, shop) => Tuple.Create(shopPrefix, shop)
            , new { fiSL = fiSL }, splitOn: "idShop"
        );
        foreach (var shop in shops)
            shop.Item1.Shop = shop.Item2;
        return shops.Select(t => t.Item1);
    }
}

So every shopPrefix belongs to (has) a Shop.

Q: Is this the correct way to map two objects since the Tuple approach with the following foreach to initialize the property Shop looks cumbersome?

Tim Schmelter · Accepted Answer · 2012-07-17 17:34:07Z

4

I don't think you need a IEnumerable<Tuple<>> for a simple One-To-One object relationship. The following snippet should be sufficient as well.

var shopsPrefixes = con.Query<ShopPrefix, Shop, ShopPrefix>(sql
    , (shopPrefix, shop) =>
    {
        shopPrefix.Shop = shop;
        return shopPrefix;
    }
    , new { fiSL = fiSL }
    , splitOn: "idShop"
);
return shopsPrefixes;

edited Jul 17, 2012 at 17:34

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

answered Jul 17, 2012 at 16:45

Alex

8,1561 gold badge45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tim Schmelter Over a year ago

+1 Thank you, that looks promising but now i'm getting "When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id". With the Tuple it worked. I'll modify your answer to show what i've changed (i need the ShopPrefixes instead of the Shops).

Alex Over a year ago

How does your DTO look exactly? If ShopPrefix is a child of Shop, I think you have to define the following as for the query types : Query<Shop, ShopPrefix, Shop>. I'm not sure if Dapper can do an inverse mapping from Child to Parent :)

Tim Schmelter Over a year ago

Every ShopPrefix belongs to/has a Shop(FK in DB) whereas a Shop can have multiple prefixes. I want to initialize (possibly multiple) ShopPrefix with their related Shop.

Tim Schmelter Over a year ago

You're right, only the order was wrong. So this works: con.Query<ShopPrefix, Shop, ShopPrefix>(the same order as in my original query). Thank you very much.

Collectives™ on Stack Overflow

Multi mapping in dapper.net

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related