I am trying to make an architecture using Dapper in which I am trying to give basic functionality of having Crud by default on each Tables, Just like Entity Framework does. For more complex operations on table as usual everyone will use SP. But while giving basic crud functionality I am facing problem when converting my expression to its proper formatted string.
This is my controllers code –
[HttpGet]
public async Task<IActionResult> GetAllByAge(int age)
{
return Ok(await _unitofWork.Employees.GetAllAsync(e => e.Age == age));
}
This is my generic repository code –
public async virtual Task<IEnumerable<T>> GetAllAsync(Expression<Func<T, bool>>? expression = null)
{
string query = $"select * from {_tableName}";
if (expression is not null)
{
query += $"where {QueryTranslator.TranslateExpressionToSql(expression)}";
}
return await _db.LoadDataAsync<dynamic, T>(query, new { }, true);
}
Now TranslateExpressionToSql method is –
public static string TranslateExpressionToSql<TEntity>(Expression<Func<TEntity, bool>> predicate)
{
var body = predicate.Body;
var sqlQuery = GenerateSqlFromExpression(body);
return sqlQuery;
}
Now main problem exists in GenerateSqlFromExpression method which is as below –
private static string GenerateSqlFromExpression(Expression expression)
{
if (expression is BinaryExpression binaryExpression)
{
var left = GenerateSqlFromExpression(binaryExpression.Left);
var right = GenerateSqlFromExpression(binaryExpression.Right);
var operation = binaryExpression.NodeType switch
{
ExpressionType.Equal => "=",
ExpressionType.NotEqual => "<>",
ExpressionType.LessThan => "<",
ExpressionType.LessThanOrEqual => "<=",
ExpressionType.GreaterThan => ">",
ExpressionType.GreaterThanOrEqual => ">=",
ExpressionType.AndAlso => "AND",
ExpressionType.OrElse => "OR",
_ => throw new NotSupportedException($"Unsupported operation: {binaryExpression.NodeType}")
};
return $"({left} {operation} {right})";
}
else if (expression is MemberExpression memberExpression)
{
return memberExpression.Member.Name;
}
else if (expression is ConstantExpression constantExpression)
{
return $"'{constantExpression.Value}'";
}
else
{
throw new NotSupportedException($"Unsupported expression type: {expression.NodeType}");
}
}
Problem is suppose I pass 4 value in age from controller GenerateSqlFromExpression method should return Age = 4 but it returns (Age = age).