How to Make Search Engine with Postgresql Full Text Search In Asp.Net Core (5.0)
In this post, We will test postgresql full text search with api in Asp.net core (5.0)
Development Environment
- Windows 10
- Postgresql 12
- Dotnet 5.0
- EntityFramework Core 5
For detail info about Postgresql Full Text Search, this article you can examine
We will make an api project, this api will be the bridge between with postgresql fts
We open as Asp.Net Core (5.0) web api
We create api controller named MovieController
We make connection settings in appsetting.json and startup.cs
1 2 3 4 5 6 7 8 9 10 11 12 13 |
{ "Logging": { "LogLevel": { "Default": "Information", "Microsoft": "Warning", "Microsoft.Hosting.Lifetime": "Information" } }, "AllowedHosts": "*", "ConnectionStrings": { "PostgresqlConnection": "User ID=postgres;Password=123;Server=localhost;Port=5432;Database=FirstMyDb;Integrated Security=true;Pooling=true;" } } |
Startup.cs:
1 2 3 4 5 6 7 8 9 10 11 |
public void ConfigureServices(IServiceCollection services) { services.AddControllers(); services.AddDbContext<ApplicationDbContext>( options => options.UseNpgsql(Configuration.GetConnectionString("PostgresqlConnection"))); services.AddSwaggerGen(c => { c.SwaggerDoc("v1", new OpenApiInfo { Title = "MovieSearchApp", Version = "v1" }); }); } |
We do single layer architecture as it is a simple api
We will act according to the table we created in the link. There is a SearchKeywords column where we will search in the Movie entity, and there are two options here.
- We can create tsvector column in NpgsqlTsVector type, So we can use extensions which special prepare for fts
- if the other option, tsvector column create in string type, in this case we can just write pure sql
Let's start with the first method, where I will talk about how to write both methods and their pros and cons.
Movie.cs
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[Keyless] public class Movie { public int Rank { get; set; } public string Title { get; set; } public string Genre { get; set; } public string Description { get; set; } public string Director { get; set; } public string Actors { get; set; } public int? Year { get; set; } public decimal? Rating { get; set; } public string Votes { get; set; } public NpgsqlTsVector SearchKeywords { get; set; } } |
MovieController
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
[Route("api/[controller]/[action]")] [ApiController] public class MovieController : ControllerBase { private ApplicationDbContext _db; public MovieController(ApplicationDbContext Db) { _db = Db; } [HttpGet] public IActionResult Find(string q) { var model = _db.Movies.Where(p => p.SearchKeywords.Matches(q)).ToList(); return Ok(model); } } |
We test api on swagger
Şimdi 2.yöntemimizi test edelim. Önce SearchKeywords propertyimizi string olarak değiştirelim. Değiştirdikten sonra Find actionuna ham sql sorgumuzu yazalım
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[HttpGet] public IActionResult Find(string q) { if (!String.IsNullOrEmpty(q)) { var model = _db.Movies .FromSqlRaw(@"Select * from public.""Movies"" where ""SearchKeywords"" @@ to_tsquery('english',{0})", q) .Select(m => new { Title = m.Title, Description = m.Description, Rank = m.Rank }).ToList(); return Ok(model); } return Ok(""); } |
Test ettiğimizde aşağıdaki sonuçlar gelecek
Sonuç
Her iki yöntemde de sonuçları elde ettik ama Ham sql zorunlu olmadıkça yazılmamalı eğer ki orm framework’ü bazı sql desteklerini vermiyorsa mecbur kullanılabilir örnek olarak npsql entity framework kütüphanesinde tsvector desteği yok mecburen ham sql yazılmak zorunda. Diğer türlü sql sorgularıyla yapılacak işlemler ile hızlı sonuç alabilirsiniz ama temiz kod yazılamaz ileride problemlere sebep olur.
Elasticsearch’e ihtiyaç olmayan durumlarda yani az datamızın olması bunun yanında, syntax’ının çabuk öğrenilmemesi gibi nedenlerle postgresql bu konuda gayet güzel bir alternatif olmakta.
