Using linq with Contains - getting error

Question

I am new to Linq and still trying to get familiar with it. I have below linq query to find the duplicates and it works perfectly like below:

//"MergedName" is the Data column I am querying and finding the duplicate Names.

 var duplicates = result.AsEnumerable()
            `.Select(dr => dr.Field<string("MergedName").Replace("'", "''"))
            .GroupBy(x => x)
            .Where(g => g.Count() > 1)
            .Select(g => g.Key)
            .ToList();

  foreach (string duplicate in duplicates.ToArray())
            {
                // Logic to keep one and delete another duplicate.
            }

Now, I want to find the Similar Names on the same column "MergedName". For e.g : John Smith and John Smith Jr. I wrote something with .Where clause but something is wrong with my syntax

 var duplicates = result.AsEnumerable()
                .Select(dr => dr.Field<string>("MergedName").Replace("'", "''"))
                .Where(C => C.Field<string>("MergedName").ToLower().IndexOf(C.Field<string>("MergedName").ToLower().Trim()) != 1)
                .Select(g => g.Key)
                .ToList();

foreach (string duplicate in duplicates.ToArray())
            {
               // Logic to keep one and delete another duplicate.
            }

Error : On Where Statement - "String does not contain a definition for 'Field' and the best extension method overload 'System.Data.DatarowExtensions.Field has some invalid arguments'.

Can you please help me with this code ? or any other way where I can find the similar names using contains.

Answer 1

Its hard to say without you posting the type of original collection, but the problem seems to be exactly what is stated in your error message.

LINQ works in iterative steps and after call to Select(dr => dr.Field<string>("MergedName").Replace("'", "''")) you next expresion works with collection of strings. And there in no method .Field on String type

I guess you could try simplifying C.Field<string>("MergedName").ToLower()in your where clause to C.ToLower()

You are also not doing any grouping in your second statement so Select(g => g.Key) wont work, because there is no Key property on String type either.

This addresses only syntax, your Where clause still looks pretty weird. You are comparing each string to itself.

You could try something like

var names = result.AsEnumerable()
            .Select(dr => dr.Field<string("MergedName").Replace("'", "''").ToLower().Trim())
            .ToList(); //ToList not necessary here, but could prevent multiple executions of the expresion


var duplicates = names.Where(n => names.Any(m => n.IndexOf(m) != -1)) //quadratic complexity
            .ToList();

Just put your own condition into Any part of the last statement, you have two strings m and n there and can compare them however you wish.
This is definitely not the best performing solution of your problem, but it uses LINQ as in your questinon and is easy to write and understand.

After clarification:

var enumerableResult = result.AsEnumerable();
var duplicates = enumerableResult.
                .Where(dr => enumerableResult.Any(dr2 => /*your comparison*/)
                .ToList();

comparison could be something like:

dr.Field<string>("MergedName").Replace("'", "''").Trim().ToLower().IndexOf(dr2.Field<string>("MergedName").Replace("'", "''").Trim().ToLower()) != -1

This condition is based on the one in your question, not the one in your comment. But you dont need to use this inline sytax and can call some custom method, so it would look like .Any(dr2 => AreSamePerson(dr, dr2))

This has again quadratic complexity, it is problem only if you have a lot of records to compare.

Now you get collection of your person objects instead of just strings. Keep in mind that you cant just delete members of duplicate collection from original one but some rather complex logic is needed.

So the best solution seems to be:

var duplicates = result.AsEnumerable()
            .GroupBy(x => x, new PersonyComparer() )
            .Where(g => g.Count() > 1)

class PersonyComparer : IEqualityComparer<Person>//person is the type of objects that are in starting collection
    {
        public bool Equals(Person b1, Person b2)
        {
            if (b2 == null && b1 == null)
                return true;
            else if (b1 == null | b2 == null)
                return false;


            if(/*your condition*/)
                return true;
            else
                return false;
        }

        public int GetHashCode(Person bx)
        {
            return 0; //you must make sure that objects that are equal have same hashcode
        }
    }

This can lead to problems, so make sure that your equal function is symetric (if a==b then b == a) and transitive (if a==b and b==c then a==c). Otherwise your groupping could be screwed a bit.

Then you can iterate over the objects of duplicate collection

foreach(var pgroup in duplicates)
{
    foreach(var person in pgroup .Skip(1))
    {
        //remove from original collection
    }
}

Answer 2

Let me show you by example why you shouldn't want this. As Noxor correctly stated, a viable approach would be to use an IEqualityComparer. But now the question is: what is equal? Your "contains equality" introduces a fuzziness you can't solve.

Let me explain this in the most basic way, forgetting case and string replacements. See this little Linqpad program:

void Main()
{
    var dt = new DataTable();
    dt.Columns.Add("MergedName", typeof(string));

    dt.Rows.Add("Abby Kelley Foster");
    dt.Rows.Add("Kelley Foster");
    dt.Rows.Add("Abby Kelley");

    dt.AsEnumerable()
        .Select(r => r.Field<string>("MergedName"))
        .GroupBy(s => s, new SubstringComparer())
        .Select(g => new { g.Key, Count = g.Count() })
        .Dump();

}

public class SubstringComparer : IEqualityComparer<string>
{
    public bool Equals(string left, string right)
    {
        return left.Contains(right) || right.Contains(left);
    }

    public int GetHashCode(string value)
    {
        return 0; // Just return 0; There is no hashing mechanism implemented that gives "Abby Kelley Foster" and "Abby Kelley" the same hashcode.
    }
}

What's the output? Right:

Abby Kelley Foster  3

But let's change the order of the data rows now:

    dt.Rows.Add("Abby Kelley");
    dt.Rows.Add("Kelley Foster");
    dt.Rows.Add("Abby Kelley Foster");

Can you deduct the output? Here it is:

Abby Kelley 1
Kelley Foster   2

What happened to Abby Kelley Foster?

The comparer first encountered the two first unequal rows, counted 1 for Abby Kelley and went on comparing Kelley Foster and Abby Kelley Foster: bingo! "Equal". However, at this point it never returns to the first row to compare it to the third row.

You could try a more sophisticated (but still simple) algorithm that compares all rows, but then you'll get

Abby Kelley Foster  3

Still wrong. Only Abby Kelley and Abby Kelley Foster are the same person. Kelley Foster is someone else entirely. In other words: you can't resolve this by any automated algorithm. Only exact equalities can be determined by a simple algorithm.

To hit this home with a contrived example: suppose one entry is nothing but "Jr.". Now all names with "Jr." will be seen as duplicates!