CODEWITHSUNDEEP
.netdatabaselinq
Zia Kiyani
Zia Kiyani

Reputation: 822

Order By on the Basis of Dictint Group count

I've to bring those documents from the table which have a specific word, and I've to use order by so that those documents which have highest count comes first. for example

Document 1: this is a school. this is my school
Document 2: this is our school
Document 3: my school is this this 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'

Reault is
result

I want to order by Descending on the basis of count of unique id... I actually need LINQ query for this issue

This is my database schema

Schema

hopefully I've stated my problem clearly...

Upvotes: 4

Views: 109

Answers (3)

Suresh Kumar Veluswamy
Suresh Kumar Veluswamy

Reputation: 4363

Here is a sample using 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);
    }
}

Upvotes: 2

Suresh Kumar Veluswamy
Suresh Kumar Veluswamy

Reputation: 4363

Here is a Linq query.

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();

This groups by the document Id and does a descending sort and returns the groups. Hope this is helpful for you.

Upvotes: 1

user3162968
user3162968

Reputation: 1046

Maybe SQL query will be helpful for you or somebody else (perhaps you can translate it into linq)

First we need count(ID). Lets find it:

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

Then we can inner join above table with 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 ID sorted by number of different locations.

Upvotes: 0

Related Questions