4.0.13.523 SD.LLBLGen.Pro.DQE.SqlServer.dll 4.0.13.0725 SD.LLBLGen.Pro.ORMSupportClasses.dll 4.0.13.406 SD.LLBLGen.Pro.ORMSupportClasses.Web.dll DotNet 4.0 vs2010 project Adapter template SQL Server 2008 R2

I have a timing issue that I want to track down. A pair of tables that need to be fetched together both have a [DATETIME] column in the primary key.

This works fine as long as the datetimes used are not the same minute. i.e. if I wait a minute between each save, when I next fetch the collection it works. If I save twice in a minute it will only fetch one of the rows in the prefetch. So saving and fetching the top level enities are working as expected.

Somewhere this datetime is being rounded or used to the minute to see how to create the sql to get the rows needed in the prefetch path. So if there are two top level rows in the collection, each with different dates but at the same minute, the prefetch calculation thinks that only one row needs to be fetched in the prefetch.

To make things more confusing, the sql generated contains the full datetime down to the milliseconds.

Any ideas on what could be going wrong?

milliseconds in the DB are rounded off to I think 300ms per datetime, so it might be the values in the DB are actually different. If you want to store time+date as a value which is close to the millisecond, consider storing DateTime.Ticks instead. It's an int64, which is much more precise.

I think that's the issue related to this. DateTime with time values is a pain with predicates, it might look like it should fetch some rows, but due to the rounding of the milliseconds, it won't.

Does that mean I change the column in the database to be long or datetime2 then convert back to DateTime when I need as a date in the application?

Deleted and recreated entity and all is good

Does that mean I change the column in the database to be long or datetime2 then convert back to DateTime when I need as a date in the application?

Yes, or a dateTimeOffset.

Just an update.

Because in our real world, this datetime is rarely if ever updated twice in the same minute (unlike in tests) I have decided to go the other way and use granularity of a minute as enforced by smalldatetime in t-sql, rather than working with milliseconds or less.

To cover the occasional incident where the time would create a duplicate row, I have tested and added a minute where appropriate. On tests, it can sometimes look like we are working in the future, but that doesn't actually matter. This is working fine in all scenarios now.

Thanks for all the help and suggestions. I probably wouldn't have got to a sensible solution without them:-)