MVC CRUD with Elasticsearch child, parent documents

This article demonstrates how to search for data in Elasticsearch using parent and child documents. The documents are all saved inside the same index and the child documents are saved on the same shard using the parent id as the routing. As in the previous ElasticsearchCRUD tutorials, an autocomplete in used to search for the parent documents. When one is selected, the first N child addresses are found and displayed in the jTable table. Address documents can be added, updated or deleted.

Code: https://github.com/damienbod/WebSearchWithElasticsearchChildDocuments

Other tutorials:

Part 1: ElasticsearchCRUD introduction
Part 2: MVC application search with simple documents using autocomplete, jQuery and jTable
Part 3: MVC Elasticsearch CRUD with nested documents
Part 4: Data Transfer from MS SQL Server using Entity Framework to Elasticsearch
Part 5: MVC Elasticsearch with child, parent documents
Part 6: MVC application with Entity Framework and Elasticsearch
Part 7: Live Reindex in Elasticsearch
Part 8: CSV export using Elasticsearch and Web API
Part 9: Elasticsearch Parent, Child, Grandchild Documents and Routing
Part 10: Elasticsearch Type mappings with ElasticsearchCRUD
Part 11: Elasticsearch Synonym Analyzer using ElasticsearchCRUD
Part 12: Using Elasticsearch German Analyzer
Part 13: MVC google maps search using Elasticsearch
Part 14: Search Queries and Filters with ElasticsearchCRUD
Part 15: Elasticsearch Bulk Insert
Part 16: Elasticsearch Aggregations With ElasticsearchCRUD
Part 17: Searching Multiple Indices and Types in Elasticsearch
Part 18: MVC searching with Elasticsearch Highlighting
Part 19: Index Warmers with ElasticsearchCRUD

Setting up the document search engine

AdventureWorks2012 is used to fill the search engine with data. It can be downloaded here.

Here’s the transfer method used (Entity child objects are saved as child documents in the “stateprovinces” index):

public void SaveToElasticsearchStateProvince()
{
	IElasticsearchMappingResolver elasticsearchMappingResolver = new ElasticsearchMappingResolver();
	using ( var elasticsearchContext = new ElasticsearchContext("http://localhost:9200/", new ElasticsearchSerializerConfiguration(elasticsearchMappingResolver,true,true)))
	{
		elasticsearchContext.TraceProvider = new ConsoleTraceProvider();
		using (var databaseEfModel = new SQLDataModel())
		{
			int pointer = 0;
			const int interval = 20;
			bool firstRun = true;
			int length = databaseEfModel.StateProvince.Count();

			while (pointer < length)
			{
				var collection = databaseEfModel.StateProvince.OrderBy(t => t.StateProvinceID).Skip(pointer).Take(interval).ToList<StateProvince>();

				foreach (var item in collection)
				{
					var ee = item.CountryRegion.Name;
					elasticsearchContext.AddUpdateDocument(item, item.StateProvinceID);
				}

				if (firstRun)
				{
					elasticsearchContext.SaveChangesAndInitMappingsForChildDocuments();
					firstRun = false;
				}
				else
				{
					elasticsearchContext.SaveChanges();
				}

				pointer = pointer + interval;
			}
		}
	}
}

More information which explains how to transfer the data can be found here.

The example to transfer can be found here.

Note: When you import data using code first from an existing database in Entity Framework, ElasticsearchCRUD requires that a [key] attribute is added to the child object primary keys. These are used to define the document _id for each document. A document can only have one _id!.

ElasticSearchProvider for child, parent documents

The ElasticSearchProvider class implements the access layer to Elasticsearch. The repository uses ElasticsearchCRUD to access Elasticsearch. The ElasticsearchSerializerConfiguration class needs to be configured for child/parent documents. The default configuration is set for nested documents, so this needs to be changed. I have constructed everything in the default constructor, this could also be used with an IoC using construction injection. The _context has the same life cycle as the class and needs to be disposed.

private const string ConnectionString = "http://localhost:9200/";
private readonly IElasticsearchMappingResolver _elasticsearchMappingResolver;
private readonly ElasticsearchContext _context;

public ElasticSearchProvider()
{
	_elasticsearchMappingResolver = new ElasticsearchMappingResolver();
	_elasticsearchMappingResolver.AddElasticsearchMappingForEntityType(
		typeof(Address), 
		new ElasticsearchMappingAddress()
	);
	_context = new ElasticsearchContext(
		ConnectionString, 
		new ElasticsearchSerializerConfiguration(
			_elasticsearchMappingResolver,
			true,
			true
		)
	);
}

The mapping for an Address type is defined because this is a child document in the parent index ‘stateprovinces’. The default mapping needs to be overrided due to this. The ElasticSearchMapping class can be inherited and only one method needs to be implemented: GetIndexForType. Now the Address Type will be mapped correctly.

using System;
using ElasticsearchCRUD;

namespace WebSearchWithElasticsearchChildDocuments.Search
{
	public class ElasticsearchMappingAddress : ElasticsearchMapping
	{
		// This address type is a child type form stateprovince in the stateprovinces index
		public override string GetIndexForType(Type type)
		{
			return "stateprovinces";
		}
	}
}

This class in then added to the Resolver above in the constructor.

 _elasticsearchMappingResolver.AddElasticsearchMappingForEntityType(
        typeof(Address), 
        new ElasticsearchMappingAddress()
    );

Now the search provide can implement CRUD functions.

public IEnumerable<T> QueryString<T>(string term) 
{ 
        return _context.Search<T>(BuildQueryStringSearch(term)).PayloadResult.Hits.HitsResult.Select(t =>t.Source).ToList();
}

private Search BuildQueryStringSearch(string term)
{
	var names = "";
	if (term != null)
	{
		names = term.Replace("+", " OR *");
	}
	var search = new Search
	{
		Query = new Query(new QueryStringQuery(names + "*"))
	};
	return search;
}

public void AddUpdateDocument(Address address)
{
        // if the parent has changed, the child needs to be deleted and created again. This in not required in this example
	_context.AddUpdateDocument(address, address.AddressID, address.StateProvinceID);
	_context.SaveChanges();
}

public void UpdateAddresses(long stateProvinceId, List<Address> addresses)
{
	foreach (var item in addresses)
	{
		_context.AddUpdateDocument(item, item.AddressID, item.StateProvinceID);
	}

	_context.SaveChanges();
}

public void DeleteAddress(long addressId)
{
	_context.DeleteDocument<Address>(addressId);
	_context.SaveChanges();
}

public List<SelectListItem> GetAllStateProvinces()
{
    var result = from element in _context.Search<StateProvince>("").PayloadResult.Hits.HitsResult.Select(t => t.Source)
    select new SelectListItem
    {
       Text = string.Format("StateProvince: {0}, CountryRegionCode {1}",
                    element.StateProvinceCode, element.CountryRegionCode),
       Value = element.StateProvinceID.ToString()
    };

    return result.ToList();
}

public PagingTableResult<Address> GetAllAddressesForStateProvince(string stateprovinceid, int jtStartIndex, int jtPageSize, string jtSorting)
{
	var result = new PagingTableResult<Address>();
	var data = _context.Search<Address>(
					BuildSearchForChildDocumentsWithIdAndParentType(
						stateprovinceid, 
						"stateprovince",
						jtStartIndex, 
						jtPageSize, 
						jtSorting)
				);

	result.Items = data.PayloadResult.ToList();
	result.TotalCount = data.TotalHits;
	return result;
}

// {
//  "from": 0, "size": 10,
//  "query": {
//  "term": { "_parent": "parentdocument#7" }
//  },
//  "sort": { "city" : { "order": "desc" } }"
// }
private Search BuildSearchForChildDocumentsWithIdAndParentType(object parentId, string parentType, int jtStartIndex, int jtPageSize, string jtSorting)
{
	var search = new Search
	{
		From = jtStartIndex,
		Size = jtPageSize,
		Query = new Query(new TermQuery("_parent", parentType + "#" + parentId))
	};

	var sorts = jtSorting.Split(' ');
	if (sorts.Length == 2)
	{
		var order = OrderEnum.asc;
		if (sorts[1].ToLower() == "desc")
		{
			order = OrderEnum.desc;
		}

		search.Sort = CreateSortQuery(sorts[0].ToLower(), order);
	}
	return search;
}

public SortHolder CreateSortQuery(string sort, OrderEnum order)
{
	return new SortHolder(
		new List<ISort>
		{
			new SortStandard(sort)
			{
				Order = order
			}
		}
	);
}

The _context.Search method accepts any Json string which is directly used in the Elasticsearch Search API. Elasticsearch provides good documentation how this Json query is put together. Because the queries are so simple in this example, I have added them using a StringBuilder. If more complex queries are required, maybe you should use NEST for the search functionality. ElasticsearchCRUD focus is doing CRUD operations, data transfers for simple, nested or parent/child documents easily.

SearchController

Now that the backend is implemented, a search controller needs to be implement for the views. The jTable table accesses the controller directly using ajax requests. The jTable requires that the data is in its required format. The table implements paging and sends also the parentId of the stateprovince document (Used for the address child document routing in Elasticsearch)

using System;
using System.Collections.Generic;
using System.Web.Mvc;
using WebSearchWithElasticsearchChildDocuments.Search;

namespace WebSearchWithElasticsearchChildDocuments.Controllers
{
	[RoutePrefix("Search")]
	public class SearchController : Controller
	{
		readonly ISearchProvider _searchProvider = new ElasticSearchProvider();

		[HttpGet]
		public ActionResult Index()
		{
			return View();
		}

		[Route("Search")]
		public JsonResult Search(string term)
		{
			return Json(_searchProvider.QueryString<StateProvince>(term), "AddressListForStateProvince", JsonRequestBehavior.AllowGet);
		}
    
		[Route("GetAddressForStateProvince")]
		public JsonResult GetAddressForStateProvince(string stateprovinceid, int jtStartIndex = 0, int jtPageSize = 0, string jtSorting = null)
		{
			try
			{
				var data = _searchProvider.GetAllAddressesForStateProvince(stateprovinceid, jtStartIndex, jtPageSize, jtSorting);
				return Json(new { Result = "OK", Records = data.Items, TotalRecordCount = data.TotalCount });
			}
			catch (Exception ex)
			{
				return Json(new { Result = "ERROR", Message = ex.Message });
			}
		}

		[Route("CreateAddressForStateProvince")]
		public JsonResult CreateAddressForStateProvince(Address address)
		{
			try
			{
				_searchProvider.AddUpdateDocument(address);
				return Json(new { Result = "OK", Records = address });
			}
			catch (Exception ex)
			{
				return Json(new { Result = "ERROR", Message = ex.Message });
			}
		}

		[HttpPost]
		[Route("DeleteAddress")]
		public ActionResult DeleteAddress(long addressId, long selectedstateprovinceid)
		{
			_searchProvider.DeleteAddress(addressId, selectedstateprovinceid);
			return Json(new { Result = "OK"});
		}
	}
}

The Razor view is as follows:

@model WebSearchWithElasticsearchChildDocuments.Models.SearchModel

<br/>

<fieldset class="form">
    <legend></legend>
    <table width="500">
        <tr>
            <th></th>
        </tr>
        <tr>
            <td>
                <label for="autocomplete">Search: </label>
            </td>
        </tr>
        <tr>
            <td>
                <input id="autocomplete" type="text" style="width:500px" />
            </td>
        </tr>
        
        
    </table>
</fieldset>

<div id="addressResultsForStateProvince" />
<input name="selectedstateprovinceid" id="selectedstateprovinceid" type="hidden" value="" />

@section scripts
{
    <link href="http://localhost:49907/Content/themes/flat/jquery-ui-1.10.3.min.css" rel="stylesheet" />
    <link href="~/Scripts/jtable/themes/jqueryui/jtable_jqueryui.min.css" rel="stylesheet" />
    <script type="text/javascript">

        $(document).ready(function() {

            var updateResults = [];
            $("input#autocomplete").autocomplete({
                source: function(request, response) {
                    $.ajax({
                        url: "http://localhost:49907/Search/search",
                        dataType: "json",
                        data: {
                            term: request.term,
                        },
                        success: function(data) {
                            var itemArray = new Array();
                            for (i = 0; i < data.length; i++) {
                                var labelData = data[i].Name + ", " + data[i].StateProvinceCode + ", " + data[i].CountryRegionCode;
                                itemArray[i] = { label: labelData, value: labelData, data: data[i] }
                            }

                            console.log(itemArray);
                            response(itemArray);
                        },
                        error: function(data, type) {
                            console.log(type);
                        }
                    });
                },
                select: function(event, ui) {
                    $("#selectedstateprovinceid").val(ui.item.data.StateProvinceID);
                    $('#addressResultsForStateProvince').jtable('load', {selectedstateprovinceid : ui.item.data.StateProvinceID});
                    console.log(ui.item);
                }
            });

                $('#addressResultsForStateProvince').jtable({
                    title: 'Address list of selected StateProvince',
                    paging: true,
                    pageSize: 10,
                    sorting: true,
                    multiSorting: true,
                    defaultSorting: 'City asc',
                    actions: {
                        deleteAction: function (postData, jtParams) {
                            return $.Deferred(function ($dfd) {
                                $.ajax({
                                    url: 'http://localhost:49907/Search/DeleteAddress?addressId=' + postData.AddressID + "&selectedstateprovinceid=" + $('#selectedstateprovinceid').val(),
                                    type: 'POST',
                                    dataType: 'json',
                                    data: postData,
                                    success: function (data) {
                                        $dfd.resolve(data);
                                    },
                                    error: function () {
                                        $dfd.reject();
                                    }
                                });
                            });
                        },
                        listAction: function (postData, jtParams) {
                            return $.Deferred(function ($dfd) {

                                console.log(jtParams);
                                $.ajax({
                                    url: 'http://localhost:49907/Search/GetAddressForStateProvince?stateprovinceid=' + $('#selectedstateprovinceid').val(),
                                    type: 'POST',
                                    dataType: 'json',
                                    data: jtParams,
                                    success: function (data) {
                                        $dfd.resolve(data);
                                    },
                                    error: function () {
                                        $dfd.reject();
                                    }
                                });
                            });
                        },
                        createAction: function (postData) {
                            return $.Deferred(function ($dfd) {
                                $.ajax({
                                    url: 'http://localhost:49907/Search/CreateAddressForStateProvince?stateprovinceid=' + $('#selectedstateprovinceid').val(),
                                    type: 'POST',
                                    dataType: 'json',
                                    data: postData,
                                    success: function (data) {
                                        $dfd.resolve(data);
                                    },
                                    error: function () {
                                        $dfd.reject();
                                    }
                                });
                            });
                        },
                    updateAction: function(postData) {
                        return $.Deferred(function ($dfd) {
                            $.ajax({
                                url: 'http://localhost:49907/Search/CreateAddressForStateProvince?stateprovinceid=' + $('#selectedstateprovinceid').val(),
                                type: 'POST',
                                dataType: 'json',
                                data: postData,
                                success: function (data) {
                                    $dfd.resolve(data);
                                },
                                error: function () {
                                    $dfd.reject();
                                }
                            });
                        });
                    }
                },
                fields: {
                    AddressID: {
                        key: true,
                        create: true,
                        edit: true,
                        list: true
                    },
                    AddressLine1: {
                        title: 'AddressLine1',
                        width: '20%'
                    },
                    AddressLine2: {
                        title: 'AddressLine2',
                        create: true,
                        edit: true,
                        width: '20%'
                    },
                    City: {
                        title: 'City',
                        create: true,
                        edit: true,
                        width: '15%'
                    },
                    StateProvinceID: {
                        title: 'StateProvinceID',
                        create: false,
                        edit: false,
                        width: '10%'
                    },
                    PostalCode: {
                        title: 'PostalCode',
                        create: true,
                        edit: true,
                        width: '10%'
                    },
                    ModifiedDate: {
                        title: 'ModifiedDate',
                        edit: false,
                        create: false,
                        width: '15%',
                        display: function (data) { return moment(data.record.ModifiedDate).format('DD/MM/YYYY HH:mm:ss'); }
                    }
                }
            });
        });
    </script>
}

When the autocomplete selects a StateProvince item, it loads the jTable with the child address items. These items can be then be edited, deleted or new ones added to the parent StateProvince. The required javascript libraries and the css files are included in the MVC bundles. (jQuery-UI, moment.js and jTable)

Here’s how it looks:
el_crud_childParent_01

Links:

https://www.nuget.org/packages/ElasticsearchCRUD/

http://www.elasticsearch.org/blog/introducing-elasticsearch-net-nest-1-0-0-beta1/

http://www.elasticsearch.org/

https://github.com/elasticsearch/elasticsearch-net

http://nest.azurewebsites.net/

http://jqueryui.com/autocomplete/

http://joelabrahamsson.com/extending-aspnet-mvc-music-store-with-elasticsearch/

http://joelabrahamsson.com/elasticsearch-101/

http://www.spacevatican.org/2012/6/3/fun-with-elasticsearch-s-children-and-nested-documents/

http://thomasardal.com/elasticsearch-migrations-with-c-and-nest/

http://momentjs.com/

http://jtable.org/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: