LINQ implement multiple select where condition

Question

I have 2 datatables.

        DataTable wdt = new DataTable();
        wdt.Columns.Add("wName", typeof(string));
        wdt.Columns.Add("userID1", typeof(string));
        wdt.Columns.Add("userID2", typeof(string));
        wdt.Columns.Add("userID3", typeof(string));
        wdt.Columns.Add("dttime", typeof(DateTime));

        DataTable mdt = new DataTable();
        mdt.Columns.Add("iD", typeof(string));
        mdt.Columns.Add("firstname", typeof(string));
        mdt.Columns.Add("lastname", typeof(string));

        DataTable dt = new DataTable();
        dt.Columns.Add("wName", typeof(string));
        dt.Columns.Add("user1", typeof(string));
        dt.Columns.Add("user2", typeof(string));
        dt.Columns.Add("user3", typeof(string));
        dt.Columns.Add("dttime", typeof(DateTime));

        for (int i = 0; i < wdt.Rows.Count; i++)
        {
            DataRow ndr = dt.NewRow();
            ndr[0] = wdt.Select()[i][0].ToString();

            ndr[1] = (from r in mdt.AsEnumerable()
                              where r.Field<int>("iD") == Convert.ToInt32(wdt.Rows[i][1])
                              select r.Field<string>("firstName") + " " + r.Field<string>("lastName")).First<string>();
                ndr[2] = (from r in mdt.AsEnumerable()
                          where r.Field<int>("iD") == Convert.ToInt32(wdt.Rows[i][2])
                          select r.Field<string>("firstName") + " " + r.Field<string>("lastName")).First<string>();
                ndr[3] = (from r in mdt.AsEnumerable()
                          where r.Field<int>("iD") == Convert.ToInt32(wdt.Rows[i][3])
                          select r.Field<string>("firstName") + " " + r.Field<string>("lastName")).First<string>();


           ndr[4] = wdt.Select()[i][4].ToString();

           dt.Rows.Add(ndr);
        }

In the above code, i get a new datatable dt from calculating the data from wdt & mdt. But here, i have to run the LINQ syntaxes in a loop. Is it possible to avoid loop & the same work to be done in a single LINQ loop?

Datatable1 :

iD  firstname  lastname
1      b           v
2      d           c
3      f           g
4      s           o

....

Datatable2 :

Code        userid1    userid2      userid3  work
    1f           1           3           6       gg
    2g           1           4           7       gg
    3b           3           4           7       gg
    4v           4           3           8       gg

Expected New Datatable :

Code    username1   username2   username3  work
1f           a           b           c       gg
2g           d           f           r       gg
3b           c           h           g       gg
4v           d           s           h       gg

Here, iD from datatable1 & userID1, userID2, userID3 are same.

You shall use join's refer to this link for details: http://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql — Abbas, Apr 22 '15 at 05:48
The code available is only for checking 1 column, but i want to check 3 columns & insert values in a new datatable. — Harshit, Apr 22 '15 at 06:16
@bogojane - It's tough to understand from your query, can you show some sample data for these datatables and result you are expecting? — Rahul Singh, Apr 22 '15 at 07:55

Hossein Salmanian · Answer 1 · 2015-04-22T15:51:14.647

username1 you can Make a query using left join and return new on the fly object witch have all property you need as below

    var newData = (from a in wdt.AsEnumerable()
        join user1Info in mdt.AsEnumerable() on a["userID1"] equals user1Info["iD"] into lUser1Info
        join user2Info in mdt.AsEnumerable() on a["userID2"] equals user2Info["iD"] into lUser2Info
        join user3Info in mdt.AsEnumerable() on a["userID3"] equals user3Info["iD"] into lUser3Info
        from user1Info in lUser1Info.DefaultIfEmpty()
        from user2Info in lUser2Info.DefaultIfEmpty()
        from user3Info in lUser3Info.DefaultIfEmpty()
        select new
        {
            wName = a["wName"].ToString(),
            username1 = user1Info == null ? string.Empty : user1Info["firstname"].ToString() + user1Info["lastname"],
            username2 = user2Info == null ? string.Empty : user2Info["firstname"].ToString() + user2Info["lastname"],
            username3 = user3Info == null ? string.Empty : user3Info["firstname"].ToString() + user3Info["lastname"],
            dttime = a["dttime"]
        }).ToList();

In the above code, i am getting the same name from all the 3 usernames i.e. username1, username2, username3. But as i have mentioned in the code, they could be different. — Harshit, Apr 22 '15 at 11:05

LINQ implement multiple select where condition

1 Answers1