Dapper Query方法实体ID映射错误问题咨询

Hey there, let's work through this Dapper mapping problem you're hitting. The core issue here is almost certainly that your postTagsSql query is returning multiple columns named Id (one from PostTag and one from PostTagIndex), and Dapper doesn't know which one to map to your PostTag.Id property—it just picks the last one it encounters, leading to incorrect values.

Here are three straightforward solutions to fix this:

1. Restrict your SQL query to only return PostTag fields #

The simplest fix is to update your SQL to explicitly select only the columns your PostTag class needs, avoiding the extra Id from PostTagIndex. For example:

SELECT pt.Id, pt.Alias, pt.Name, pt.Level
FROM PostTag pt
JOIN PostTagIndex pti ON pt.Id = pti.PostTagId
WHERE pti.PostId = @postId

By prefixing columns with the table alias (pt.) and omitting pti.Id, you eliminate the duplicate Id column entirely, so Dapper maps the correct value to PostTag.Id.

2. Use column aliases and Dapper's Column attribute #

If you need to keep multiple Id columns in your query (for other purposes), you can alias the PostTag.Id column in SQL and map it explicitly with Dapper's Column attribute:

First, update your PostTag class to add the attribute:

public class PostTag { 
    [Dapper.Column("PostTagId")]
    public int Id { get; set; } 
    public string Alias { get; set; } 
    public string Name { get; set; } 
    public int Level { get; set; } 
}

Then adjust your SQL to alias the PostTag.Id column:

SELECT pt.Id AS PostTagId, pti.Id AS PostTagIndexId, pt.Alias, pt.Name, pt.Level
FROM PostTag pt
JOIN PostTagIndex pti ON pt.Id = pti.PostTagId
WHERE pti.PostId = @postId

Now Dapper will correctly map the PostTagId alias to your PostTag.Id property.

3. Configure a custom TypeMap (for global control) #

If you don't want to modify your entity class or SQL, you can set up a custom type map for PostTag to tell Dapper exactly which column to use for Id:

// Add this somewhere in your application initialization (e.g., startup)
Dapper.SqlMapper.SetTypeMap(
    typeof(PostTag),
    new CustomPropertyTypeMap(
        typeof(PostTag),
        (type, columnName) => 
            columnName.Equals("Id", StringComparison.OrdinalIgnoreCase) 
                ? type.GetProperty(nameof(PostTag.Id))
                : type.GetProperty(columnName)
    )
);

This ensures that when Dapper encounters an Id column, it maps it to PostTag.Id—and ignores any other Id columns from joined tables (though you still might want to clean up your SQL for clarity).

Quick Recap #

The root cause is duplicate Id columns in your query result. Start with solution 1—it's the cleanest and most efficient. If you need to keep extra columns, go with solution 2. Solution 3 is best for global, app-wide mapping rules.

内容的提问来源于stack exchange，提问作者Serg