如何用Dapper嵌套列表查询获取带关联实体的Continent数据？

Alright, let's work through how to map your nested Continent/ContinentPart/Country/City hierarchy using Dapper with that left join query. The key here is handling duplicate parent records that come from the multi-table join, and correctly populating the nested lists without duplicates.

Step 1: Confirm Your POCO Class Structures #

First, let's make sure your POCOs are set up to hold the nested collections. Here's how they should look (add any additional properties you need for each entity):

public class Continent
{
    public int ContinentId { get; set; }
    public DateTime RecDate { get; set; }
    // Add other Continent-specific properties here (e.g., Name, Area)
    public List<ContinentPart> ContinentParts { get; set; } = new List<ContinentPart>();
    public List<Country> Countries { get; set; } = new List<Country>();
}

public class ContinentPart
{
    public int ContinentPartId { get; set; }
    public int ContinentId { get; set; }
    // Add other ContinentPart properties here (e.g., RegionName, Population)
}

public class Country
{
    public int CountryId { get; set; }
    public int ContinentId { get; set; }
    // Add other Country properties here (e.g., CountryName, Capital)
    public List<City> Cities { get; set; } = new List<City>();
}

public class City
{
    public int CityId { get; set; }
    public int CountryId { get; set; }
    // Add other City properties here (e.g., CityName, Population)
}

Step 2: Dapper Query & Nested Mapping Implementation #

We'll use a dictionary to track existing Continent instances (to avoid duplicates from the join) and Dapper's multi-mapping feature to split the result set into each entity type. Here's the full implementation:

using (IDbConnection db = new SqlConnection(_conf["ConnectionStrings:WorkConStr"]))
{
    // Dictionary to track unique Continent instances
    var continentLookup = new Dictionary<int, Continent>();

    string query = @"SELECT 
                        c.*,
                        cp.*,
                        co.*,
                        ci.*
                    FROM Continent as c
                    LEFT JOIN ContinentPart as cp ON c.ContinentId = cp.ContinentId
                    LEFT JOIN Country as co ON c.ContinentId = co.ContinentId
                    LEFT JOIN City ci ON co.CountryId = ci.CountryId
                    ORDER BY c.ContinentId, co.CountryId, ci.CityId";

    // Execute query and map to nested entities
    db.Query<Continent, ContinentPart, Country, City, Continent>(
        query,
        (continent, part, country, city) =>
        {
            // Handle Continent deduplication
            if (!continentLookup.TryGetValue(continent.ContinentId, out var existingContinent))
            {
                existingContinent = continent;
                continentLookup.Add(existingContinent.ContinentId, existingContinent);
            }

            // Add ContinentPart if it exists and isn't already in the list
            if (part != null && !existingContinent.ContinentParts.Any(p => p.ContinentPartId == part.ContinentPartId))
            {
                existingContinent.ContinentParts.Add(part);
            }

            // Add Country and its Cities if Country exists
            if (country != null)
            {
                // Check if Country is already in the Continent's Countries list
                var existingCountry = existingContinent.Countries.FirstOrDefault(co => co.CountryId == country.CountryId);
                if (existingCountry == null)
                {
                    existingCountry = country;
                    existingContinent.Countries.Add(existingCountry);
                }

                // Add City if it exists and isn't already in the Country's Cities list
                if (city != null && !existingCountry.Cities.Any(ci => ci.CityId == city.CityId))
                {
                    existingCountry.Cities.Add(city);
                }
            }

            return existingContinent;
        },
        // Tell Dapper where to split the result set into different entities
        splitOn: "ContinentPartId,CountryId,CityId"
    );

    // Final list of continents with all nested data
    var continentsWithNestedData = continentLookup.Values.ToList();
}

Key Notes & Best Practices #

• Order Matters: The ORDER BY clause ensures that all records for the same Continent, then same Country, are grouped together. This makes it easier to deduplicate entities without missing any nested items.
• SplitOn Parameter: The splitOn value tells Dapper to split the result set at the first occurrence of each specified column. This is critical for correctly mapping each segment of the row to the corresponding POCO.
• Null Checks: Since we're using LEFT JOIN, some entities (like ContinentPart or City) might be null. Always check for null before adding to collections to avoid null reference exceptions.
• Duplicate Prevention: Using the dictionary for continents and checking existing items in lists ensures we don't add duplicate parent entities (which would happen because each join row repeats the parent data).
• Column Aliases: If your tables have columns with the same name (e.g., Name in Continent, Country, and City), rename them in the SQL query with aliases (e.g., c.Name as ContinentName) and update your POCO properties to match, or use Dapper's column mapping attributes.

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