✅ 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
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; }
}
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);
";
}
UserCompanyTable, that have relation between user and companies
On repository I'm using this way to get user with relation companies3 Replies
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using Npgsql;
using Tiss.Domain.Repositories;
using Tiss.Domain.Users.Models;
using Tiss.Infra.Repositories.User.Entities;
using Tiss.Infra.Repositories.User.Queries;
using Tiss.Shared.EnvironmentVariables;
namespace Tiss.Infra.Repositories.User;
public class PostgresqlUserRepository : IUserRepository
{
private readonly NpgsqlConnection _connection;
private readonly UserTableQueries _queries = new();
public PostgresqlUserRepository(ApplicationEnvVars applicationEnvVars)
{
_connection = new NpgsqlConnection(applicationEnvVars.PostgreSqlEnvVars.ConnectionString);
_connection.Open();
}
public async Task<UserModel?> GetUserByTaxId(string taxId)
{
try
{
var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("TaxId", taxId);
var queryResult = await _connection.QueryAsync<UserEntity>(_queries.GetUserByTaxId(), dynamicParameters);
var userModel = ToDomain(queryResult);
return userModel;
}
catch (Exception e)
{
// TODO: add logger;
Console.WriteLine(e);
throw;
}
}
private UserModel ToDomain(IEnumerable<UserEntity> userEntities) =>
new()
{
Name = userEntities.First().Name,
Password = userEntities.First().Password,
Email = userEntities.First().Email,
TaxId = userEntities.First().TaxId,
RelatedCompanies = userEntities.Select(v => v.CompanyId).ToList()
};
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using Npgsql;
using Tiss.Domain.Repositories;
using Tiss.Domain.Users.Models;
using Tiss.Infra.Repositories.User.Entities;
using Tiss.Infra.Repositories.User.Queries;
using Tiss.Shared.EnvironmentVariables;
namespace Tiss.Infra.Repositories.User;
public class PostgresqlUserRepository : IUserRepository
{
private readonly NpgsqlConnection _connection;
private readonly UserTableQueries _queries = new();
public PostgresqlUserRepository(ApplicationEnvVars applicationEnvVars)
{
_connection = new NpgsqlConnection(applicationEnvVars.PostgreSqlEnvVars.ConnectionString);
_connection.Open();
}
public async Task<UserModel?> GetUserByTaxId(string taxId)
{
try
{
var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("TaxId", taxId);
var queryResult = await _connection.QueryAsync<UserEntity>(_queries.GetUserByTaxId(), dynamicParameters);
var userModel = ToDomain(queryResult);
return userModel;
}
catch (Exception e)
{
// TODO: add logger;
Console.WriteLine(e);
throw;
}
}
private UserModel ToDomain(IEnumerable<UserEntity> userEntities) =>
new()
{
Name = userEntities.First().Name,
Password = userEntities.First().Password,
Email = userEntities.First().Email,
TaxId = userEntities.First().TaxId,
RelatedCompanies = userEntities.Select(v => v.CompanyId).ToList()
};
}
Looks like nothing has happened here. I will mark this as stale and this post will be archived until there is new activity.
?