本文演示如何使用实体框架与MS SQL Server作为主数据库和Elasticsearch搜索/选择功能。该应用程序结合了Elasticsearch的功能,用于搜索和快速选择,以及用于CUD事务(创建,更新和删除)的实体框架。
设置文档搜索引擎
AdventureWorks2012用于填充搜索引擎的数据。可以在这里下载。
MS SQL Server是主数据库。数据需要加载到Elasticsearch中,而辅助持久化需要被初始化。在应用程序生命周期开始时,此任务通常只执行一次。以下方法使用实体框架读取所需的数据,并将其保存到Elasticsearch的批量请求中。 JsonIgnore
和Key
属性被添加到实体类。作为子文档保存到Elasticsearch的实体需要主键的Key
属性。所有不支持的属性或不需要的属性都用JsonIgnore属性标记。
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);
}
}
}
}
}
}
}
此方法的调用是全局asax Application_Start
方法。 这也可以部署为单独的Windows服务或可以随时调用的控制台应用程序,并且还可以间隔提供验证检查,并对两个持久层执行一些管理或完整性检查。
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);
}
}
}
使用实体框架 code first 从现有数据库创建Address
类。 Key
和JsonIgnore
属性已添加到ElasticsearchCRUD序列化所需的属性。 应用程序使用所有层的address
类。 通常,view model
类将直接用于视图而不是实体类。 BusinessEntityAddress
已经从搜索引擎中删除,因为在此应用程序中搜索不是必需的。
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
因为同一个类可以用于EF和ElasticsearchCRUD,这使得在两个持久层中更新,创建或删除实体/文档非常容易。 实体上下文和elasticsearchCRUD上下文都在ElasticsearchProvider
的构造函数中初始化。 Address
类需要一个ElasticsearchMappingAddress
,因为Address
类被保存为Elasticsearch
中StateProvince
的子节点。
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需要定义父索引。 这样做如下:
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";
}
}
}
现在可以为搜索提供者实现创建,更新和删除方法。这需要对标准EF repository进行小的更改。在Elasticsearch中搜索或找到address 项。这些文档结果未附加到EF上下文中。因此,当需要CUD操作时,需要从主数据库附加或获取数据项。有不同的方法来做到这一点。
EF事务在Elasticsearch动作之前完成。我们只需要主数据库中存在的Elasticsearch中的数据。辅助数据库可能与主数据库不同步。您需要管理作业,并根据需要修复辅助层。应用程序的系统要求将定义如何完成。例如,如果它是一个国家的政府部门的应用程序,您可以在晚上执行此操作,而不必担心实时数据更改。对于全球应用,您需要实时操作。
public void AddUpdateDocument(Address address)
{
address.ModifiedDate = DateTime.UtcNow;
address.rowguid = Guid.NewGuid();
var entityAddress = _entityFrameworkContext.Address.Add(address);
_entityFrameworkContext.SaveChanges();
//我们使用具有适当ID的实体结果
_elasticsearchContext.AddUpdateDocument(entityAddress, entityAddress.AddressID, entityAddress.StateProvinceID);
_elasticsearchContext.SaveChanges();
}
public void UpdateAddresses(long stateProvinceId, List<Address> addresses)
{
foreach (var item in addresses)
{
// 如果父类已经更改,则需要删除该子类并再次创建。 这在这个例子中不是必需的
var addressItem = _elasticsearchContext.SearchById<Address>(item.AddressID);
// 需要在这里更新一个实体
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();
}
在上一个示例中,MVC控制器中的提供程序就像以前一样使用。 控制器提供StateProvince
搜索,并且当选择省份时,将使用jTable
的列表操作从Elasticsearch检索子address 对象,并根据需要进行排序或分页。Address 搜索如下:
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
}
}
);
}
搜索控制器非常简单。 它为视图提供所有操作,并根据需要调用提供程序方法:
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"});
}
}
}
然后可以在视图中使用MVC控制器。 razor html视图创建autocomplete 控件以及来自所选StateProvince
的address 子项的jTable
。
注意:所需的JavaScript库和CSS文件都包含在MVC包中。
@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>
}
应用程序如下所示:
结论
现在该应用程序具有超强大的功能,用于搜索的Elasticsearch的高性能以及MS SQL Server中CUD操作的交易。 这对于具有大量数据的MVC应用来说是一个很好的解决方案。Elasticsearch与其他搜索引擎不谋而合。