Group Join and Orderby while maintaining previous query

Question

While working on a problem I realized I need to get query where the data from Table "questions" is reliant on count of Primary key "questions.Id" in another table "Upvotes" where "questionId" is a foreign key to "questions.Id" and can have multiple entries.

So how my system works is I add entries of upvotes to upvote table and I can simply count(particular question id) and total number of questions.

I am stuck with figuring out how to get list of all questions and their respective upvotes.

Example Question Table:

 id(pk)             question         
  1                 "first quues"
  2                 "second ques"
  3                 "third ques"

Example Upvote table:

id(pk)             questionid           userid(user who upvoted)
  1                    2                   "alpha"
  2                    2                   "charlie"
  3                    1                   "bravo"
  4                    2                   "alpha"

Expected output:

id(question.id)      question              upvotecount
   2                   second ques             3
   1                   first  ques             1
   3                   third  ques             0  

(Notice the order & count)

Queries I tried so far:
Closest to my output but require storage in separate variable:

var t = query
    .Select(x => new
    {  
        question = x.Id,
        count = (from upvotes2 in model.Upvotes
                 where upvotes2.QuestionId == x.Id
                 select upvotes2).Count()
     })
    .OrderByDescending(c => c.count);

foreach (var a in t)
    Console.WriteLine("" + a);

What I am trying to make it as

query = query
    .Select(x => new Question
    {  
        UpvoteCount = (from upvotes2 in model.Upvotes
                       where upvotes2.QuestionId == x.Id
                       select upvotes2).Count()
    })
    .OrderByDescending(c => c.UpvoteCount);

foreach (var a in query)
    Console.WriteLine("" + a);

The latter gives me:

System.NotSupportedException: 'The entity or complex type mYpROEJT.DataAccess.CodeFirstModel.Question cannot be constructed in a LINQ to Entities query.

whereas former is close to output which is:

"query" is of type IQueryable<Question> "Question" is a class generated from Entity and I added a [NotMapped] int UpvoteCount there

{ question = 5, upcount = 2 }
{ question = 3, upcount = 1 }
{ question = 2, upcount = 0 }
{ question = 1, count = 0 }
{ question = 4, count = 0 } 

EDIT 1: To add to original post. I want to return list of Questions and an Upvote count along.

Answer 1

So you have a collection of Questions. Every Question has zero or more Upvotes. Every Upvote belongs to exactly one Question, using the foreign key QuestionId. This is a standard one-to-many relationship.

There is also a one-to-many relationship between Users and Upvotes: every User has zero or more Upvotes, every Upvote belongs to exactly one User using a foreign key.

If you use the entity-framework code-first conventions, you will have designed these class similar to the following:

public class Question
{
    public int Id {get; set;}

    // every Question has zero or more Upvotes:
    public virtual ICollection<Upvote> Upvotes {get; set;}

    public string QuestionText {get; set;}
    ... // other properties and relations
}
public class Upvote
{
    public int Id {get; set;}

    // every Upvote belongs to exactly one Question using foreign key:
    public int QuestionId {get; set;}
    public virtual Question Question {get; set;}

    // every Upvote was done by exactly one User using foreign key:
    public int UserId {get; set;}
    public virtual User User {get; set;}

    ... // other properties and relations
}
public class User
{
    public int Id {get; set;}

    // every User has zero or more Upvotes:
    public virtual ICollection<Upvote> Upvotes {get; set;}

    ... // other properties and relations
}

For completeness tthe DbContext:

public class MyDbContext : DbContext
{
    public DbSet<Question> Questions {get; set;}
    public DbSet<UpVote> Upvotes {get; set;}
    public DbSet<User> Users {get; set;}
}

Because I stuck to the entity framework code first conventions, this is all that entity framework needs to know to detect the one-to-many relationships. It might be that you want different identifiers for your tables or columns. In that case you need to use fluent API or attributes.

Having designed your classes in the proper entity framework way makes your query really easy and intuitive:

using (var dbContext = new MyDbContext())
{
    var result = dbContext.Questions      // take your table Questions
        .Select(question => new           // for every question in this table, make one new object
        {
             Id = question.Id,
             Text = question.QuestionText,
             UpvoteCount = question.Upvotes.Count,
        });
}

Because I use the virtual ICollection<Upvote> Upvotes, entity framework knows that a GroupJoin between the Questions table and the Upvotes table is needed. In Sql this will become an inner join followed by a group by.

If you chose to deviate from the standard entity-framework one-to-many designs, you can't use ICollection.Count, you'll have to do the GroupJoin yourself:

var result = dbContext.Questions.GroupJoin(  // GroupJoin Questions with Upvotes
     dbContext.Upvotes,
     question => question.Id,                // from every question take the Id,
     upVote => upvote.QuestionId,            // from every upvote take the QuestionId,
     (question, upvotes) => new              // for every question with its matching Upvotes
     {                                       // make one new object
          Id = question.Id,
          Text = question.QuestionTxt,
          UpvoteCount = upvotes.Count(),
     });

Finally you want to order by decreasing UpvoteCount:

var finalResult = result.OrderByDescending(joinedItem => joinedItem.UpvoteCount);