Which approach is better in LINQ?

Question

I am using linq in c# and I have a quick question.

I have shown very minimum amount of code here, in real-time there are also some sorting operations. I would like to know from below which approach should I use??

Approach 1

 public class UserDetails
    {
        private dbContext db = new dbContext();
        public List<User> ActiveUser()
        {
          return db.Users.Where(m => m.Active == true).ToList();                   
        }
        public List<User> InActiveUser()
        {
          return db.Users.Where(m => m.Active == false).ToList();                   
        }
       
    }

Approach 2

 public class UserDetails
    {
       List<Defect> allUser = new db.Users.ToList();
        public List<User> ActiveUser()
        {
          return allUser.Where(m => m.Active == true).ToList();                   
        }
        public List<User> InActiveUser()
        {
          return allUser.Where(m => m.Active == false).ToList();                   
        }
       
    }

There are more than 20 methods which are fetching the data, every method is fetching data from same table with different where condition. My question is Should I create dbContext and then use separate query in every method (Approch 1) OR Should I create one List and fetch all data and filter it in method itself using where condition. (Approch 2)

Answer 1

It depends on the circumstances. In general, I would prefer approach 1 as you usually can't predict how many users are in your database and you will pull all users into the memory which will cause a big memory overhead. Also for big amounts of users you will take advantage of database optimisations like indexes and query execution plans.

If we are talking about a small amount of users approach 2 might be the more performant one as you reduce the amount of roundtrips to the database. But next to the memory overhead it also has other issues like caching the users and missing database updates.

However, I'd almost always prefer approach 1 as it's good practice to do most of the filtering and sorting work on the database as it is optimized for doing this kind of things. With approach 2 you might get into trouble as your user base grows over time and it will be hard to track down caching or memory issues. Also the difference between one and two database roundtrips is mostly neglegible if you don't have a really bad connection or do it many times.