//protected string GetColumnDataType(ColumnReferenceExpression value, Dictionary<NamedTableView, IDictionary<string, DataTypeView>> columnDataTypes) //{ // var columnName = value.MultiPartIdentifier.Identifiers.GetName().ToLower(); // var types = columnDataTypes.Where(t => t.Key.Name.ToLower().Contains(columnName)); // //so.... technically this could resolve to multiple columns, but I have no clue which one to pick as the column does not have any reference to the parent query. // var typ = types.FirstOrDefault(); // if (typ.Key != null) // { // //return typ.Value..DataType.; // } // return null; //} /// <summary> /// Gets the data type of the column. /// </summary> /// <param name="sqlObj">The SQL object.</param> /// <param name="query">The query.</param> /// <param name="column">The column.</param> /// <param name="model">The model.</param> /// <param name="variables">The variables.</param> /// <returns></returns> protected static string GetColumnDataType(TSqlObject sqlObj, QuerySpecification query, ColumnReferenceExpression column, TSqlModel model, IList <DataTypeView> variables) { TSqlObject referencedColumn = null; var columnName = column.MultiPartIdentifier.Identifiers.Last().Value.ToLower(); var columns = sqlObj.GetReferenced(DacQueryScopes.All).Where(x => x.ObjectType == Column.TypeClass && x.Name.GetName().ToLower().Contains($"[{columnName}]") ).Distinct().ToList(); if (columns.Count == 0) { //we have an aliased column, probably from a cte, temp table, or sub-select. we need to try to find it var visitor = new SelectScalarExpressionVisitor(); sqlObj.GetFragment().Accept(visitor); //sqlObj.GetFragment() //try to find a select column where the alias matches the column name we are searching for var selectColumns = visitor.Statements.Where(x => _comparer.Equals(x.ColumnName?.Value, columnName)).ToList(); //if we find more than one match, we have no way to determine which is the correct one. if (selectColumns.Count == 1) { return(GetDataType(sqlObj, query, selectColumns.First().Expression, variables)); } else { return(null); } } else if (columns.Count > 1) { var tablesVisitor = new TableReferenceWithAliasVisitor(); if (column.MultiPartIdentifier.Identifiers.Count > 1) { sqlObj.GetFragment().Accept(tablesVisitor); var columnTableAlias = column.MultiPartIdentifier.Identifiers.First().Value; var tbls = tablesVisitor.Statements.Where(x => _comparer.Equals(x.Alias?.Value, columnTableAlias) || _comparer.Equals(x.GetName(), $"[{columnTableAlias}]")); //if we find more than one table with the same alias, we have no idea which one it could be. if (tbls.Count() == 1) { referencedColumn = GetReferencedColumn(tbls.FirstOrDefault(), columns, columnName); } else { foreach (var tbl in tbls) { referencedColumn = GetReferencedColumn(tbl, columns, columnName); if (referencedColumn != null) { break; } } } } else { query.Accept(tablesVisitor); if (tablesVisitor.Count == 1) { referencedColumn = GetReferencedColumn(tablesVisitor.Statements.FirstOrDefault(), columns, columnName); } else { foreach (var tbl in tablesVisitor.Statements) { referencedColumn = GetReferencedColumn(tbl, columns, columnName); if (referencedColumn != null) { break; } } } } } else { referencedColumn = columns.FirstOrDefault(); } if (referencedColumn != null) { TSqlObject dataType = null; //sometimes for some reason, I have to call getreferenced multiple times to get to the datatype. nfc why.... while (dataType == null && referencedColumn != null) { var colReferenced = referencedColumn.GetReferenced(DacQueryScopes.All); dataType = colReferenced.FirstOrDefault(x => _comparer.Equals(x.ObjectType.Name, "DataType")); if (dataType == null) { //try the next? referenced column. referencedColumn = colReferenced.FirstOrDefault(x => x.ObjectType == Column.TypeClass); } else { break; } } if (dataType != null) { return(dataType.Name.Parts.First()); } } return(null); }