I struggled for a while with Linq2SQL to get the date out in UTC. Now, back to ground one again. I hope this is not to painful. I made a simple example. The uncommented code is what I had hoped would work, but it just returns whatever date is in the database. The commented out line is what works in L2S.

                var q = from data in metaData.CodeGenTest
                        let myUtc = data.LoadDateNotNull.ToUniversalTime() 
                        select new MyResultWithDate
                                   {
                                      MyDate = myUtc
                                      // MyDate = new DateTime(data.LoadDateNotNull.Ticks,DateTimeKind.Utc) {worked with L2S}
                                   };

The main issue is: which timezone is used to store the datetime in the database? If you use a given timezone other than UTC, you first have to convert the datetime to that timezone and then to UTC. This is important when you fetch data: if you save it in timezone A and fetch it using timezone B, you get skewed results. It's therefore recommended to either store times/dates in UTC format, so you can convert them to whatever timezone you have, OR always use the same timezone as the one you used to save it.

Back to your query. You call 'ToUniversalTime', which is a method without mapping, so it has to be ran in memory. To do that best, is inside the projection. So this query does what you want:

var q = from o in metaData.Order
        where o.OrderDate != null
        select new { DBDate = o.OrderDate, UTCDate = o.OrderDate.Value.ToUniversalTime() };

Though as I said above, this converts the datetime in the DB first to the local timezone of the fetch code, then it converts THAT result to UTCDate. If the local timezone of the fetch logic could be different from the local timezone of the save logic, you'll get a different date.

OK, so then this seems to work:

                var q = from data in metaData.CodeGenTest
                        let myUtc = data.LoadDateNotNull.ToUniversalTime() 
                        select new MyResultWithDate
                                   {
                                       MyDate = data.LoadDateNotNull.ToUniversalTime() // according to Frans
                                      //MyDate = myUtc
                                      // MyDate = new DateTime(data.LoadDateNotNull.Ticks,DateTimeKind.Utc) {worked with L2S}
                                   };

I think I follow what you are saying. We always store our dates in UTC time and then convert back to the local timezone based on what the person has set in their web app's profile (we maintain that). That way our time is store absolute.

Thanks for the help

sort of related to this is I have a timezone column named LoadDate_tz,

LLBLGen Pro keeps changing it's name to LoadDate_tz. I've set all the options I could fid in preferences that referred to camel casing, but catalog refresh does not seem to fix the issue.

Continuing to struggle with the LoadDate_Tz problem. The database column is LoadDate_tz. I've tried dropping the table from the designer and refresh it, tried renaming with right mouse button, and it seems to still stay at Tz, not tz.

What am I missing?

Thanks

You should set EnforcePascalCasingAlways to false in project properties and manually rename it.

I had it set to false. When I rename it, it does not change. If I rename it to: LoadDate_tz_ab, it even renames that to LoadDate_Tz_Ab.

Somehow, it does not seem to be respecting EnforcePascalCasingAlways

pkellner wrote:

I had it set to false. When I rename it, it does not change. If I rename it to: LoadDate_tz_ab, it even renames that to LoadDate_Tz_Ab.

Somehow, it does not seem to be respecting EnforcePascalCasingAlways

Sorry, my bad. You also have to set MakeElementNamePascalCasing to false. Otherwise that setting is taking over.

I could successfully rename an entity to Foo_bar after I had set both to false.

MS guidelines suggest that '_' are removed and Pascal casing is used for element names, hence the focus on that naming style.

Thanks, that worked.

Back to my original DateTime UTC Problem.

I'm trying to set either null or the UTC date to a result and getting the error: "The parameter at position 2 is of an unsupported type: Call". I've got the L2S working code below it.

Here it is:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ThreePLogic.Data.ThreePLogicAccessLLBL.DatabaseSpecific;
using ThreePLogic.Data.ThreePLogicAccessLLBL.EntityClasses;
using ThreePLogic.Data.ThreePLogicAccessLLBL.Linq;

namespace ConsoleTestApp
{
    class Program
    {
        public class MyResultWithDate
        {
            public DateTime? MyDate { get; set; }
            public DateTime? MyDateNotNull { get; set; }  // yes, I know this is DateTime? and not DateTime. It is what I need.
        }

        static void Main(string[] args)
        {

            using (var adapter = new DataAccessAdapter())
            {
                adapter.ConnectionString = "Data Source=.;Initial Catalog=3plogic ;Persist Security Info=True;User ID=3plogic ;Password=xx";
                               var metaData = new LinqMetaData(adapter);

                var q = from data in metaData.CodeGenTest
                        select new MyResultWithDate
                                   {
                                       MyDateNotNull = data.LoadDateNotNull.ToUniversalTime(),

                                       MyDate =
                                           data.LoadDate.HasValue
                                               ? (data.LoadDate.Value).ToUniversalTime()
                                               : (DateTime?)null
                                       // L2S:  LoadDate = myData.LoadDate == null ? null :  (DateTime?) new DateTime(myData.LoadDate.Value.Ticks,DateTimeKind.Utc),
                                   };
                var r = q.ToList();
        }
    }
}

This actually works, but it's very awkward. is there a better solution that I can do without the let statement?

var q = from data in metaData.CodeGenTest
                        let d = data.LoadDate.Value.ToUniversalTime()
                        select new MyResultWithDate
                                   {
                                       MyDateNotNull = data.LoadDateNotNull.ToUniversalTime(),

                                       MyDate =
                                           data.LoadDate.HasValue
                                               ? (DateTime?)(d)
                                               : (DateTime?)null

                                       // L2S:  LoadDate = myData.LoadDate == null ? null :  (DateTime?) new DateTime(myData.LoadDate.Value.Ticks,DateTimeKind.Utc),
                                   };

The 'ToUniversalTime' isn't mapped to a db function, which causes this problem. You can map this function using a custom function mapping and pass it to LinqMetaData, but there are other ways to solve this in this case: As the dates are already stored in UTC format, you'll get UTC date/time values from the db (as the query simply reads the values as they're coming from the db). What I find a little strange is that the let using statement doesn't fail, but when you use it in the case statement it does. I'll check that out.

To solve this more elegantly, you should use a static method which is ran during projection of the resultset:

public static class MyDateTimeFunctions
{
    public static DateTime? ConvertToUTC(DateTime? toConvert)
    {
        return toConvert.HasValue? toConvert.Value.ToUniversalTime():toConvert;
    }
}

and the query:

var q = from data in metaData.CodeGenTest
        select new MyResultWithDate
                 {
                     MyDateNotNull = MyDateTimeFunctions.ConvertToUTC(data.LoadDateNotNull),
                     MyDate = MyDateTimeFunctions.ConvertToUTC(date.LoadDate)
                 };

Of course, an extension method is also possible here but that would be a bit confusing with ToUniversalTime...

(edit): the ToUniversalTime is done in-memory if the call isn't already inside a DB function call. The ?: statement you have in your projection is a CASE statement which is constructed in a DBFunction call, so the arguments of that statement, which all runs inside the DB, have to be convertable to elements which can be passed to the DB, so the ToUniversalTime call can't be ran in-memory as it's INSIDE a case statement. As there's no mapping to convert ToUniversalTime to a db construct, it runs into a problem. As L2S does things a little differently, it might be they don't run into this problem (or they added a mapping for ToUniversalTime, don't know), but in our system, due to the mix of in-memory code and db oriented code, it's not going to work out.

However the function I showed above solves it properly