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); }