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!

CodePudding user response：

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;