复杂Dynamics CRM 365项目：QueryExpression与FetchXML选型咨询

Hey there, let's break down your options based on your exact scenario—since you're aiming for a quick migration from CRM 4.0 to Dynamics 365 with complex SQL-heavy logic, here's my practical take:

1. Go with FetchXML (It’s Your Best Bet for Quick Migration) #

Your original plugin relies on complex GROUP BY, JOIN, and aggregate operations—FetchXML is the only out-of-the-box querying option in Dynamics 365 that natively supports all these features, which will let you map your existing SQL logic directly without major rewrites.

The lack of built-in debugging is a valid pain point, but there are easy workarounds to fix that:

• Use XrmToolBox’s FetchXML Builder: This is non-negotiable for FetchXML work. It lets you visually build/modify queries, test them against your Dynamics 365 instance in real-time, and even convert between FetchXML, QueryExpression, and SQL. You can immediately validate if your query returns the same data as your original SQL.
• Log FetchXML strings in your ASP.NET app: When your code generates a FetchXML query, write it to your application logs (e.g., Serilog, NLog). You can then copy this string into FetchXML Builder or the Dynamics 365 Advanced Find tool to debug issues.
• Test via Dynamics 365 Web API: Send a POST request to your org’s Web API endpoint with the FetchXML payload (example: POST https://yourorg.crm.dynamics.com/api/data/v9.2/accounts?fetchXml=<your-fetch-string>) and inspect the JSON response to verify results.

2. Why QueryExpression Isn’t Ideal for Your Scenario #

QueryExpression does support basic aggregates, but it gets extremely clunky for complex GROUP BY with multiple joins or nested aggregations. You’d end up writing far more code than necessary, and some of your original SQL logic might not translate cleanly—this will slow down your migration, which is the opposite of your goal. Save QueryExpression for simple, straightforward queries only.

3. Bonus Tips for Migrating Your RDLC Reports & Stored Procedures #

Since your RDLC reports rely on stored procedures and custom SQL views, here’s how to adapt them to Dynamics 365:

• Replace direct SQL calls with FetchXML: In your ASP.NET app, execute FetchXML queries via the Dynamics 365 Organization Service or Web API, convert the results to a DataTable, and pass that to your RDLC report. This eliminates dependencies on SQL Server custom objects and aligns with Dynamics 365’s data access patterns.
• Use Custom APIs for ultra-complex logic: If some of your stored procedures have business logic that can’t be easily translated to FetchXML, wrap that logic in a Dynamics 365 Custom API (either a plugin-based API or an Azure Function-backed API). Your ASP.NET app can then call this API to get the required data, keeping your migration aligned with platform best practices.
• Leverage SQL to FetchXML conversion tools: XrmToolBox also has a tool that converts SQL queries to FetchXML—this will drastically speed up translating your existing report SQL into valid FetchXML.

Quick Migration Action Plan #

1. Use XrmToolBox’s SQL to FetchXML converter to translate your original SQL queries and custom views.
2. Validate each converted FetchXML query in FetchXML Builder to ensure it matches the original SQL’s output.
3. Integrate FetchXML calls into your ASP.NET app using the Organization Service or Web API.
4. Adapt your RDLC reports to use the FetchXML-derived data instead of direct SQL/stored procedures.
5. Test end-to-end, using logging and FetchXML Builder to debug any discrepancies.

内容的提问来源于stack exchange，提问作者autopenta