Incorrect mapping with Dapper, .NET Core and Postgres

Question

Thanks in advance. I'm seeing an issue with my mapping using Postgres and Dapper. I'm trying to map an Organization object, which has a one-to-many relationship with the Location object. The following is my mapping code:

public Organization GetOrganizationById(long id)
        {
            var query = @"SELECT 
                 o.organization_id,
                 o.guid,
                 o.name,
                 o.is_active,
                 o.created,
                 l.location_id,
                 l.org_id,
                 l.guid,
                 l.internal_identifier,
                 l.name,
                 l.address,
                 l.city,
                 l.state,
                 l.zip,
                 l.phonenumber,
                 l.is_active,
                 l.opendate,
                 l.closedate,
                 l.created
                FROM organization AS o
                 INNER JOIN location as l 
                    ON o.organization_id = l.org_id
                 WHERE o.organization_id = @Id";

            using (var con = new NpgsqlConnection(_connectionString))
            {

                var orgResult = con.Query<Organization, List<Location>, Organization>(
                    query,
                    (org, locations) =>
                    {
                        org.Locations = locations;
                        return org;
                    },
                    new {Id = id},
                    splitOn: "location_id").FirstOrDefault();
                
                
                return orgResult;
            }
        }

I have the following objects created:

    public class Organization
    {
        public long OrganizationId { get; set; }
        public Guid Guid { get;set; }
        public string Name { get; set; }
        public bool IsActive { get; set; }
        public DateTime Created { get; set; }
        
        //[JsonIgnore]
        public List<Location> Locations { get; set; }
        
    }

and

    public class Location
    {
        public long LocationId { get; set; }
        public long OrgId { get; set; }
        public Guid Guid { get; set; }
        public string InternalIdentifier { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Zip { get; set; }
        public string PhoneNumber { get; set; }
        public bool IsActive { get; set; }
        public DateTime OpenDate { get; set; }
        public DateTime? CloseDate { get; set; }
        public DateTime Created { get; set; }
        
        [JsonIgnore]
        public Organization Organization { get; set; }

    }

Now the issue is, when I get the results it's not even close to accurate. Though the query when copied into a SQL client it's returning the correct results.

Here's what I see in the JSON response along with what's incorrect:

{
  "organizationId": 0, // This is incorrect. The organization ID is one (this appears to be the default int/long)
  "guid": "4fc55437-8497-4388-be48-c6b8c5dfee93", // This is correct
  "name": "TestOrg", // This is correct
  "isActive": false, // This is incorrect. Both locations are active, as is the organization
  "created": "2021-01-27T05:20:42.287925", // This is correct 
  "locations": [] // Incorrect, there should be two locations present
}

Anyway, is there anything you think I'm missing with the mapping that would prevent these records from not mapping to their POCOs correctly?

Answer 1

Dapper requires your model property names to mirror your table (not case sensitive).

• You can try using the [ColumnName()] attribute above your properties. I've heard this works for some but it didn't for me. I ended up using AutoMapper and created a mapper object.

[ColumnName("SomeFieldName")]
public string SomeProperty { get; set; }

Before you look into that (cause it's a pain) try using Dapper's QueryMultiple() method.

Dapper's Query Multiple

You may have to play around with it a bit. It's been awhile since I've written the full QueryMultiple() method the Dapper way. I made a wrapper class to containerize my dapper functions (I just containerize all 3rd party packages incase of exceptions or something). Here's one of my methods using QueryMultiple(). to return 2 data sets using out parameters. You can adjust this to return as may sets as you need.

/// <summary>
/// Executes a query with multiple results and stores the result sets in the out parameters.
/// </summary>
/// <typeparam name="T1">The type of the first result set.</typeparam>
/// <typeparam name="T2">The type of the second result set.</typeparam>
/// <typeparam name="P">The parameter type. Generally an autonomous/dynamic object, a <see cref="DynamicParameters"/>, or an <see cref="ExpandoObject"/>.</typeparam>
/// <param name="sql">The SQL query string or stored procedure.</param>
/// <param name="parameters">The parameter(s) for the stored procedure.</param>
/// <param name="results1">The first result set.</param>
/// <param name="results2">The second result set.</param>
/// <param name="queryType">
/// <para>The query's command type.</para>
/// Defaults to <strong><see cref="CommandType.StoredProcedure"/></strong>.
/// </param>
public void ExecuteQueryMultiple<T1, T2, P>(string sql, P parameters, 
    ConnStringKey connectionKey, // This is a personal app setting enum...
    out List<T1> results1, out List<T2> results2, 
    CommandType queryType = CommandType.StoredProcedure)
{
    using (IDbConnection connection = new 
        SqlConnection(configurations.GetConnectionString(connectionKey)))
    {
        using (SqlMapper.GridReader sqlReader = connection.QueryMultiple(sql, 
            parameters, commandType: queryType, commandTimeout: ConnectionTimeout))
        {
            results1 = sqlReader.Read<T1>().AsList();
            results2 = sqlReader.Read<T2>().AsList();
        }
    }
}

You can use something similar if your SQL was like this:

SELECT 
    o.organization_id,
    o.guid,
    o.name,
    o.is_active,
    o.created,
FROM organization AS o
WHERE o.organization_id  = @Id

SELECT
    l.location_id,
    l.org_id,
    l.guid,
    l.internal_identifier,
    l.name,
    l.address,
    l.city,
    l.state,
    l.zip,
    l.phonenumber,
    l.is_active,
    l.opendate,
    l.closedate,
    l.created
FROM location AS l
WHERE l.org_id = @Id  

Of course, then you'd have to aggregate the two result sets in the code.

---

Usage:

var someParams = new { Id = someId };

sql = "your sql";

_sql.ExecuteQueryMultiple<Organization, Location, dynamic>(
    sql, someParams, 
    // this is an enum of mine that ties back to my appsettings.json or app.config file to get the connection string
    MyConfigurationConnectionKey, 
    out List<Organization> organizations, 
    out List<Location> locations, 
    CommandType.Text);

// Aggregate the two data sets...
organizations.Locations = locations; // or however you need to do it