Hello folks,

I have been working on Business Connectivity Services with SQL tables since a while. And there has been a requirement, where Three Tables are linked through a primary key - foreign key relationship. These tables are to be searchable. But the specific requirement here is that when searched for content in the child tables, the parent item related to it should be displayed on the search result page instead of the child table parent entity.

I questioned my client why would you want to do that way. He replied " Consider the child tables are paragraphs in a document, when searched for content within it, i should be able to see the link to the document , but need not be each paragraph". Which makes sense to me. After a little search, mails could figure out that this is very much possible and can be achieved through "Associations as Attachments".

So here is how we did it.

As i said there are three tables. Table 1 (Cars) , Table 2 ( Parts) Table 3 (Issues)

Here, as per the SQL Table 1 is directly linked to Table 2. Table 2 is linked to Table 3. But there is no direct association between Table 1 and Table 3.

BCS Association is implemented between Table 1 and Table 2, a Custom implementation.

To ensure, that Table 1 Item be displayed on the search results when searched for Content within Table 3, a Foriegn-key less association is made. By saying that, i mean i have implemented a custom stored procedure to read the content from Table 3 which are linked to Table 1 through Table 2. Using Joins.

Now to ensure that, the association acts to make the child table as an attachment , here is what need to be added in the method instance of the association.

<MethodInstances>
<Association Name="PartItemToRevisionItem" Type="AssociationNavigator" ReturnParameterName="revisionAssociationItemList" IsCached="false">
<Properties>
<Property Name="AttachmentAccessor" Type="System.String"></Property>
</Properties>
<SourceEntity Name="PartItem" Namespace="MultiBCS.MultiEntity" />
<DestinationEntity Name="RevisionItem" Namespace="MultiBCS.MultiEntity" />
</Association>
</MethodInstances>

This specific tag ensures that the child item be crawled as an attachment.

I will be writing a complete post on creating a BCS Association for search sooner.

I Thank Joseph who is my lead who have given me enough opportunity to research and execute , Scot Hilier a MVP who gave the right pointer to start with.

Thank you. Happy coding .