DataAccess Layer and class, where do I put my functions?

Question

I'm using Dapper to populate my class using a Data Access class. In this class I do all my CRUD operation.

public class Product
{
   public int id { get; set; }
   public string description {get;set;}
   public string value {get;set;}
}

public class ProductDa
{
  //CRUD operations using Dapper
  public List<Product> GetAllElements();
}

Now I need to create a function that return the count of elements with a specific value.

Public int ReturnCountValue(int val)

In this function I will call my GetAllElements and then use LINQ to return the desired value.

What I'm wondering is, where should I put the ReturnCountValue function: ProductDa (the data access layer) or Product (the base class) ?

Answer 1

Given the pattern you are using, I think a good mental model for you would be the following:

Product represents a row in the database--it is a plain old C# object (POCO) with nothing especially smart about it. It should simply be a collection of Auto-properties, just like you have in your example.

ProductDa should be where your data access occurs. To figure out if you are accessing data, you should ask yourself "will I be querying the database to implement this method?" or "will I be using Dapper to implement this method?" If the answer is yes, then the interaction with the database goes in that class and its methods.

As for whether you should use LINQ's Count() method or bundle your own: at the Data access level, you should probably avoid bundling your own method for Count(). The data access layer should be about abstracting away the details of how you are querying the database; further manipulations by consumers of your data access layer (Count, Any, First, etc.) are OK. Plus, since you are already returning a List<Product>, you have access to the List.Count property so it is trivial to access for callers.

Given all that, I would amend your ProductDa class as follows

public class ProductDa
{
    //CRUD operations using Dapper
    public List<Product> GetAllElements();
    //query database using Dapper to get all the elements where Value == value
    public List<Product> GetAllElementsWithValue(int value);
}

and consume the class as follows to get the number of Products returned:

var dataAccess = new ProductDa();
var threeValuedItems = dataAccess.GetAllElementsWithValue(3);
int numberOfItems = threeValuedItems.Count;

or, put more concisely:

var dataAccess = new ProductDa();
var numberOfItemsWithValueThree = dataAccess.GetAllElementsWithValue(3).Count;