✅ Dapper relation tables
In my domain I have this class
Each user can have multiplies companies, so I have this SQL statement
The query result could return multiply rows due
On repository I'm using this way to get user with relation companies
public class UserModel : BaseModel
{
public required string Name { get; set; }
public required string Password { get; set; }
public required string Email { get; set; }
public required string TaxId { get; set; }
public bool IsActive { get; set; } = false;
public required List<int> RelatedCompanies { get; set; }
}public class UserModel : BaseModel
{
public required string Name { get; set; }
public required string Password { get; set; }
public required string Email { get; set; }
public required string TaxId { get; set; }
public bool IsActive { get; set; } = false;
public required List<int> RelatedCompanies { get; set; }
}Each user can have multiplies companies, so I have this SQL statement
public class UserTableQueries
{
private const string UserTableName = "tiss.user";
private const string UserCompanyTableName = "tiss.user_company";
public string GetUserByTaxId() =>
$@"
SELECT
{UserTableName}.id as Id,
{UserTableName}.name as Name,
{UserTableName}.tax_id as TaxId,
{UserTableName}.password as Password,
{UserTableName}.email as Email,
{UserTableName}.active as IsActive,
{UserTableName}.created_at as CreatedAt,
{UserTableName}.updated_at as UpdatedAt,
{UserTableName}.updated_at as UpdatedAt,
{UserCompanyTableName}.company_id as CompanyId
FROM {UserTableName}
LEFT JOIN {UserTableName} ON {UserTableName}.id = {UserCompanyTableName}.user_id
WHERE
{UserTableName}.tax_id = (@TaxId);
";
}public class UserTableQueries
{
private const string UserTableName = "tiss.user";
private const string UserCompanyTableName = "tiss.user_company";
public string GetUserByTaxId() =>
$@"
SELECT
{UserTableName}.id as Id,
{UserTableName}.name as Name,
{UserTableName}.tax_id as TaxId,
{UserTableName}.password as Password,
{UserTableName}.email as Email,
{UserTableName}.active as IsActive,
{UserTableName}.created_at as CreatedAt,
{UserTableName}.updated_at as UpdatedAt,
{UserTableName}.updated_at as UpdatedAt,
{UserCompanyTableName}.company_id as CompanyId
FROM {UserTableName}
LEFT JOIN {UserTableName} ON {UserTableName}.id = {UserCompanyTableName}.user_id
WHERE
{UserTableName}.tax_id = (@TaxId);
";
}The query result could return multiply rows due
UserCompanyTableUserCompanyTable, that have relation between user and companiesOn repository I'm using this way to get user with relation companies
