0

I have found that LINQ to Entities needs a primary key on the table in order to return correct results. Without it, I get the expected number of rows but including duplicates (and accordingly, missed rows). This problem is described here and here, and I consider it to be a bug.

In one of my tables, each row is unique but I cannot create a compound key across all fields because nullable columns cannot be used in primary keys (again, I consider this a SQL Server limitation).

So... how can I get correct results when selecting from this table using LINQ to Entities? I believe the "key" may be to create an "Entity Key" across all columns in the Visual Studio model designer but I'm not sure how to do this. Setting Entity Key = true on nullable columns throws an exception.

At one stage I gave up and added an identity int column with auto-increment enabled and used that as PK, which solved the issue, but I had to throw this out because of the volume of data being deleted/inserted all the time (it's not possible to simply reset the auto-increment counter on a schedule because not all of the rows are deleted, causing clashes).

My last resort will be to add a bigint identity column as PK, get rid of the auto-increment seed value resetter and hope it lasts "long enough" for the life of the application, but I'm not comfortable with this. (Edit: OK... it will last long enough. My main concern is performance)

Community
  • 1
  • 1
Chris B
  • 679
  • 2
  • 13
  • 32
  • Every serious table ***ought to have a primary key*** .... – marc_s Apr 05 '13 at 08:29
  • 2
    A `bigint` identifier, if you are inserting 10,000 rows per second, should last you about 29 million years. Is that long enough? – Tim Rogers Apr 05 '13 at 08:41
  • 3
    I can only feel pity for a programmer living in the year 29,002,013 that needs to rebuild the PK on a 29 million year old database. – Flater Apr 05 '13 at 09:14
  • @Flater thanks, that made me laugh - but the reason I'm not comfortable is the size of the column. I'm concerned about performance. We won't be inserting anywhere near that number of rows per second. – Chris B Apr 05 '13 at 10:01
  • @marc_s I agree, but it's a SQL Server limitation that I can't create one that includes nullable columns. I think it is perfectly valid to have a PK that includes nullable columns. – Chris B Apr 05 '13 at 10:02

0 Answers0