Working with Azure Search in C#.NET

Standard

What is Azure Search

Azure Search is a service in Microsoft Azure Cloud Platform providing indexing and querying capabilities. Azure Search is intended to provide developers with complex search capabilities for mobile and web development while hiding infrastructure requirements and search algorithm complexities.

I like the idea of having an index service provider being part of my solution as it allows developers to perform searches quickly and effortlessly without going through the pain of writing essay-length SQL query.

The key capabilities of Azure Search include scalable full-text search over multiple languages, geo-spatial search, filtering and faceted navigation, type-ahead queries, hit highlighting, and custom analyzers.

azure-search-daniel-foo

Why Azure Search (or index provider in general)

  1. It’s Brilliant – Having an index provider sitting in between application and database is a great way to shield the database from unnecessary or inefficient Read requests. This allows database I/O and computing power to be reserved for operations that truly matters.
  2. It’s Modern – Traditional search approach requires developers to write essay-length SQL query to retrieve simple aggregated data. Index provider such as Azure Search allows developers to initiate a search request without writing complicated search algorithm (for example geo-spatial search) and no complex SQL query is required (for example faceted navigation).
  3. It Scales – Solutions that gain the most benefited from index provider are relatively larger enterprise system. Such system often require scaling to certain extain. Scaling Azure Search in Microsoft Azure Cloud Platform is several clicks away compared to having an on-premise service provider such as Solr.
  4. It’s FREE – Microsoft Azure provides a free tier for Azure Search. Developers can create and delete Azure Search service for development and self-learning purpose.

Use Case

A car classified site is a good example to make use of index service provider such as Azure Search. I will use a local car classified site, Carlist.my to illustrate several features the system can potentially tap into.

Type-ahead Queries allow developer to implement auto suggestions as user types. In the following example, as I was typing “merce”, the site returns me a list of potential Mercedes-Benz car model that I might be interested in.

type-ahead-queries

Facet allow developers to retrieve aggregated data (such as car make, car model, car type, car location, car price range, etc) without writing writing complex SQL query, which also means saving the Read load at database. In the following example, the site returns me a list of Mercedes-Benz model and the count in the bracket to indicate how many classified ads are available for the specific model.

facet

Filter allows developer to retrieve documents that fit the searching criteria without writing complex SQL queries with endless INNER JOIN and WHERE clauses. In the following example, I specified that I want all the used Mercedes-Benz, model of E-Class E200, variant of Avantgarde from Kuala Lumpur with the price range of RM100,000 to RM250,000. You can imagine the kind of INNER JOIN and WHERE clauses the poor developer has to design dynamically if this were to be retrieved from a database directly.

filter

Another feature the car classified site can potentially tap into is Geo-spatial Search although it is not seen implemented. For example if I were to search for a specific car in a dealership, the portal can suggest similar cars from other dealerships nearby to the dealership I’m looking at. That way when I make a trip to visit a dealership, I can also visit other nearby dealerships that have the similar cars.

Using C#.NET to work with Azure Search

Let’s roll our sleeves and hack some codes. I will be using a C#.NET console application to illustrate how we can design and create an index, upload documents into the index and perform several types of searches on the index. This solution will be used to simulate some of the potential codes required by a car classified portal.

First, we create a solution name AzureSearchSandbox.

We will need “Microsoft.Azure.Search” NuGet package from NuGet.org. Open your Package Manager Console and run the following command:

Upon successful installation, you will see several NuGet packages are added into your packages.config file in your solution.

Note that you will only need to install “Microsoft.Azure.Search”, the other packages are dependencies. The dependencies are resolved automatically. 

In your solution, add a new class Car.cs

This Car object will be used to represent your Azure Search document.

Next we create a static Helper.cs class to take care of the initialization of the index in Azure Search.

Initialize() is the main method to kick start our Azure Search index. Unlike other on-premise index service that require certain amount of setup such as Solr, it doesn’t take long to have our index up and running. In Solr, I have to install Solr using NuGet, install the right Java version, set the environment variable and finally create a core in Solr admin portal. With Azure Search, no upfront set up is required.

The index is created in CreateIndex() method, where we tell Azure Search client SDK that we want an index with the fields we define in our Index object.

To ensure this set of code is running on a fresh index, we have DeleteIfIndexExist() method to ensure the previous index is removed. We call this right before CreateIndex() method.

Next, we add a new class Uploader.cs to deal with the documents we are about to upload into our Azure Search index.

PrepareDocuments() is a simple method to construct a list of dummy Car object for our searches later on.

Upload() method gets the dummy Car objects from PrepareDocuments() and pass these object into Azure Search client SDK to upload the documents into index in a batch. Note that we added a 2000 millisecond sleep time to allow our service to upload and process car documents properly before moving on to next part of the code, which is search. However in practical sense, we would not want to add sleep time in our upload code. Instead, the component that takes care of searching should expect index is not available immediately. We also catch IndexBatchException implicitly to handle the index in case the batch upload of index failed. In this example, we merely output the index key. In practical sense, we should implement a retry or at least logging the failed index.

Once the index upload operation is completed, we will add another class Searcher.cs to take care of the searching capability.

SearchDocuments() method is to handle the searching mechanism on the index we created earlier. No fancy algorithm, only passing specific instruction to Azure Search client SDK on what we are looking for and display them. In this method, we take care simple text search, filter and facets. There are much more capabilities Azure Search client SDK can provide. Feel free to explore the SearchParameters and response object on your own.

Putting them all together in Program.cs

First we define index service name and API key to create a search index client instance. The instance is returned by Helper.Initialize(). We will make use of this instance for both search and upload later.

After initializing the index, we call Upload() method to upload some dummy car documents to the index.

Next, we perform the following searches:

  1. Simple text search. We will search for the text “Perodua” in the documents.

The result as following. Azure Search index returns 2 documents which contains the keyword “Perodua”

perodua

2. Using Filter. A more targeted and efficient approach to look for documents. In the following example, first we look for Category field which is equal to ‘Hatchback’; second we look for Price field which is greater than 100,000 and is a ‘Sedan’ category. More details on how to write expression syntax in Azure search.

The result as following: Car with the category of Hatchback and car cost more than 100,000 and is a Sedan.

filter

3. Searching facets. With facets, developer will no longer need to write long query that combines Count() and endless GROUP BY clauses.

If we have a traditional database table that represent the dummy car data, this is equivalent to “SELECT Category, Count(*) FROM Car GROUP BY Category”.

Result as following:

facets

This example might not look like a big deal if you have a small data set or simple data structure. Facet is super handy and fast when you have large number of data and when your query is getting more complex. The ability to define which facet is required in C# codes make the”query” much cleaner and easier to maintain.

One last thought…

You can clone the above source code from my GitHub repo. I will be happy to accept Pull Request if you want to demonstrate how to make use of other capabilities in Azure Search client SDK. Remember to change the API key and service name before compiling. Have fun hacking Azure Search!

Facebook Feed Widget in Sitecore Habitat

Standard

In the previous post we have a introductory view on what Sitecore Habitat is. In this post we will get our hands dirty creating a Facebook Widget in Sitecore Habitat.

Out of the box, Sitecore Habitat contains number of Features. One of the Features is “Social” and we will use Sitecore.Feature.Social project in Habitat solution for Facebook Feed Widget. Before we get started, let’s examine what is available in Sitecore.Feature.Social project.

social

From here we can see that Social comes with certain implementation for Twitter feed. If we visit our Habitat instance, we can see there is a Twitter feed shows in the following page:

habitat-twitter

From here, we will create a Facebook feed to replace the Twitter feed.

First step, we create a new data template to cater for the fields for Facebook feed.

template

Click on image for full size view

Then, we create an item in Content Editor named FacebookDetails for setting the actual properties for the Facebook feed. In this example, we are using Sitecore Facebook page for demonstration purpose.

Facebook-item

Click on image for full size view

The complete properties are as following:

FacebookUrl: https://www.facebook.com/Sitecore
Type: timeline
Width: 350
Height: 600
Hide Facebook Cover: [unchecked]
Hide Face Pile: [checked]
Title: Sitecore Facebook Feed

Type is a Droplist. Facebook feed supports multiple types, namely timeline, events and messages. By having Type as a Droplist, user can select which kind of feed to fetch from Facebook. However for simplicity sake, you can also configure your Type as a Single-Line Text. Doing that you will have to type in the Type field yourself each time you were to change the feed type. Not a show stopper, but something nice to have 🙂

Next, we will move to Visual Studio to tie the fields to a FacebookFeed View that we are about to create.

Look for Template.cs under Sitecore.Feature.Social project. Add the following struct into the class. Do note that the GUID in your instance will be different from the GUID displayed here if you are creating the fields by yourself. Hence, remember to replace the GUID in these properties. If you installing the Sitecore package provided at the end of this post, the GUID will remain the same.

This is the code that “tie” the fields we created earlier in Content Editor. The next thing we will do is to create a view that consume these properties’ GUID.

Add a new FacebookFeed View in Sitecore.Feature.Social project under Social folder.

FacebookFeed

Add in the following code to construct a Facebook Feed widget.

Note all the HTML attributes are the fields we created earlier in data template and the values are the fields we filled in FacebookDetail item in Content Editor.

Now, compile the solution (or project). Ensure that FacebookFeed.cshtml and Sitecore.Feature.Social.dll are copied over to your Sitecore instance. Again, if you are installing the package provided at the end of this article, you will not need to copy the files over.

At this point, we have successfully created created a Facebook Feed Widget. However the widget has not been configured to display on any page on our instance. Now let’s create and configure the necessary items in Content Editor for our Facebook Feed Widget.

Create a view rendering item to represent the View (.cshtml) we have created earlier.

Click on image to view full size

Click on image to view full size

Add FacebookFeed rendering view into Layout that we want to display the View from. For simplicity sake, we use “col-wide-1” as this layout is readily available on Social page.

col-wide-1

Click on image to view full size

Next, we will now move to Experience Editor to replace the Twitter Feed with Facebook Feed Widget we have just created.

Navigate to Social page using Breadcrumb and remove the existing Twitter Feed.

Remove-twitter

Add the new Facebook Feed into the page. Check the “Open the Properties dialog box immediately” option:

Add-Rendering

Click on Browse under Data Source:

Browse-DataSource

Select FacebookDetails and click OK

Associate-Content

On your browser, navigate to http://habitat.local/en/Modules/Feature/Social and Facebook Feed Widget will appear in your page:

Facebook-Feed-Widget

Obviously, this is the most perfect Facebook Feed Widget we can build (just kidding!). You are welcome to modify any steps to cater for your own Sitecore instance.

The objective of this article is to guide you through the steps in creating a Facebook Feeds Widget in Sitecore Habitat. If you would like to have a Facebook Feeds Widget in Sitecore Habitat without going through (most of) the steps, feel free to download and install the ready made package.

Download Sitecore-Habitat-Facebook-Feed-Widget package.

Json Data in SQL Server

Standard

The rise of NoSQL database such as mongoDb is largely due to the agility to store data in non-structured format. A fixed schema is not required like traditional relational databases such as SQL Server.

However, NoSQL database such as mongoDb is not a full-fledged database system. It is designed for very specific use cases. If you don’t know why you need to use NoSQL in your system, chances are you don’t need to. For those who find it essential to use a NoSQL database, often they only use NoSQL database for certain portion of their system and then use another RDBMS for the remaining part of their system that have more traditional business use cases.

Wouldn’t it be nice if RDBMS is able to support similar data structure – having the ability to store flexible data format without altering database tables?

Yes, it is possible. For years, software developers have been storing various JSON data in one table column. Then, developers will make use of library such as Newtonsoft.Json within the application (data access layer) to deserialize the data to make sense out of the JSON data.

Reading / Deserializing JSON

This works. However “JsonConvert.DeserializeObject” method is working extremely hard to deserialize the whole JSON data to only retrieve a simple field such as Name.

Imagine there is a requirement for searching certain Genres on a table that has 1 million row of records, the application codes will have to read 1 million row of records, then perform filtering on the application side. Bad for performance. Now imagine if you have a more complex data structure than the example above…

The searching mechanism will be much efficient if developers can pass a query (SQL statement) for database to handle the filtering. Unfortunately SQL Server does not support querying JSON data out of the box.

It is impossible to directly query JSON data in SQL Server until the introduction of a library known as JSON SelectJSON Select allows you to write SQL statement to query JSON data directly from SQL Server.

How JSON Select Works

First you need to download an installer from their website. When you run the installer, you need to specify the database you wish to install this library at:

JsonSelect

What this installer essentially does is to create 10 functions in the database you have targeted. You can see the functions at:

SSMS > Databases > [YourTargetedDatabase] > Programmability > Functions > Scalar-valued Functions

functions

Next, you can start pumping in some JSON data in your table to test it out.

I create a Student table with the following structure for my experiment:

table

In my StudentData column, I enter multiple rows of records in the following structure:

For demonstrating the query purpose, I have entered multiple rows as following:

all-rows

If you want to write a simple statement to read the list of student names in JSON data, you can simply write:

You will get result as following in SSMS:

name

How about more complex query? Does it work with Aggregate Functions?

If you want to find out about how many students come from each city and what is their average age, you can write your SQL Statement as following:

You will get result as following in SSMS:

complex-query

It appears the library allows you to query any JSON data in your table column using normal T-SQL syntax. The only difference is you need to make use of the predefined scalar-valued functions to wrap around the values you want to retrieve.

Few Last Thoughts…

  1. The good about this library is it allows developers to have hybrid version of storage (NoSQL & relational database) under one roof – minus the deserialize code at application layer. Developer can continue using the classical RDBMS for typical business use cases and leverage on the functions provided in the library to deal with JSON data.
  2. The bad about this library is it lacks proven track record and commercial use cases to demonstrate the robustness and stability.
  3. Although the library is not free, the license cost is relatively affordable at $AU 50. However the library is free for evaluation.
  4. SQL Server 2016 provides native support for JSON data. This library is only useful for SQL Server 2005 to 2014 where upgrading to 2016 is not a feasible option.

Dependency Injection for Web Service

Standard

Dependency Injection allows developers to cleanly inject a portion of concrete code implementation into the bigger scope of the system base on certain logical condition. Dependency Injection has been a preferred way of implementing code. In fact, ASP.NET 5 (Visual Studio 2015) supports Dependency Injection as 1st class citizen. Dependency Injection removes the need of having tons of if-else statement in code implementation and keep the codes clean. Some developers implement Dependency Injection even there isn’t such need at the moment as a standard practice because they might be needed in the future. Personally I’d prefer not to implement Dependency Injection unless there is a “good reason” for implementing it.

One of the “good reasons” is while designing a web service. Web service often serve multiple clients having various needs base on certain logical condition. It is web service responsibility to handle the various logic implementation. For example a web service is serving clients from various countries on the same endpoint might need to execute different codes to produce localized result depending on who is triggering the end point.

Consider this simple scenario:

We need to design a WCF web service that serve multiple countries on an eCommerce platform. There is an endpoint to accept a Product Id and the endpoint will return the formatted local price. The local price calculation depend on various factors such as tax, shipping, marketing promotion, and other business consideration to lower or higher the price for each country through custom discount mechanism.

requirement

Here is a good scenario to create a WCF web service that implements Dependency Injection to separate different calculation formula for respective country.

In the sample code, the Dependency Injection library that we are using is WCF (C#.NET) with Autofac.

Create a BaseService class. In BaseService class, we define a static container to register and store a list of logic. In this example, IProduct is an interface that get registered with different logic classes (UsProduct, UkProduct, MyProduct) depending on the logical condition.

We create a BaseService class for this purpose so that all the services in WCF could inherit BaseService class and access to BuildContainer method, which will be common among all services. In the following example, all public API method would be required to build the container to initialize the logic classes.

Base service class

Catalog service class

  1. Create a new Catalog service in WCF.
  2. Add a method GetProductPrice. It has UriTemplate as following which means Country and ProductId are parameters to be constructed in the Url endpoint.
  3. Inherits BaseService. Implements IProduct.
  4. BuildContainer is a method defined in parent class.

Define the respective logic class for each country…

United State product logic class

UsProduct class is also the parent class for rest of the country class. For example, the method GetProductBasePriceById is applicable for all countries implementation, hence this method stays at the parent class (UsProduct) so that it could be accessed by the child classes (UkProduct & MyProduct).

United Kingdom product logic class

UkProduct class inherits UsProduct and implements IProduct. GetProductBasePriceById could be accessed by UkProduct as UsProduct is the parent class.

Malaysia product logic class

MyProduct class inherits UsProduct and implements IProduct. GetProductBasePriceById could be accessed by UkProduct as UsProduct is the parent class.

All the product logic classes implement IProduct interface so that they all could be registered into Autofac container builder. (The following code is part of BaseService class shown earlier)

By registering the interface with the appropriate concrete logic class (base on the logical condition of country), Autofac would build a static container that allows the whole application know which concrete logic implementation to call.

If we put this into test using Postman, we would get the following result

Product price for United State

Country is specified by replacing {Country} parameter to “Us”. Result is returned base on UsProduct logic class implementation.

Us-test

Product price for United Kingdom

Country is specified by replacing {Country} parameter to “Uk”. Result is returned base on UkProduct logic class implementation.

Uk-test

Product price for Malaysia

Country is specified by replacing {Country} parameter to “My”. Result is returned base on MyProduct logic class implementation.

My-test

Some consideration…

  1. Performance for building a container and inject dependency on runtime instead of direct initialization of concrete class. From the above 3 examples, we could see each request completed within 15-16ms. I ran a few more test switching between countries, most of the request completed in less than 20ms, which shows there isn’t any major overhead for builder the container.
  2. What are some other reasons to implement Dependency Injection? Answer is unit test. With Dependency Injection in place, the codes would be much testable. (If you currently have codes that is not testable, consider using Shim under Microsoft.Fakes. However this reason is arguable as we do not necessary need Dependency Injection to test our code. All we need is appropriate interfaces in the codes.

One last thought…

Dependency Injection is a clean way of separating codes implementation which conforms to a standard set of Interface. With Dependency Injection developer no longer need to separate which line to execute using complex if-else statement. It makes the code base much cleaner and easier to implement different codes base on logical condition. The drawback is Dependency Injection makes debugging more complicated as the developer need to first figure out which class has been injected during runtime. It is a recommended approach if you have a standard set of interface but requires different codes to be executed base on logical condition.