C# WPF项目中能否用Dapper映射含多关系的SQL Server数据表？

Absolutely, Dapper handles relational object mapping really well even without EF—let’s break down your options based on your scenario:

You have two reliable approaches to map one-to-many relationships with Dapper and stored procedures:

Multi-Mapping (Single Result Set) #

If your stored procedure returns a combined result set with columns for both the parent (Manager/City) and child (Facility) entities, you can use Dapper's multi-mapping feature to split the row into your objects and assemble the relationship.

For example, suppose your stored procedure GetManagersWithFacilities returns columns like:
ManagerName, ManagerPhone, FacilityName, FacilityDescription

Here’s how to map this to your Manager class with its Facilities list:

using (var connection = new SqlConnection("YourConnectionString"))
{
    var managers = connection.Query<Manager, Facility, Manager>(
        "GetManagersWithFacilities",
        (manager, facility) => 
        {
            // Initialize the list if it's null
            manager.Facilities ??= new List<Facility>();
            manager.Facilities.Add(facility);
            return manager;
        },
        splitOn: "FacilityName", // Tell Dapper where the Facility columns start
        commandType: CommandType.StoredProcedure)
        // Group results to avoid duplicate Manager instances
        .GroupBy(m => m.Name)
        .Select(group => 
        {
            var mergedManager = group.First();
            mergedManager.Facilities = group.Select(m => m.Facilities.First()).ToList();
            return mergedManager;
        })
        .ToList();
}

The splitOn parameter is key here—it tells Dapper to split the row into two objects starting at the specified column. We then group the results to merge duplicate Manager instances (since each row will have the same Manager data paired with one Facility).

Split Queries (Multiple Result Sets) #

Alternatively, you can have your stored procedure return two separate result sets: first all Managers, then all Facilities linked to them. Use Dapper's QueryMultiple to read both sets and manually associate the data:

using (var connection = new SqlConnection("YourConnectionString"))
using (var multi = connection.QueryMultiple("GetManagersAndFacilities", commandType: CommandType.StoredProcedure))
{
    var managers = multi.Read<Manager>().ToList();
    var facilities = multi.Read<Facility>().ToList();

    // Associate Facilities with their Manager (use an ID instead of Name for reliability!)
    foreach (var manager in managers)
    {
        manager.Facilities = facilities.Where(f => f.ManagerId == manager.Id).ToList();
    }
}

Pro tip: Always use numeric IDs (like ManagerId, FacilityId) for associations instead of strings like Name—this avoids bugs from duplicate names and is more efficient.

This is the most maintainable approach for long-term development. Your existing Manager, City, and Facility classes with List<Facility> properties align perfectly with your data model, making your business logic easier to write and debug.

That said, if you’re dealing with a one-off, simple query, you could use a flat "combined" class to hold all fields:

public class ManagerFacilityFlat
{
    public string ManagerName { get; set; }
    public string ManagerPhone { get; set; }
    public string FacilityName { get; set; }
    public string FacilityDescription { get; set; }
}

But you’ll still need to manually convert this flat data into your relational classes if you need to work with the one-to-many relationship later. Stick with your original class structure unless you have a specific reason not to.

• For multi-mapping: Use clear column prefixes (e.g., Manager_Name, Facility_Name) to avoid ambiguity, and make sure your splitOn parameter points to the first column of the child entity.
• For split queries: Return result sets in a logical order (parents first, then children) and include foreign keys (like ManagerId) in the child result set to make association straightforward.
• Avoid relying on string fields (like Name) for relationships—use unique IDs instead.

If Slapper.AutoMapper didn’t work for you, it’s likely because it needs explicit configuration for key fields or relationship rules. Dapper’s native mapping is more straightforward for these scenarios, no extra setup required.

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