Here's what happens and some issues I'm having. Some to be expected - some I'm not sure of what the best practice is to handle.

 

If a user in CST enters a date to search on a DATE field in SQL Server, XML serialization picks it up and offsets it one hour so '2011-08-29' becomes '2011-08-29 01:00' when LINQ actually goes to query the database. SQL Server and LINQ in their wisdom decide it's ok for a query against a DATE field to include time and the query returns zero records. Is there a recommended way to handle this?

 

For date/time in general, should I be using the newer date offset data type in SQL Server to store date/time values? If a user enters a time in CST such as '2011-08-29 07:35', when it reaches the server, it becomes '2011-08-29 08:35' and then saves to the db as the last date/time. This is fine until one day our data center moves and is then located in CST. At that point, the db still shows the '08:35' time and XML serialization wouldn't change it back to '07:35'. None of the examples I see show using any other date/time data type, but could you offer your suggestion on how to handle this?

 

Lastly, all of this of course impacts reporting after the fact. So I'm trying to take all this into consideration on how I'm getting data out. We're currently using SSRS, which would run local to the server, so if the data doesn't stay relative to the time zone, it would appear to be off (a CST time converted to EST would look like it was off an hour).

 

What I would prefer to do (maybe not recommended) is keep everything relative to the time zone where data is entered. So if a CST entered time is '2011-08-29 07:35', that's how I'd like to store in the db because if someone in EST looks at a report for CST, they would want to see '07:35' (the time relative to CST in which something happened). This would also solve any reporting problems for our environment. Is there any way to get DevForce (or the XML serializer) to ignore the time zone offset?

 

Thanks for anyone's input and let me know if I can clarify further.

Scott

 

Have you tried setting the DateTimeKind to Unspecified?

 

var date = myDate;
entity.property = new DateTime(date.Year, date.Month, date.Day, 0,0,0, DateTimeKind.Unspecified)

 

Regards,

   Silvio.

 

- When I retrieve data and pass to the client, it has Kind = Unspecified. I'm guessing this is because the SMALLDATETIME field in SQL Server doesn't store time zone information, so somewhere in taking SQL data and creating the entity, it passes that information.

 

- When I add a new record client side, the date defaults to Kind = Local, which allows the problem to occur in the XML serialization. I am able to work around the problem if I intercept every single add/update before it goes to the SaveChanges() call.

 

Maybe I'm out of touch with things, but I see where data binding is being pushed more and more so I attempt to use binding sources and bind to grids and such automatically. Using this approach, when I go to save, I have to interject code that loops through all possible entities and set all the date's Kind value. And just to complicate future development, if I add a field to the database and it happens to be a date field, well, I have to remember to go add some custom code to handle setting Kind.

 

I've Googled and read most all of today and I'm just kind of shocked that either not that many people find this to be a problem or maybe just don't post about it. Even if I could go along with storing the time zone converted date in the database, has no one run into the problem where selecting a date from a calendar control gets time converted in the LINQ query and therefore querying a DATE field (no time here) results in a search for DATE+TIME (no results)?

 

Last time I had to cross time zones in a thick client (I have to support disconnected and other factors), I used .net remoting, which apparently doesn't have the same issues. Seems like all this progress foreward in technology just takes several more steps to complicating development and causing more overhead.

 

Enough babble on my part. :) So do you think the only way to preserve the local entered time is to intercept the data before SaveChanges() is called and set Kind? I haven't built a test SL app for this, but a disconnected SL app would have to run into the same issues (I guess a connected one would also since it executes locally).

 

I couldn't find any other approach. Howerver, rather than intercepting the data before saving, you could use the PropertyInterceptor:

 

 

You can find detailed information about PropertyInterceptors in the http://drc.ideablade.com/xwiki/bin/view/Documentation/property-interceptors - DevForce Resource Center .

 

Regards,

   Silvio.

 

I created the following and put in my DM project, but it didn't have any impact. (Sorry, couldn't figure out how to paste pretty code). I'm assuming I need to inherit the class from somewhere, but I couldn't find anything on your referenced link that said how to actually implement it.

 

public class PropertyInterceptorsDefinitions

{

[BeforeSet]

public static void BeforeSetAnyDateTimeProperty(IEntityPropertyInterceptorArgs args)

{

if (args.Value.GetType() == typeof(DateTime) && !args.EntityProperty.Name.Equals("UpdateDate", StringComparison.OrdinalIgnoreCase))

{

var date = (DateTime)args.Value;

args.Value = DateTime.SpecifyKind(date, DateTimeKind.Unspecified);

}

}

}

 

 

You can add the snippet above to the partial class of the entity you want to the logic to apply.

 

Or, if most of your entities would need such an interceptor, you could create a base class and add the interceptor to it. Beware that I wouldn't use this approach unless there is a considerably large number of properties that would need it (imagine this check being done for every property of every entity...)

 

Even in the snippet I provided, the logic will apply for every property of the entity in question. You could refine it to only trigger upon an specific property change:

 

 

By the way, you can automatically add partial classes and/or a base type by setting it in the edmx properties:

 

 

 

Regards,

   Silvio

Scott