Order by Dictint Group count

Question

I have to bring these documents from the table that have the specific word, and I have to use the order to get the first documents that have the highest score, first. eg

Document 1: this is a school. this is my school
Document 2: this is our school
Document 3: my school is this

Now if i use

select Document.Id, Document_Word.Location from Document, Document_Word, word where Document.Id = Document_Word.Document_Id and Document_Word.Word_Id = Word.Id and Word.Word = 'this'

Cancel  

I want to order in decreasing order of counting a unique identifier ... I really need a LINQ query for this problem

This is my database schema.

I hope that I clearly stated my problem ...

Answer 1

Here is an example using the Entity Framework,

using (var context = new MyDbContext())
{
    var documentEntities = (from document in context.Documents
        join document_word in context.Document_Word on document equals document_word.Document
        join word in context.Words on document_word.Word equals word
        where word.Word1 == "this" // Filter for word = "this"
        group document_word by document_word.Document.Id into documentWordGroup // First group Document_Words by document Id so that we can sort based on the Id count
        let IdCount = documentWordGroup.Count() // store the count into a variable IdCount
        orderby IdCount descending // here we sort by the IdCount
        select documentWordGroup).ToArray() // select the sorted Document_Word groups
        .SelectMany(dw => dw); // combine the groups into a single array of Document_Words

    //Display the result in the Console output
    Console.WriteLine("ID" + "\t" + "Location");
    foreach (var document in documentEntities)
    {
        Console.WriteLine(document.Document.Id + "\t" + document.Location);
    }
}

Answer 2

Linq.

var res = (from document in Documents
    join document_word in DocumentWords on document.Id equals document_word.Document_Id
    join word in Words on document_word.WordId equals word.wordId
    group document by document.Id
    into g
    let IdCount = g.Count()
    orderby IdCount descending
    select new {Key = g.Key, Items = g.Distinct().ToList()}).
    SelectMany(x => x.Items).ToList();

. , .

Answer 3

, SQL- - (, linq)

count (ID). :

SELECT *,count(ID) FROM document GROUP BY ID order by count(ID)

Then we can inner join over the table with the document:

SELECT * FROM document
INNER JOIN ( SELECT *,count(ID) FROM document GROUP BY ID order by count(ID) ) y
ON document.ID=y.ID

This code will return an identifier sorted by the number of different locations.