有效地过滤一个数据表匹配C＃中另一个数据表的多个列

I have two datatables and I have a function to filter matching records. Following is my function.

我有两个数据表,我有一个过滤匹配记录的功能。以下是我的功能。

public static DataTable FilterCandidates(DataTable FullCandidateData, DataTable RequiredCandidateIDs)
{
    DataTable ReqCandidateData = new DataTable();
    try
    {
        var MacthingIDs = FullCandidateData.AsEnumerable().Select(r => r.Field<string>("Candidate_ID")).Intersect(RequiredCandidateIDs.AsEnumerable().Select(r => r.Field<string>("Candidate_ID")));

        ReqCandidateData = (from row in FullCandidateData.AsEnumerable()
                                      join Candidate_ID in MacthingIDs
                                      on row.Field<string>("Candidate_ID") equals Candidate_ID
                                      select row).CopyToDataTable();
        return ReqCandidateData;
    }
    catch (Exception ex)
    {
        ReqCandidateData = FullCandidateData.Clone();
        return ReqCandidateData;
    }
}

Is there any other effecient way of doing this, Somehow I am not comfortable in assuming that exception is equivalent to No matching records. Also I can do that by finding the length, but it will increase the no.of loops drastically, I have around 90,000 candidates in my First Datatable and it keeps growing.

有没有其他有效的方法来做到这一点,不知怎的,我不习惯假设异常相当于没有匹配的记录。我也可以通过查找长度来做到这一点,但它会大大增加no.of循环,我的第一个数据表中有大约90,000个候选者并且它会不断增长。

Also please let me know how can I do this if I have multiple column comparison, like Candidate_ID and Job_ID

另外,如果我有多列比较,请告诉我如何执行此操作,例如Candidate_ID和Job_ID

2 个解决方案

#1

You can use a single join to server your purpose. The first function below matches only Candidate_ID and second function matches both Candidate and job id. Refer to third method to get all the rows from "FullCandidateData" that do not match "RequiredCandidateIDs"

您可以使用单个联接来为您的目的服务。下面的第一个函数只匹配Candidate_ID,第二个函数匹配Candidate和job id。请参阅第三种方法以获取“FullCandidateData”中与“RequiredCandidateIDs”不匹配的所有行

public static System.Data.DataTable FilterCandidates(System.Data.DataTable FullCandidateData, System.Data.DataTable RequiredCandidateIDs)
{
    System.Data.DataTable reqCandidateData = new System.Data.DataTable();
    try
    {
        var reqCandidateDt = (from candidateRow in FullCandidateData.AsEnumerable()
                              join reqCandidateRow in RequiredCandidateIDs.AsEnumerable()
                              on candidateRow["Candidate_ID"].ToString() equals reqCandidateRow["Candidate_ID"].ToString()
                              select candidateRow);

        if (reqCandidateDt.Count() > 0)
        {
            reqCandidateData = reqCandidateDt.CopyToDataTable();
        }
    }
    catch (Exception ex)
    {

    }
    return reqCandidateData;
}




public static System.Data.DataTable FilterCandidates(System.Data.DataTable FullCandidateData, System.Data.DataTable RequiredCandidateIDs)
{
    System.Data.DataTable reqCandidateData = new System.Data.DataTable();
    try
    {
        var reqCandidateDt = (from candidateRow in FullCandidateData.AsEnumerable()
                              join reqCandidateRow in RequiredCandidateIDs.AsEnumerable()
                              on
                              new { CandidateId = candidateRow["Candidate_ID"].ToString(), Job_Id = candidateRow["Job_ID"].ToString() }
                              equals
                              new { CandidateId = reqCandidateRow["Candidate_ID"].ToString(), Job_Id = reqCandidateRow["Job_Id"].ToString() }
                              select candidateRow);

        if (reqCandidateDt.Count() > 0)
        {
            reqCandidateData = reqCandidateDt.CopyToDataTable();
        }
    }
    catch (Exception ex)
    {

    }
    return reqCandidateData;
}

public static System.Data.DataTable FilterCandidates(System.Data.DataTable FullCandidateData, System.Data.DataTable RequiredCandidateIDs)
{
    System.Data.DataTable reqCandidateData = new System.Data.DataTable();
    try
    {
        var reqCandIds = RequiredCandidateIDs.AsEnumerable().Select(reqCandidateRow => reqCandidateRow["Candidate_ID"].ToString());
        var reqCandidateDt = (from candidateRow in FullCandidateData.AsEnumerable()
                              where !reqCandIds.Contains(candidateRow["Candidate_ID"].ToString())
                              select candidateRow);

        if (reqCandidateDt.Count() > 0)
        {
            reqCandidateData = reqCandidateDt.CopyToDataTable();
        }
    }
    catch (Exception ex)
    {

    }
    return reqCandidateData;
}

#2

I have around 90,000 candidates in my First Datatable and it keeps growing.

我的第一个数据表中有大约90,000名候选人,并且它一直在增长。

With respect to this, you would probably need to start designing/thinking about the load.

关于这一点,您可能需要开始设计/考虑负载。

I would suggest:

我会建议:

1. Filter data before it comes into datatable on basis of date - give user facility to search data from Archive table

在数据基于日期进入数据表之前过滤数据 - 为用户提供从Archive表中搜索数据的工具

2. Yes, choose right and multiple key to filter out data. Filter sequence also matters in this case.

是的,选择正确和多个键来过滤数据。在这种情况下,过滤顺序也很重要。

3. Keep target of keeping count of data in control limit or else you will end in Performance issues

保持将数据计数保持在控制限制的目标,否则您将以性能问题结束

4. For filtering large dataset, you can think to take it to database side, it will be more efficient and fast.

对于过滤大型数据集,您可以考虑将其带到数据库端,它将更高效,更快速。