ADO.NET Entity Data Model – Unexpected Number of Rows Affected

I use the Entity Data Model in almost all of my projects now. Major time saver usually, but today I received an error that I hadn’t seen before:

Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

What gives? I am adding an object to a really simple collection using this code:

using (HCEntities context = new HCEntities())
{
 Alert a = new Alert()
 {
  Description = "test",
  AgentGUID = "aaaa",
  Timestamp = DateTime.Now
 };
 context.AddToAlerts(a);
 context.SaveChanges();
}

The Entity Framework is attempting to insert into this table:

CREATE TABLE [dbo].[Alerts]
(
 [ID]           INT IDENTITY(1,1)  NOT NULL,
 [AgentGUID]    VARCHAR (100)      NOT NULL,
 [Timestamp]    DATETIME          NOT NULL,
 [Description]  VARCHAR(500)      NULL
 CONSTRAINT [PK_Alerts] PRIMARY KEY 
 (
  [ID],
  [AgentGUID], 
  [Timestamp]
 )

 CONSTRAINT [FK_Alerts_Agent] FOREIGN KEY ([AgentGUID]) 
 REFERENCES [dbo].[Agent] ([AgentGUID]) 
 ON DELETE CASCADE ON UPDATE NO ACTION
)

Solution

It turns out that the Entity Framework doesn’t like IDENTITY columns participating in a composite primary key. Given that the identity column is unique by definition, a composite key makes no sense in this scenario anyway.

I updated the primary key definition and the problem was fixed. The exception thrown by the framework is fairly cryptic though.

Ancient SQL 2000 Bug

Recently I’ve been running an automated data collection across a couple of hundred SQL Server instances. I hit an issue today when I ran a particular query on some servers running 8.00.760 (SQL 2000 SP3)

select filename 
from sysaltfiles
where name = 'tempdev'

Not very exciting – it returns the path as expected:

E:\MSSQL\DATA\tempdb.mdf

However when my script ran this slight variation of the query above, I got a really strange result:

declare @tmp varchar(255)
select @tmp =filename
from sysaltfiles
where name = 'tempdev'

print @tmp

E:\MSSQL\DATA\tempdb.mdfft SQL Server\MSSQL\DATA\tempdb.mdf

It turns out that when you move tempdb on a server running this build, there was a bug where it didn’t properly clean out the value stored in sysaltfiles. The original value is overwritten, and a null marks the end of the new string. Looks ok in the results pane, but when you print it, it wreaks havoc on your output.

I doubt anyone is still seriously running this build, but there you go. Old school dirty data.