private void VisitReferenceTypeColumn(ColumnReferenceExpression node, TSqlFragment parent, string sourceProperty, IList <Identifier> identifiers, Identifier identifier, Property property, string fieldName)
{
Field field = null;
BinaryLiteral binaryLiteral = null;
if (fieldName == "uuid")
{
if (property.Fields.Count == 1)
{
field = property.Fields[0];
}
else
{
field = property.Fields.Where(f => f.Purpose == FieldPurpose.Object).FirstOrDefault();
}
}
else if (fieldName == "type")
{
if (property.Fields.Count == 1)
{
string HexTypeCode = $"0x{property.PropertyTypes[0].ToString("X").PadLeft(8, '0')}";
binaryLiteral = new BinaryLiteral()
{
Value = HexTypeCode
};
}
else
{
field = property.Fields.Where(f => f.Purpose == FieldPurpose.TypeCode).FirstOrDefault();
}
}
else if (fieldName == "TYPE")
{
if (property.Fields.Count == 1)
{
binaryLiteral = new BinaryLiteral()
{
Value = "0x08"
};
}
else
{
field = property.Fields.Where(f => f.Purpose == FieldPurpose.Discriminator).FirstOrDefault();
}
}
// TODO: throw new MissingMemberException !? if nonexistent field referenced
if (binaryLiteral == null)
{
identifier.Value = field.Name; // change object property identifier to SQL table column identifier
identifiers.RemoveAt(identifiers.Count - 1); // uuid | type | TYPE
}
else
{
TransformToBinaryLiteral(node, parent, sourceProperty, binaryLiteral);
}
}
public override void ExplicitVisit(BinaryLiteral node)
{
if (node.Value.StartsWith("0x") && node.Value.Length <= 10)
{
var value = Convert.ToUInt32(node.Value, 16); //Using ToUInt32 not ToUInt64, as per OP comment
lastExpression = new cs.CodePrimitiveExpression(value);
}
else
{
throw new NotImplementedException(node.AsText());
}
}
private void Transform(TSqlFragment parent, string sourceProperty, FunctionCall functionCall, int typeCode)
{
string HexTypeCode = $"0x{typeCode.ToString("X").PadLeft(8, '0')}";
BinaryLiteral binaryLiteral = new BinaryLiteral()
{
Value = HexTypeCode
};
PropertyInfo pi = parent.GetType().GetProperty(sourceProperty);
bool isList = (pi.PropertyType.IsGenericType && pi.PropertyType.GetGenericTypeDefinition() == typeof(IList <>));
if (isList)
{
IList list = (IList)pi.GetValue(parent);
int index = list.IndexOf(functionCall);
list[index] = binaryLiteral;
}
else
{
pi.SetValue(parent, binaryLiteral);
}
}
private void CheckRewriteable(BooleanExpression search)
{
var bce = search as BooleanComparisonExpression;
var haveIsNull = false;
var haveLiteral = false;
Literal literal = new BinaryLiteral();
var isNull = new FunctionCall();
if (bce.FirstExpression is FunctionCall)
{
var func = bce.FirstExpression as FunctionCall;
if (func.FunctionName.Value.ToLowerInvariant() == "isnull")
{
haveIsNull = true;
isNull = func;
}
}
if (bce.SecondExpression is FunctionCall)
{
var func = bce.SecondExpression as FunctionCall;
if (func.FunctionName.Value.ToLowerInvariant() == "isnull")
{
haveIsNull = true;
isNull = func;
}
}
if (bce.FirstExpression is Literal)
{
haveLiteral = true;
literal = bce.FirstExpression as Literal;
}
if (bce.SecondExpression is Literal)
{
haveLiteral = true;
literal = bce.SecondExpression as Literal;
}
if (haveLiteral && haveIsNull)
{
var firstParam = isNull.Parameters.FirstOrDefault();
if (!(firstParam is ColumnReferenceExpression))
{
return;
}
var secondParam = isNull.Parameters.LastOrDefault();
if (secondParam is Literal)
{
if (secondParam.GetType() != literal.GetType())
{
return;
}
var isSameLiteral = (secondParam as Literal).Value == literal.Value;
var isEquals = bce.ComparisonType == BooleanComparisonType.Equals;
if (isEquals && isSameLiteral)
{
BuildEqualsSameLiteral(search, firstParam, literal);
return;
}
if (!isEquals && !isSameLiteral)
{
BuildNotEqualsNotSameLiteral(search, firstParam, literal);
return;
}
if (!isEquals && isSameLiteral)
{
BuildNotEqualsIsSameLiteral(search, firstParam, literal);
return;
}
if (isEquals && !isSameLiteral)
{
BuildIsEqualsIsNotSameLiteral(search, firstParam, literal);
}
}
}
}
public override void Visit(BinaryLiteral node) { this.action(node); }
public override void ExplicitVisit(BinaryLiteral node)
{
node.Value = "0xABCD";
base.ExplicitVisit(node);
}
public override void ExplicitVisit(BinaryLiteral fragment)
{
_fragments.Add(fragment);
}
public override void ExplicitVisit(BinaryLiteral node)
{
base.ExplicitVisit(node);
}
public void visit(BinaryLiteral n)
{
throw new NotImplementedException();
}
private void CheckRewriteable(BooleanExpression search)
{
var bce = search as BooleanComparisonExpression;
var haveIsNull = false;
var haveLiteral = false;
Literal literal = new BinaryLiteral();
var isNull = new FunctionCall();
if (bce.FirstExpression is FunctionCall)
{
var func = bce.FirstExpression as FunctionCall;
if (func.FunctionName.Value.ToLower() == "isnull")
{
haveIsNull = true;
isNull = func;
}
}
if (bce.SecondExpression is FunctionCall)
{
var func = bce.SecondExpression as FunctionCall;
if (func.FunctionName.Value.ToLower() == "isnull")
{
haveIsNull = true;
isNull = func;
}
}
if (bce.FirstExpression is Literal)
{
haveLiteral = true;
literal = bce.FirstExpression as Literal;
}
if (bce.SecondExpression is Literal)
{
haveLiteral = true;
literal = bce.SecondExpression as Literal;
}
if (haveLiteral && haveIsNull)
{
var firstParam = isNull.Parameters.FirstOrDefault();
if (!(firstParam is ColumnReferenceExpression))
{
return;
}
var secondParam = isNull.Parameters.LastOrDefault();
if (secondParam is Literal)
{
if (secondParam.GetType() != literal.GetType())
{
return;
}
var isSameLiteral = (secondParam as Literal).Value == literal.Value;
var isEquals = bce.ComparisonType == BooleanComparisonType.Equals;
if (isEquals && isSameLiteral)
{
BuildEqualsSameLiteral(search, firstParam, literal);
return;
}
if (!isEquals && !isSameLiteral)
{
BuildNotEqualsNotSameLiteral(search, firstParam, literal);
return;
}
if (!isEquals && isSameLiteral)
{
BuildNotEqualsIsSameLiteral(search, firstParam, literal);
return;
}
if (isEquals && !isSameLiteral)
{
BuildIsEqualsIsNotSameLiteral(search, firstParam, literal);
}
}
}
}
public override void visit(BinaryLiteral n)
{
n.Scope = Scope;
}
public virtual void VisitBinaryLiteral(BinaryLiteral binaryLiteral)
{
DefaultVisit(binaryLiteral);
}
public ASTType visit(BinaryLiteral n)
{
return new BinaryType();
}
/// <summary>
/// Converts a FetchXML <condition> to a SQL condition
/// </summary>
/// <param name="metadata">The metadata cache to use for the conversion</param>
/// <param name="condition">The FetchXML condition to convert</param>
/// <param name="prefix">The alias or name of the table that the <paramref name="condition"/> applies to</param>
/// <param name="aliasToLogicalName">The mapping of table alias to logical name</param>
/// <returns>The SQL condition equivalent of the <paramref name="condition"/></returns>
private static BooleanExpression GetCondition(IAttributeMetadataCache metadata, condition condition, string prefix, IDictionary <string, string> aliasToLogicalName)
{
// Start with the field reference
var field = new ColumnReferenceExpression
{
MultiPartIdentifier = new MultiPartIdentifier
{
Identifiers =
{
new Identifier {
Value = condition.entityname ?? prefix
},
new Identifier {
Value = condition.attribute
}
}
}
};
// Get the metadata for the attribute
BooleanComparisonType type;
ScalarExpression value;
if (!aliasToLogicalName.TryGetValue(condition.entityname ?? prefix, out var logicalName))
{
logicalName = condition.entityname ?? prefix;
}
var meta = metadata[logicalName];
var attr = meta.Attributes.SingleOrDefault(a => a.LogicalName == condition.attribute);
// Get the literal value to compare to
if (attr == null)
{
value = new StringLiteral {
Value = condition.value
}
}
;
else if (attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.BigInt ||
attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Integer ||
attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Picklist ||
attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.State ||
attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Status)
{
value = new IntegerLiteral {
Value = condition.value
}
}
;
else if (attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Boolean)
{
value = new BinaryLiteral {
Value = condition.value
}
}
;
else if (attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Decimal ||
attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Double)
{
value = new NumericLiteral {
Value = condition.value
}
}
;
else if (attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Money)
{
value = new MoneyLiteral {
Value = condition.value
}
}
;
else if (attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Lookup ||
attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Owner ||
attr.AttributeType == Microsoft.Xrm.Sdk.Metadata.AttributeTypeCode.Customer)
{
value = new IdentifierLiteral {
Value = condition.value
}
}
;
else
{
value = new StringLiteral {
Value = condition.value
}
};
// Apply the appropriate conversion for the type of operator
switch (condition.@operator)
{
case @operator.above:
case @operator.containvalues:
case @operator.eqbusinessid:
case @operator.eqorabove:
case @operator.eqorunder:
case @operator.equserid:
case @operator.equserlanguage:
case @operator.equseroruserhierarchy:
case @operator.equseroruserhierarchyandteams:
case @operator.equseroruserteams:
case @operator.equserteams:
case @operator.infiscalperiod:
case @operator.infiscalperiodandyear:
case @operator.infiscalyear:
case @operator.inorafterfiscalperiodandyear:
case @operator.inorbeforefiscalperiodandyear:
case @operator.lastfiscalperiod:
case @operator.lastfiscalyear:
case @operator.lastmonth:
case @operator.lastsevendays:
case @operator.lastweek:
case @operator.lastxdays:
case @operator.lastxfiscalperiods:
case @operator.lastxfiscalyears:
case @operator.lastxhours:
case @operator.lastxmonths:
case @operator.lastxweeks:
case @operator.lastxyears:
case @operator.lastyear:
case @operator.nebusinessid:
case @operator.neuserid:
case @operator.nextfiscalperiod:
case @operator.nextfiscalyear:
case @operator.nextmonth:
case @operator.nextsevendays:
case @operator.nextweek:
case @operator.nextxdays:
case @operator.nextxfiscalperiods:
case @operator.nextxfiscalyears:
case @operator.nextxhours:
case @operator.nextxmonths:
case @operator.nextxweeks:
case @operator.nextxyears:
case @operator.nextyear:
case @operator.notcontainvalues:
case @operator.notunder:
case @operator.olderthanxdays:
case @operator.olderthanxhours:
case @operator.olderthanxminutes:
case @operator.olderthanxmonths:
case @operator.olderthanxweeks:
case @operator.olderthanxyears:
case @operator.on:
case @operator.onorafter:
case @operator.onorbefore:
case @operator.thisfiscalperiod:
case @operator.thisfiscalyear:
case @operator.thismonth:
case @operator.thisweek:
case @operator.thisyear:
case @operator.today:
case @operator.tomorrow:
case @operator.under:
case @operator.yesterday:
// These FetchXML operators don't have a direct SQL equivalent, so convert to the format
// field = function(arg)
// so <condition attribute="createdon" operator="lastxdays" value="2" /> will be converted to
// createdon = lastxdays(2)
type = BooleanComparisonType.Equals;
value = new FunctionCall {
FunctionName = new Identifier {
Value = [email protected]()
}
};
if (condition.value != null)
{
((FunctionCall)value).Parameters.Add(new StringLiteral {
Value = condition.value
});
}
break;
case @operator.beginswith:
case @operator.notbeginwith:
return(new LikePredicate {
FirstExpression = field, SecondExpression = new StringLiteral {
Value = condition.value + "%"
}, NotDefined = condition.@operator == @operator.notbeginwith
});
case @operator.between:
case @operator.notbetween:
return(new BooleanTernaryExpression {
FirstExpression = field, TernaryExpressionType = condition.@operator == @operator.between ? BooleanTernaryExpressionType.Between : BooleanTernaryExpressionType.NotBetween, SecondExpression = new StringLiteral {
Value = condition.Items[0].Value
}, ThirdExpression = new StringLiteral {
Value = condition.Items[1].Value
}
});
case @operator.endswith:
case @operator.notendwith:
return(new LikePredicate {
FirstExpression = field, SecondExpression = new StringLiteral {
Value = "%" + condition.value
}, NotDefined = condition.@operator == @operator.notendwith
});
case @operator.eq:
type = BooleanComparisonType.Equals;
break;
case @operator.ge:
type = BooleanComparisonType.GreaterThanOrEqualTo;
break;
case @operator.gt:
type = BooleanComparisonType.GreaterThan;
break;
case @operator.@in:
case @operator.notin:
var @in = new InPredicate {
Expression = field, NotDefined = condition.@operator == @operator.notin
};
foreach (var val in condition.Items)
{
@in.Values.Add(new StringLiteral {
Value = val.Value
});
}
return(@in);
case @operator.le:
type = BooleanComparisonType.LessThanOrEqualTo;
break;
case @operator.like:
case @operator.notlike:
return(new LikePredicate {
FirstExpression = field, SecondExpression = new StringLiteral {
Value = condition.value
}, NotDefined = condition.@operator == @operator.notlike
});
case @operator.lt:
type = BooleanComparisonType.LessThan;
break;
case @operator.ne:
case @operator.neq:
type = BooleanComparisonType.NotEqualToBrackets;
break;
case @operator.@null:
case @operator.notnull:
return(new BooleanIsNullExpression {
Expression = field, IsNot = condition.@operator == @operator.notnull
});
default:
throw new NotImplementedException();
}
var expression = new BooleanComparisonExpression
{
FirstExpression = field,
ComparisonType = type,
SecondExpression = value
};
return(expression);
}