retrieving database-computed properties of classes within entity framework

Question

I have a good understanding of EF, and generated my database successfully. Now, I am struggling with adding dynamic properties to one of the entity classes. For example, I have a Post class, and other users can make comments to the posts. When I list the existing posts, I want to display the number of comments made to corresponding post.

One solution might be having a property called CommentCount, and updating the Post by increasing the (int) value of the CommentCount property by 1 when a new comment is made.

The other solution, and I think it is a better solution, is that when retrieving the post from the DB, the number of comments associated with the post can be computed and retrieved at the same time and assigned to CommentCount property of the post instance. However, I do not know how to achieve this with EF.

Which approach is highly recommended? Or, is there any other ways of doing this? If it is the second one, how can I achieve this with EF?

Also check this http://stackoverflow.com/questions/1987836/creating-a-non-mapped-property-in-an-entity-entity-framework — Vojtěch Dohnal, Mar 09 '15 at 09:04
my question is about how to bind data to `CommentCount`, how should I change `Post` class so that when I retrieve the `DBSet Posts`, each `Post` instance will have the correct `CommentCount` value? — renakre, Mar 09 '15 at 16:29
It is not good practice to count comments each time, I would suggest a field inside Post table with CommentCount, which should be updated whenever comment was added or removed. — Akash Kava, Mar 10 '15 at 08:01

Juan Carlos Oropeza · Answer 1 · 2015-03-09T17:04:11.657

If you calculation is very complex you should try creating a View in your DB and then add it to your Model?

But if your Model have something simple like

class Post {
    public int postid { get; set; }
    public virtual ICollection<comment> comment { get; set; }
}

In your controller you can do

db.post(x => x.postid == yourid).comments.count()

to get total of comment

or in your view

@foreach (var item in Model)
{
    <li>item.postid;</li>
    <li>item.comment.Count();</li>
}

Or update your class

class Post {
    public int postid { get; set; }
    public virtual ICollection<comment> comment { get; set; }
    public int CommentCount
    {
        get 
        { 
            return comment.Count();
        }
    }

}

Just remember bring related data in your query. In my case POI have properties parish_id, sector_id, city_id and parish have municipality, and municipality have state.

Using this query I can get Poi with all the related data.

filter = db.poi
           .Include("parish")
           .Include("sector")
           .Include("city")
           .Include("parish.municipality")
           .Include("parish.municipality.state")
           .Where(x => x.sector_id == SectorID);

Considering that it will be a complex calculation, my question is about how to bind data to `CommentCount`, how should I change `Post` class so that when I retrieve the `DBSet Posts`, each `Post` instance will have the correct `CommentCount` value? — renakre, Mar 09 '15 at 16:29
@erkaner I update my comment, adding a property to the class. Take note if you want to change the class you need do it in a different derived class, Otherwise you lose the code if do an EF update from DB. Let me know if that works for you. Also will help if you show the code of your class. — Juan Carlos Oropeza, Mar 09 '15 at 16:54
@erkaner i also add a example of eager loading. Using `Include` will bring the relationed data. — Juan Carlos Oropeza, Mar 09 '15 at 17:05

Vojtěch Dohnal · Accepted Answer · 2015-03-10T09:45:08.350

1) You should simply consider not putting the property called CommentCount into your model. When you develop for example a WPF Windows application, you should consider using MVVM pattern and the CommentCount would belong to your ViewModel class and not to your Model class. There you implement INotifyPropertyChanged and you can use it from your frontend Views. Analogically there is MVC pattern for ASP.NET etc.

There are other design patterns like Repository pattern. Using this pattern you can create the CommentCount in your repository class and not in your model class. This would be similar to your second solution.

2) I assume from your question that you are using code-first approach:

generated my database successfully

If you do so and you wish to include CommentCount directly in your Model class, you can do it this by adding partial class file to your project like this:

namespace DBModel.Models
{
    public partial class Post
    {
        public int CommentsCount
        {
            get { return this.Comments.Count; }
        }
        ...

But I cannot see why to create extra property in your model just for that.

On the other hand adding this field as a computed field into your SQL database could make sense and then it would be part of your EF model.

retrieving database-computed properties of classes within entity framework

2 Answers2