1

I have 2 simple objects.

public class Person
{
  public int Id { get; set; }
  public string Name { get; set; }
  public List<Message> Messages { get; set; }
  //public int MessageCount { get; set; }
}

Public class Message
{
   public int Id { get; set; }
   public string Text { get; set; }
}

I need to show MessageCounts for each person which is simply the number of messages a person has wrote. How can I do it?

I have some ideas but I think they will be very slow, since I will need to return a list of persons and on each I need the count.

My Ideas

1- in code

[NotMapped]
public int MessageCount { get {return Messages.Count()}; private set; }

This is the simplest way that I could imagine, but at the same time it seems to be very slow on a large database since for each person it needs to go and fetch the message count separately which is crazy.

2- computed column with a function to return it

It seems like a better plan. right? However, I could not find the whole solution anywhere.

I know I can decorate my property with [DatabaseGenerated(DatabaseGeneratedOption.Computed)] which will make it read from a computed field, but then how to create a function that returns the value and use that?

I've found something here but he uses the code from the same table which can be done with normal computed fields. I also this post Calculated column in EF Code First but non of the answers was to my question.

--

Considering my question, it should be something that you can seen in many applications. Isn't there any easy and high performance way to do it?

Update

Thanks to people who commented, I guess the best way is to create 2 types, 1 that corresponds to the real person class and using that for normal CRUD actions and the other which is just a view coming from a join to show lists. Any ideas? :)

Ashkan S
  • 10,464
  • 6
  • 51
  • 80
  • For me, the easiest and fastest way of doing that is by creating a StoredProcedure. Using a computed column is basically the same thing as your first option, but instead it's the SQL Server that execute the whole request. So yes, it's gonna be faster, but not as fast as a SP. – Atlasmaybe Aug 10 '17 at 15:02
  • if you say "fastest", do you mean at runtime? then it should be the computed column approach, otherwise (overall time) the fastest way should be through a SP, though the join you suggested should be fairly quick if you manage to re-use the precompiled query and don't worry about materializing the values (I'm not quite sure if Count or Count() would actually materialize the collection, you could try it out) – DevilSuichiro Aug 10 '17 at 16:08
  • (1) implies lazy loading, although the sample model is not eligible for that (the navigation property is not `virtual`). IMO if you need `Person` and message count, but no `Messages`, then you should simply create DTO/ViewModel class and use projection. – Ivan Stoev Aug 10 '17 at 16:57
  • Thanks guys for the answers. Then I guess the best way is to create 2 types, 1 that corresponds to the real person class and using that for normal CRUD actions and the other which is just a view coming from a join to show lists. what do you think? :) – Ashkan S Aug 11 '17 at 08:47

1 Answers1

-1

You will need a relation field between the Message and the user who wrote it, something like this:

Public class Message
{
   public int Id { get; set; }
   public string Text { get; set; }
   public int UserId {get; set; }
}

then when you can count the messages with a simple linq query like this:

context.Messages.Count(m=> m.UserId == id);

linq is optimized to do this the best as possible, but is the database is very large you will need an approach of the optimization by design, and is better to have a persisted field of the messages count and you can increase it with triggers in the publication or something like that.

Daniel Forero
  • 95
  • 6
  • How is that different from the first approach? Either way, you have to call the DB on every person and run the count. so if you have 100 people in your db, you will have 100 calls to db asking for the count of messages – Ashkan S Aug 11 '17 at 08:31
  • Linq is optimized and it have cache configuration to do not the sum every time, but I gave you another alternative: "but is the database is very large you will need an approach of the optimization by design, and is better to have a persisted field of the messages count and you can increase it with triggers in the publication or something like that." In the database access there is nothing else to do, always you can use cache and web storage in the client to avoid go to the database every time. – Daniel Forero Aug 11 '17 at 14:39
  • Ok, you misunderstood my comment. If you want to return a list of 50 users containing Id, name and messageCount, what will happen in your code? EF will take your list of users and for each will call DB to take the count, meaning 50+1 calls to DB. If there are millions of rows (expected from message table :) ) then it will be very slower than a normal join. And no, EF cannot cache it for you because the results is different for each Person – Ashkan S Aug 14 '17 at 13:31
  • Maybe you miss this part: "but is the database is very large you will need an approach of the optimization by design, and is better to have a persisted field of the messages count and you can increase it with triggers in the publication or something like that." -> this means no join, just a query over the users table. – Daniel Forero Aug 14 '17 at 16:01