Linq-To-Sql issue with datetime?

Question

Ok, I'm using SQL Server Express 2008 and .Net 3.5 (c#)

I have a couple of datetime fields in the database and am trying to make an edit to a row (Using Linq-to-Sql) I receive the error "Row not found or changed."

I have spent some time getting the generated SQL and it seems that the issue is caused by the milliseconds attached to the datetime.

Generated SQL that does not work,

 @p5: Input DateTime (Size = 0; Prec = 0; Scale = 0) [20/10/2009 16:04:45]
 @p6: Input DateTime (Size = 0; Prec = 0; Scale = 0) [23/10/2009 10:15:36]
 @p7: Input DateTime (Size = 0; Prec = 0; Scale = 0) [23/10/2009 09:27:27]

AND ([SignUpDate] = @p5) 
AND ([LastActivityDate] = @p6) 
AND ([LastLoginDate] = @p7)

if i modify it myself like so it works,

 @p5: Input DateTime (Size = 0; Prec = 0; Scale = 0) [20/10/2009 16:04:45.390]
 @p6: Input DateTime (Size = 0; Prec = 0; Scale = 0) [23/10/2009 10:15:36.733]
 @p7: Input DateTime (Size = 0; Prec = 0; Scale = 0) [23/10/2009 09:27:27.747]

AND ([SignUpDate] = @p5) 
AND ([LastActivityDate] = @p6) 
AND ([LastLoginDate] = @p7)

What are my options in ways to get arround this?

Just to add this is my edit code,

var UserToEdit = this.GetUser(UserId);

UserToEdit.Forename = Fields["Forename"];
UserToEdit.Surname = Fields["Surname"];
UserToEdit.DateOfBirth = Convert.ToDateTime(Fields["DateOfBirth"]);
UserToEdit.DisplayName = Fields["DisplayName"];
UserToEdit.TelephoneNumber = Fields["TelephoneNumber"];

_db.SubmitChanges();

Answer 1

where foo.SignupDate >= signUpDate
  && foo.SignUpDate < signUpDate.AddSeconds(1)
  && foo.LastActivityDate >= lastActivityDate 
  && foo.LastActivityDate < lastActivityDate.AddSeconds(1)
  && foo.LastLoginDate >= lastActivityDate 
  && foo.LastLoginDate < lastActivityDate.AddSeconds(1)

Answer 2

Since the only difference in your example is milliseconds then I would use SQL Profiler to determine if the original Select return milliseconds. Then see if you can fix that issue. It seems that the row data contains milliseconds but your select is not returning them.

After you do that and you still have the issue we can see what the next step will be.

Answer 3

See this link,

http://stackoverflow.com/questions/805968/system-data-linq-changeconflictexception-row-not-found-or-changed

# High precision datetime fields are used. The solution is to set

UpdateCheck to never for that column your DBML file

This has resolved my issue but feel a bit like a hack.

I'm leaving this open to see what others think.