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与其他搜索引擎不谋而合。