VOOZH about

URL: https://www.cdata.com/kb/tech/elasticsearch-ado-linqtoef.rst

⇱ LINQ to Elasticsearch Data

LINQ to Elasticsearch Data

👁 Jerod Johnson
Jerod Johnson
Director, Technology Evangelism
LINQ offers versatile querying capabilities within the .NET Framework (v3.0+), offering a straightforward method for programmatic data access through CData ADO.NET Data Providers. In this article, we demonstrate the use of LINQ to retrieve information from the Elasticsearch Data Provider.

This article illustrates using LINQ to access tables within the Elasticsearch via the CData ADO.NET Data Provider for Elasticsearch. To achieve this, we will use LINQ to Entity Framework, which facilitates the generation of connections and can be seamlessly employed with any CData ADO.NET Data Providers to access data through LINQ.

About Elasticsearch Data Integration

Accessing and integrating live data from Elasticsearch has never been easier with CData. Customers rely on CData connectivity to:

  • Access both the SQL endpoints and REST endpoints, optimizing connectivity and offering more options when it comes to reading and writing Elasticsearch data.
  • Connect to virtually every Elasticsearch instance starting with v2.2 and Open Source Elasticsearch subscriptions.
  • Always receive a relevance score for the query results without explicitly requiring the SCORE() function, simplifying access from 3rd party tools and easily seeing how the query results rank in text relevance.
  • Search through multiple indices, relying on Elasticsearch to manage and process the query and results instead of the client machine.

Users frequently integrate Elasticsearch data with analytics tools such as Crystal Reports, Power BI, and Excel, and leverage our tools to enable a single, federated access layer to all of their data sources, including Elasticsearch.

For more information on CData's Elasticsearch solutions, check out our Knowledge Base article: CData Elasticsearch Driver Features & Differentiators.


Getting Started


See the help documentation for a guide to setting up an EF 6 project to use the provider.

  1. In a new project in Visual Studio, right-click on the project and choose to add a new item. Add an ADO.NET Entity Data Model.
  2. Choose EF Designer from Database and click Next.
  3. Add a new Data Connection, and change your data source type to "CData Elasticsearch Data Source".

  4. Enter your data source connection information.

    Set the Server and Port connection properties to connect. To authenticate, set the User and Password properties, PKI (public key infrastructure) properties, or both. To use PKI, set the SSLClientCert, SSLClientCertType, SSLClientCertSubject, and SSLClientCertPassword properties.

    The data provider uses X-Pack Security for TLS/SSL and authentication. To connect over TLS/SSL, prefix the Server value with 'https://'. Note: TLS/SSL and client authentication must be enabled on X-Pack to use PKI.

    Once the data provider is connected, X-Pack will then perform user authentication and grant role permissions based on the realms you have configured.

    Below is a typical connection string:

    Server=127.0.0.1;Port=9200;User=admin;Password=123456;
    5. 👁 Required connection properties for the data source. (QuickBooks is shown.)
  5. If saving your entity connection to App.Config, set an entity name. In this example we are setting ElasticsearchEntities as our entity connection in App.Config.
  6. Enter a model name and select any tables or views you would like to include in the model.
👁 The available tables in the underlying data source. (QuickBooks is shown.)

Using the entity you created, you can now perform select , update, delete, and insert commands. For example: 

ElasticsearchEntities context = new ElasticsearchEntities();

var ordersQuery = from orders in context.Orders
 select orders;

foreach (var result in ordersQuery) {
 Console.WriteLine("{0} {1} ", result.Id, result.OrderName);
}

See "LINQ and Entity Framework" chapter in the help documentation for example queries of the supported LINQ.