How to get data without conditions in C#

Question

Hi. I have 2 data tables like this:

I want to get the ID in Table1 if the User in Table2 exists or does not exist

This is the code I test and get the data:

string idGet = "";

string getValue = "Select ID, Port, User from Table1";
DataTable dtgetValue = XLDL.ReadTable(getValue);
if(dtgetValue.Rows.Count > 0)
{
    List<ListOtherUser> listOtherUser = new List<ListOtherUser>();
    for (int i = 0; i < dtgetValue.Rows.Count; i++)
    {
        listOtherUser.Add(new ListOtherUser { ID = dtgetValue.Rows[i]["ID"].ToString(), User = dtgetValue.Rows[i]["User"].ToString(), Port = dtgetValue.Rows[i]["Port"].ToString() });
    }
    
    foreach (var itemuser in listOtherUser)
    {
        string checkUser = "Select ID from Table2 where User = N'" + itemuser.User + "'";
        DataTable dtcheckUser = XLDL.ReadTable(checkUser);
        if (dtcheckUser.Rows.Count > 0)
        {
            idGet += itemuser.ID + ",";                                        
        }
        else
        {
            //Here I want to continue to get the data of row ID=3 from Table1. However I don't know how to solve it?
        }
    }
}

As the data above I want the output as: idGet = 1 and 3 from Table1

With data from Table1 and Table2:

As the data above I want the output as: idGet = 2 and 3 from Table1

Looking forward to a solution from everyone. Thank you!

I want to help but don't understand the question fully. The results you're expecting don't match with the tables you're giving based on your question. You want the id's from table 1 where there is a user that matches in table 2? — zgc7009, Jan 31 '23 at 12:51
@zgc7009 Exactly. However how do I get ID=3 from table 1, without having to get ID=2? As in my example: Result of ID should be 1.3. You may notice that the Port of ID 1 and 3 are the same. I got ID = 1, but ID = 3 I don't know how to get it. — Chim Di Tru, Jan 31 '23 at 13:33

ClearlyClueless · Accepted Answer · 2023-01-31T18:15:02.190

1

The best solution here though would be to do some SQL joins and exclude it coming in, but if you want a solution in code instead, depdending on how large that dataset is/will be, this is a good spot for LINQ.

    var result = Table2.Where(p => table1.All(p2 => p2.UserID != p.UserID));

Adapted from this question.

If you opted for SQL, your query would look something more like this and while looking at your logic, you should absolutely not do that how you are trying to. That is so many single db calls for absolutely no reason.

    Select Table1.ID 
    FROM Table1 LEFT OUTER JOIN
        Table2 ON Table1.User = Table2.User
    WHERE Table2.ID IS NULL;

edited Jan 31 '23 at 18:15

answered Jan 31 '23 at 15:53

ClearlyClueless

545
2
13

To expand for the person asking the question, the concept here is that you would use the SQL query where the inner join acts as your filter. The inner join says only return values from Table 2 where the UserId doesn't exist on Table 1 – zgc7009 Jan 31 '23 at 18:00
1

A left outer join and a where Table2.something IS NULL would be more appropriate. I've edited my answer to include a SQL solution which, truthfully, is how this should be getting done after looking at the code in the OP a bit deeper. – ClearlyClueless Jan 31 '23 at 18:16
Thank you, I have tried and customized – Chim Di Tru Feb 01 '23 at 06:57

How to get data without conditions in C#

1 Answers1