2

I have two DataTables in a DataSet linked together by a DataRelation and I'm trying to select all parent rows that have a child row with value x.

The parent table contains products and the child table contains categories that the product is in.

DataSet dsProducts = new DataSet();

DataTable dtProducts = new DataTable("products");
dtProducts.Columns.Add("entity_id", typeof(int));
dtProducts.Columns.Add("sku", typeof(string));
dtProducts.Columns.Add("mpn", typeof(string));
dtProducts.Columns.Add("brand", typeof(string));
dtProducts.Columns.Add("name", typeof(string));
dtProducts.Columns.Add("description", typeof(string));
dtProducts.Columns.Add("short_description", typeof(string));
dtProducts.Columns.Add("image", typeof(string));
dtProducts.Columns.Add("weight", typeof(double));
dtProducts.Columns.Add("qty", typeof(double));
dtProducts.Columns.Add("cost", typeof(double));
dtProducts.Columns.Add("price", typeof(double));
dtProducts.PrimaryKey = new DataColumn[] { dtProducts.Columns["entity_id"] };
dsProducts.Tables.Add(dtProducts);

DataTable dtCategories = new DataTable("categories");
dtCategories.Columns.Add("entity_id", typeof(int));
dtCategories.Columns.Add("category_id", typeof(int));
dsProducts.Tables.Add(dtCategories);

dsProducts.Relations.Add(new DataRelation("entity_id", dtProducts.Columns["entity_id"], dtCategories.Columns["entity_id"]));

EDIT

I've pieced together this piece of Linq code which works but seems pointless having a DataRelation

var rows = from prods in dsProducts.Tables["products"].AsEnumerable()
            join cats in dsProducts.Tables["categories"].AsEnumerable() on prods.Field<int>("entity_id") equals cats.Field<int>("entity_id")
            where cats.Field<int>("category_id") == id
            select prods;
Christian
  • 3,708
  • 3
  • 39
  • 60

2 Answers2

1

Try:

DataRow[] rows = dsProducts.Tables["products"].Select("entity_id=" + id);

instead of:

DataRow[] rows = dsProducts.Tables["products"].Select("Child(entity_id).category_id = " + id);

Because you already create relation between two table with common column "entity_id",so as long as you entity_id is matched, you get your desired result.

C-Pound Guru
  • 15,967
  • 6
  • 46
  • 67
  • that will bring back all the products with that entity_id. Im looking for a rowset of products who are only in a particular category – Christian Jan 16 '12 at 18:41
0
DataRelation relation = dsProducts.Relations.Add(new DataRelation("entity_id", dtProducts.Columns["entity_id"], dtCategories.Columns["entity_id"]));

DataRow[] childRows = dsProducts.Tables["categories"].Select("category_id=" + id);
foreach (DataRow row in childRows) {
   DataRow[] parentRows = row.GetParentRows(relation); // parentRows[0] should be your parent
}
C-Pound Guru
  • 15,967
  • 6
  • 46
  • 67
  • Shouldnt it be categories for the childrows `DataRow[] childRows = dsProducts.Tables["categories"]` and relation should be `entity_id` – Christian Jan 16 '12 at 18:58