Sometimes when you want to manage your code efficiency you may need to optimize some .Net implementation.
Instead of relying on Entity Framework or other closed binary implementation you may need to implement your specific AdHoc behavior, this is a good example to do so…
Underneath you will find my proposal to implement a Linq to SQLLite conversion with C#. This is a generic code and may be optimized in many way for your implementation needs. It use System.Linq but remediation can be done very easily to not depend on this library.
using System.Collections;
using System.Linq.Expressions;
using System.Reflection;
namespace Com.Maximilienzakowski.Dal.Common.Repository
{
public interface ICommandTextWhereArgBuilder<T> where T : class
{
string Build(Expression<Func<T, bool>> whereExpression);
string Build(Expression node);
}
public class SqliteCommandTextWhereArgBuilder<T> : ICommandTextWhereArgBuilder<T> where T : class
{
private readonly IDictionary<ExpressionType,ISqlLiteWhereCommandExpressionBuilder> _whereExpressionsBuilders;
private readonly IObjectToSqlValueConverter _objectToSqlValueConverter = new ObjectToSqlValueConverter();
public SqliteCommandTextWhereArgBuilder()
{
var sqlLiteWhereCommandExpressionBuilders = new ISqlLiteWhereCommandExpressionBuilder[]
{
new SqlLiteLambdaWhereCommandExpressionBuilder<T>(this),
new SqlLiteAndWhereCommandExpressionBuilder<T>(this),
new SqlLiteAndAlsoWhereCommandExpressionBuilder<T>(this),
new SqlLiteEqualWhereCommandExpressionBuilder<T>(this),
new SqlLiteContainsCallWhereCommandExpressionBuilder<T>(this),
new SqliteConvertAccessWhereCommandExpressionBuilder<T>(this),
new SqliteMemberAccessWhereCommandExpressionBuilder<T>(_objectToSqlValueConverter),
new SqliteConstantWhereCommandExpressionBuilder<T>(_objectToSqlValueConverter),
};
_whereExpressionsBuilders = sqlLiteWhereCommandExpressionBuilders.ToDictionary(m => m.ExpressionType, m => m);
}
public string Build(Expression<Func<T, bool>> whereExpression)
{
var expression = whereExpression.Reduce;
return Build(expression());
}
public string Build(Expression node)
{
if(!_whereExpressionsBuilders.TryGetValue(node.NodeType, out var whereExpressionBuilder)) return "UNDEFINED EXPRESSION BUILDER";
return whereExpressionBuilder.Build(node);
}
}
public class SqlLiteContainsCallWhereCommandExpressionBuilder<T> : ISqlLiteWhereCommandExpressionBuilder where T : class
{
private readonly ICommandTextWhereArgBuilder<T> _commandTextWhereArgBuilder;
public ExpressionType ExpressionType => ExpressionType.Call;
public SqlLiteContainsCallWhereCommandExpressionBuilder(ICommandTextWhereArgBuilder<T> commandTextWhereArgBuilder)
{
_commandTextWhereArgBuilder = commandTextWhereArgBuilder ?? throw new ArgumentNullException(nameof(commandTextWhereArgBuilder));
}
public string Build(Expression? node)
{
if (node.NodeType != ExpressionType.Call) return string.Empty;
var methodCallNode = (MethodCallExpression)node;
if (methodCallNode.Method.Name != "Contains") return "UNHANDLED METHOD CALL";
if(methodCallNode.Arguments.Count == 2)
{
return $"({ _commandTextWhereArgBuilder.Build(methodCallNode.Arguments.Last())} IN {_commandTextWhereArgBuilder.Build(methodCallNode.Arguments.First())})";
}
else if(methodCallNode.Arguments.Count == 1)
{
var left = _commandTextWhereArgBuilder.Build(methodCallNode.Arguments.Single());
return $"({left} IN {_commandTextWhereArgBuilder.Build(methodCallNode.Object)})";
}
return "UNHANDLED CONTAINS METHOD CALL";
}
}
public class SqlLiteLambdaWhereCommandExpressionBuilder<T> : ISqlLiteWhereCommandExpressionBuilder where T : class
{
private readonly ICommandTextWhereArgBuilder<T> _commandTextWhereArgBuilder;
public ExpressionType ExpressionType => ExpressionType.Lambda;
public SqlLiteLambdaWhereCommandExpressionBuilder(ICommandTextWhereArgBuilder<T> commandTextWhereArgBuilder)
{
_commandTextWhereArgBuilder = commandTextWhereArgBuilder ?? throw new ArgumentNullException(nameof(commandTextWhereArgBuilder));
}
public string Build(Expression? node)
{
if(node.NodeType != ExpressionType.Lambda) return string.Empty;
var lambdaNode = (LambdaExpression)node;
return _commandTextWhereArgBuilder.Build(lambdaNode.Body);
}
}
public class SqlLiteAndWhereCommandExpressionBuilder<T> : ISqlLiteWhereCommandExpressionBuilder where T : class
{
private readonly ICommandTextWhereArgBuilder<T> _commandTextWhereArgBuilder;
public ExpressionType ExpressionType => ExpressionType.And;
public SqlLiteAndWhereCommandExpressionBuilder(ICommandTextWhereArgBuilder<T> commandTextWhereArgBuilder)
{
_commandTextWhereArgBuilder = commandTextWhereArgBuilder ?? throw new ArgumentNullException(nameof(commandTextWhereArgBuilder));
}
public string Build(Expression? node)
{
if (node.NodeType != ExpressionType) return string.Empty;
var binaryExpressionType = (BinaryExpression)node;
return $"({_commandTextWhereArgBuilder.Build(binaryExpressionType.Left)}) AND ({_commandTextWhereArgBuilder.Build(binaryExpressionType.Right)})";
}
}
public class SqlLiteAndAlsoWhereCommandExpressionBuilder<T> : ISqlLiteWhereCommandExpressionBuilder where T : class
{
private readonly ICommandTextWhereArgBuilder<T> _commandTextWhereArgBuilder;
public ExpressionType ExpressionType => ExpressionType.AndAlso;
public SqlLiteAndAlsoWhereCommandExpressionBuilder(ICommandTextWhereArgBuilder<T> commandTextWhereArgBuilder)
{
_commandTextWhereArgBuilder = commandTextWhereArgBuilder ?? throw new ArgumentNullException(nameof(commandTextWhereArgBuilder));
}
public string Build(Expression? node)
{
if (node.NodeType != ExpressionType) return string.Empty;
var binaryExpressionType = (BinaryExpression)node;
return $"({_commandTextWhereArgBuilder.Build(binaryExpressionType.Left)}) AND ({_commandTextWhereArgBuilder.Build(binaryExpressionType.Right)})";
}
}
public class SqlLiteEqualWhereCommandExpressionBuilder<T> : ISqlLiteWhereCommandExpressionBuilder where T : class
{
private readonly ICommandTextWhereArgBuilder<T> _commandTextWhereArgBuilder;
public ExpressionType ExpressionType => ExpressionType.Equal;
public SqlLiteEqualWhereCommandExpressionBuilder(ICommandTextWhereArgBuilder<T> commandTextWhereArgBuilder)
{
_commandTextWhereArgBuilder = commandTextWhereArgBuilder ?? throw new ArgumentNullException(nameof(commandTextWhereArgBuilder));
}
public string Build(Expression? node)
{
if (node.NodeType != ExpressionType) return string.Empty;
var binaryExpressionType = (BinaryExpression)node;
return $"{_commandTextWhereArgBuilder.Build(binaryExpressionType.Left)} = {_commandTextWhereArgBuilder.Build(binaryExpressionType.Right)}";
}
}
public class SqliteMemberAccessWhereCommandExpressionBuilder<T> : ISqlLiteWhereCommandExpressionBuilder where T : class
{
private readonly IObjectToSqlValueConverter _objectToSqlValueConverter;
public ExpressionType ExpressionType => ExpressionType.MemberAccess;
public SqliteMemberAccessWhereCommandExpressionBuilder(IObjectToSqlValueConverter objectToSqlValueConverter)
{
_objectToSqlValueConverter = objectToSqlValueConverter ?? throw new ArgumentNullException(nameof(objectToSqlValueConverter));
}
public string Build(Expression? node)
{
if (node.NodeType != ExpressionType) return string.Empty;
var memberExpression = (MemberExpression)node;
if(memberExpression.Member.MemberType == MemberTypes.Field)
{
if(memberExpression.Expression == null) return "NULL UNDERLYING EXPRESSION IN MEMBER EXPRESSION";
if (memberExpression.Expression.NodeType != ExpressionType.Constant) return "NOT A CONSTANT EXPRESSION NODE TYPE IN MEMBER EXPRESSION";
var constantExpression = (ConstantExpression)memberExpression.Expression;
var fieldInfo = (FieldInfo)memberExpression.Member;
return $"\"{_objectToSqlValueConverter.Convert(fieldInfo.GetValue(constantExpression.Value))}\"";
}
return $"\"{memberExpression?.Member?.Name}\"";
}
}
public class SqliteConvertAccessWhereCommandExpressionBuilder<T> : ISqlLiteWhereCommandExpressionBuilder where T : class
{
private readonly ICommandTextWhereArgBuilder<T> _commandTextWhereArgBuilder;
public ExpressionType ExpressionType => ExpressionType.Convert;
public SqliteConvertAccessWhereCommandExpressionBuilder(ICommandTextWhereArgBuilder<T> commandTextWhereArgBuilder)
{
_commandTextWhereArgBuilder = commandTextWhereArgBuilder ?? throw new ArgumentNullException(nameof(commandTextWhereArgBuilder));
}
private static readonly IDictionary<Tuple<Type, Type>, string> _formatBySourceAndDestinationTupleTypes =
new Dictionary<Tuple<Type, Type>, string>()
{
{Tuple.Create(typeof(int),typeof(long)), "{0}" },
};
public string Build(Expression? node)
{
if (node.NodeType != ExpressionType) return string.Empty;
var unaryExpression = (UnaryExpression)node;
if (!_formatBySourceAndDestinationTupleTypes.TryGetValue(Tuple.Create(unaryExpression.Operand.Type, unaryExpression.Type), out var format))
return "UNKNONW CONVERT EXPRESSION";
return string.Format(format, _commandTextWhereArgBuilder.Build(unaryExpression.Operand));
}
}
public class SqliteConstantWhereCommandExpressionBuilder<T> : ISqlLiteWhereCommandExpressionBuilder where T : class
{
private readonly IObjectToSqlValueConverter _objectToSqlValueConverter1;
public ExpressionType ExpressionType => ExpressionType.Constant;
public SqliteConstantWhereCommandExpressionBuilder(IObjectToSqlValueConverter objectToSalValueConverter)
{
_objectToSqlValueConverter1 = objectToSalValueConverter ?? throw new ArgumentNullException(nameof(objectToSalValueConverter));
}
public string Build(Expression? node)
{
if (node.NodeType != ExpressionType) return string.Empty;
var constantExpression = (ConstantExpression)node;
return _objectToSqlValueConverter1.Convert(constantExpression.Value);
}
}
public class ObjectToSqlValueConverter : IObjectToSqlValueConverter
{
private readonly IDictionary<Type, Func<object, string>> _valueConversionFuncByType =
new Dictionary<Type, Func<object, string>>()
{
{typeof(string), value => $"'{value}'"},
{typeof(int), value => $"{value}"},
{typeof(long), value => $"{value}"},
{typeof(short), value => $"{value}"},
{typeof(float), value => $"{value}"},
{typeof(double), value => $"{value}"},
{typeof(decimal), value => $"{value}"},
};
public string Convert(object? value)
{
if (value == null) return "NULL";
var valueType = value.GetType();
if (valueType.IsGenericType && valueType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
var underlyingValueType = Nullable.GetUnderlyingType(valueType);
if (underlyingValueType == null) return "UNKNOWN CASE OF NULLABLE UNDERLYING NULL VALUE";
return Convert(System.Convert.ChangeType(value, underlyingValueType));
}
if((valueType.IsArray || valueType.IsGenericType && valueType.GetGenericTypeDefinition().IsAssignableTo(typeof(IEnumerable))))
{
var enumerable = (IEnumerable)value;
return $"({string.Join(",", Enumerate(enumerable).Select(Convert))})";
}
if (!_valueConversionFuncByType.TryGetValue(valueType, out var conversionFunc)) return "UNKNOWN TYPE";
return conversionFunc(value);
}
private IEnumerable<object> Enumerate(IEnumerable enumerable)
{
foreach(var value in enumerable)
{
yield return value;
}
}
}
}