服务器端Fetchxml返回不同的结果 [英] Server-side Fetchxml returns different results

问题描述

我们的其中一个过程使用户可以通过选择一个视图然后单击功能区按钮来批量插入相关记录.保存该表单，设置一个标志，然后一个插件完成其工作.

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.

我们正在使用带有视图选择器的子网格，以允许用户即时选择或创建自己的视图.选择视图后，将显示结果数(最多5k).

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.

当插件运行完全相同的fetchxml服务器端(检索用户查询或保存的查询，然后检索+ FetchExpression)时，结果将更改.我们不仅获得了不同数量的记录，而且一些记录也有所不同.

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>

该插件以管理员身份运行.更改插件用户无效-好像使用FetchExpression从CRM中提取记录时未考虑当前用户时区.

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.

如何确保fetchxml表达式在客户端和服务器端返回相同的结果?

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

可能相关: MSDN线程.

感谢您的时间.

按照达里尔的建议，我运行了一条SQL跟踪.结果令人费解.客户端查询的日期已正确偏移(从CRM，即高级查找"运行)-这意味着使用用户的时区设置正确翻译了fetchxml.对于服务器端相同的查询，不会发生这种情况.输出SQL包含日期过滤器原样"，没有时区偏移.我认为无论查询执行上下文的来源如何，都会发生相同的翻译.

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.

推荐答案

使用日期时，请始终记住要转换为utc，因为这是CRM将它们存储在数据库中的方式.

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

本机CRM高级查找将查看当前用户所在的时区，并在执行SQL查询之前将其输入高级查找的任何时间转换为UTC.您的插件控件将需要执行相同的操作.这些是将条件放入Fetch Xml/Linq表达式/查询表达式中之前需要执行的步骤.

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. 通过其SystemUserId获取用户的UserSetting.TimeZoneCode.
2. 从步骤1查找TimeZoneCode的TimeZoneDefinition.StandardName
3. 调用TimeZoneInfo.FindSystemTimeZoneById()传入步骤2中的标准名称(您可以将步骤1和2合并为一个查询，但是我更喜欢使用步骤1的输入来缓存步骤3的结果，以提高性能改进.即使用以TimeZoneCode为键，TimeZoneInfo为值的字典)
4. 使用此函数获取您要在插件查询中使用的时间的UTC值:

---

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;
}

这篇关于服务器端Fetchxml返回不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！