Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

EF6: Full-text Search with Database First Approach

I found this link to make full-text search work through linq. However, the code seems to be targeting database first approach. How to make it work with Database First Approach?

Relevant part of code:

public class NoteMap : EntityTypeConfiguration<Note>
{
    public NoteMap()
    {
        // Primary Key
        HasKey(t => t.Id);
    }
}
public class MyContext : DbContext
{
    static MyContext()
    {
        DbInterception.Add(new FtsInterceptor());
    }
    public MyContext(string nameOrConnectionString) : base(nameOrConnectionString)
    {
    }
    public DbSet<Note> Notes { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new NoteMap());
    }
}

As seen above the function OnModelCreating is only called in Code First Approach. I wonder what needs to change to make the code in link work for Database First approach

like image 631
Shyamal Parikh Avatar asked Oct 18 '22 22:10

Shyamal Parikh


2 Answers

I suggest different approach. Create Table-valued function with fulltext search on SQL server and call it from Entity Framework with parameter. Simplified example from my project which search fulltext over two tables and can be easily called from EF:

CREATE FUNCTION [dbo].[GetRealtyMapFulltext]
(@criteria nvarchar(4000))
RETURNS TABLE
AS
RETURN (SELECT 
   realty.Id AS realtyId,
   ( COALESCE(ftR.Rank,0) + COALESCE(ftObec.Rank,0)) AS FtRank
   FROM realty
   LEFT JOIN ruian_obec ON realty.obecId = ruian_obec.obec_kod
   Left JOIN CONTAINSTABLE(Realty, *, @criteria) ftR ON realty.Id = ftR.[Key] 
   Left JOIN CONTAINSTABLE(ruian_obec, *, @criteria) ftObec ON realty.obecId = ftObec.[Key] 
   AND ( COALESCE(ftR.Rank,0) + COALESCE(ftObec.Rank,0)  > 0)
like image 137
Tomas Kubes Avatar answered Oct 30 '22 21:10

Tomas Kubes


Database-first generates all classes as partials including very handy partial OnContextCreated method.

I can not test it at the moment, but you could try adding MyContext_FTS.cs file with following code :

public partial class MyContext
{
    partial void OnContextCreated()
    {         
          DbInterception.Add(new FtsInterceptor());
    }
}
like image 35
Edgars Pivovarenoks Avatar answered Oct 30 '22 21:10

Edgars Pivovarenoks