使用SqlDataReader时，如何通过SQL select语句中的别名将重复的列名与不同的源表/子查询区分开? [英] How to differentiate duplicate column names from different source tables/subqueries by alias in an SQL select statement when using SqlDataReader?

问题描述

假设我有一个从数据库中读取的POCO实体，它们的主键列名称分别为"ID".

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

如果从多个别名为a和b的表或子查询(如select a.*, b.* from a, b)中进行选择，则所选列将包括两个ID列(a.ID和b.ID)，但是源表/子查询的别名会丢失.我想知道是否有一种方法可以为任意选择查询的输出列的源保留或动态获得这样的顶级别名.

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.

我知道我可以更改输出列名称或按顺序访问它们，但是我需要按列的原始名称+源表/子查询的别名(例如"a.ID"和"b")访问列. ID".

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似乎默认情况下不会保留列的基表信息，也永远不会保留别名信息.

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

是否有一种方法可以区分SqlDataReader中的这些列，或者获取它以保留每一列的源表/子查询别名?

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

在此处提出了类似的问题:在C#SqlConnection中使用相同名称的2个SQL列名称之间进行区分，但是答案是为列提供唯一的别名，这不是我想要的.我希望能够为表使用别名，同时保留原始列名.

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.

在这里提出要求:如何从SqlDataReader获取列的表名，并在这里回答:

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.

推荐答案

我怀疑另一个人错了，所以我要回答我自己的问题.

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

在SQL Server 2012(可能还有更早的版本)中，我发现如果我调用"set showplan_xml on;"，在任意查询上，例如:

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;

返回的XML包含以下内容作为其第一级/顶级OutputList，它清楚地显示了所有列，不仅显示了它们的源表，还显示了它们的源子查询的别名.

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>

因此，在我的数据库API中，由于我将所有查询字符串都整齐地存储在字典中，因此我实际上可以在应用程序启动时执行缓存预热，将其全部运行到xml_showplan上，解析XML的顶级输出列，然后将表别名和列名完整映射到它们的输出顺序.

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.

实际上，我在这里使用了一些技巧.它实际上不是子查询的别名，而是基表的别名.如果在基表上不包含别名，则XML中不存在Alias属性.但是，非常简单的方法是确保每次引用实际表时，都为其赋予一个别名，该别名应使该表输出到XML，然后就可以了.

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，它会产生:

<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.

这篇关于使用SqlDataReader时，如何通过SQL select语句中的别名将重复的列名与不同的源表/子查询区分开?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！