0

We are starting our new application in which we using MS SQL DB for all our insert/update/delete/select ie. both SELECT and DML operations.

But, soon we will have two different sources. SQL Server DB and Elastic Search. The idea is to perform all the DML operations on MS Sql DB but will be using Elastic Search for ALL the fetch (select) operations. The data is replicated in ES from MS SQL DB.

At present our project structure looks something like this:

Controller (UI Layer)

CreateStock(Stock stock){
_serviceLayer.CreateStock(stock);
}

List<Stock> GetStocks(){
var stocks =  _serviceLayer.GetStocks()  
}

ServiceLayer

CreateStock(Stock stock){
  // business logic
_repository.CreateStock(stock)
}

List<Stock> GetStocks(){
 return _repository.GetStocks(stock)  
}

Repository

CreateStock(Stock){
    //Insert data to MS SQL DB with ADO.NET
}

List<Stock> GetStocks(){
  //Get data from MS SQL DB with ADO.NET
}

FUTURE REQUIREMENT:

Controller

Repository

CreateStock(Stock){
    //Insert data to MS SQL DB with ADO.NET
}

List<Stock> GetStocks(){
  //GET DATA FROM OTHER DATA SOURCE (ES)
}

Now of course I could create whole new repository classes for new implementation with Elastic search as the data source. Both the repositories will inherit from common interface. But problem with that approach is that I will be duplicating the DML operations in both the classes.

How can I reuse the DML code in future?

  1. Should I create two repositories? One for DML and another for SELECT? So I could create a single implementation for DML Repository and two implementation for SELECT repository?

  2. Should I create a single repository but then add another later after that?

Is there an option 3?

There is a possibility that we decide to USE SQL DB for both DML and Reads when Elastic Search is down for a few days.

  • Why not do the elastic search inside the repository, and then use the list of IDs to do a search in SQL using a where some_id in (the list of IDs returned by elastic search)? – Greg Burghardt Oct 10 '19 at 19:38
  • @GregBurghardt I am just a lowly developer. That decision rests in the hands of the mighty Architects. – SamuraiJack Oct 10 '19 at 19:48
  • if architects dictate how you write a SQL query, you need to capitalize the "m" as well --- Mighty Architects. – Greg Burghardt Oct 10 '19 at 22:16
0

As long as the object fetched from elastic search does not need to be used in a DML operation, you can try mapping the elastic search result to a separate class and just return that in the "search" methods. Finding an entity by primary key/identifier can still delegate to a SQL query, or an ORM method.

Don't get too picky about adhering to all of the .NET recommendations for repositories. Frankly, I think they overcomplicate the pattern.

If you really want to keep things "pure" from a repository perspective just create another repository dedicated to search, and be done with it. Otherwise use the identifiers returned from the elastic search to fetch those records from the database:

elasticResults = elasticSearch.Find(...)

dbResults = select * from foo where foo_id in (elasticResults.Select(e => e.FooId))

(As a very abstraction example).

This way you are returning ORM entities from the repository, yet using elastic search to do the search. Selecting records from the database by a list of IDs should be pretty snappy.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.