ElasticsearchCRUD使用(六)【EF和Elasticsearch的MVC应用程序】

本文演示如何使用实体框架与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与其他搜索引擎不谋而合。