This article demonstrates how to use Entity Framework with MS SQL Server as a primary database and Elasticsearch for the search/select functionality. The application combines the power and performance of Elasticsearch for search and fast selects and also Entity Framework for CUD transactions (Create, Update and Delete).
Code: https://github.com/damienbod/WebSearchWithElasticsearchEntityFrameworkAsPrimary
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.
MS SQL Server is the primary database. The data needs to be loaded into Elasticsearch and the secondary persistent needs to be initialized. This task is usually executed only once, at the start of the application life cycle. This following method reads the required data using entity framework and saves it in bulk requests to Elasticsearch. The JsonIgnore and Key attributes are added to the entity classes. An entity which is saved to Elasticsearch as a child document require a Key attribute for the primary key. All none supported properties or properties which are not required are marked with a JsonIgnore attribute.
using System;
using System.Diagnostics;
using System.Linq;
using ElasticsearchCRUD;
using ElasticsearchCRUD.Tracing;
using WebSearchWithElasticsearchEntityFrameworkAsPrimary.DomainModel;
namespace WebSearchWithElasticsearchEntityFrameworkAsPrimary.Search
{
public class InitializeSearchEngine
{
private readonly Stopwatch _stopwatch = new Stopwatch();
public void SaveToElasticsearchStateProvinceIfitDoesNotExist()
{
IElasticsearchMappingResolver elasticsearchMappingResolver = new ElasticsearchMappingResolver();
using (var elasticSearchContext = new ElasticsearchContext("http://localhost:9200/", new ElasticsearchSerializerConfiguration(elasticsearchMappingResolver, true, true)))
{
if (!elasticSearchContext.IndexTypeExists<StateProvince>())
{
elasticSearchContext.TraceProvider = new ConsoleTraceProvider();
using (var databaseEfModel = new EfModel())
{
int pointer = 0;
const int interval = 20;
bool firstRun = true;
int length = databaseEfModel.StateProvince.Count();
while (pointer < length)
{
_stopwatch.Start();
var collection = databaseEfModel.StateProvince.OrderBy(t => t.StateProvinceID).Skip(pointer).Take(interval).ToList<StateProvince>();
_stopwatch.Stop();
Console.WriteLine("Time taken for select {0} Address: {1}", interval, _stopwatch.Elapsed);
_stopwatch.Reset();
_stopwatch.Start();
foreach (var item in collection)
{
var ee = item.CountryRegion.Name;
elasticSearchContext.AddUpdateDocument(item, item.StateProvinceID);
}
if (firstRun)
{
elasticSearchContext.SaveChangesAndInitMappingsForChildDocuments();
firstRun = false;
}
else
{
elasticSearchContext.SaveChanges();
}
_stopwatch.Stop();
Console.WriteLine("Time taken to insert {0} Address documents: {1}", interval, _stopwatch.Elapsed);
_stopwatch.Reset();
pointer = pointer + interval;
Console.WriteLine("Transferred: {0} items", pointer);
}
}
}
}
}
}
}
The call for this method is in the global asax Application_Start method. This could also be deployed as a separate windows service or a console application which can be called at anytime and also provide validation checks at intervals, and do some management or sanity checks on both persistence layers.
using System.Web.Http;
using System.Web.Mvc;
using System.Web.Optimization;
using System.Web.Routing;
using WebSearchWithElasticsearchEntityFrameworkAsPrimary.Search;
namespace WebSearchWithElasticsearchEntityFrameworkAsPrimary
{
public class WebApiApplication : System.Web.HttpApplication
{
protected void Application_Start()
{
var initializeSearchEngine = new InitializeSearchEngine();
initializeSearchEngine.SaveToElasticsearchStateProvinceIfitDoesNotExist();
AreaRegistration.RegisterAllAreas();
GlobalConfiguration.Configure(WebApiConfig.Register);
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
BundleConfig.RegisterBundles(BundleTable.Bundles);
}
}
}
The Address class is created using Entity Framework code first from an existing database. The Key and JsonIgnore attributes have been added to the properties required for the ElasticsearchCRUD serialization. The application uses the address class for all layers. Usually view model classes would be used for the views and not the entities classes directly. The BusinessEntityAddress has been removed from the search engine because this not required for the search in this application.
using Newtonsoft.Json;
namespace WebSearchWithElasticsearchEntityFrameworkAsPrimary.DomainModel
{
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Spatial;
[Table("Person.Address")]
public partial class Address
{
public Address()
{
BusinessEntityAddress = new HashSet<BusinessEntityAddress>();
}
[Key]
public int AddressID { get; set; }
[Required]
[StringLength(60)]
public string AddressLine1 { get; set; }
[StringLength(60)]
public string AddressLine2 { get; set; }
[Required]
[StringLength(30)]
public string City { get; set; }
public int StateProvinceID { get; set; }
[Required]
[StringLength(15)]
public string PostalCode { get; set; }
[JsonIgnore]
public DbGeography SpatialLocation { get; set; }
public Guid rowguid { get; set; }
public DateTime ModifiedDate { get; set; }
public virtual StateProvince StateProvince { get; set; }
[JsonIgnore]
public virtual ICollection<BusinessEntityAddress> BusinessEntityAddress { get; set; }
}
}
Search Provider
Because the same class can be used for both Entity Framework and also ElasticsearchCRUD, this makes it very easy to update, create or delete entities/documents in both persistent layers. Both the entity context and the elasticsearchCRUD context are initialized in the constructor of the ElasticsearchProvider. The Address class requires an ElasticsearchMappingAddress because the Address class is saved as a child of the StateProvince in Elasticsearch.
private const string ConnectionString = "http://localhost:9200/";
private readonly IElasticsearchMappingResolver _elasticsearchMappingResolver;
private readonly ElasticsearchContext _elasticsearchContext;
private readonly EfModel _entityFrameworkContext;
public ElasticsearchProvider()
{
_elasticsearchMappingResolver = new ElasticsearchMappingResolver();
_elasticsearchMappingResolver.AddElasticSearchMappingForEntityType(typeof(Address), new ElasticsearchMappingAddress());
_elasticsearchContext = new ElasticsearchContext(ConnectionString, new ElasticsearchSerializerConfiguration(_elasticsearchMappingResolver,true,true));
_entityFrameworkContext = new EfModel();
}
ElasticsearchMappingAddress needs to define the parent index. This is done as follows:
using System;
using ElasticsearchCRUD;
namespace WebSearchWithElasticsearchEntityFrameworkAsPrimary.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";
}
}
}
Now the create, update and delete methods can be implemented for the search provider. This needs small changes to a standard Entity Framework repository. The address items are searched for or found in Elasticsearch. These document results are not attached to the Entity Framework context. So when the CUD operations are required, the items need to be attached or got from the primary database. There are different ways of doing this.
Entity framework transactions are completed before the Elasticsearch actions. We only want data in Elasticsearch which exists in the primary database. It is possible that the secondary database is out of sync with the primary database. You require management jobs which control this and fix up the secondary layer as required. The system requirements of the application would define how this is done. For example if it’s a application for a government department in a single country, you could do this at night and not have to worry about live data changes. For global applications, you require live operations.
public void AddUpdateDocument(Address address)
{
address.ModifiedDate = DateTime.UtcNow;
address.rowguid = Guid.NewGuid();
var entityAddress = _entityFrameworkContext.Address.Add(address);
_entityFrameworkContext.SaveChanges();
// we use the entity result with the proper ID
_elasticsearchContext.AddUpdateDocument(entityAddress, entityAddress.AddressID, entityAddress.StateProvinceID);
_elasticsearchContext.SaveChanges();
}
public void UpdateAddresses(long stateProvinceId, List<Address> addresses)
{
foreach (var item in addresses)
{
// if the parent has changed, the child needs to be deleted and created again. This in not required in this example
var addressItem = _elasticsearchContext.SearchById<Address>(item.AddressID);
// need to update a entity here
var entityAddress = _entityFrameworkContext.Address.First(t => t.AddressID == addressItem.AddressID);
if (entityAddress.StateProvinceID != addressItem.StateProvinceID)
{
_elasticsearchContext.DeleteDocument<Address>(addressItem.AddressID, new RoutingDefinition { ParentId = stateprovinceid });
}
entityAddress.AddressLine1 = item.AddressLine1;
entityAddress.AddressLine2 = item.AddressLine2;
entityAddress.City = item.City;
entityAddress.ModifiedDate = DateTime.UtcNow;
entityAddress.PostalCode = item.PostalCode;
item.rowguid = entityAddress.rowguid;
item.ModifiedDate = DateTime.UtcNow;
_elasticsearchContext.AddUpdateDocument(item, item.AddressID, item.StateProvinceID);
}
_entityFrameworkContext.SaveChanges();
_elasticsearchContext.SaveChanges();
}
public void DeleteAddress(int addressId, int stateprovinceid)
{
var address = new Address { AddressID = addressId };
_entityFrameworkContext.Address.Attach(address);
_entityFrameworkContext.Address.Remove(address);
_entityFrameworkContext.SaveChanges();
_elasticsearchContext.DeleteDocument<Address>(addressId, new RoutingDefinition { ParentId = stateprovinceid });
_elasticsearchContext.SaveChanges();
}
The provider is used like before in the previous example in a MVC controller. The controller provides a StateProvince search and when a province is selected, the child address objects are retrieved from Elasticsearch using the jTable’s listaction with sorting or paging as required. The Address search is as follows:
public PagingTableResult<Address> GetAllAddressesForStateProvince(string stateprovinceid, int jtStartIndex, int jtPageSize, string jtSorting)
{
var result = new PagingTableResult<Address>();
var data = _elasticsearchContext.Search<Address>(
BuildSearchForChildDocumentsWithIdAndParentType(
stateprovinceid,
"stateprovince",
jtStartIndex,
jtPageSize,
jtSorting)
);
result.Items = data.PayloadResult.ToList();
result.TotalCount = data.TotalHits;
return result;
}
public IEnumerable<T> QueryString<T>(string term)
{
var results = _elasticsearchContext.Search<T>(BuildQueryStringSearch(term));
return results.PayloadResult.Hits.HitsResult.Select(t =>t.Source).ToList();
}
// {
// "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 search controller is very simple. It provides all actions for the view and calls the provider methods as required:
using System;
using System.Collections.Generic;
using System.Web.Mvc;
using WebSearchWithElasticsearchEntityFrameworkAsPrimary.DomainModel;
using WebSearchWithElasticsearchEntityFrameworkAsPrimary.Search;
namespace WebSearchWithElasticsearchEntityFrameworkAsPrimary.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, string stateprovinceid)
{
try
{
address.StateProvinceID = Convert.ToInt32(stateprovinceid);
_searchProvider.AddUpdateDocument(address);
return Json(new { Result = "OK", Record = address });
}
catch (Exception ex)
{
return Json(new { Result = "ERROR", Message = ex.Message });
}
}
[Route("UpdateAddressForStateProvince")]
public JsonResult UpdateAddressForStateProvince(Address address)
{
try
{
_searchProvider.UpdateAddresses(address.StateProvinceID, new List<Address> { 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(int addressId, int stateprovinceid)
{
_searchProvider.DeleteAddress(addressId, stateprovinceid);
return Json(new { Result = "OK"});
}
}
}
The MVC controller can then be used in the view. The razor html view creates the autocomplete control and also the jTable for the address child items from the selected StateProvince.
Note: The required Javascript libraries and CSS files are included in the MVC bundles.
@model WebSearchWithElasticsearchEntityFrameworkAsPrimary.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:49908/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">
function RefreshPage() {
$('#addressResultsForStateProvince').jtable('load', { selectedstateprovinceid: $('#selectedstateprovinceid').val() });
}
$('#addressResultsForStateProvince').jtable({
title: 'Address list of selected StateProvince',
paging: true,
pageSize: 10,
sorting: true,
multiSorting: true,
defaultSorting: 'ModifiedDate desc',
actions: {
listAction: function (postData, jtParams) {
return $.Deferred(function ($dfd) {
$.ajax({
url: 'http://localhost:49908/Search/GetAddressForStateProvince?stateprovinceid=' + $('#selectedstateprovinceid').val() + '&jtStartIndex=' + jtParams.jtStartIndex + '&jtPageSize=' + jtParams.jtPageSize + '&jtSorting=' + jtParams.jtSorting,
type: 'POST',
dataType: 'json',
data: postData,
success: function (data) {
$dfd.resolve(data);
},
error: function () {
$dfd.reject();
}
});
});
},
deleteAction: function (postData, jtParams) {
return $.Deferred(function ($dfd) {
$.ajax({
url: 'http://localhost:49908/Search/DeleteAddress?addressId=' + postData.AddressID + "&stateprovinceid=" + $('#selectedstateprovinceid').val(),
type: 'POST',
dataType: 'json',
data: postData,
success: function (data) {
$dfd.resolve(data);
},
error: function () {
$dfd.reject();
}
});
});
},
createAction: function (postData) {
var resultData = $.ajax({
url: 'http://localhost:49908/Search/CreateAddressForStateProvince?stateprovinceid=' + $('#selectedstateprovinceid').val(),
type: 'POST',
dataType: 'json',
data: postData,
success: function (data) {
return data;
},
error: function () {
}
});
return resultData;
},
updateAction: function (postData) {
var resultData = $.ajax({
url: 'http://localhost:49908/Search/UpdateAddressForStateProvince?stateprovinceid=' + $('#selectedstateprovinceid').val(),
type: 'POST',
dataType: 'json',
data: postData,
success: function (data) {
return data;
},
error: function () {
}
});
return resultData;
}
},
recordAdded: function(event, data) {
RefreshPage();
},
recordDeleted: function(event, data) {
//RefreshPage();
},
fields: {
AddressID: {
key: true,
create: false,
edit: false,
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'); }
}
}
});
$(document).ready(function() {
var updateResults = [];
$("input#autocomplete").autocomplete({
source: function(request, response) {
$.ajax({
url: "http://localhost:49908/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);
}
});
});
</script>
}
The application looks like this:
Conclusion
Now the application has the super power, high performance of Elasticsearch for search and the transactions for the CUD operations in MS SQL Server. This is a great solution for MVC applications with lots of data. Elasticsearch scales well unlike other search engines.
Links:
https://www.nuget.org/packages/ElasticsearchCRUD/
http://www.elasticsearch.org/blog/introducing-elasticsearch-net-nest-1-0-0-beta1/
https://github.com/elasticsearch/elasticsearch-net
http://nest.azurewebsites.net/
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/
Software Engineering 
hats off sir! amazing very helpful
Thanks
#piyush
Awsome!
Sorry to bother with this question. There’s one thing that i did not understand. Why are addresses being saved as a child of stateprovince in elastic ? Is not not the other way around i.e an address has a state province