Matching several keywords against articles containing them then order by desc

I am trying to figure out how to solve this problem, but can’t wrap my head around it- or to find the best way to do it. What I want to achieve is the following (Kw stands for keyword).

Existing kw in db across all articles:
Kw1, Kw2, Kw3, Kw4, Kw5

  • How to get identities of inserted data records using SQL bulk copy
  • Data Import - Excel to SQL Azure?
  • How do you get around multiple database connections inside a TransactionScope if MSDTC is disabled?
  • Sql query to convert nvarchar to int
  • Product price comparison in sql
  • SQL Server: Why would i add “;1” to the end of a stored procedure name?
  • Articles using the keywords

    Art1: Kw1, Kw2, Kw3

    Art2: Kw1, Kw2, Kw4

    Art3: Kw4, Kw1, Kw5

    Art4: Kw5, Kw4, Kw2

    Keywords to match (submitted by user)

    Kw1, Kw2, Kw3

    Rank based on most number of keyword matches

    Art 1

    Art 2

    Art 3 and 4

    Tech specs

    I am trying to achieve this using a ASP.NET MVC project with Entity Framework. The project is connected to a MS SQL database containing one table with the articles, and another table with the keywords connected with each article.

    My initial thoughts was to use LINQ in some way, but I find it hard to figure out just how to do this, if even possible.

    All help sending me towards the right path on how to solve this would be very much appreciated!

    Edit: Data structure

    **KeywordTbl**
    [KnowledgeDatabaseKeywordId] INT           IDENTITY (1, 1) NOT NULL,
    [Keyword]                    NVARCHAR (64) NOT NULL,
    [KnowledgeDatabaseId]        INT           NOT NULL,
    
    **ArticlesTbl**
    [KnowledgeDatabaseId] INT             IDENTITY (1, 1) NOT NULL,
    [Title]               NVARCHAR (200)  NOT NULL,
    [Summary]             NVARCHAR (1000) NOT NULL,
    [Article]             NVARCHAR (MAX)  NOT NULL,
    

    Example

    **ArticlesTbl**
    KnowledgeDatabaseId = 1
    Title = "My title"
    Summary = "A short summary"
    Article = "Longer text"
    
    **KeywordTbl**
    KnowledgeDatabaseKeywordId = 1
    Keyword = "Kw1"
    KnowledgeDatabaseId = 1
    
    KnowledgeDatabaseKeywordId = 2
    Keyword = "Kw2"
    KnowledgeDatabaseId = 1
    

  • How can I add parameter to Filter stored procedure XML in C#?
  • Database design (SQL CE) in Mvc 4 project using Entity Framework Code First - a few questions
  • MVC 4 controler - using dynamic StaffID's & BusinessID's instead of hard coded ones
  • Linq To Sql using AttachAll. DuplicateKeyException
  • Subsonic 3/ASP.net MVC issue trying to override class column properties
  • Populate a Dropdown List in MVC View
  • One Solution collect form web for “Matching several keywords against articles containing them then order by desc”

    Without knowing what your data structure is this is a rather hard question to answer, but hopefully the below is a good starting point to get your head around how SQL may be able to handle what you want.

    If you have your Keywords held in a table alongside your Articles, you can create a table of Keywords to look up, either with temp tables, table variables or derived tables (I have used table variables for ease of writing the example) and then count all the matches that are returned from a left join:

    declare @t table (ArticleID int, Keyword nvarchar(10));
    insert into @t values (1,'Kw1'),(1,'Kw2'),(1,'Kw3'),(2,'Kw1'),(2,'Kw2'),(2,'Kw4'),(3,'Kw4'),(3,'Kw1'),(3,'Kw5'),(4,'Kw5'),(4,'Kw4'),(4,'Kw2');
    
    declare @kw table (Keyword nvarchar(10));
    insert into @kw values('Kw1'),('Kw2'),('Kw3');
    
    select t.ArticleID
            ,count(k.Keyword) as Matches
    from @t t
        left join @kw k
            on(t.Keyword = k.Keyword)
    group by t.ArticleID
    order by Matches desc
            ,t.ArticleID;
    

    Output:

    +-----------+---------+
    | ArticleID | Matches |
    +-----------+---------+
    |         1 |       3 |
    |         2 |       2 |
    |         3 |       1 |
    |         4 |       1 |
    +-----------+---------+
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.