Dapper complex mapping Dapper.Extensions Dapper.FluentMap

Question

I have a bit of a problem with my code and getting dapper to play nicely with it.

When I say my code it was inherited, so this is not my design.

I am trying to replace Entity Framework as the calls to the database are less than efficient so I wanted to be more in control of the SQL produced so Dapper seemed like the obvious choice.

The issue I am having is that I am struggling to map the poco classes I have to the dapper multi query.

The issue I have is as follows:

    public Feed GetFeedDapper(int feedId)
        {
            Feed feed = null;

            var sql =
            @"
            SELECT * FROM Feeds WHERE FeedId= @FeedId
            SELECT * FROM FeedFilterParameters WHERE FeedId = @FeedId
            SELECT * FROM TeamFeeds WHERE FeedId = @FeedId";

            using (var multi = DbConnection.QueryMultiple(sql, new { FeedId = feedId }))
            {
                feed = multi.Read<Feed>().Single();
                feed.Parameters = multi.Read<FeedFilterParameter>().ToList();
                feed.TeamFeeds = multi.Read<TeamFeed>().ToList();
            } 

            return feed;
        }

This is getting me the correct results from the database, which is fine, the issue is that not all properties on the Feed object are being mapped. The feed has a property called InboundProperties that is of type InboundProperties as shown below, and in the database they are stored as InboundProperties_{PropName}. These properties are not being mapped and nothing I try in DapperExtensions or FluentMap are working.

public class InboundProperties
{
    public string ExternalRef { get; set; }
    public string ExternalRefPrevious { get; set; }
    public string ExternalId { get; set; }
    public string ExternalName { get; set; }
    public string ExternalToken { get; set; }
    public int ExternalAPICounts { get; set; }
    public string ExternalLink { get; set; }
    public string ExternalPicture { get; set; }
    public string LastProcessedMessageId { get; set; }
    public long? LastProcessedMessageTime { get; set; }
    public DateTime? MessageCountStartDT { get; set; }
    public Int32 TenancyId { get; set; }
    public virtual int FeedScopeInt { get; set; }
}

Can anyone help me map these properties??

Just out of curiosity, what did you try with Dapper.FluentMap? — Henk Mollema, Jan 20 '15 at 23:05
I tried to map the properties, but it was complaining about duplicate names as the same column name exists in the child tables. — jimplode, Jan 21 '15 at 12:34

score 2 · Answer 1 · answered Jan 20 '15 at 15:16

This is how I solved it, but accepted #rraszewski answer as it also works, but just means I need to take care of all the selects in a very manual way.

public Feed GetFeedDapper(int feedId)
{
    Feed feed = null;

    var multiPredicate = new GetMultiplePredicate();
    multiPredicate.Add<Feed>(Predicates.Field<Feed>(x => x.FeedId, Operator.Eq, feedId));
    multiPredicate.Add<InboundProperties>(Predicates.Field<InboundProperties>(x => x.FeedId, Operator.Eq, feedId));
    multiPredicate.Add<OutboundProperties>(Predicates.Field<OutboundProperties>(x => x.FeedId, Operator.Eq, feedId));
    multiPredicate.Add<FeedFilterParameter>(Predicates.Field<FeedFilterParameter>(x => x.FeedId, Operator.Eq, feedId));
    multiPredicate.Add<TeamFeed>(Predicates.Field<TeamFeed>(x => x.FeedId, Operator.Eq, feedId));

    var result = DbConnection.GetMultiple(multiPredicate);

    feed = result.Read<Feed>().Single();
    feed.InboundProperties = result.Read<InboundProperties>().Single();
    feed.OutboundProperties = result.Read<OutboundProperties>().Single();
    feed.Parameters = result.Read<FeedFilterParameter>().ToList();
    feed.TeamFeeds = result.Read<TeamFeed>().ToList();

    return feed;
}

Then I map the classes:

public class FeedMapper : ClassMapper<Feed>
{
    public FeedMapper()
    {
        base.Table("Feeds");
        Map(f => f.FeedId).Key(KeyType.Identity);

        Map(f => f.Owner).Ignore();
        Map(f => f.TeamFeeds).Ignore();
        Map(f => f.FeedDirection).Ignore();
        Map(f => f.InboundProperties).Ignore();
        Map(f => f.Parameters).Ignore();
        Map(f => f.OutboundProperties).Ignore();
        Map(f => f.RelatedTeams).Ignore();

        AutoMap();
    }
}

public class InboundPropertiesMapper : ClassMapper<InboundProperties>
{
    public InboundPropertiesMapper()
    {
        base.Table("Feeds");
        Map(f => f.FeedId).Key(KeyType.Identity);

        Map(f => f.Channel).Ignore();
        Map(f => f.FeedScope).Ignore();

        Map(f => f.ChannelInt).Column("InboundProperties_ChannelInt");
        Map(f => f.ExternalAPICounts).Column("InboundProperties_ExternalAPICounts");
        Map(f => f.ExternalId).Column("InboundProperties_ExternalId");
        Map(f => f.ExternalLink).Column("InboundProperties_ExternalLink");
        Map(f => f.ExternalName).Column("InboundProperties_ExternalName");
        Map(f => f.ExternalPicture).Column("InboundProperties_ExternalPicture");
        Map(f => f.ExternalRef).Column("InboundProperties_ExternalRef");
        Map(f => f.ExternalRefPrevious).Column("InboundProperties_ExternalRefPrevious");
        Map(f => f.ExternalToken).Column("InboundProperties_ExternalToken");
        Map(f => f.FeedScopeInt).Column("InboundProperties_FeedScopeInt");
        Map(f => f.LastProcessedMessageId).Column("InboundProperties_LastProcessedMessageId");
        Map(f => f.LastProcessedMessageTime).Column("InboundProperties_LastProcessedMessageTime");
        Map(f => f.MessageCountStartDT).Column("InboundProperties_MessageCountStartDT");
        Map(f => f.TenancyId).Column("InboundProperties_TenancyId");
        AutoMap();
    }
}

public class OutboundPropertiesMapper : ClassMapper<OutboundProperties>
{
    public OutboundPropertiesMapper()
    {
        base.Table("Feeds");
        Map(f => f.FeedId).Key(KeyType.Identity);

        Map(f => f.Channel).Ignore();
        Map(f => f.FeedType).Ignore();

        Map(f => f.OutboundFeedTypeInt).Column("OutboundProperties_OutboundFeedTypeInt");
        Map(f => f.TenancyId).Column("OutboundProperties_TenancyId");
        AutoMap();
    }
}

Seems a good solution; But unfortunately I cannot find `GetMultiplePredicate`. Can you guide please? Thanks in advance. — amiry jd, Dec 18 '16 at 11:31
This is part of the Dapper Extensions Library https://www.nuget.org/packages/DapperExtensions — jimplode, Dec 19 '16 at 12:03

rraszewski · Accepted Answer · 2015-01-20T14:51:29.747

1

Have you tried sth like that:

Feed feed = null;

var sql =
@"
SELECT * FROM Feeds WHERE FeedId= @FeedId
SELECT InboundProperties_ExternalRef as ExternalRef, InboundProperties_ExternalRefPrevious as ExternalRefPrevious FROM Feeds as InboundProperties WHERE FeedId= @FeedId
SELECT * FROM FeedFilterParameters WHERE FeedId = @FeedId
SELECT * FROM TeamFeeds WHERE FeedId = @FeedId";

using (var multi = DbConnection.QueryMultiple(sql, new { FeedId = feedId }))
{
    feed = multi.Read<Feed>().Single();
    feed.InboundProperties = multi.Read<InboundProperties>().Single();
    feed.Parameters = multi.Read<FeedFilterParameter>().ToList();
    feed.TeamFeeds = multi.Read<TeamFeed>().ToList();
} 

return feed;

I mapped only 2 first properties, but if it works you will know how to map all properties.

edited Jan 20 '15 at 14:51

answered Jan 20 '15 at 14:04

rraszewski

1,135
7
21

I think this may work, however this just feels wrong and kludgey... I will see if it will work though and get back to you – jimplode Jan 20 '15 at 14:28
This would work, but I do not like the fact that I have to do the mapping manually on every select, and due to the fact I have to replace upwards of 100 calls this is going to be a bit time consuming. I have opted for using dapper extensions, will post that as an answer as well, as it allows you to control the mapping slightly more easily. – jimplode Jan 20 '15 at 14:56
I can agree that it is not the best solution. What about reading `Feeds` to `dynamic` type and creating `Feed` object from dynamic object? – rraszewski Jan 20 '15 at 14:58
That could also work.... I like the dapper extension stuff where I can map objects at a class level. so presuming that is still going to be more efficient than EF I am going to give that a go and see what happens. – jimplode Jan 20 '15 at 15:09
Last thing. It is rather my question ;). Have you tried https://github.com/henkmollema/Dapper-FluentMap ? – rraszewski Jan 20 '15 at 15:15
I had a look at FluentMap, but could not make it work with the setup I had!! I might re-investigate it just for completness and if I get that work also put the code up here. – jimplode Jan 20 '15 at 15:17

Raúl Bojalil · Answer 3 · 2018-06-09T21:22:22.490

I was not satisfied with the solutions presented here (too error prone), so I decided to create an extension method to map the objects using reflection.

public static class ConnectionExtensions
{
    public static IEnumerable<T> QueryIncludeNestedObjects<T>(this SqlConnection connection, string sql)
    {
        var queryResults = connection.Query<dynamic>(sql);
        var typeOfTMain = typeof(T);

        foreach(var row in queryResults)
        {
            var mappedObject = Activator.CreateInstance<T>();

            foreach (var col in row)
            {
                var colKey = (string)col.Key;
                var colValue = (object)col.Value;

                if(colKey.Contains("_"))
                {
                    var subObjNameAndProp = colKey.Split('_');

                    var subProperty = typeOfTMain.GetProperty(subObjNameAndProp[0]);

                    if (subProperty == null) continue;

                    var subObj = subProperty.GetValue(mappedObject);

                    if(subObj == null)
                    {
                        subObj = Activator.CreateInstance(subProperty.PropertyType);
                        typeOfTMain.GetProperty(subObjNameAndProp[0]).SetValue(mappedObject, subObj);
                    }

                    subObj.GetType().GetProperty(subObjNameAndProp[1])
                        .SetValue(subObj, colValue);

                }
                else
                    typeOfTMain.GetProperty(colKey)?.SetValue(mappedObject, colValue);

            }

            yield return mappedObject;
        }
    }
}

Then you use it this way:

dbConnection.QueryIncludeNestedObjects<Feed>("SELECT * FROM Feeds");

If you want to use it inside a QueryMultiple like in this question just modify the method like this:

public static IEnumerable<T> ReadIncludeNestedObjects<T>(this GridReader gridReader)
{
    var queryResults = gridReader.Read<dynamic>();
    ...

Dapper complex mapping Dapper.Extensions Dapper.FluentMap

3 Answers3