ElasticsearchCRUD翻译系列之（四）：利用实体框架从MS SQL Server获取数据传输到Elasticsearch

原文：https://damienbod.com/2014/10/14/transferring-data-to-elasticsearch-from-ms-sql-server-using-elasticsearchcrud-and-entity-framework/

本文展示了如何将数据从MS SQL Server 2014传输到ElasticSearch。使用实体框架（代码首先从数据库访问）访问MS SQL Server。ElasticSearch文档是使用ElasticSearchCrud创建的，并插入到批量请求中。实体框架实体可以直接用于ElasticSearchCrud。可以将包含所有嵌套实体的整个实体保存为单个文档，也可以只保存没有任何子对象的主实体。

Code: https://github.com/damienbod/DataTransferSQLWithEntityFrameworkToElasticsearch  （Fork: https://github.com/wtujvk/DataTransferSQLWithEntityFrameworkToElasticsearch）

  1.  ElasticsearchCRUD 介绍
  2. 使用自动完成、jQuery和JTALE的简单文档搜索MVC应用程序
  3. 具有嵌套文档的MVC搜索的CRUD
  4. 利用实体框架从MS SQL Server获取数据传输到Elasticsearch
  5. 带有子文档的MVC的搜索
  6. 基于实体框架和搜索的MVC应用
  7. 实时重建Elasticsearch的索引
  8. 基于搜索和Web API导出CSV
  9. 父、子、孙子记录和路由的检索
  10. Elasticsearch的类型映射到ElasticsearchCRUD
  11. 使用搜索语句的搜索同义词分析器
  12. 使用德国分析器搜索
  13. 基于谷歌的MVC地图搜索
  14. 查询和过滤的搜索功能
  15. Elasticsearch批量插入
  16. Elasticsearch聚合搜索
  17. 多个Index和Type的搜索
  18. 搜索高亮
  19. 索引权重

AdventureWorks2012用作数据库。可在此处下载。您需要先安装数据库，然后代码才能工作。

应用

创建一个新的控制台应用程序，并从Nuget下载ElasticSearchCrud和实体框架。

从AdventureWorks数据库创建代码优先数据库

向项目添加新项，选择ADO.NET实体数据模型：

现在，首先从数据库选项中选择代码。数据库已存在

 

 

从Person 实体Schemen的角度来看The address表 and the person表 作为文档 根。

创建的地址类需要更改。必须删除dbgeography空间位置，因为这不是受支持的类型。在ElasticSearchCrud v1.0.8或更高版本中，可以使用jsonignore属性忽略这一点。

namespace DataTransferSQLToEl.SQLDomainModel
{
    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 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; }
 
        // This type is not supported yet...
        //public DbGeography SpatialLocation { get; set; }
 
        public Guid rowguid { get; set; }
 
        public DateTime ModifiedDate { get; set; }
 
        public virtual StateProvince StateProvince { get; set; }
    }
}

选择实体并将文档添加到ElasticSearch。地址转移的实现方法如下：

public void SaveToElasticsearchAddress()
{
    IElasticsearchMappingResolver elasticsearchMappingResolver = new ElasticsearchMappingResolver();
    using (var elasticsearchContext = new ElasticsearchContext("http://localhost:9200/", elasticsearchMappingResolver))
    {
        //elasticsearchContext.TraceProvider = new ConsoleTraceProvider();
        using (var modelPerson = new ModelPerson())
        {
            int pointer = 0;
            const int interval = 100;
            int length = modelPerson.CountryRegion.Count();
 
            while (pointer < length)
            {
                stopwatch.Start();
                var collection = modelPerson.Address.OrderBy(t => t.AddressID).Skip(pointer).Take(interval).ToList<Address>();
                stopwatch.Stop();
                Console.WriteLine("Time taken for select {0} AddressID: {1}", interval, stopwatch.Elapsed);
                stopwatch.Reset();
 
                foreach (var item in collection)
                {
                    elasticsearchContext.AddUpdateDocument(item, item.AddressID);
                    string t = "yes";
                }
 
                stopwatch.Start();
                elasticsearchContext.SaveChanges();
                stopwatch.Stop();
                Console.WriteLine("Time taken to insert {0} AddressID documents: {1}", interval, stopwatch.Elapsed);
                stopwatch.Reset();
                pointer = pointer + interval;
                Console.WriteLine("Transferred: {0} items", pointer);
            }
        }
    }
}

一次选择一百个实体并将其添加到ElasticSearchCrud上下文中。这只是将对象添加到内存中的集合中。调用savechanges方法时，每个实体都被序列化为JSON对象。当所有项都序列化后，httpclient实例将HTTP批量发布请求中的所有对象发送到ElasticSearch。重复此操作，直到所有项目都已转移。ElasticSearchCrud序列化所有子元素，仅限1-n。对已转换的父对象的任何引用都将被忽略并保存为空属性。

创建的ElasticSearch映射如下（针对Person and Address文档）

{
    "addresss": {
        "mappings": {
            "address": {
                "properties": {
                    "addressid": {
                        "type": "long"
                    },
                    "addressline1": {
                        "type": "string"
                    },
                    "addressline2": {
                        "type": "string"
                    },
                    "city": {
                        "type": "string"
                    },
                    "modifieddate": {
                        "type": "date",
                        "format": "dateOptionalTime"
                    },
                    "postalcode": {
                        "type": "string"
                    },
                    "rowguid": {
                        "type": "string"
                    },
                    "stateprovince": {
                        "properties": {
                            "countryregion": {
                                "properties": {
                                    "countryregioncode": {
                                        "type": "string"
                                    },
                                    "modifieddate": {
                                        "type": "date",
                                        "format": "dateOptionalTime"
                                    },
                                    "name": {
                                        "type": "string"
                                    }
                                }
                            },
                            "countryregioncode": {
                                "type": "string"
                            },
                            "isonlystateprovinceflag": {
                                "type": "boolean"
                            },
                            "modifieddate": {
                                "type": "date",
                                "format": "dateOptionalTime"
                            },
                            "name": {
                                "type": "string"
                            },
                            "rowguid": {
                                "type": "string"
                            },
                            "stateprovincecode": {
                                "type": "string"
                            },
                            "stateprovinceid": {
                                "type": "long"
                            },
                            "territoryid": {
                                "type": "long"
                            }
                        }
                    },
                    "stateprovinceid": {
                        "type": "long"
                    }
                }
            }
        }
    },
    "persons": {
        "mappings": {
            "person": {
                "properties": {
                    "additionalcontactinfo": {
                        "type": "string"
                    },
                    "businessentityid": {
                        "type": "long"
                    },
                    "demographics": {
                        "type": "string"
                    },
                    "emailaddress": {
                        "properties": {
                            "businessentityid": {
                                "type": "long"
                            },
                            "emailaddress1": {
                                "type": "string"
                            },
                            "emailaddressid": {
                                "type": "long"
                            },
                            "modifieddate": {
                                "type": "date",
                                "format": "dateOptionalTime"
                            },
                            "rowguid": {
                                "type": "string"
                            }
                        }
                    },
                    "emailpromotion": {
                        "type": "long"
                    },
                    "firstname": {
                        "type": "string"
                    },
                    "lastname": {
                        "type": "string"
                    },
                    "middlename": {
                        "type": "string"
                    },
                    "modifieddate": {
                        "type": "date",
                        "format": "dateOptionalTime"
                    },
                    "namestyle": {
                        "type": "boolean"
                    },
                    "personphone": {
                        "properties": {
                            "businessentityid": {
                                "type": "long"
                            },
                            "modifieddate": {
                                "type": "date",
                                "format": "dateOptionalTime"
                            },
                            "phonenumber": {
                                "type": "string"
                            },
                            "phonenumbertype": {
                                "properties": {
                                    "modifieddate": {
                                        "type": "date",
                                        "format": "dateOptionalTime"
                                    },
                                    "name": {
                                        "type": "string"
                                    },
                                    "phonenumbertypeid": {
                                        "type": "long"
                                    }
                                }
                            },
                            "phonenumbertypeid": {
                                "type": "long"
                            }
                        }
                    },
                    "persontype": {
                        "type": "string"
                    },
                    "rowguid": {
                        "type": "string"
                    },
                    "suffix": {
                        "type": "string"
                    },
                    "title": {
                        "type": "string"
                    }
                }
            }
        }
    }
 
}
View Code

使用ElasticSearchCrud可以如下读取保存的对象。

public Address GetAddressFromElasticsearch(int id)
{
    Address address;
    IElasticsearchMappingResolver elasticsearchMappingResolver = new ElasticsearchMappingResolver();
    using (var elasticsearchContext = new ElasticsearchContext("http://localhost:9200/", elasticsearchMappingResolver))
    {
        address = elasticsearchContext.GetDocument<Address>(id);
    }
 
    return address;
}

然后可以在控制台应用程序中使用。完成后，所有具有嵌套子对象的对象都将在ElasticSearch中保存为文档。

using System;
 
namespace DataTransferSQLToEl
{
    class Program
    {
        static void Main(string[] args)
        {
            var repo = new Repo();
            repo.SaveToElasticsearchPerson();
            repo.SaveToElasticsearchAddress();
 
            var personX = repo.GetPersonFromElasticsearch(345);
            var addressX = repo.GetAddressFromElasticsearch(22);
            Console.WriteLine(addressX);
            Console.WriteLine(personX);
        }
    }
}

如果只保存父实体并且不包含子实体（ElasticSearch中的嵌套对象），则可以在ElasticSearchCrud上下文构造函数中设置此选项。

  
bool saveChildEntitiesAsNestedObjects = false;
 
using (var elasticSearchContext = 
              new ElasticsearchContext(
                 "http://localhost:9200/", 
                  elasticsearchMappingResolver, 
                  saveChildEntitiesAsNestedObjects 
              )
      )
{
  // Do you coding here...
}

链接

https://damienbod.wordpress.com/2014/09/22/elasticsearch-crud-net-provider/

http://www.elasticsearch.org/

posted on 2019-01-29 16:36  余昭(Ray)  阅读(497)  评论(0)    收藏  举报