Server-side Fetchxml returns different results

Question

One of our procedures lets users bulk-insert related records by picking a view and then hitting a ribbon button. The form is saved, a flag is set, and a plugin then does its job.

We are using a subgrid with a view selector to let users pick or create their own views on the fly. Once a view is selected, the number of results (provided is lte 5k) is shown.

When a plugin runs the very same fetchxml server side (Retrieve of userquery or savedquery, then Retrieve + FetchExpression), the results change. We get not only a different number of records but also some records are different.

We concluded that the issue has to do with timezones. Some filters included "on-or-after" operators along with date values. Example:

<filter type="and">
  <condition attribute="modifiedon" operator="on-or-after" value="2011-01-01" />
  <condition attribute="modifiedon" operator="on-or-before" value="2011-12-31" />
</filter>

The plugin ran as admin. Changing the plugin user has no effect - as if the current user timezone is not considered when pulling out records from the CRM using a FetchExpression.

How can I ensure that a fetchxml expression returns the same results client-side and server-side?

Probably related: MSDN thread.

Thanks for your time.

Edit: following Daryl's suggestion, I ran a SQL trace. Results are puzzling. Dates are correctly offset for client-side queries (ran from CRM, i.e. Advanced Find) - this means the fetchxml is correctly translated using the user's timezone settings. This does not happen for the same query, server-side; the output SQL contains the date filters "as-is", with no timezone offset. I assumed the same translation happened no matter the origin of the query execution context.

Edit 2: A flag in an hidden region of code (my last debugging resort) was preventing the plugin from instantiating the service in the running user's context. Everything runs fine now. Thanks everyone for your time and your help, it's much appreciated.

Answer 1

When working with dates, always remember to convert to utc since that is how CRM stores them in the database.

The native CRM Advanced find is going to look at whatever the current user's time zone is, and convert that whatever time they enter into the advanced find to UTC before performing a SQL query. Your plugin control will need to do the same thing. These are the steps you'll need to perform before putting the criteria in the Fetch Xml / Linq Expression / Query Expression.

1. Get the user's UserSetting.TimeZoneCode via their SystemUserId.
2. Lookup the TimeZoneDefinition.StandardName for the TimeZoneCode from step 1
3. Call TimeZoneInfo.FindSystemTimeZoneById() passing in the Standard Name from step 2 (you can combine steps 1 and 2 into a single query, but I prefer to cache the results of step three using the input from step 1 for a slight performance improvement. ie. use a dictionary with the TimeZoneCode as the key and the TimeZoneInfo as the value)
4. Use this function to get the UTC value for the time that you're going to use in your plugin query:

---

public static DateTime ConvertTimeToUTC(DateTime time, TimeZoneInfo timeZone)
{
    if (time.Kind != DateTimeKind.Unspecified)
    {
        // If the DateTime is created with a specific time zone(ie DateTime.Now), getting the offset will
        // blow chow if it isn't the correct time zone:
        // The UTC Offset of the local dateTime parameter does not match the offset argument.
        //Parameter name: offset

        // This quick check will recreate the serverLocal time as unspecified

        time = new DateTime(
            time.Year,
            time.Month,
            time.Day,
            time.Hour,
            time.Minute,
            time.Second,
            time.Millisecond);

    }
    var offest = new DateTimeOffset(time, timeZone.GetUtcOffset(time));
    return offest.UtcDateTime;
}