C# Linq to SQl，从多个表中选择相同的列

Linq to SQl, select same column from multiple tables

提问by David Anderson

I've been trying to develop a linq query that returns the ItemNumber column of all my tables in the database, but so far I haven't been able to do it successfully.

我一直在尝试开发一个 linq 查询，该查询返回数据库中所有表的 ItemNumber 列，但到目前为止我还没有成功。

Basically I have a table for each kind of hardware component in a computer, and each table has a ItemNumber column. I need to query all of the tables in one bang, and return the ItemNumber values in a flat list/array. (Essentially I want to be able to do the below)

基本上，我为计算机中的每种硬件组件都有一个表，每个表都有一个 ItemNumber 列。我需要一次性查询所有表，并在平面列表/数组中返回 ItemNumber 值。（基本上我希望能够做到以下几点）

foreach (var c in items) {
                Console.WriteLine(c.ItemNumber);
            }

Searching the net to no avail, could someone show me an example of how to do this? My best attempt at it is the following, but I don't understand Sql enough to accomplish this.

搜索网络无济于事，有人可以向我展示如何执行此操作的示例吗？我对此的最佳尝试如下，但我对 Sql 的了解不足以完成此操作。

var items = from hc in dc.DataBoxPCHardwareCases
                         from hhd in dc.DataBoxPCHardwareHardDrives
                         from hkb in dc.DataBoxPCHardwareKeyboards
                         from hmm in dc.DataBoxPCHardwareMemories
                         from hmo in dc.DataBoxPCHardwareMonitors
                         from hmb in dc.DataBoxPCHardwareMotherboards
                         from hms in dc.DataBoxPCHardwareMouses
                         from hod in dc.DataBoxPCHardwareOpticalDrives
                         from hps in dc.DataBoxPCHardwarePowerSupplies
                         from hpc in dc.DataBoxPCHardwareProcessors
                         from hsp in dc.DataBoxPCHardwareSpeakers
                         from hvc in dc.DataBoxPCHardwareVideoCards
                         from sos in dc.DataBoxPCSoftwareOperatingSystems
                         select new { hc, hhd, hkb, hmm, hmo, hmb, hms, hod, hps, hpc, hsp, hvc, sos };

采纳答案by Rex M

What you are describing is a union:

你所描述的是一个联合：

SELECT ItemNumber FROM tbl1 UNION SELECT ItemNumber FROM tbl2

SELECT ItemNumber FROM tbl1 UNION SELECT ItemNumber FROM tbl2

In LINQ:

在 LINQ 中：

var itemCounts = (from hhd in dc.HHD select hhd.ItemNumber)
                 .Union((from hkb in dc.HKB select hkb.ItemNumber)
                         .Union(from hmm in dc.HMM select hmm.ItemNumber)) 
                 and so on

Note that using UNIONs like this is not really very efficient. You are getting the entire data set in one round-trip to the database, but the database server must do a separate query for each UNION, so if you are planning on doing something complex against a lot of data, you might be better off rethinking your database design.

请注意，像这样使用 UNION 并不是很有效。您将在一次到数据库的往返中获取整个数据集，但数据库服务器必须为每个 UNION 执行单独的查询，因此如果您计划对大量数据执行一些复杂的操作，最好重新考虑你的数据库设计。

回答by Christopher Edwards

Union See here

联盟 看这里

var items = (from hc in dc.DataBoxPCHardwareCases select hc.ItemNumber).Union
            (from hhd in dc.DataBoxPCHardwareHardDrives select hkd.ItemNumber).Union
            (from hkb in dc.DataBoxPCHardwareKeyboards select hkb.ItemNumber).Union
            (from hmm in dc.DataBoxPCHardwareMemories select hhh.ItemNumber).Union
            (from hmo in dc.DataBoxPCHardwareMonitors select hmo.ItemNumber).Union
            (from hmb in dc.DataBoxPCHardwareMotherboards select hmb.ItemNumber).Union
            (from hms in dc.DataBoxPCHardwareMouses select hms.ItemNumber).Union
            (from hod in dc.DataBoxPCHardwareOpticalDrives select hod.ItemNumber).Union
            (from hps in dc.DataBoxPCHardwarePowerSupplies select hps.ItemNumber).Union
            (from hpc in dc.DataBoxPCHardwareProcessors select hpc.ItemNumber).Union
            (from hsp in dc.DataBoxPCHardwareSpeakers select hsp.ItemNumber).Union
            (from hvc in dc.DataBoxPCHardwareVideoCards select hvc.ItemNumber).Union
            (from sos in dc.DataBoxPCSoftwareOperatingSystems select sos.ItemNumber)

回答by Amy B

Don't use Union - instead use Concat!

不要使用 Union - 而是使用Concat！

• LinqToSql's Unionis mapped to T-Sql's Union.
• LinqToSql's Concatis mapped to T-Sql's Union All.

• LinqToSql 的Union映射到 T-Sql 的Union。
• LinqToSql 的Concat映射到 T-Sql 的Union All。

The difference is that Unionrequires that the lists be checked against each other and have duplicates removed. This checking costs time and I would expect your part numbers are globally unique (appears in a single list only) anyway. Union Allskips this extra checking and duplicate removal.

不同之处在于Union要求列表相互检查并删除重复项。这种检查需要时间，我希望您的零件号是全球唯一的（仅出现在单个列表中）。 Union All跳过这个额外的检查和重复删除。

List<string> itemNumbers =
  dc.DataBoxPCHardwareCases.Select(hc => hc.ItemNumber)
  .Concat(dc.DataBoxPCHardwareHardDrives.Select( hkd => hkd.ItemNumber ))
  .Concat(dc.DataBoxPCHardwareKeyboards.Select( hkb => hkb.ItemNumber ))
  .Concat(dc.DataBoxPCHardwareMemories.Select( hhh => hhh.ItemNumber ))
  .Concat(dc.DataBoxPCHardwareMonitors.Select( hmo => hmo.ItemNumber ))
  .Concat(dc.DataBoxPCHardwareMotherboards.Select( hmb => hmb.ItemNumber ))
  .Concat(dc.DataBoxPCHardwareMouses.Select( hms => hms.ItemNumber ))
  .Concat(dc.DataBoxPCHardwareOpticalDrives.Select( hod => hod.ItemNumber ))
  .Concat(dc.DataBoxPCHardwarePowerSupplies.Select( hps => hps.ItemNumber ))
  .Concat(dc.DataBoxPCHardwareProcessors.Select( hpc => hpc.ItemNumber ))
  .Concat(dc.DataBoxPCHardwareSpeakers.Select( hsp => hsp.ItemNumber ))
  .Concat(dc.DataBoxPCHardwareVideoCards.Select( hvc => hvc.ItemNumber ))
  .Concat(dc.DataBoxPCSoftwareOperatingSystems.Select( sos => sos.ItemNumber ))
  .ToList();