Query disable expand surrogate foreign keys

Hello,

Is there a way to disable a query in AX from expanding the surrogate keys. It joins in too many tables, that I dont really need at all.

In Aif Services, there is a function expandSurrogateForeignKeys(), to do just that.

Can you give us an example of the expansion you want to prevent?

I have surrogate key fields (RecId fields) in my query and I have joined a total of 7 tables, with 10 joins, but AX 2012 tells me that there are over 99 joins in my query and if I look at the query string, then I see a lot of tables there that are not in the AOT query.

Sorry, this is not enough information for me to understand the problem. I have no idea where are you using the query, whether it’s really related to foreign keys and not table inheritance etc.

Either wait for somebody else or give me a better description of what you’re doing.

Hi Martin,

I have the same experience in AX 2012 R2 CU7. I create an AOT query in which I have PurchLine table as datasource. When I display the SQL statement of the query, I see that AX joined automatically other tables to the select. I guess it is based on foreign key. It has an advantage e.g. in a report, that fields (maybe only replacement keys) from the automatically joined tables can be used on the report.

Below see the query with only PrchLLine table as datasource, part of its select statement and result in Visual Studio. As you can see, the DirPartyTable.Name field was automatically offered for the Requester id in the PurchLine.

Static query:

5657.AOTQuery.JPG

Extract from SQL statement:

AX SSRS report query:

Hello,

I actually found a solution here.

If you are creating a service as I am, you have 2 methods

  1. expandSurrogateForeignKeys - Creates foreign key relationship based joins in the query (the problem being that AX expands all surrogate foreign keys and thus joins a plethora of tables). This can be turned off but in case you DO want to expand a few foreign keys, so you can get the real values of say a Worker instead of the workers RecId, you can always leave it turned on and modify function 2).

  2. AxdBase.isExpandableSurrogateForeignKey - This method checks on every surrogate foreign key, if it is expandable. You can easily make a switch here, to only expand the surrogate foreign keys you want, so to keep the query small and get all the info you want.

Karl

@Karl: I’m glad to hear that you’ve found a solution, although I still don’t understand what you needed. You said on the beginning that “In Aif Services, there is a function expandSurrogateForeignKeys(), to do just that”, which seemed to suggest that you were already aware of expandSurrogateForeignKeys and that your question wasn’t about AIF services.

@Lukas: So you’re talking about SSRS data source, not just about queries in AX, correct?