C# Linq to check duplicate values with dynamic column names

Question

I have a table in SQL server with 4 columns as shown below,

ID  ColName Value       Identifier
1   Name    Test User 1 A100
2   Country USA         A100
3   Name    Test User 2 A101
4   Gender  Male        A101
5   Country France      A101
6   Name    Test User 3 A102
7   Country USA         A102
8   Name    Test User 1 A103
9   Country USA         A103

Using C# Linq, I am trying to find out if a value already exists in the above table. For example, let's say I have a value like, Name = Test User 1 and Country = USA, how can i efficiently check if these values already exists in the above table?

I have tried an approach but, it is extremely slow with larger data in the table,

var userName = "Test User 1";
var country = "1-USA";

var results = records.SelectMany(x => x.UserDetails).Where(x => x.ColName.Equals("Name") && 
!string.IsNullOrEmpty(x.Value) && x.Value.Equals(userName )).ToList();

            if (results != null && results.Count > 0)
            {
                var identifiers = results.Select(x => x.Identifier).Distinct().ToList();

                var matchingCountries = records.SelectMany(x => x.UserDetails).
                Where(x => x.ColName.Equals("Country") && identifiers.Contains(x.Identifier)).ToList();

                var infoList = results.Select(x => new SearchInfoList { Name = x.Value, Identifier = x.Identifier,
                Country = (matchingCountries.Where(r => r.Identifier.Equals(x.Identifier)).Select(r => r.Value).FirstOrDefault()) }).ToList();

                infoList = infoList.Select(x => { x.Country = Helper.IncludeCode(codes, x.Country); return x; }).ToList();

                int recordCount = infoList.Where(x => x.Name.Equals(userName) && x.Country.Equals(country)).Count();


                if (recordCount > 0)
                {
                    return true;
                }
            }

Is there any simple way using C# Linq to check if there is a matching name/country combination with same identifier already exists in the above table? Thanks in advance.

How many of the 'fields' have to match to be considered a duplicate. Name is obvious, but which other fields? is just 'country'? — DaniDev, Apr 09 '21 at 07:38
@DaniDev Yes, Name and Country. There could be other fields too but, we need to match only 2 fields. — JGV, Apr 09 '21 at 07:40
"s there any simple way using C# Linq to check if there is a matching name/country combination with same identifier already exists in the above table?" yes, you need to group by those to columns and check if you find any grouping that has more than 1 entry. This is a common approach — Mong Zhu, Apr 09 '21 at 07:41
@MongZhu, could you please provide direction of how to do the grouping on this data? — JGV, Apr 09 '21 at 07:43
yes, I found the duplicate that shows how to find duplicates ;) — Mong Zhu, Apr 09 '21 at 07:44
@MongZhu, I am not sure if my question is a duplicate. In my case the data is different. And as I mentioned in the question, Name/country combination with same identifier is considered as a match. — JGV, Apr 09 '21 at 07:49
Try like this. `var l = records.SelectMany(x => x.UserDetails); var y = l.Where(x => x.ColName.Equals("Name") && !string.IsNullOrEmpty(x.Value) && x.Value.Equals(userName)).Join(l.Where(x => x.ColName.Equals("Country") && !string.IsNullOrEmpty(x.Value) && x.Value.Equals(country)), name => name.Identifier, country => country.Identifier, (n, c) => new { Name = n, Country = c }).Any();` — Karan, Apr 09 '21 at 08:17
Use `.ToList()` instead of `.Any()` if you want complete list. `.Any()` will just return `true/false` based on if match found or not. — Karan, Apr 09 '21 at 08:18
@Karan, thanks for the code sample. Let me try and will update the status. — JGV, Apr 09 '21 at 14:56

Karan · Answer 1 · 2021-04-13T04:25:44.760

Try using .Where and .Join like below.

var userDetails = records.SelectMany(x => x.UserDetails);
var isExists = userDetails.Where(x => x.ColName.Equals("Name") && !string.IsNullOrEmpty(x.Value) && x.Value.Equals(userName))
                          .Join(userDetails.Where(x => x.ColName.Equals("Country") && !string.IsNullOrEmpty(x.Value) && x.Value.Equals(country)), 
                                name => name.Identifier,
                                country => country.Identifier, 
                                (n, c) => new { Name = n, Country = c })
                          .Any();

To make above code more readable you can get Enumerable object for names & countries like below. Note that do not use .ToList() while you get value in names & countries object for better performance. Because .ToList() will immediately evaluate condition and allocate memory for result while Enumerable object will only evaluate when required (i.e in this case it will evaluate with .Join). You can refer IEnumerable vs List

var names = records.SelectMany(x => x.UserDetails).Where(x => x.ColName.Equals("Name") && !string.IsNullOrEmpty(x.Value) && x.Value.Equals(userName));
var countries = records.SelectMany(x => x.UserDetails).Where(x => x.ColName.Equals("Country") && !string.IsNullOrEmpty(x.Value) && x.Value.Equals(country));
var isExists = names.Join(countries, 
                          name => name.Identifier,
                          country => country.Identifier, 
                          (n, c) => new { Name = n, Country = c })
                    .Any();

C# Linq to check duplicate values with dynamic column names

1 Answers1