Skip to content

Instantly share code, notes, and snippets.

@d1820
Last active December 29, 2023 17:57
Show Gist options
  • Save d1820/d87ee11dd01ba045ecfe980775ed1714 to your computer and use it in GitHub Desktop.
Save d1820/d87ee11dd01ba045ecfe980775ed1714 to your computer and use it in GitHub Desktop.
Convert Linq Where expressions to user friendly string
foreach (var item in spec.WhereExpressions)
{
var sqlVisitor = new ExportsAPI.Domain.Visitors.SqlSearchExpressionVisitor();
string sqlString = sqlVisitor.VisitExpression(item.Filter, test);
sql.Add(sqlString);
}
SQL list:
[0]: "(Assigned.UserName = 'Awesome')"
[1]: "(Id = 'fb9244e7-72d5-4842-95a2-7b44af136a9c')"
[2]: "(ClientId = 'fb9244e7-72d5-4842-95a2-7b44af136a9c')"
[3]: "((CreatedBy.UserName LIKE 'Dan') OR (ModifiedBy.UserName LIKE 'Dan'))"
[4]: "((CreatedTimeUtc.Value >= 12/14/2023 12:00:00 AM))"
[5]: "(LastUpdatedTimeRaw >= 1703635200)"
[6]: "(Vessel.CarrierSCAC LIKE 'scac')"
[7]: "(NominationInfo.Ref LIKE '123')"
[8]: "(NominationInfo.MOT = 'Sea')"
[9]: "(NominationInfo.Code = 'MyCode')"
[10]: "(State = Initialized)"
[11]: "(((ReceivedAt.Value >= 12/20/2023 12:00:00 AM) AND (ReceivedAt.Value <= 12/20/2023 11:59:59 PM)))"
[12]: "('daysOpen' > 1)"
Final Output as String:
"(Assigned.UserName = 'Awesome')
AND (Id = 'fb9244e7-72d5-4842-95a2-7b44af136a9c')
AND (ClientId = 'fb9244e7-72d5-4842-95a2-7b44af136a9c')
AND ((CreatedBy.UserName LIKE 'Dan') OR (ModifiedBy.UserName LIKE 'Dan'))
AND ((CreatedTimeUtc.Value >= 12/14/2023 12:00:00 AM))
AND (LastUpdatedTimeRaw >= 1703635200)
AND (Vessel.CarrierSCAC LIKE 'scac')
AND (NominationInfo.Ref LIKE '123') A
ND (NominationInfo.MOT = 'Sea')
AND (NominationInfo.Code = 'MyCode')
AND (State = Initialized)
AND (((ReceivedAt.Value >= 12/20/2023 12:00:00 AM) AND (ReceivedAt.Value <= 12/20/2023 11:59:59 PM)))
AND ('daysOpen' > 1)"
public class SqlExpressionVisitor : ExpressionVisitor
{
private StringBuilder _sqlBuilder = new();
private object _searchObject;
public string VisitExpression(Expression expression, object searchObject)
{
_searchObject = searchObject;
Visit(expression);
return _sqlBuilder.ToString();
}
protected override Expression VisitBinary(BinaryExpression node)
{
_sqlBuilder.Append('(');
var leftNode = node.Left.ToString();
if (!leftNode.Contains("HasValue"))
{
Visit(node.Left);
switch (node.NodeType)
{
case ExpressionType.Equal:
_sqlBuilder.Append(" = ");
break;
case ExpressionType.NotEqual:
_sqlBuilder.Append(" <> ");
break;
case ExpressionType.LessThanOrEqual:
_sqlBuilder.Append(" <= ");
break;
case ExpressionType.LessThan:
_sqlBuilder.Append(" < ");
break;
case ExpressionType.GreaterThan:
_sqlBuilder.Append(" > ");
break;
case ExpressionType.GreaterThanOrEqual:
_sqlBuilder.Append(" >= ");
break;
case ExpressionType.OrElse:
_sqlBuilder.Append(" OR ");
break;
case ExpressionType.AndAlso:
_sqlBuilder.Append(" AND ");
break;
//Add more cases as needed
default:
throw new NotSupportedException($"Unsupported binary expression type: {node.NodeType}");
}
}
Visit(node.Right);
_sqlBuilder.Append(')');
return node;
}
protected override Expression VisitConstant(ConstantExpression node)
{
var value = node.Value;
if (value == null)
{
_sqlBuilder.Append("NULL");
}
else
{
AppendWithProperEncoding(value);
}
return node;
}
private void AppendWithProperEncoding(object? value)
{
switch (Type.GetTypeCode(value?.GetType()))
{
case TypeCode.String:
case TypeCode.DateTime:
_sqlBuilder.Append($"'{value}'");
break;
case TypeCode.Object:
break;
default:
_sqlBuilder.Append(value);
break;
}
}
// Add the following method to the SqlExpressionVisitor class
protected override Expression VisitMethodCall(MethodCallExpression node)
{
switch (node.Method.Name)
{
case "Contains" when node.Object != null && node.Arguments.Count >= 1:
_sqlBuilder.Append('(');
Visit(node.Object);
_sqlBuilder.Append(" LIKE ");
Visit(node.Arguments[0]);
_sqlBuilder.Append(')');
break;
case "GetCreatedDateTimeToCheck" when node.Object != null:
ExecuteMethod("GetCreatedDateTimeToCheck");
break;
case "GetUpdatedRawTimeToCheck" when node.Object != null:
ExecuteMethod("GetUpdatedRawTimeToCheck");
break;
case "GetReceivedAtStartDateTimeToCheck" when node.Object != null:
ExecuteMethod("GetReceivedAtStartDateTimeToCheck");
break;
case "GetReceivedAtEndDateTimeToCheck" when node.Object != null:
ExecuteMethod("GetReceivedAtEndDateTimeToCheck");
break;
default:
// Handle other method calls if needed
base.VisitMethodCall(node);
break;
}
return node;
}
private void ExecuteMethod(string methodName)
{
var methodInfo = _searchObject.GetType().GetMethod(methodName);
if (methodInfo != null)
{
var result = methodInfo.Invoke(_searchObject, null);
_sqlBuilder.Append(result);
}
else
{
_sqlBuilder.Append("[Unable to calculate]");
}
}
protected override Expression VisitMember(MemberExpression node)
{
var exp = node.Expression?.ToString();
if (exp?.StartsWith("s") == true || exp?.StartsWith("s.") == true) //this is the left side
{
var checkNode = node;
if (checkNode.NodeType == ExpressionType.MemberAccess)
{
var leftName = new List<string>();
while (checkNode != null && checkNode.NodeType == ExpressionType.MemberAccess)
{
leftName.Add(checkNode.Member.Name);
checkNode = checkNode.Expression as MemberExpression;
}
leftName.Reverse();
_sqlBuilder.Append(string.Join(".", leftName));
}
else if (checkNode.NodeType == ExpressionType.Parameter)
{
_sqlBuilder.Append(checkNode.Member.Name);
}
return node;
}
var val = TryGetValue(node);
AppendWithProperEncoding(val ?? "unknown");
return node;
}
private object? TryGetValue(MemberExpression node)
{
try
{
var po = node.Member as PropertyInfo;
var val = po?.GetValue(_searchObject);
return val;
}
catch
{
return default;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment