Dapper复杂映射Dapper.Extensions Dapper.FluentMap

Question

我的代码有点问题，无法很好地使用dapper。

当我说我的代码是继承的，所以这不是我的设计。

我正试图取代实体框架，因为对数据库的调用效率较低，所以我想更多地控制生成的SQL，所以Dapper似乎是显而易见的选择。

我遇到的问题是，我很难将我所拥有的poco类映射到dapper多查询。

我的问题如下：

    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;
        }

这让我从数据库中得到了正确的结果，这很好，问题是并非Feed对象上的所有属性都被映射。提要有一个名为InboundProperties的属性，其类型为InboundProperty，如下所示，并且在数据库中它们存储为InboundProperties_{PropName}。这些属性没有被映射，我在DapperExtensions或FluentMap中尝试的任何东西都不起作用。

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; }
}

有人能帮我绘制这些房产的地图吗？？

Answer 1

我就是这样解决的，但接受了#rraszewski的答案，因为它也有效，但这意味着我需要以非常手动的方式处理所有的选择。

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;
}

然后我映射类：

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();
    }
}

Answer 2

你试过这样的东西吗：

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;

我只映射了两个第一个属性，但如果它有效，你就会知道如何映射所有属性。

Answer 3

我对这里提供的解决方案不满意(太容易出错(，所以我决定创建一个扩展方法来使用反射映射对象。

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;
        }
    }
}

然后你用这种方式：

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

如果你想像这个问题中那样在QueryMultiple中使用它，只需修改这样的方法：

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