Click here to Skip to main content
15,924,317 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
I am trying to parse through a large number of xml files using Readxml to create a dataset, and then query the resulting dataset using linq sql to get the data I need. This works great except some of the files have extra tables. The development network I use is not connected to internet so I cannot share my actual code.
I would like to code something like:
Var q1 =
(from d1 in ds.Tables[“table1”].AsEnumerable()
Join p in ds.Tables[“table2”].AsEnumerable() on d1.field<int>(‘group_id’) equals on p.field<int>(‘group_id’)
If ds.Tables.contains(“table3”)
{

Join d3in ds.Tables[“table3”].AsEnumerable() on p.field<int>(‘group_id’) equals on ds.field<int>(‘group_id’)
}……
Any ideas on how I can do this?
Posted

1 solution

created a sample code for doing the same. Assuming join clause has to be applied on same columns for each extra available table.

C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace JoinMoreThanTwoTablesDynamically
{
    class Program
    {
        static void Main(string[] args)
        {
            //Created a List of List of Int for sample purpose.
            //Same as dataTable is available in a dataset.
            List<list><int>> dataset = new List<list><int>> { 
            new List<int>{1,2,5,6},
            new List<int>{1,3,5,6},
            new List<int>{4,5,6},
            new List<int>{1,5}
            };

            //Query for joining first two tables
            var q1 = from d1 in dataset[0]
                     join d2 in dataset[1]
                        on d1 equals d2
                     select d1;

            //Retrieve how many tables are in there in your dataset
            int noOfTables = dataset.Count;

            //If you have more  than two tables
            //Below Code will run for each table from third table in your dataset.
            if (noOfTables > 2)
            {
                for (int i = 2; i < noOfTables; i++)
                {
                    q1 = from newd1 in q1
                         join d3 in dataset[i]
                         on newd1 equals d3
                         select newd1;
                }
            }
            //Display Result of join on Console Window.
            foreach (var item in q1)
            {
                Console.WriteLine(item.ToString());
            }

        }
    }
}


Hope this will help !! Happy Programming !!
 
Share this answer
 
v3
Comments
kesiw 16-Dec-13 16:20pm    
Thank you for your quick response! I do not think this will work as I have a different number of tables in the xml files, I do not use all the tables, and the joins are on different fields for different tables.
kesiw 17-Dec-13 15:14pm    
I have tried something similiar to what you suggest, however, as I loop through the tables expecting d3 to change it does not.
it works the first time through - when i == 0, the field "myfieldOnlyInOneTable" exists only in the second table, so the first part of the if returns false so it does not look for the value,
hwoever, the second time through it sees that it is in the table, however, the d3 still hold the value of the first table, so it fails with the msg that myfieldOnlyInOneTable does not exist in the first table.
I look for a value in the select new using:
myValue = (dataset[i].Columns.Contains("myfieldOnlyInOneTable")) ? d3.Field<string>("myfieldOnlyInOneTable") : string.Empty

any ideas?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900