Help me convert this SQL query to LINQ

Question

I'm trying to convert this SQL query to LINQ:

SELECT Items.p_Name Product, DiamondCategory.dc_Name Category, Diamond.d_Weight Weight
FROM DiamondCategory
INNER JOIN Diamond ON DiamondCategory.dc_Id = Diamond.dc_Id
INNER JOIN Items ON Diamond.p_Id = Items.p_Id where Items.p_Id = 1

Union gives no results:

var qry = (from d in myDatabaseDataSet.Diamond select d.d_Weight).Union
          (from c in myDatabaseDataSet.Items select c.p_Name).Union
          (from e in myDatabaseDataSet.DiamondCategory select e.dc_Name);

Answer 1

Union does not do what you want, you need a Join. This is the general answer:

from item in myDatabaseDataSet.Items
  where item.p_Id = 1
join diamond in myDatabaseDataSet.Diamond 
  on diamond.p_Id equals item.p_Id
join category in myDatabaseDataSet.DiamondCategory 
  on diamond.dc_Id equals category.dc_Id
select new 
{ 
  Product = item.p_Name, 
  Weight = diamond.d_Weight, 
  Category = category.dc_Name 
};

EDIT: From your tags, it seems like you are using LINQ to SQL. If so, then something like this would suffice, assuming you have foreign key constraints between the tables you've mentioned:

from item in myDatabaseDataSet.Items, 
  diamond in item.Diamonds, 
  category in diamond.DiamondCategory
where item.p_Id = 1
select new 
{ 
  Product = item.p_Name, 
  Weight = diamond.d_Weight, 
  Category = category.dc_Name 
};

Answer 2

You shouldn't be using Union for this.

From MSDN:

Produces the set union of two sequences.

You need to use Join, which:

Correlates the elements of two sequences based on matching keys.