locked
How to design repository pattern to find data for single column or multiple column RRS feed

  • Question

  • User-1223304583 posted

    see this code first. it is very basic repository patter.

    public interface ITodoRepository
        {
            void Add(TodoItem item);
            IEnumerable<TodoItem> GetAll();
            TodoItem Find(long key);
            void Remove(long key);
            void Update(TodoItem item);
        }
        
        
    public class TodoRepository : ITodoRepository
        {
            private readonly TodoContext _context;
    
            public TodoRepository(TodoContext context)
            {
                _context = context;
                Add(new TodoItem { Name = "Item1" });
            }
    
            public IEnumerable<TodoItem> GetAll()
            {
                return _context.TodoItems.ToList();
            }
    
            public void Add(TodoItem item)
            {
                _context.TodoItems.Add(item);
                _context.SaveChanges();
            }
    
            public TodoItem Find(long key)
            {
                return _context.TodoItems.FirstOrDefault(t => t.Key == key);
            }
    
            public void Remove(long key)
            {
                var entity = _context.TodoItems.First(t => t.Key == key);
                _context.TodoItems.Remove(entity);
                _context.SaveChanges();
            }
    
            public void Update(TodoItem item)
            {
                _context.TodoItems.Update(item);
                _context.SaveChanges();
            }
        }    

    specially see this function

            public TodoItem Find(long key)
            {
                return _context.TodoItems.FirstOrDefault(t => t.Key == key);
            }

    the above function can only find data against key field but suppose i have a employee data and i want to search employee data by empid or employee name or

    two fields in where clause like we write say i want to search employee data based on employee name and location id.

    how tell me how to customize the above Find as a result it allow me to search on any field or may be multiple fields at time.

    code with sample code for explanation.

    thanks

    Thursday, March 23, 2017 11:20 AM

Answers

  • User527778624 posted

    Hi,

    I suggest, change GetAll() not Find()

    public IEnumerable<TodoItem> GetAll(bool final = true)
    {
    	var items = _context.TodoItems;
    	return (final) ? items.ToList() : items;
    }
    

    then in your business logic, call:

    //to get all items
    var items = repo.GetAll();
    
    //or
    //to filter further (where) and/or project further (select) var items = repo.GetAll(false).Where(x=>x.Name="xyz" && x.Key==5).ToList();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 23, 2017 11:46 AM
  • User527778624 posted

    Hi,

    sorry this line is not clear return
    (final)
    ? items.ToList()
    : items;
    if final is true then it return list else return items object..........not clear the intention. please tell me more about this line

    final = true -> returns data fetched from database as an in-memory list. (List)

    final = false -> sql query preparation not completed, will build further by caller. upon calling ToList(), fetches fitlered, projected data from database. (IQueryable)

    if we pass true then we can not search multiple field like ?

    We can still, but its performance burden. filtering, projection at sql server is better than at in-memory.

    when GetAll function allow me to search any fields then why should i have find function ?

    Find() is useful to get single item by PK.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 23, 2017 1:23 PM
  • User527778624 posted

    Hi,

    final=false
    _context.TodoItems
    returns the IQueryable which doesn't contain any data but sql query.
     It will start fetching data from database when you call ToList(), Single(), FirstOrDefault() or enumerate through foreach.
     During this meantime, we can still add conditions (where) and fields selection (select) to the IQueryable (sql query) - which is what we are doing with below call:
     var items = repo.GetAll(false).Where(x=>x.Name="xyz" && x.Key==5).ToList();

    final=true
     _context.TodoItems.ToList()
     returns IList which has data stored in our application memory with all TodoItems data fetched from sqlserver. Here even if you call where condition or select fields, it works, but you won't get the performance benefit compared with IQueryable (filtering at sql server etc).

    check this site, or google for: IQueryable vs IEnumerable

    http://stackoverflow.com/questions/4844660/differences-between-iqueryable-list-ienumerator

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 23, 2017 3:49 PM

All replies

  • User527778624 posted

    Hi,

    I suggest, change GetAll() not Find()

    public IEnumerable<TodoItem> GetAll(bool final = true)
    {
    	var items = _context.TodoItems;
    	return (final) ? items.ToList() : items;
    }
    

    then in your business logic, call:

    //to get all items
    var items = repo.GetAll();
    
    //or
    //to filter further (where) and/or project further (select) var items = repo.GetAll(false).Where(x=>x.Name="xyz" && x.Key==5).ToList();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 23, 2017 11:46 AM
  • User-1223304583 posted

    sorry this line is not clear return (final) ? items.ToList() : items;

    if final is true then it return list else return items object..........not clear the intention. please tell me more about this line

    if we pass true then we can not search multiple field like ?

    var items = repo.GetAll(true).Where(x=>x.Name="xyz" && x.Key==5).ToList();

    when GetAll function allow me to search any fields then why should i have find function ?

    please help me to understand. thanks

    Thursday, March 23, 2017 12:16 PM
  • User527778624 posted

    Hi,

    sorry this line is not clear return
    (final)
    ? items.ToList()
    : items;
    if final is true then it return list else return items object..........not clear the intention. please tell me more about this line

    final = true -> returns data fetched from database as an in-memory list. (List)

    final = false -> sql query preparation not completed, will build further by caller. upon calling ToList(), fetches fitlered, projected data from database. (IQueryable)

    if we pass true then we can not search multiple field like ?

    We can still, but its performance burden. filtering, projection at sql server is better than at in-memory.

    when GetAll function allow me to search any fields then why should i have find function ?

    Find() is useful to get single item by PK.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 23, 2017 1:23 PM
  • User-1223304583 posted

    still not clear what u said.

    final = false -> sql query preparation not completed, will build further by caller. upon calling ToList(), fetches fitlered, projected data from database. (IQueryable)

    also below not clear ->

    mamoni.kol2017

    if we pass true then we can not search multiple field like ?

    We can still, but its performance burden. filtering, projection at sql server is better than at in-memory.

    Thursday, March 23, 2017 3:08 PM
  • User527778624 posted

    Hi,

    final=false
    _context.TodoItems
    returns the IQueryable which doesn't contain any data but sql query.
     It will start fetching data from database when you call ToList(), Single(), FirstOrDefault() or enumerate through foreach.
     During this meantime, we can still add conditions (where) and fields selection (select) to the IQueryable (sql query) - which is what we are doing with below call:
     var items = repo.GetAll(false).Where(x=>x.Name="xyz" && x.Key==5).ToList();

    final=true
     _context.TodoItems.ToList()
     returns IList which has data stored in our application memory with all TodoItems data fetched from sqlserver. Here even if you call where condition or select fields, it works, but you won't get the performance benefit compared with IQueryable (filtering at sql server etc).

    check this site, or google for: IQueryable vs IEnumerable

    http://stackoverflow.com/questions/4844660/differences-between-iqueryable-list-ienumerator

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 23, 2017 3:49 PM