Create a new row from 2 tables

Question

Hello,

New to LINQ and c# but having problem with one specific problem.

I have 2 Data tables.

DataTable 1 has Name, House Number in it. DataTable 2 holds Street, FirstHouseNumber, LastHouseNumber.

What I want to do is create a new table that has Name, House Number, Street in it but I keep ending up with lots of DataTable1 Count * DataTable2 Count when I would only expect the same count as DataTable 1.

Heres the expression I am using:

var funcquery = from name in DT1.AsEnumerable()
                from street in DT2.AsEnumerable()
                where name.Field<int>("Number") >= street.Field<int>("FirstHouseNumber") && name.Field<int>("Number") <= street.Field<int>("LastHouseNumber")
                select new { Name = name.Field<string>("Name"), Number = name.Field<int>("Number"), street.Field<string>("Street")   };

What am I doing wrong? I just cant get it at all.

TIA.

Answer 1

This will be easier to think about with a more specific example:

Name   House Number
------ ------------
Chris  123
Ben    456
Joe    789

Street   First House  Last House
         Number       Number
-------- ------------ ------------
Broadway 100          500
Main     501          1000

To demonstrate the results using this data, I use the following code:

class HouseInfo
{
   public string Name;
   public int HouseNumber;
   public HouseInfo(string Name, int HouseNumber)
   {
      this.Name = Name;
      this.HouseNumber = HouseNumber;
   }
}
class StreetInfo
{
   public string Street;
   public int FirstHouseNumber;
   public int LastHouseNumber;
   public StreetInfo(string Street, int FirstHouseNumber, int LastHouseNumber)
   {
      this.Street = Street;
      this.FirstHouseNumber = FirstHouseNumber;
      this.LastHouseNumber = LastHouseNumber;
   }
}
static void Main(string[] args)
{
   HouseInfo[] houses = new HouseInfo[] {
      new HouseInfo("Chris", 123),
      new HouseInfo("Ben", 456),
      new HouseInfo("Joe", 789) };
   StreetInfo[] streets = new StreetInfo[] {
      new StreetInfo("Broadway", 100, 500),
      new StreetInfo("Main", 501, 1000)};

   var funcquery = from name in houses
                   from street in streets
                   where name.HouseNumber >= street.FirstHouseNumber && name.HouseNumber <= street.LastHouseNumber
                   select new { Name = name.Name, Number = name.HouseNumber, Street = street.Street };
   var results = funcquery.ToArray();
   for (int i = 0; i < results.Length; i++)
   {
      Console.WriteLine("{0} {1} {2}", results[i].Name, results[i].Number, results[i].Street);
   }
}

The results are:

Chris 123 Broadway
Ben 456 Broadway
Joe 789 Main

This seems to be what you are after, so I don't think the problem is in the code you've given. Perhaps it is in the data itself (if multiple streets include the same house range) or something else. You may need to supply more specific data that demonstrates your problem.

Here's the same code implemented using DataTables instead of classes. It yields the same result:

System.Data.DataTable DT1 = new System.Data.DataTable("Houses");
System.Data.DataTable DT2 = new System.Data.DataTable("Streets");
DT1.Columns.Add("Name", typeof(string));
DT1.Columns.Add("Number", typeof(int));
DT2.Columns.Add("Street", typeof(string));
DT2.Columns.Add("FirstHouseNumber", typeof(int));
DT2.Columns.Add("LastHouseNumber", typeof(int));
DT1.Rows.Add("Chris", 123);
DT1.Rows.Add("Ben", 456);
DT1.Rows.Add("Joe", 789);
DT2.Rows.Add("Broadway", 100, 500);
DT2.Rows.Add("Main", 501, 1000);
var funcquery = from System.Data.DataRow name in DT1.Rows
                from System.Data.DataRow street in DT2.Rows
                where (int)name["Number"] >= (int)street["FirstHouseNumber"]
                && (int)name["Number"] <= (int)street["LastHouseNumber"]
                select new { Name = name["Name"], Number = name["Number"], Street = street["Street"] };
var results = funcquery.ToArray();
for (int i = 0; i < results.Length; i++)
{
   Console.WriteLine("{0} {1} {2}", results[i].Name, results[i].Number, results[i].Street);
}