How to differentiate duplicate column names from different source tables/subqueries by alias in an SQL select statement when using SqlDataReader?

Question

Suppose I have POCO entities being read from a database and they each have "ID" as their primary key column name.

If selecting from more than one table or subquery with aliases a and b like select a.*, b.* from a, b, then the selected columns will include two ID columns (a.ID and b.ID), but the source table/subquery aliases are lost. I want to know if there's a way to preserve or dynamically obtain such a top-level alias for the source of the output columns of an arbitrary select query.

I know I can change the output column names or access them by ordinal, but I need to access the columns by their original name + the alias of the source table/subquery, such as "a.ID" and "b.ID".

SqlDataReader does not seem to preserve base table information for the columns by default, and never preserves alias information.

Is there a way to differentiate these columns in the SqlDataReader or get it to preserve the source table/subquery aliases for each column?

A similar question was asked here: Differentiating between 2 SQL column names with the same name in a C# SqlConnection, but the answer is to give the columns unique aliases, which is not what I want. I want to be able to use an alias for the table, while preserving the original column names.

Asked here: How to get table name of a column from SqlDataReader and answered here: https://stackoverflow.com/a/3111208/88409, but they mention that it won't return table aliases, only the original base table, which is useless for subqueries with aliases.

UPDATE: Seems it cannot be done. One of the questions linked to a question saying it was unanswered, but it was actually answered: Getting table schema from a query Seems that aliases are like "source code" for a query, and they are stripped out during the compilation/optimization process so that only real table info is left in the end.

Triynko · Answer 1 · 2013-10-23T09:00:36.580

The other guy was wrong as I suspected, so I'm going to answer my own question.

In SQL Server 2012 (and probably earlier versions), I found that if I call 'set showplan_xml on;' on an arbitrary query like:

select * from (select * from Lessons a) a inner join (select * from LessonTypes b) b on a.LessonTypeID = b.ID;

The returned XML includes the following as its first/top-level OutputList, which clearly shows all columns, and not only their source tables but the alias of their source subquery.

<OutputList>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[a]" Column="ID"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[a]" Column="Name"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[a]" Column="Description"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[a]" Column="Enabled"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[a]" Column="LessonTypeID"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[LessonTypes]" Alias="[b]" Column="ID"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[LessonTypes]" Alias="[b]" Column="Name"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[LessonTypes]" Alias="[b]" Column="Description"/>
 </OutputList>

So, in my database API, since I store all queries strings neatly in a dictionary, I can actually perform a cache warm-up at application start that runs them all through with xml_showplan on, parse the XMLs top-level output columns, and then have a complete mapping of table alias and column names to their output ordinal.

Actually, I've used a little trick here. It's not actually the alias of the subquery, but the alias of the base table. If you do not include an alias on the base table, then the Alias attribute is not present in the XML. However, it is very simple to make sure that every time you reference an actual table, you give it an alias, that should cause it to be output to the XML, and there you go.

Just to drive the point home, the aliases stick, even when joining the table to itself in a query such as:

select * from Lessons a, Lessons b, Lessons c, which produces:

<OutputList>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[a]" Column="ID"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[a]" Column="Name"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[a]" Column="Description"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[a]" Column="Enabled"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[a]" Column="LessonTypeID"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[b]" Column="ID"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[b]" Column="Name"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[b]" Column="Description"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[b]" Column="Enabled"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[b]" Column="LessonTypeID"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[c]" Column="ID"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[c]" Column="Name"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[c]" Column="Description"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[c]" Column="Enabled"/>
    <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[c]" Column="LessonTypeID"/>
</OutputList>

So as you can see, the aliases are in fact intact and retrievable.

just to add... this only goes so far. Being able to differentiate columns of the same name cannot occur beyond a single level, because then the column names become ambiguous within the subquery, which is not valid SQL, because SQL lacks the ability to drill down into subquery with dot notation. — Triynko, Oct 23 '13 at 09:40
For example, although you can run select * from (select * from Lessons a) a, (select * from Lessons b) b, you cannot then wrap that entire thing in parenthesis and select * from it as (subquery) c, because the query validation will fail saying something like The column 'ID' was specified multiple times for 'c'. And this is just a limitation of SQL (for now), because you can't drill down like `select c.a.*, c.b.* from...` — Triynko, Oct 23 '13 at 09:41

How to differentiate duplicate column names from different source tables/subqueries by alias in an SQL select statement when using SqlDataReader?

1 Answers1

Linked