IndianTechnoEra: .NET + SQL Server + Elasticsearch Hybrid Search Architecture

This blog explains how to integrate Elasticsearch into a .NET Web API (Controller → Service → Repository → SQL Server) layered architecture so you can achieve Google-like fast search while still keeping SQL Server as your source of truth.

---

1. 🧠 Problem in Traditional SQL Search

In your current system:

• Stored Procedures handle search
• Uses LIKE '%text%'
• Multiple JOINs + STRING_AGG + JSON

❌ Problems:

• Full table scan
• Slow performance on large data
• Pagination becomes expensive
• Text search is not intelligent

---

2. ⚡ Solution: Elasticsearch + SQL Server (Hybrid Model)

Architecture:

Client (Angular / React)
        ↓
.NET Web API (Controller)
        ↓
Service Layer
        ↓
┌──────────────────────────────┐
│ 1. Elasticsearch (Search)    │  ← FAST SEARCH ENGINE
│ 2. SQL Server (Source Data)  │  ← SYSTEM OF RECORD
└──────────────────────────────┘
        ↓
Repository Layer

---

3. 🔥 What is Elasticsearch?

Elasticsearch is a distributed full-text search engine based on Apache Lucene.

Key Features:

• Full-text search (like Google)
• Fuzzy search (typo handling)
• Fast filtering + ranking
• Handles millions of records efficiently

---

4. 🏗️ Index Design (VERY IMPORTANT)

Instead of querying SQL tables, we create an Index (like a NoSQL document store).

Index: requisitions

📌 Sample Document Structure

{
  "requisition_id": 101,
  "requisition_code": "REQ-001",
  "client_name": "TCS",
  "business_group": "IT",
  "budget": 500000,

  "jobs": [
    {
      "job_id": 1,
      "job_title": "Backend Developer",
      "locations": ["Gurgaon", "Pune"],
      "skills": ["C#", ".NET", "SQL"]
    }
  ]
}

---

5. 🔄 Data Sync (SQL → Elasticsearch)

Whenever data changes in SQL Server, we push it to Elasticsearch.

Option A: Background Service (Recommended)

SQL Insert/Update
        ↓
Domain Event / Service Call
        ↓
Elastic Index Update

✔ .NET Sync Code Example

public async Task IndexRequisition(Requisition req)
{
    var doc = new
    {
        requisition_id = req.Id,
        requisition_code = req.Code,
        client_name = req.ClientName,
        business_group = req.BusinessGroup,
        budget = req.Budget,
        jobs = req.Jobs.Select(j => new
        {
            job_id = j.Id,
            job_title = j.Title,
            locations = j.Locations,
            skills = j.Skills
        })
    };

    await _elasticClient.IndexAsync(doc, i => i.Index("requisitions"));
}

---

6. 🔍 Search Query (FAST LIKE GOOGLE)

Example: “developer tcs delhi”

POST requisitions/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "multi_match": {
            "query": "developer tcs",
            "fields": [
              "client_name",
              "requisition_code",
              "jobs.job_title",
              "jobs.skills"
            ]
          }
        }
      ],
      "filter": [
        { "term": { "business_group": "IT" } },
        { "term": { "is_active": true } }
      ]
    }
  },
  "from": 0,
  "size": 20,
  "sort": [
    { "budget": "desc" }
  ]
}

---

7. 🧩 .NET Layered Integration

📌 Controller

[HttpGet("search")]
public async Task Search(string q)
{
    var result = await _service.SearchRequisitionsAsync(q);
    return Ok(result);
}

📌 Service Layer

public async Task<List<RequisitionDto>> SearchRequisitionsAsync(string query)
{
    var esResult = await _elasticRepo.SearchAsync(query);

    var ids = esResult.Select(x => x.RequisitionId).ToList();

    // OPTIONAL: enrich data from SQL
    return await _sqlRepo.GetRequisitionsByIds(ids);
}

📌 Elasticsearch Repository

public async Task<List<RequisitionEsModel>> SearchAsync(string query)
{
    var response = await _client.SearchAsync<RequisitionEsModel>(s => s
        .Index("requisitions")
        .Query(q => q
            .MultiMatch(m => m
                .Query(query)
                .Fields(f => f
                    .Field("client_name")
                    .Field("jobs.job_title")
                    .Field("jobs.skills")
                )
            )
        )
    );

    return response.Documents.ToList();
}

---

8. ⚡ Why Hybrid Search is Best

Feature	SQL SP	Elasticsearch
Text Search	❌ Slow (LIKE)	⚡ Very Fast
Fuzzy Search	❌ Not possible	✔ Yes
Pagination	Slow	Fast
Complex Filtering	Medium	Fast

---

9. 🚀 Final Hybrid Flow

User Search
    ↓
.NET API
    ↓
Elasticsearch (FAST FILTER + SEARCH)
    ↓
Get IDs
    ↓
SQL Server (optional full details)
    ↓
Response to UI

---

🔥 Conclusion

If your system has:

• Large requisition data
• Complex joins
• Search-heavy UI

Then replacing SP-based LIKE search with Elasticsearch Hybrid Search will give:

• 10x–100x faster search
• Scalable architecture
• Better user experience (Google-like search)

---