0

I have a CheckedListbox which contains values from some table called products. The idea is to check the products that are associated to a customer. Now it does save correctly in an link table, yet when loading it again, the items that were checked do not get loaded correctly into the CheckedListbox.

So from that link table where, I would like to get all rows from just one column. All tables are already loaded into the application so I don't want to use sql.

I've tried using linq, with no success, Ids is just empty here.

int[] Ids = (from m in dataset.Tables["LinkTable"].AsEnumerable()
                where m.Field<int>("customerId") == customerId
                select m.Field<int>("productId")).ToArray();

Then, if I do succeed to get those Id's, I would like to get the indexes of those primary keys so I can set the correct products to checked. I've tired doing it like this, but this gives me error in other parts of the program, because I am setting a Primary key to a global datatable. Datagridviews don't like that.

        DataColumn[] keyColumns = new DataColumn[1]; 
        keyColumns[0] = dataset.Tables["products"].Columns["Id"];
        currentPatient.GetTheDataSet.Tables["products"].PrimaryKey = keyColumns;

        foreach (int Id in Ids)
        {
            DataRow row = dataset.Tables["Products"].Rows.Find(Id);
            int index = dataset.Tables["Products"].Rows.IndexOf(row);
            clbMedications.SetItemChecked(index, true);

        }

I would like to do that last part without specifying a primary key, I couldn't find how to do that in linq.

I know it consists of 2 questions, but perhaps this can be done with just one linq statement so I better combine them.

CularBytes
  • 9,924
  • 8
  • 76
  • 101

1 Answers1

1

[EDIT]

Finally, i think i've got what you need:

var qry = (from p in ds.Tables["products"].AsEnumerable()
    select new {
        Id = p.Field<int>("Id"),
        Index = ds.Tables["products"].Rows.IndexOf(p),
        Checked = ds.Tables["LinkTable"].AsEnumerable().Any(x=>x.Field<int>("productId") == p.Field<int>("Id") && x.Field<int>("customerId")==customerid)
    }).ToList();

Above query returns the list, which you can bnid with CheckedListbox.

Maciej Los
  • 8,468
  • 1
  • 20
  • 35
  • I understand it until the part of `select new`, Are you making new variables there? c.CustomerName for example doesnt exist... Kind of new to linq as you probably found out already – CularBytes Jan 08 '15 at 14:58
  • Then I still don't know how I can use that var for my solution. – CularBytes Jan 08 '15 at 15:07
  • It's just an example... I wanted to show you that you can join datatables into one result set. If you want to get *productid* from **LinkTable** then to get index of *productid*, then using join's is a way to resolve your issue. – Maciej Los Jan 09 '15 at 08:24
  • Thanks, however in the meantime I already managed to get pull it off in a different way. Still thankyou for your time! – CularBytes Jan 09 '15 at 14:21