Solved

Filtering common fields from datatables without loops

Posted on 2010-09-07
13
666 Views
Last Modified: 2012-05-10
Hi,
I have a dataset with 2 datatables in it. One has the list of Employees from Department1 and the other has the list of Employees from Department2. I need the list of employees (as a datatable) who belong to both Department1 and Department2. How can this be done without loops?
0
Comment
Question by:rajasoorya_kc
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 22

Expert Comment

by:Om Prakash
ID: 33624329
DataTable Relational Operators in C# - UNION Method
http://weblogs.sqlteam.com/davidm/archive/2004/01/15/724.aspx

but this loops through both table and gives one datatable.
0
 

Author Comment

by:rajasoorya_kc
ID: 33624456
Thanks Om Prakash
But I am looking for a solution with no loops involved. Is there a way?
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 125 total points
ID: 33624489
Below you can find a small example, where I created 2 datatables and give the result back through a linq-query
        private void linqJoin()
        {
            // department1 datatable
            DataTable department1 = new DataTable("Department1");
            department1.Columns.Add(new DataColumn("EmployeeName"));
            DataRow datarow = department1.NewRow();
            datarow[0] = "alfa";
            department1.Rows.Add(datarow);
            datarow = department1.NewRow();
            datarow[0] = "beta";
            department1.Rows.Add(datarow);
            datarow = department1.NewRow();
            datarow[0] = "charlie";
            department1.Rows.Add(datarow);

            // department1 datatable
            DataTable department2 = new DataTable("Department2");
            department2.Columns.Add(new DataColumn("EmployeeName"));
            datarow = department2.NewRow();
            datarow[0] = "alfa";
            department2.Rows.Add(datarow);
            datarow = department2.NewRow();
            datarow[0] = "beta";
            department2.Rows.Add(datarow);
            datarow = department2.NewRow();

            var dataRows = from  dataRows1 in department1.AsEnumerable() 
                            join  dataRows2 in department2.AsEnumerable() 
                            on dataRows1.Field<string>("EmployeeName") equals dataRows2.Field<string>("EmployeeName") 
                            select dataRows1;

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 7

Expert Comment

by:illusio
ID: 33625013

Hi,
If the intent on avoiding loops is to increase performance, you can use a simple system with three passes through the datatables (2 sorting passes and one iteration).

- Sort both datatables on the employee id or name (whatever is appropriate).
- Then write a single loop with two indexes (pseudocode):
while (table1Index < table1.Count && table2Index < table2.Count)
{
        if (table1[table1Index].Employee == table2[table2Index].Employee) {
              tableResult.Add(table1[table1Index])
              table1Index++; table2Index++;
        } else {
               if (table1[table1Index].Employee < table2[table2Index].Employee)
                       table1Index++;
                else
                       table2index++;
       }
}

Kind regards,
Peter
0
 
LVL 8

Assisted Solution

by:Gururaj Badam
Gururaj Badam earned 125 total points
ID: 33625019

DataTable dt1 = CreateEmployeeTable();
            DataTable dt2 = CreateEmployeeTable();

            DataSet ds = new DataSet();
            ds.Tables.Add(dt1);
            ds.Tables.Add(dt2);

            ds.Relations.Add(new DataRelation("Employees", dt1.Columns["EmpId"], dt2.Columns["EmpId"]));

            var empIds = from emp in ds.Tables[0].Rows.Cast<DataRow>()
                                  where emp.GetChildRows().Count<DataRow>() > 0
                                  select emp["EmpId"];

private DataTable CreateEmployeeTable()
        {
            DataTable table = new DataTable();
            table.Columns.Add("EmpId");
            table.Columns.Add("EmpName");

            return table;
        }

Open in new window

0
 

Author Comment

by:rajasoorya_kc
ID: 33625145
Thanks for the tip Dhaest
You suggestion helped. Here is the modified code to convert the selected rows to a datatable
IEnumerable<DataRow> enumerableDataRowCollection = from subSiteRows in subSites.AsEnumerable()
                               join groupNameRows in dataGroupNames.AsEnumerable()
                                   on subSiteRows.Field<string>("Title") equals groupNameRows.Field<string>("GroupName")
                               select subSiteRows;
                dataSource = enumerableDataRowCollection.CopyToDataTable<DataRow>();

Open in new window

0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33625222
>> Thanks for the tip Dhaest

Why did'nt you accept my solution than ?
0
 

Author Comment

by:rajasoorya_kc
ID: 33625257
Oops... i clicked the wrong one
NOM to Novice. Novice's suggestion was right too.
Is there a way to accept two solutions?
0
 
LVL 8

Expert Comment

by:Gururaj Badam
ID: 33625270
Ask admin!
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33625276
just click the button Request Attention  (first post), and then tell in the comments that you want to split the points
0
 
LVL 8

Expert Comment

by:Gururaj Badam
ID: 33625285
Before you do that clearly judge which one you wish to accept as solution.

It's not just for points but later ppl may come to this to follow what has been accepted as solution and implement the same in their problem.

I don't mind if awarded points will be revoked but as far as the problem has been answered correctly and accurately.
0
 

Author Closing Comment

by:rajasoorya_kc
ID: 33627465
Thanks for the suggestions
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question