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

  • Table Pivot in SQL Server
  • Show first occurrence of a 'space' in a column
  • Date Select boxs values convert to SQL data base date type value
  • Updating a column with a constant gives the error ‘Subquery returned more than 1 value’
  • create one insert stored for multiple tables
  • TRANCOUNT mismatch
  • 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

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


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

  • Error when creating a Controller in Visual Studio 2012
  • Linq to Sql Add and Delete in same transaction
  • Difference between EF.SqlServer and EF.SqlServerCompact
  • ASP.NET MVC 5- Getting Roles for specfic user
  • Using a .MDF SQL Server Database with ASP.NET Versus Using SQL Server
  • Using Entity framework with SQL Server 2012 (local *.mdf database)
  • 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


    | 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.