How to combine two column values and find the duplicates in that using C# Linq

Gani_tpt 1,546 Reputation points
2021-04-08T15:34:49.807+00:00

L want to find the duplicate if any in the combined string.

for example below is the data table.

I want to concatenate the two column values (EMPNO and DEPT) from the data table.

and then find the duplication in that.

85826-image.png

How to form the above requirement in LINQ C#

NOTE : last box Step-2 is the final result.

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,268 questions
0 comments
Accepted answer
  1. Karen Payne MVP 35,036 Reputation points
    2021-04-09T11:29:42.203+00:00

    Here is an example passing the key to check 

    private void HasDuplicatesDemo(string identifier)  
{  
    var tblData = new DataTable();  
      
    tblData.Columns.Add("EMPNO", typeof(string));  
    tblData.Columns.Add("NAME", typeof(string));  
    tblData.Columns.Add("DEPT", typeof(string));  
    tblData.Columns.Add("CATEGORY", typeof(string));  
    tblData.Rows.Add("AM-101", "RAFEEK", "CA-12-MM", "BOLD");  
    tblData.Rows.Add("AM-101", "RAFEEK", "CA-13-AB", "NEW");  
    tblData.Rows.Add("AM-101", "RAFEEK", "CA-13-AB", "NEW");  
    tblData.Rows.Add("AM-102", "MANA", "CA-12-MM", "CESS");  
    tblData.Rows.Add("AM-102", "MANA", "CA-12-MM", "CESS");  
    tblData.Rows.Add("AM-102", "MANA", "CA-13-XL", "REVERSE");  
      
    tblData.Columns.Add("ID", typeof(string)).Expression = "EMPNO +NAME";  
    tblData.Columns["ID"].SetOrdinal(0);  
      
      
    bool hasDuplicates1 = tblData.AsEnumerable().Where(row => row.Field<string>("ID") == identifier)  
        .GroupBy(row => row[0]).Any(gr => gr.Count() > 1);  
  
    Console.WriteLine($"From {nameof(HasDuplicatesDemo)}: {hasDuplicates1}");   
  
}  
private void HasNoDuplicatesDemo(string identifier)  
{  
    var tblData = new DataTable();  
  
    tblData.Columns.Add("EMPNO", typeof(string));  
    tblData.Columns.Add("NAME", typeof(string));  
    tblData.Columns.Add("DEPT", typeof(string));  
    tblData.Columns.Add("CATEGORY", typeof(string));  
    tblData.Rows.Add("AM-101", "RAFEEK", "CA-12-MM", "BOLD");  
    tblData.Rows.Add("AM-102", "MANA", "CA-12-MM", "CESS");  
  
    tblData.Columns.Add("ID", typeof(string)).Expression = "EMPNO +NAME";  
    tblData.Columns["ID"].SetOrdinal(0);  
  
    bool hasDuplicates1 = tblData.AsEnumerable().Where(row => row.Field<string>("ID") == identifier)  
        .GroupBy(row => row[0]).Any(gr => gr.Count() > 1);  
  
    Console.WriteLine($"From {nameof(HasNoDuplicatesDemo)}:  {hasDuplicates1}");  
  
}

    Click event 

    private void runButton_Click(object sender, EventArgs e)  
{  
    HasDuplicatesDemo("AM-101RAFEEK");  
    HasNoDuplicatesDemo("AM-101RAFEEK");  
}

    86277-f2.png

    Then in the second example let's be clear 

    private void HasNoDuplicatesDemo(string identifier)  
{  
    var tblData = new DataTable();  
  
    tblData.Columns.Add("EMPNO", typeof(string));  
    tblData.Columns.Add("NAME", typeof(string));  
    tblData.Columns.Add("DEPT", typeof(string));  
    tblData.Columns.Add("CATEGORY", typeof(string));  
    tblData.Rows.Add("AM-101", "RAFEEK", "CA-12-MM", "BOLD");  
    tblData.Rows.Add("AM-102", "MANA", "CA-12-MM", "CESS");  
  
    tblData.Columns.Add("ID", typeof(string)).Expression = "EMPNO +NAME";  
    tblData.Columns["ID"].SetOrdinal(0);  
  
    bool hasDuplicates1 = tblData.AsEnumerable().Where(row => row.Field<string>("ID") == identifier)  
        .GroupBy(row => row[0]).Any(gr => gr.Count() > 1);  
  
    Console.WriteLine($"From {nameof(HasNoDuplicatesDemo)}:  {hasDuplicates1  == false}");  
  
}

    86209-f3.png

    You can also create an extension method 

    using System.Data;  
using System.Linq;  
  
namespace CommonExtensions  
{  
    public static class DataTableExtensions  
    {  
        /// <summary>  
        /// Determine if the DataTable has duplications based on a column  
        /// where the column may be a concatenation of two or more columns  
        /// </summary>  
        /// <param name="sender">DataTable to check for duplicates</param>  
        /// <param name="columnName">Column name to check, if column does not exists a runtime exception is thrown</param>  
        /// <param name="identifier">Value in columnName</param>  
        /// <returns></returns>  
        public static bool HasDuplicates(this DataTable sender, string columnName, string identifier) =>   
            sender.AsEnumerable()  
                .Where(row => row.Field<string>(columnName) == identifier)  
                .GroupBy(row => row[0]).Any(gr => gr.Count() > 1);  
  
    }  
}

    Usage 

    Console.WriteLine($"From {nameof(HasNoDuplicatesDemo)}:  {tblData.HasDuplicates("ID", "AM-101RAFEEK")}");

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Karen Payne MVP 35,036 Reputation points
    2021-04-08T17:26:44.497+00:00

    Try the following if you only need to determine if there are duplicates.

    • Create an expression column e.g. table.Columns.Add("EmpNoDept", typeof(string), "EmpNo + Dept");
    • Get distinct and assert the table count against the distinctValues count DataView view = new DataView(table);
      DataTable distinctValues = view.ToTable(true, "EmpNoDept");

    Or via Lambda in the case the indexer [0] is looking at the first column data so keeping with using an expression change the index to the column index of EmpNoDept.

    var duplicates = table.AsEnumerable().GroupBy(row => row[0]).Where(gr => gr.Count() > 1);
  2. Karen Payne MVP 35,036 Reputation points
    2021-04-09T10:27:48.59+00:00

    Two approaches

    Option 1

    bool hasDuplicates = table.AsEnumerable().GroupBy(row => row[0]).Any(gr => gr.Count() > 1);

    Option 2

    if (table.AsEnumerable().GroupBy(row => row[0]).Any(gr => gr.Count() > 1))
{
    // has dups
}

    Then try the following

                var tblData = new DataTable();

            tblData.Columns.Add("EMPNO", typeof(string));
            tblData.Columns.Add("NAME", typeof(string));
            tblData.Columns.Add("DEPT", typeof(string));
            tblData.Columns.Add("CATEGORY", typeof(string));
            tblData.Rows.Add("AM-101", "RAFEEK", "CA-12-MM", "BOLD");
            tblData.Rows.Add("AM-101", "RAFEEK", "CA-13-AB", "NEW");
            tblData.Rows.Add("AM-101", "RAFEEK", "CA-13-AB", "NEW");
            tblData.Rows.Add("AM-102", "MANA", "CA-12-MM", "CESS");
            tblData.Rows.Add("AM-102", "MANA", "CA-12-MM", "CESS");
            tblData.Rows.Add("AM-102", "MANA", "CA-13-XL", "REVERSE");

            tblData.Columns.Add("ID", typeof(string)).Expression = "EMPNO +NAME";
            tblData.Columns["ID"].SetOrdinal(0);

            bool hasDuplicates = tblData.AsEnumerable()
                .GroupBy(row => row[0]).Any(gr => gr.Count() > 1);

            bool hasDuplicates1 = tblData.AsEnumerable().Where(row => row.Field<string>("ID") == "AM-101RAFEEK")
                .GroupBy(row => row[0]).Any(gr => gr.Count() > 1);
  3. Karen Payne MVP 35,036 Reputation points
    2021-04-09T11:23:02.423+00:00

    In regards to always returning true, I copied your code, added the column with an expression then tested it with two examples 

    private void HasDuplicatesDemo()  
{  
    var tblData = new DataTable();  
      
    tblData.Columns.Add("EMPNO", typeof(string));  
    tblData.Columns.Add("NAME", typeof(string));  
    tblData.Columns.Add("DEPT", typeof(string));  
    tblData.Columns.Add("CATEGORY", typeof(string));  
    tblData.Rows.Add("AM-101", "RAFEEK", "CA-12-MM", "BOLD");  
    tblData.Rows.Add("AM-101", "RAFEEK", "CA-13-AB", "NEW");  
    tblData.Rows.Add("AM-101", "RAFEEK", "CA-13-AB", "NEW");  
    tblData.Rows.Add("AM-102", "MANA", "CA-12-MM", "CESS");  
    tblData.Rows.Add("AM-102", "MANA", "CA-12-MM", "CESS");  
    tblData.Rows.Add("AM-102", "MANA", "CA-13-XL", "REVERSE");  
      
    tblData.Columns.Add("ID", typeof(string)).Expression = "EMPNO +NAME";  
    tblData.Columns["ID"].SetOrdinal(0);  
      
    bool hasDuplicates = tblData.AsEnumerable()  
        .GroupBy(row => row[0]).Any(gr => gr.Count() > 1);  
      
    bool hasDuplicates1 = tblData.AsEnumerable().Where(row => row.Field<string>("ID") == "AM-101RAFEEK")  
        .GroupBy(row => row[0]).Any(gr => gr.Count() > 1);  
  
    Console.WriteLine($"From {nameof(HasDuplicatesDemo)}: {hasDuplicates}, {hasDuplicates1}");   
  
}  
private void HasNoDuplicatesDemo()  
{  
    var tblData = new DataTable();  
  
    tblData.Columns.Add("EMPNO", typeof(string));  
    tblData.Columns.Add("NAME", typeof(string));  
    tblData.Columns.Add("DEPT", typeof(string));  
    tblData.Columns.Add("CATEGORY", typeof(string));  
    tblData.Rows.Add("AM-101", "RAFEEK", "CA-12-MM", "BOLD");  
    tblData.Rows.Add("AM-102", "MANA", "CA-12-MM", "CESS");  
  
    tblData.Columns.Add("ID", typeof(string)).Expression = "EMPNO +NAME";  
    tblData.Columns["ID"].SetOrdinal(0);  
  
    bool hasDuplicates = tblData.AsEnumerable()  
        .GroupBy(row => row[0]).Any(gr => gr.Count() > 1);  
  
    bool hasDuplicates1 = tblData.AsEnumerable().Where(row => row.Field<string>("ID") == "AM-101RAFEEK")  
        .GroupBy(row => row[0]).Any(gr => gr.Count() > 1);  
  
    Console.WriteLine($"From {nameof(HasNoDuplicatesDemo)}: {hasDuplicates}, {hasDuplicates1}");  
  
}

    Click a button 

    private void runButton_Click(object sender, EventArgs e)  
{  
    HasDuplicatesDemo();  
    HasNoDuplicatesDemo();  
}

    Results

    86311-f1.png