Get DataRow if column names in a string array have matching values in a string array

Question

I'm trying to get a DataRow from a dtResult datatable if column name in [colName] list has a matching value as [grbByValue] list. my goal in the below code is to get [test1] and [test2] return datarow from dtResult and should be the same as [update] (which is hard coded). but have issue in both test1 & test2. test1 has error and don't know how fix and test2 is returning null.

rule is a DataTable that looks like this:

All the below logic is run for each row of rule.

dtResult is also a DataTable that looks like this:

EDITED CODE

string[] grpby = { "ageband","gender","code"};
List<string> grbByValue = new List<string>() { "1","85+","1","1010"};

        DataTable dtResult = new DataTable();
        DataColumn dc = dtResult.Columns.Add("id", typeof(int));
        dc.AutoIncrement = true;
        dc.AutoIncrementSeed = 1;
        dc.AutoIncrementStep = 1;

        dtResult.Columns.Add("DataSourceID");
        dtResult.Columns["DataSourceID"].DefaultValue = "1";
        dtResult.Columns.Add("RuleID");
        dtResult.Columns.Add("GroupBy0");
        dtResult.Columns.Add("GroupBy1");
        dtResult.Columns.Add("GroupBy2");
        dtResult.Columns.Add("GroupBy3");
        dtResult.Columns.Add("GroupBy4");
        dtResult.Columns.Add("GroupBy5");
        dtResult.Columns.Add("Result", typeof(decimal));
        dtResult.Columns["Result"].DefaultValue = 0.00;


        var colName = (from a in dtResult.Columns.Cast<DataColumn>()
                       where a.ColumnName.ToString().StartsWith("GroupBy")
                       select a.ColumnName).OrderBy(x => x).ToList();
        colName.Insert(0, "RuleID");
        colName = colName.GetRange(0, grbByValue.Count);

        //comment/UNCOMMENT below to test [test1]
        //DataRow z = dtResult.NewRow();
        //for (int i = 0; i < grbByValue.Count; i++)
        //{
        //    z[colName[i]] = grbByValue[i];
        //}
        //dtResult.Rows.Add(z.ItemArray);

        var distDtResult = dtResult.DefaultView.ToTable(true, colName.ToArray());
        bool exist = false;
        DataRow update = null;

        foreach (DataRow dr in distDtResult.Rows)
        {
            var row = dr.ItemArray.ToList();
            exist = row.SequenceEqual(grbByValue);
            if (exist == true)
            {
                //var test1 = (from t1 in distDtResult.AsEnumerable().Where(r => r.ItemArray == dr.ItemArray)
                //             join t2 in (from m in dtResult.AsEnumerable()
                //                         select new
                //                         {
                //                             //ideally the below column list will be derived from [colName] dynamically
                //                             RuleID = m.Field<string>("RuleID"),
                //                             GroupBy0 = m.Field<string>("GroupBy0"),
                //                             GroupBy1 = m.Field<string>("GroupBy1"),
                //                             GroupBy2 = m.Field<string>("GroupBy2")
                //                         }) on t1.ItemArray equals t2.ItemArray
                //             select new
                //             {
                //                 t2
                //             }).FirstOrDefault();

                update = dtResult.AsEnumerable().Where(r =>
                                                r.Field<int>("id") == 1 &&
                                                r.Field<string>("DataSourceID") == "1" &&
                                                r.Field<string>("RuleID") == "1" &&
                                                r.Field<string>("GroupBy0") == "85+" &&
                                                r.Field<string>("GroupBy1") == "1" &&
                                                r.Field<string>("GroupBy2") == "1010").FirstOrDefault();


                break;
            }
        }

        if (exist == false)
        {
            DataRow a = dtResult.NewRow();
            for (int i = 0; i < grbByValue.Count; i++)
            {
                a[colName[i]] = grbByValue[i];
            }
            dtResult.Rows.Add(a.ItemArray);

            var test2 = dtResult.AsEnumerable().Where(r => r.ItemArray.Equals(a.ItemArray)).FirstOrDefault();
            update = dtResult.AsEnumerable().Where(r =>
                                               r.Field<int>("id") == 1 &&
                                               r.Field<string>("DataSourceID") == "1" &&
                                               r.Field<string>("RuleID") == "1" &&
                                               r.Field<string>("GroupBy0") == "85+" &&
                                               r.Field<string>("GroupBy1") == "1" &&
                                               r.Field<string>("GroupBy2") == "1010").FirstOrDefault();

        }

Can you include some sample input? It looks like it would be stored in grpby but that is not defined/populated in your snippets. — B. Witter
– B. Witter, Commented Jun 1, 2020 at 16:10
I think that helps, but I am not sure it is clear what the expected output is given your ask and the code snippets provided. We don't see what rule is and we can only assume what dtResult and dr look like. That may be enough to get good answers for you =D — B. Witter
– B. Witter, Commented Jun 1, 2020 at 16:51
@B.Witter thanks for the suggestion. first timer here. hope it's better now. — Coconut
– Coconut, Commented Jun 1, 2020 at 17:37
No problem, we all start somewhere. I am trying to model this myself to see what we can do, but I am not sure what some sample rows of dtResult would be. And it seems that grbByValue is unused, is that intended? — B. Witter
– B. Witter, Commented Jun 1, 2020 at 18:35

B. Witter · Accepted Answer · 2020-06-01 20:29:58Z

This might be a good starting point, at least to better ask questions and move towards an answer.

string[] colName = { "RuleID", "GroupBy0", "GroupBy1", "GroupBy2" };

// "All the below logic is run for each row of rule"
// this goes through each row of the rule DataTable
foreach (DataRow rule in ruleTable.Rows)
{
    // This is going to be equivalent to the grpby variable you specified
    var groupRules = rule.Field<string>("GroupBy").ToString().Split("|");

    // Some sort of mapping may need to go here to go from "ageband" to "GroupBy0", "gender" to "GroupBy1", etc.

    foreach(DataRow row in dtResult.Rows)
    {
        DataTable distDtResult = dtResult.DefaultView.ToTable(true, colName);

        var updateTEST = from dr in distDtResult.AsEnumerable()
                         where dr.Field<string>("RuleID") == rule["RuleID"].ToString()
                         && dr.Field<string>("GroupBy0") == row["GroupBy0"].ToString() // ageband
                         && dr.Field<string>("GroupBy1") == row["GroupBy1"].ToString() // gender
                         && dr.Field<string>("GroupBy2") == row["GroupBy2"].ToString() // code
                         // more
                         select dr;
    }
}

Collectives™ on Stack Overflow

Get DataRow if column names in a string array have matching values in a string array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related