Johnny

When setting an association between entities we use one of the following approaches to get back results:

• DirectoryLink – Using the DirectoryLink approach, if i search for a customer, i will get his entity as result but clicking on the result will take me to the profile page of the entity where i can see all the products he owns.
• AttachmentAccessor – Using the AttachmentAccessor approach, if i search for a customer, i will get it’s parent entities back, meaning the product(s) he/she is related to.

So how do we create this magnificent beast called associated entities? Well, read ahead and find out!

The data source

For the purpose of the tutorial i created a simple database with two tables: Products and Customers.  The tables are connected by the ProductId foreign key. Each product has a one-to-many relation to customers. To download the script that generates the tables and demo data click here.

The BCS model

In order to skip the boring parts of creating the BCS model, its entities and all the type descriptors I’ve created a starter solution that you can download it right here. Open the solution with Visual Studio 2012 and double click the AssociatedBcsModel.bdcm file. You should get the following BCS model: The model already includes the minimum required methods (ReadList and ReadItem) for both entities and a DAL that is used to connect to the database. To simplify things, the connection string is defined within the DAL file using the _connection variable:

private static string _connection = "Server=.;Database=Hippodevs;Trusted_Connection=True";

In a real world application we will probably use the Secure Store to hold this kind of information.

Creating the association

To get things rolling let’s create an association. Right click anywhere on the BCS model area and select Add and then Association:

The Association Editor popup appears. This popup is the key for setting an association:

First, we set the association name. Since we want to keep things simple for the sake of this tutorial, let’s leave the default name. Next, we set the source and destination entities. I mentioned in the beginning of the tutorial that our goal is to have a one-to-many relationship between a product and its customers, so in this case product is our source entity and customer is our destination entity.

Next up is the foreign key association check box. Our relationship is based on foreign key (ProductId) so just leave this check box checked.

Now comes the interesting part: mapping the identifier. This is the step where we tell FAST (or SharePoint 2013 search) which property on our destination entity maps to the identifier of the source entity. In our destination entity we have a product id property (ProductId) which should be matched with the identifier of the source entity, named PrdId. Scroll down the fields list until you spot the following field:

ReadList.returnParameter.CustomerEntityList.CustomerEntity.ProductId

As you can see from the field name, this is the ProductId property of the customer entity. Open the Source ID dropdown next to the field we just spotted and select the one possible option, PrdId. You now mapped the two entities together! Exciting isn’t it?

To keep things simple, leave the names of the methods as they are and click the OK button. Your entities should now look as follows:

Notice the nice line in the middle connecting the two entities? That means they are connected. In addition, each of the entities spots a new method which is used to tell the search engine how to get the list of child (or destination) entities ids that will be called by the crawl process.

Coding the ProductEntityToCustomerEntity method

One of the new methods that got added during the last step is ProductEntityToCustomerEntity. This method is used to tell the search engine the ids of the items we wish to have as the child entities of a specific product entity. The method takes an id as an argument and return an ienumerable of the child entities:

public static IEnumerable<ProductEntity> CustomerEntityToProductEntity(string custId)
{
    throw new System.NotImplementedException();
}

In order to get the list of customers who bought a specific product we set our sql query as follows:

Select ID from Customers where ProductID='id'

Add a new method to the DAL file to handle the call to the SQL server as follows:

public static IEnumerable<CustomerEntity> GetListOfCustomersForProduct(string prdId)
        {
            List<CustomerEntity> Entities = new List<CustomerEntity>();
            SqlConnection EntityConnection = null;
            SqlDataReader SqlReader = null;

            try
            {
                //Connection DB
                EntityConnection = new SqlConnection(_connection);
                //Open COnnection ( will be close at finnaly statement)
                EntityConnection.Open();
                //Declare Sql Command 
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = EntityConnection;
                cmd.CommandText = string.Format("Select ID from Customers where ProductID='{0}'", prdId);
                SqlReader = cmd.ExecuteReader();
                if (SqlReader.HasRows)
                {
                    DataTable dt = new DataTable();
                    dt.Load(SqlReader);

                    foreach (DataRow row in dt.Rows)
                    {
                        Entities.Add(new CustomerEntity() { CustomerId = row["ID"].ToString(), ProductId = prdId });
                    }
                }
                return Entities;
            }
            catch (Exception ex)
            {
                //Write to log
                return Entities;

            }
            finally
            {
                // close reader
                if (SqlReader != null)
                {
                    SqlReader.Close();
                }

                // close connection
                if (EntityConnection != null)
                {
                    EntityConnection.Close();
                }
            }
        }

Go back to the ProductEntityToCustomerEntity and change it as follows so it will call the new method we just created:

public static IEnumerable<CustomerEntity> ProductEntityToCustomerEntity(string prdId)
        {
            return DAL.DAL.GetListOfCustomersForProduct(prdId);
        }

 

Setting the AttachmentAccessor property

When we created our BCS model and its associations we also created two new methods for the association. How does the search engine knows which of those it should call during the crawl process? The answer is simple, by setting the AttachmentAccessor property on the desired association.

Open your BCS model in XML view (right click on it, select Open With… and then XML (Text) Editor). Look for an association named ProductEntityToCustomerEntityAssociationNavigator, which will look as follows:

<Association Name="ProductEntityToCustomerEntityAssociationNavigator" Type="AssociationNavigator" ReturnParameterName="customerEntityList" ReturnTypeDescriptorPath="CustomerEntityList">
    <SourceEntity Name="ProductEntity" Namespace="BcsAssociationDemo.AssociatedBcsModel" />
    <DestinationEntity Name="CustomerEntity" Namespace="BcsAssociationDemo.AssociatedBcsModel" />
</Association>

This is the declaration of the association we created earlier. Notice its type is AssociationNavigator. If we want this to be the association the search engine takes into consideration, all we have to do is add the AttachmentAccessor property to it, as follows:

<Association Name="ProductEntityToCustomerEntityAssociationNavigator" Type="AssociationNavigator" ReturnParameterName="customerEntityList" ReturnTypeDescriptorPath="CustomerEntityList">
                  <Properties>
                    <Property Name="AttachmentAccessor" Type="System.String">x</Property>
                  </Properties>
                  <SourceEntity Name="ProductEntity" Namespace="BcsAssociationDemo.AssociatedBcsModel" />
                  <DestinationEntity Name="CustomerEntity" Namespace="BcsAssociationDemo.AssociatedBcsModel" />
                </Association>

 

The “Secret Sauce”

We are almost ready for the first crawl, but before we do that consider the following: if we leave the BCS model as it is now, the ReadList method of both entities will fire and the search engine will crawl all of the items in both entities. This is exactly what we want for the product entity but no where near what we want for the customers. For customers we only want to crawl the customers that are related to a product.

The ReadList method is mandatory for any BCS entity, so if we want to make sure no customer entity gets crawled without it being associated to a product, all we have to do is change the ReadList method for the customer entity as follows:

public static IEnumerable<CustomerEntity> ReadList()
        {
            //return DAL.DAL.GetListOfCustomers();
            List<CustomerEntity> customers = new List<CustomerEntity>();
            return customers;
        }

To the Crawl-Mobile!

Time for the fun part! Deploy the solution to your local SharePoint environment. Make sure you give the BCS model permissions, as in any BCS solution. Navigate to your FAST Content SSA service and create a new content source based on the line of business (BCS) solution we just deployed. Go ahead and make a full crawl. You should be all set and ready to go in about 3 minutes.

Head back to your FAST Query SSA service and add the newly created productentity.productname crawled property to the Title managed property. Perform another full crawl.

Once everything is set head to your FAST search center (or SharePoint 2013′s search center) and try to search for Playstation 3. You should get the following result:

Nothing unexpected here right? We search for a product, we get the product details from the product entity.

Now, go ahead and search for a customer name, for example Sandra J. Wynn:

We searched for a customer but instead of getting the customer details from its entity – we get the details of its parent entity, a product!

If you map additional crawled properties to managed properties and request them back in the core search results web part settings page – they too will be returned!

Epilogue

The BCS model we created is simple but it defiantly shows the strength of working with related entities. Just imagine how much easier its becoming for you to crawl a complex entity which is connected to many other entities in a one-to-many relationship.

Try to map a crawled property of the customer entity to a managed property and add it to the refinement panel. You can now refine the results (product entities) based on customers properties.

To download the finished solution click here.

 

 

 

Google+