Grouping Linq to Sql vs. Linq to Objects

Question

I have a table in SQL Server 2008:

CREATE TABLE [dbo].[Pages](
    [Id] [uniqueidentifier] NOT NULL,
    [PageName] varchar(400) NOT NULL,
    [Count] int NOT NULL,
CONSTRAINT [PK_Pages] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)) ON [PRIMARY]

And a unique index on that table:

CREATE UNIQUE NONCLUSTERED INDEX [IX_PageName] ON [dbo].[Pages]
(
    [PageName] ASC
)

I get a bunch of page names from a text file which I group in Linq and count the occurrences.

var data = pages.GroupBy(x => x.PageName.ToLower().Trim())
                .Select(x => new Page()
                {
                    Id = Guid.NewGuid(),
                    PageName = x.Key,
                    Count = x.Count(),
                }) 
                .ToList();

I know that grouping in Linq to objects differs from Ling to Sql in that an IEnumerable grouping is case sensitive and an index in T-SQL ignores white space at the end of varchar. That's why I do grouping with ToLower() and Trim() above, but the index exception still fires. I have a lot of non-alpha carachters (forward slashes, backslashes, commas, dots etc) in the page names in the text file but I can't figure out which ones are causing the exception.

EDIT:

Here's the exception I get when debugging.

Cannot insert duplicate key row in object 'dbo.Pages' with unique index 'IX_PageName'.
The statement has been terminated.

SOLUTION:

Thanks to @Panagiotis Kanavos I solved it with the Sql profiler. A couple of the strings had asian letters in the middle of the string, e.g. 嘊 and my collation SQL_Latin1_General_CP1_CI_AS stored them as question marks. Still not sure this was a duplicate question since it had nothing to do with trailing blanks.

So what exception are you actually getting when you try to insert `data`? As you can see from your example, a unique index violation actually tells you the erroneous value! — Jamiec, Oct 04 '16 at 10:54
@Jamiec edited my post with the exception, in visual studio I don't get that value in the exception. In SQL server I do get the value. — Alex, Oct 04 '16 at 11:00
The SQL standard requires padding strings up to their defined lengths before comparison, so the two PageName values are considered equal by SQL Server. Either avoid mixing up padded names, or replace trailing spaces with some other character (ugly) — Panagiotis Kanavos, Oct 04 '16 at 11:05
Have you checked for any *existing* values, with or without trailing spaces? Which values cause the problem? — Panagiotis Kanavos, Oct 04 '16 at 11:09
@Everyone this is not a duplicate, I earlier had an example where I explained that I know that spaces at the end of a string functions differently in T-SQL but that seemed to confuse more than help, so please read the question without that example. — Alex, Oct 04 '16 at 11:09
@PanagiotisKanavos yes I have that in the code but did not include it since this happens already when the table is empty. — Alex, Oct 04 '16 at 11:10
Please post the values. This is a SQL error, not related to LINQ itself. The explanation *is* the duplicate question. Also note that the column's collation matters. If the table uses a case-insensitive collation (most likely), you'll get the same error for `Test1` and `test1` — Panagiotis Kanavos, Oct 04 '16 at 11:14
It's impossible to help without the actual values. The collation would also help. Try logging the generated statements, or use SQL Server Profiler to capture the statements that raise this error — Panagiotis Kanavos, Oct 04 '16 at 11:16
@PanagiotisKanavos yes the collation is case-insensitive, that's why I do ToLower() on all strings before grouping and inserting. The duplicate question addresses the problem of spaces at the end of a string, which I remove before inserting. Sorry but it would be difficult to provide the values since there are tens of thousands of rows in the file. — Alex, Oct 04 '16 at 11:24
Isolate the problem then. You can't fix a value problem without values. How do you insert the values anyway? SQL Server Profiler will have no problem capturing individual INSERT statements — Panagiotis Kanavos, Oct 04 '16 at 11:27

score -1 · Answer 1 · edited May 23 '17 at 12:33

-1

You are getting errors not when grouping but when inserting data into your table. Please refer to this answer: Behavior of unique index, varchar column and (blank) spaces and you will see that you cannot insert data to a column with unique index that only differ by trailing spaces.

edited May 23 '17 at 12:33

Community

1
1

answered Oct 04 '16 at 10:45

Łukasz Trzewik

1,165
2
11
26

sorry if I was unclear, that was just an example that I will get an error if I don't do Trim(), but I do Trim() in the grouping. – Alex Oct 04 '16 at 10:53

Grouping Linq to Sql vs. Linq to Objects

1 Answers1