private FetchXml.FetchType CloneFetchXml(FetchXml.FetchType fetchXml)
{
var serializer = new XmlSerializer(typeof(FetchXml.FetchType));
using (var writer = new StringWriter())
{
serializer.Serialize(writer, fetchXml);
using (var reader = new StringReader(writer.ToString()))
{
return (FetchXml.FetchType)serializer.Deserialize(reader);
}
}
}
/// <summary>
/// Converts a FetchXML query to SQL
/// </summary>
/// <param name="metadata">The metadata cache to use for the conversion</param>
/// <param name="fetch">The query object to convert</param>
/// <returns>The converted SQL query</returns>
public static string Convert(IAttributeMetadataCache metadata, FetchXml.FetchType fetch)
{
var select = new SelectStatement();
var query = new QuerySpecification();
select.QueryExpression = query;
if (fetch.top != null)
{
query.TopRowFilter = new TopRowFilter {
Expression = new IntegerLiteral {
Value = fetch.top
}
}
}
;
if (fetch.distinct)
{
query.UniqueRowFilter = UniqueRowFilter.Distinct;
}
// SELECT (columns from first table)
var entity = fetch.Items.OfType <FetchEntityType>().SingleOrDefault();
AddSelectElements(query, entity.Items, entity?.name);
// FROM
var aliasToLogicalName = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
if (entity != null)
{
query.FromClause = new FromClause
{
TableReferences =
{
new NamedTableReference
{
SchemaObject = new SchemaObjectName
{
Identifiers =
{
new Identifier {
Value = entity.name
}
}
}
}
}
};
if (fetch.nolock)
{
((NamedTableReference)query.FromClause.TableReferences[0]).TableHints.Add(new TableHint {
HintKind = TableHintKind.NoLock
});
}
// Recurse into link-entities to build joins
query.FromClause.TableReferences[0] = BuildJoins(metadata, query.FromClause.TableReferences[0], (NamedTableReference)query.FromClause.TableReferences[0], entity.Items, query, aliasToLogicalName, fetch.nolock);
}
// OFFSET
if (!String.IsNullOrEmpty(fetch.page) && fetch.page != "1")
{
var page = Int32.Parse(fetch.page);
var pageSize = Int32.Parse(fetch.count);
query.OffsetClause = new OffsetClause
{
OffsetExpression = new IntegerLiteral {
Value = ((page - 1) * pageSize).ToString()
},
FetchExpression = new IntegerLiteral {
Value = fetch.count
}
};
}
// WHERE
var filter = GetFilter(metadata, entity.Items, entity.name, aliasToLogicalName);
if (filter != null)
{
query.WhereClause = new WhereClause
{
SearchCondition = filter
};
}
// ORDER BY
AddOrderBy(entity.name, entity.Items, query);
// For single-table queries, don't bother qualifying the column names to make the query easier to read
if (query.FromClause.TableReferences[0] is NamedTableReference)
{
select.Accept(new SimplifyMultiPartIdentifierVisitor(entity.name));
}
// Check whether each identifier needs to be quoted so we have minimal quoting to make the query easier to read
select.Accept(new QuoteIdentifiersVisitor());
new Sql150ScriptGenerator().GenerateScript(select, out var sql);
return(sql);
}
