How to execute LINQ Expression with predicate

[LeandroHPA]

Hi Guys!
I am currently having problems on executing a LINQ expression against a repository.
Find below my IRepository, Repository and Controller.

IRepository Class

public interface IRepositorio<T> where T : class

    {

        IList<T> PegaTudo();

        IList<T> PegaTudoLimite(int qtd);

        T Pega(Expression<Func<T, bool>> predicate);

        void Inserir(T item);

        void Remover(T item);

        void Salvar();

    }




Repository Class

public class Repositorio<T> : IDisposable, IRepositorio<T> where T : class
    {
        protected EmpregaDB db {
            get;
            private set;
        }

        public Repositorio(EmpregaDB _db) {
            this.db = _db;
        }

        public virtual IList<T> PegaTudo() {
            return this.db.GetAll<T>().ToList();
        }

        public virtual IList<T> PegaTudoLimite(int qtd) {
            return this.db.GetAll<T>().Take(qtd).ToList();
        }

        public virtual T Pega(Expression<Func<T, bool>> predicate) {
            return this.db.GetAll<T>().FirstOrDefault(predicate);
        }

        public virtual void Inserir(T item) {
            this.db.Add(item);
        }

        public virtual void Remover(T item) {
            this.db.Delete(item);
        }

        public virtual void Salvar() {
            this.db.SaveChanges();
        }

        public void Dispose() {
            this.db = null;
        }
    }

HomeController

public class HomeController : Controller
    {
        private readonly IRepositorio<Estado> repo;

        public HomeController() {
            this.repo = new Repositorio<Estado>(new EmpregaDB());
        }

        public HomeController(IRepositorio<Estado> _repo) {
            this.repo = _repo;
        }

        public ActionResult Index()
        {
            ViewBag.Message = "Welcome to ASP.NET MVC!";

            return View();
        }

        public ActionResult About()
        {
            return View();
        }

        public JsonResult PegaEstados() {
            //var lista = repo.PegaTudo().Select(s => new { s.EstadoId, s.DscEstado }).Where( i => i.EstadoId == 1);
            var lista = repo.Pega( i => i.EstadoId == 1 );
            return Json(lista, JsonRequestBehavior.AllowGet);
        }
    }

The commented line on HomeController is working fine. but I am actually not using predicate.
How would I properly execute "Pega" for getting the same result?

Thanks,
Leandro Andrade

[Viktor Zhivkov]

Hello LeandroHPA,

The issue with your code is that you are calling .ToList() in your repository code. If you want to be able to translate the predicate to a SQL statement then you should use IQueryable<T> instead of IList<T> and defer calling .ToList() as late as possible. Another thing to watch out for is usage of .Net specific methods or methods written by you in the predicate itself. Such method call will force the query to be processed in memory and will result in sub-optimal performance of the query.

If you need any further assistance, please let us know.

Kind regards,
Viktor Zhivkov
the Telerik team

[LeandroHPA]

Hi Viktor,
First of all, Thank you so much for replying me.

I haven't got to build a line code for calling "Pega" with the instructions you gave me. I might be missing something out on the line.
I have changed IList into IQueryable<T>, but it is not working.
Would you please help me out on building the call.

IRepository

public interface IRepositorio<T> where T : class
    {
        IQueryable<T> PegaTudo();
        IQueryable<T> PegaTudoLimite(int qtd);
        T Pega(Expression<Func<T, bool>> predicate);
        //T Pega(object id);
        void Inserir(T item);
        void Remover(T item);
        void Salvar();
    }

For brevity's sake, beow is only the "Pega" Method of Repository.

public class Repositorio<T> : IDisposable, IRepositorio<T> where T : class
    {
        protected EmpregaDB db {
            get;
            private set;
        }

        public Repositorio(EmpregaDB _db) {
            this.db = _db;
        }

        public virtual T Pega(Expression<Func<T, bool>> predicate) {
            return this.db.GetAll<T>().FirstOrDefault(predicate);
        }
    }

How would I call in on my controller, considering the method below?

public JsonResult PegaEstados() {
    var lista = repository.Pega( i => i.EstadoId == 1 );
    return Json(lista, JsonRequestBehavior.AllowGet);
}

Thanks in advance

[Viktor Zhivkov]

Hi LeandroHPA,

Based on your first post I thought that the commented code in your controller is the one that you wanted to be fixed (i.e. have the predicate pushed to the database server). Because of that I suggested the change to IQueryable<T>.
If you are experiencing any issues with the Pega() method, please let us know what are they. The code in Pega() and the way you are calling the method look fine. If you don't see any SQL statements after calling this method you should know that if the same object has been already loaded in the same context no call to the database will be invoked since the FirstOrDefault() will get the result from the context's level one cache.

If you are still experiencing any issues please post more details about the outcome and the behaviour that you are expecting.

Regards,

Viktor Zhivkov
the Telerik team