Object mapping in ASP.NET Core with C#

Question

I am new to ASP.NET Core, please help me. I am trying to map class objects with SQL Server database tables; please help me to get the desire output like this:

[
    {
        "ID": 1,
        "Line1": "myaddress",
        "Line2": "address2",
        "City": "mycity",
        "State": {
                     "StateID": 1,
                     "StateName": "mystate"
                 },
        "StateID": 1,
        "ZipCode":"545588"
    }
]

My classes are:

public class Address
{
    public int Id { get; set; }
    public string Line1 { get; set; }
    public string Line2 { get; set; }
    public string City { get; set; }
    public State State { get; set; }
    public string ZipCode { get; set; }
}

public class State
{
    public int StateId { get; set; }
    public string StateName { get; set; }
}

Database tables are:

CREATE TABLE [dbo].[address]
(
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [line1] [varchar](50) NULL,
    [line2] [varchar](50) NULL,
    [city] [varchar](50) NULL,
    [stateid] [int] NULL,
    [zipcode] [varchar](20) NULL,
)

CREATE TABLE [types].[state] 
(
    [stateid] [int] IDENTITY(1,1) NOT NULL,
    [statename] [text] NULL
)

Code that reads data from database:

await using var conn_payoraddr = new SqlConnection(_connectionString.Value);
string query = "Select * from Address t1 left join types.State t2 on t1.stateid = t2.stateid where PayorId = @Id";

var result_addr = await conn_payoraddr.QueryAsync<PayorAddress>(query, new { Id = id });

Answer 1

According to your codes, it seems you are using dapper, by using the dapper to query multiple table and mapping with the nested object, you need set the state property during write query method.

More details, you could refer to below example:

Since your codes contains PayorAddress, but you just use the Address class, I directly query and map the data to Address class:

        using var conn = new SqlConnection(connectionString);
        int id = 1;
        string query = @"
    SELECT t1.*, t2.*
    FROM Address t1
    LEFT JOIN type.State t2 ON t1.stateid = t2.stateid
    WHERE t1.Id = @Id";

        var result = (await conn.QueryAsync<Address, State, Address>(
            query,
            (Address, state) => {
                Address.State = state;
                return Address;
            },
            new { Id = id },
            splitOn: "stateid"
        )).FirstOrDefault();

Class:

public class Address
{
    public long Id { get; set; }
    public string Line1 { get; set; }
    public string Line2 { get; set; }
    public string City { get; set; }
    public int StateId { get; set; }
    public State State { get; set; }
    public string ZipCode { get; set; }
}
public class State
{
    public int StateId { get; set; }
    public string StateName { get; set; }
}

Result: