How to return a model in an other one using a query?

Question

I have the following database table:

CREATE TABLE "users" (
    "id"    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "email" TEXT NOT NULL,
    "pet_name"  TEXT NOT NULL,
    "pet_age"   INTEGER NOT NULL
);

I have the following C# models:

public class User
{
    public string Email { get; set; }
    public Pet Pet { get; set; }
}

public class Pet
{
    public string Name { get; set; }
    public int Age { get; set; }
}

How could I do a SQLite SELECT * query that would return a User object, with the right Pet infos?

This is what I tried:

public static User GetUserByEmail(string email)
{
    using var con = new SQLiteConnection(Globals.DbConnectionString);

    return con.QueryFirstOrDefault<User>($"SELECT * FROM users WHERE email = @email COLLATE NOCASE", new
    {
        email
    });
}

But this wouldn't work with the Pet model, obviously it isn't magic.

Answer 1

This is what I ended up with using dynamic:

public static User GetUser(ulong discordId)
{
    using var con = new SQLiteConnection(Globals.DbConnectionString);

    var user = con.QueryFirstOrDefault<dynamic>($"SELECT * FROM {DbTable} WHERE discord_id = @discordId", new
    {
        discordId
    });

    if (user == null) return null;

    return new User
    {
        DiscordId = (ulong)user.discord_id,
        RegistrationTimestamp = (long)user.registration_timestamp,
        Email = (string)user.email,
        Balance = (double)user.balance,
        Profit = new Profit
        {
            Net = (double)user.net_profit,
            ATH = (double)user.ath_profit,
            ATL = (double)user.atl_profit
        }
    };
}