How to use LINQ to join DataTables (C#, LINQ)

Its not often I need to join multiple DataTables in my .Net classes. I attempt to do as much joining on a Dataserver as possible, but when two tables only exists in memory I must.

You can use a DataRelation object to join my two tables or I can use LINQ to get me my results. In this example, I will show you two simple DataTables and how I join the two together.

Here is my first DataTable:

DataTable dtProducts = new DataTable("Companies");
dtProducts.Columns.AddRange(new DataColumn[] { new DataColumn("CompanyName") });
dtProducts.Rows.Add(new object[] { "Microsoft" });
dtProducts.Rows.Add(new object[] { "Google" });
dtProducts.Rows.Add(new object[] { "Apple" });

Here is my second DataTable

DataTable dtProductMappings = new DataTable("CompanyProducts");
dtProductMappings.Columns.AddRange(new DataColumn[]{new DataColumn("CompanyName"), new DataColumn("ProductName") });
dtProductMappings.Rows.Add(new object[] { "Microsoft", "Windows" });
dtProductMappings.Rows.Add(new object[] { "Microsoft", "Office" });
dtProductMappings.Rows.Add(new object[] { "Google", "Internet Search" });
dtProductMappings.Rows.Add(new object[] { "Google", "Android" });
dtProductMappings.Rows.Add(new object[] { "Apple", "iOS" });
dtProductMappings.Rows.Add(new object[] { "Apple", "iPhone" });
dtProductMappings.Rows.Add(new object[] { "Apple", "iPad" });
dtProductMappings.Rows.Add(new object[] { "Apple", "iPod" });

The first table is a list companies and the second is a list of some of their products.

The two DataTables have a common column, CompanyName. I want to return only the rows in CompanyProducts table. For this example I will create an Generic Int List of the companies I want to see products for using LINQ against my Companies DataTable. In this case, I want to see all but Microsoft's products.

List<int> lintProducts = (from s in dtProducts.AsEnumerable()
                          where s.Field<string>("CompanyName") != "Microsoft"
                          select s.Field<int>("CompanyId")).ToList<int>();

This will result in lintProducts having two integers, 2 (Google) and 3 (Apple).
Now for the query to join the two together:

var companyProducts = from a 
                          in dtProductMappings.AsEnumerable()
                          where productfound(a.Field<int>("CompanyId"), lintProducts)
                          select dtProducts.Select("CompanyId = '" + a.Field<int>("CompanyId") +"'")[0]["CompanyName"]+ " - " + a.Field<string>("ProductName");

Finally, I want to display my results. Its a simple as:

Array.ForEach(companyProducts.ToArray(), z => Console.WriteLine(z + ", "));

Here are the results:

Google - Internet Search, 
Google - Android, 
Apple - iOS, 
Apple - iPhone, 
Apple - iPad, 
Apple - iPod,

Notice that I am using a function called productFound to filter out the products I am interested in.
Here is the code for productFound:

static private bool productfound(int currentProduct, List<int> seekingProduct)
{
        bool lbReturn = false;
        foreach (int s in seekingProduct)
        {
                if (s.Equals(currentProduct))
                {
                    lbReturn = true;
                    break;
                }
        }
        return lbReturn;
}

This simply allows me to search through my DataTable to look for one or more value, in this case 2 (Google) and 3 (Apple)