Ling to Sql OR stored procedures In Generics Repository for database interaction?

Question

User1765474751 posted

As generic repositories (Ling to Sql) can be use for Insert, Update, Delete etc. but it is limited only for simple Crud operations, if I want to use my generic repositories for some more complex insertion and searching like joining of tables and retrieve by stored procedures. And insert data to multiple tables using stored procedures so how will I handle it from generic repositories?

1. Can I call stored procedures from my generic repository?

2. Is it a good idea to use generic repositories + stored procedures?

3. Will it compatible for criteria base complex search?

Answer 1

User-219423983 posted

Hi Riaz_Khan,

About your needs, I think you could use the SP with generic repositories. As the second thread shows, you could create a function as below.

public IEnumerable<T> ExecWithStoreProcedure(string query, params object[] parameters)
{
        return _context.Database.SqlQuery<T>(query, parameters);
}

When you want to use, you could use the following code.

IEnumerable<Products> products = 
             _unitOfWork.ProductRepository.ExecWithStoreProcedure(
             "spGetProducts @bigCategoryId",
             new SqlParameter("bigCategoryId", SqlDbType.BigInt) { Value = categoryId } 
      );

http://stackoverflow.com/questions/9333115/repository-pattern-and-stored-procedure

http://stackoverflow.com/questions/18201646/repository-pattern-to-execute-a-stored-procedure-using-entity-framework

I hope it’s useful to you.

Best Regards,

Weibo Zhang

Answer 2

User1765474751 posted

Thanks for your Reply.
But i don't want to Mention SP name in my controller. i want some generic functions for calling stored procedures as  we use in Generics Repository.
<TEntity> GetAll<>
so it can recognize the table name.
I want jus like this.

public class CountryRepository
{
  public Country GetCountry(int CountryId, DateTime fromDate, DateTime toDate)
  {
    // EF or ADO.NET code here
  }
}

Client code would then be e.g.

var c = unitOfWork.CountryRepository.GetCountry(1, DateTime.Now.AddYears(-1), DateTime.Now);

I want just like this.. But it is in Simple Repository . I want the same in Generics Repository.
and 
what will be my //EF Code if I provide SP name  here related to TEntity.? 
as we simply do in generics repository as like this

internal void Add<T>(T entity) 
{
dbSet.Add(entity);
}

it can recognize the insertion will be in table of TEntity.
but If i want to call any Dynamic SP related to TEntity, so how will it possible.?