Model relationships about implementing vote system

I am get confused about voting system implementation. I want to know vote up and vote down counts of a Post and also save voters who voted up or down. I use below model for it.

public class Post : Entity
{
    public string Title { get; set; }
    public virtual User Owner { get; set; }
    public virtual List<User> UpVoters { get; set; }
    public virtual List<User> DownVoters { get; set; }
}

I feel a bit of problem in this design because if i have t开发者_JS百科o represent vote up and vote down count of a post so i think i have to process 2 query.Using navigation properties counts would cause performance problems if i just need counts. Am i right?

Second aproach below get confuse me too because VoteUpCount and VoteDownCount should be handled always manualy by developer. And always reupdate values when voter changed his vote and this looks bit of code smell.

public class Post : Entity
{
    public string Title { get; set; }
    public virtual User Owner { get; set; }
    public int VoteUpCount { get; set; }
    public int VoteDownCount { get; set; }
    public virtual List<User> UpVoters { get; set; }
    public virtual List<User> DownVoters { get; set; }
}

Can you suggest me which is better ? And why better ? Do i have another alternatives ?

Lastyly a post hold 3 User navigation properties and this makes me feel that something can be wrong. Is there anything wrong in that relationship ??

Instinctively, I would create a Vote entity, since you want save information on votes :

public class Vote : Entity
{
    public User User {get;set;}
    public int VoteDirection {get;set;} // 1 or -1
    // any other info...
}

Then add the Vote field in the Post class :

public class Post : Entity
{
    public string Title { get; set; }
    public virtual User Owner { get; set; }
    public virtual List<Vote> Votes { get; set; }
}

Then you count the sum of VoteDirection for each Votes...

I don't think anything is wrong here.

Your first model looks pretty OK to me. I wouldn't go for the second one unless you can prove that there is a performance hit for getting a count of related entities. Premature optimisation is the root of all evil ;-).

If you need to store the count for performance reasons than the second model is fine, too. Just make sure that adding the vote updates the count field.

Your first model is the right one, you should not compromise your model for the short-comings of EF, also the second version is not very maintainable - there is too much thinking and manual effort involved.

There is actually a workaround for EF materializing all related entities when you want to retrieve the count, the syntax is not very intuitive but does result in the right SQL query:

var myPost = context.Posts.First();
int upvotersCount = context.Entry(myPost)
                           .Collection(p => p.UpVoters)
                           .Query()
                           .Count();

This approach is detailed here.

Also as a general rule of thumb you should use ICollection<User> instead of the concrete List<User> in your model.

Using navigation properties counts would cause performance problems if i just need counts. Am i right? - what kind of performance problems do you have in mind?

Generally, adding the count fields will introduce redundancy in your database, but is an obvious performance optimization step. If I were you I would rather ask myself if I need to improve performance in this case. How many posts and votes are there going to be in the database? How many users are going to access this information simultaneously? Etc. In case your application needs to be really scalable, you can add the extra counts and make sure they are updated correctly. Otherwise, you can just keep this possibility in mind but not be in a hurry to implement it.