Linq to SQl, select same column from multiple tables

Question

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.

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)

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.

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

Solution 1

What you are describing is a union:

SELECT ItemNumber FROM tbl1 UNION SELECT ItemNumber FROM tbl2

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

Solution 2

Don't use Union - instead use Concat!

• LinqToSql's Union is mapped to T-Sql's Union.
• LinqToSql's Concat is mapped to T-Sql's Union All.

The difference is that Union requires 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 All skips this extra checking and duplicate removal.

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

Solution 3

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)