Dapper中的自定义映射 [英] Custom mapping in Dapper

问题描述

我正在尝试将CTE与Dapper和多重映射结合使用以获取分页结果。重复栏给您带来不便；

I'm attempting to use a CTE with Dapper and multi-mapping to get paged results. I'm hitting an inconvenience with duplicate columns; the CTE is preventing me from having to Name columns for example.

我想将以下查询映射到以下对象，而不是列名和属性之间的不匹配

I would like to map the following query onto the following objects, not the mismatch between the column names and properties.

查询：

WITH TempSites AS(
    SELECT
        [S].[SiteID],
        [S].[Name] AS [SiteName],
        [S].[Description],
        [L].[LocationID],
        [L].[Name] AS [LocationName],
        [L].[Description] AS [LocationDescription],
        [L].[SiteID] AS [LocationSiteID],
        [L].[ReportingID]
    FROM (
        SELECT * FROM [dbo].[Sites] [1_S]
        WHERE [1_S].[StatusID] = 0
        ORDER BY [1_S].[Name]
        OFFSET 10 * (1 - 1) ROWS
        FETCH NEXT 10 ROWS ONLY
    ) S
        LEFT JOIN [dbo].[Locations] [L] ON [S].[SiteID] = [L].[SiteID]
),
MaxItems AS (SELECT COUNT(SiteID) AS MaxItems FROM Sites)

SELECT *
FROM TempSites, MaxItems

对象：

public class Site
{
    public int SiteID { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public List<Location> Locations { get; internal set; }
}

public class Location
{
    public int LocationID { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public Guid ReportingID { get; set; }
    public int SiteID { get; set; }
}

出于某种原因，我认为存在一个命名约定会为我处理这种情况，但我在文档中找不到它。

For some reason I have it in my head that a naming convention exists which will handle this scenario for me but I can't find mention of it in the docs.

推荐答案

存在多个问题，让它们一个接一个地覆盖。

There are more than one issues, let cover them one by one.

CTE重复的列名称：

CTE不允许重复的列名，因此您必须使用别名来解析它们，最好使用一些命名约定，例如在您的查询尝试中。

CTE does not allow duplicate column names, so you have to resolve them using aliases, preferably using some naming convention like in your query attempt.

出于某种原因，我想到存在一个命名约定可以为我处理这种情况，但是我在文档中找不到该约定。

For some reason I have it in my head that a naming convention exists which will handle this scenario for me but I can't find mention of it in the docs.

您可能已经想到将 DefaultTypeMap.MatchNamesWithUnderscores 属性设置为 true ，但是将其作为代码属性的文档说明：

You probably had in mind setting the DefaultTypeMap.MatchNamesWithUnderscores property to true, but as code documentation of the property states:

应使用User_这样的列名可以让ID匹配UserId吗？

Should column names like User_Id be allowed to match properties/fields like UserId?

显然这不是解决方案。但是可以通过引入自定义命名约定轻松解决此问题，例如 {prefix} {propertyName} （默认情况下，前缀为 {className} _ ）并通过Dapper的 CustomPropertyTypeMap 。这是一个执行此操作的辅助方法：

apparently this is not the solution. But the issue can easily be solved by introducing a custom naming convention, for instance "{prefix}{propertyName}" (where by default prefix is "{className}_") and implementing it via Dapper's CustomPropertyTypeMap. Here is a helper method which does that:

public static class CustomNameMap
{
    public static void SetFor<T>(string prefix = null)
    {
        if (prefix == null) prefix = typeof(T).Name + "_";
        var typeMap = new CustomPropertyTypeMap(typeof(T), (type, name) =>
        {
            if (name.StartsWith(prefix, StringComparison.OrdinalIgnoreCase))
                name = name.Substring(prefix.Length);
            return type.GetProperty(name);
        });
        SqlMapper.SetTypeMap(typeof(T), typeMap);
    }
}

现在您只需要调用它（一次）：

Now all you need is to call it (one time):

CustomNameMap.SetFor<Location>();

将命名约定应用于您的查询：

apply the naming convention to your query:

WITH TempSites AS(
    SELECT
        [S].[SiteID],
        [S].[Name],
        [S].[Description],
        [L].[LocationID],
        [L].[Name] AS [Location_Name],
        [L].[Description] AS [Location_Description],
        [L].[SiteID] AS [Location_SiteID],
        [L].[ReportingID]
    FROM (
        SELECT * FROM [dbo].[Sites] [1_S]
        WHERE [1_S].[StatusID] = 0
        ORDER BY [1_S].[Name]
        OFFSET 10 * (1 - 1) ROWS
        FETCH NEXT 10 ROWS ONLY
    ) S
        LEFT JOIN [dbo].[Locations] [L] ON [S].[SiteID] = [L].[SiteID]
),
MaxItems AS (SELECT COUNT(SiteID) AS MaxItems FROM Sites)

SELECT *
FROM TempSites, MaxItems

，您已经完成了那部分。当然，您可以根据需要使用较短的前缀，例如 Loc _。

and you are done with that part. Of course you can use shorter prefix like "Loc_" if you like.

将查询结果映射到提供的类：

在这种特殊情况下，您需要使用 Query 方法重载，该重载允许您传递 Func< TFirst ，TSecond，TReturn> map 委托并统一 splitOn 参数，以指定 LocationID 作为拆分列。但是，这还不够。 Dapper的多重映射功能可让您将单行拆分为多个单个对象（例如LINQ Join ），而您需要 Site 和位置 列表（例如LINQ GroupJoin ）。

In this particular case you need to use the Query method overload that allows you to pass Func<TFirst, TSecond, TReturn> map delegate and unitilize the splitOn parameter to specify LocationID as a split column. However that's not enough. Dapper's Multi Mapping feature allows you to split a single row to a several single objects (like LINQ Join) while you need a Site with Location list (like LINQ GroupJoin).

可以通过使用 Query 方法将其投影为临时匿名类型，然后使用常规LINQ来实现。产生所需的输出，如下所示：

It can be achieved by using the Query method to project into a temporary anonymous type and then use regular LINQ to produce the desired output like this:

var sites = cn.Query(sql, (Site site, Location loc) => new { site, loc }, splitOn: "LocationID")
    .GroupBy(e => e.site.SiteID)
    .Select(g =>
    {
        var site = g.First().site;
        site.Locations = g.Select(e => e.loc).Where(loc => loc != null).ToList();
        return site;
    })
    .ToList();

其中 cn 已打开 SqlConnection 和 sql 是保存上述查询的字符串。

where cn is opened SqlConnection and sql is a string holding the above query.

这篇关于Dapper中的自定义映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！