CODEWITHSUNDEEP
c#sql-serverentity-frameworklucene
spiros
spiros

Reputation: 379

Combining Lucene.NET and SQL Server to retrieve entities

I want to implement a full text search engine on my website, and I ll go with Lucene.NET. My main data are stored in SQL Server using EF6.

The issue that I have is that there are multiple entities that I want to search on (eg Posts, Comments, Messages, Entries, etc). Some entities have the property Title, some other models have the property Content, and some other have Description, or a combination of all these.

So, I am thinking of implementing only one custom lucene search table, like the one below:

public class LuceneSearchResult
{
    public ModelType ModelType { get; set; }
    public int GenericId { get; set; }

    public string Title { get; set; }
    public string Content { get; set; }
    public string Description { get; set; }
}

Now, ModelType is an enum (integer) that refers to an entity class (eg Posts, Comments, Entries, etc), and GenericId is the Id (integer primary key) of the specified ModelType. For example, the unique primary key (even though Lucene does not recognise one) would be ModelType=Post, GenericId=3.

I use this approach so when I search for a string, I will use MultiFieldQueryParser to look up at Title, Content, Description columns of the LuceneSearchResult. From the results, I will get the ModelType, and GenericId, which then I will use to retrieve the correct object from SQL Server using EF6.

I am going with this approach, so I don't create many Lucene tables, one for each of my entities, which are many.

Questions:

Thanks :)

Upvotes: 1

Views: 875

Answers (1)

femtoRgon
femtoRgon

Reputation: 33341

Is this a correct approach to this problem?

Assuming it supports all your search requirements, yes.

Is it efficient to search in Lucene to get the object that contains the string, and then retrieve the whole entity from the Sql Server?

Yes, this is a pretty common pattern, really. If you need to present a list of search results for a user to select from, before displaying the entire selected entity (i.e. like a google search results page), you would probably want store enough data in lucene to present the search result list, and only query the database when the user selects one of them.

Would it be more efficient if, in the LuceneSearchResultin, I create only one column combining ModelType and GenericId?

No, I don't see any particular reason that you should do that. Presumably these fields will be stored and not indexed (that is, you won't be querying on these fields), so I don't believe there would be any significant impact from this.

Upvotes: 2

Related Questions