Linq/SQL Returning Different Results

Question

I'm curious as to why my linq group by query returns 417 results whereas my SQL interpretation returns 419? I'm looking for duplicated emails from my list. I've checked out the result set and the two email addresses that are missing from the linq set both have accents. Does linq not recognize accents? Is there a workaround? The email field type is a nvarchar(100).

Let me know if you have any questions, Thanks in advance!

var listOfContacts = (from contacts in something
                      where contacts.Team.Id.Equals(TeamGuid) && !contacts.Email.Equals(null)
                      select new {contacts.Id, EmailAddress = contacts.Email.ToLower()}).ToList();

//Full Contact List; exact amount matches

var dupeEmailsList = listOfContacts
    .GroupBy(x => x.EmailAddress)
    .Where(g => g.Count() > 1)
    .Select(y => y.Key)
    .ToList();

//Returns 417

SELECT Email, COUNT(*)
FROM something
WHERE Team = 'Actual Team Guid Inserted Here'
GROUP BY Email
HAVING (COUNT(LOWER(Email)) > 1 AND Email IS NOT NULL)
ORDER BY Email

//Returns 419

What's the [collation setting on that column](https://learn.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-column-collation?view=sql-server-2017)? — stuartd, Jun 19 '18 at 17:04
There may be an error in the ID number. You linq is looking at the ID number while the SQL is looking at the "TeamGuid" name. — jdweng, Jun 19 '18 at 17:05
@jdweng, I added Team Guid as a place holder, let me edit to display a fake guid — kpolewaczyk, Jun 19 '18 at 17:20
LINQ uses SQL to handle the query - you can remove the `ToList` and do a `ToString` to see the SQL, or use LINQPad, or debug the SQL engine. Is it possible your SQL returns two rows with `NULL` email addresses? — NetMage, Jun 19 '18 at 17:25
What happens if you remove the `ToLower()` in the Linq statement ? — sgmoore, Jun 19 '18 at 17:43
@NetMage, there are no null email addresses in the result set, both were emails with accents. — kpolewaczyk, Jun 19 '18 at 17:57
Apparently it was all in my head. If you change SQL to `GROUP BY LOWER(Email)` does that matter? — NetMage, Jun 19 '18 at 18:27

score 2 · Accepted Answer · answered Jun 19 '18 at 17:24

2

This is a known issue and the workaround has already been answered -> here and here

You have to explicitly tell it to ignore them.

answered Jun 19 '18 at 17:24

Bactos

1,233
14
26

Since he is using LINQ to SQL, and SQL returns the expected result, neither seems relevant. – NetMage Jun 19 '18 at 17:26

BatmanPriddy · Answer 2 · 2018-06-19T18:48:49.743

This is from the links provided by @Bactos.

You just need to strip out what's called Diacritics, using built in C# normalization and CharUnicodeInfo.

You'll just have to make the call for each email address like so:

var listOfContacts = (from contacts in something
                where contacts.Team.Id.Equals(TeamGuid) && !contacts.Email.Equals(null)
                select new { contacts.Id, EmailAddress = CleanUpText(contacts.Email) }).ToList();

and the method you would need would be as follows:

private static string CleanUpText(string text)
{
    var formD = text.Normalize(NormalizationForm.FormD);
    var sb = new StringBuilder();

    foreach (var ch in formD)
    {
        var uc = CharUnicodeInfo.GetUnicodeCategory(ch);

        if (uc != UnicodeCategory.NonSpacingMark)
        {
            sb.Append(ch);
        }
    }

    return sb.ToString().Normalize(NormalizationForm.FormC).ToLower();
}

Hope that helps!

score 1 · Answer 3 · answered Jun 19 '18 at 17:45

1

Because of the .ToList() in your first LINQ expression, the GROUP BY is being performed within C# on the result of Email.ToLower()

This is not at all the same as the SQL query you give, where the GROUP BY is performed on the original EMAIL column, without the ToLower(). It is not surprising that the queries return different results.

answered Jun 19 '18 at 17:45

antlersoft

14,636
4
35
55

The result difference is greater without the ToLower(). I believe it was 402 linq to 419 SQL. Doesn't SQL automatically ignore case? I tested anyways and added LOWER and it did not change the result set in SQL. – kpolewaczyk Jun 19 '18 at 17:54

score 0 · Answer 4 · answered Jun 19 '18 at 17:35

0

I think you can try to ignore NULL values in the SELECT clause.In your LINQ query you are ingnoring NULLs.

answered Jun 19 '18 at 17:35

ashish

2,028
1
10
6

There are no NULLs in the result set. The two missing have accents. – kpolewaczyk Jun 19 '18 at 18:02

Linq/SQL Returning Different Results

4 Answers4