How to perform a Where using Entity Framework

Question

I have a web application that I need to present a list containing some data, for that, I created the Model: Crm_Analise:

public class Crm_Analise
{
    [Key]
    public int id { get; set; }
    public string cod_item_CRM { get; set; }
    public string TAG { get; set; }
    public string data_creat { get; set; }
    public string modelo { get; set; }

    public int cliente_CRM { get; set; }
}

And for Scaffolding created the Controller and the Views.

However, I need to show only the ref. data to the logged-in client. So I’m using Session to store client code.

Ai in the Controller I did the following:

    public async Task<ActionResult> Index()
    {
        if(Session["cod_cli"] != null)
        {
            db.Crm_Analise.Where(x => x.cliente_CRM == Convert.ToInt32(Session["cod_cli"]));

            return View(await db.Crm_Analise.ToListAsync());
        }
        else
        {
            return Error();
        }
    }

But you haven’t changed at all, you keep bringing me the whole list.

Answer 1

Failed to assign the filtered query to ToListAsync().

Realize that the Where does not change the original collection, regardless of whether it is a list, a IQueryable or anything else, he returns a new collection with elements that meet the predicate.

On returning to view using db.Crm_Analise.ToListAsync() you are simply materializing all the items from DbSet and sending to the view.

public async Task<ActionResult> Index()
{
    if(Session["cod_cli"] != null)
    {
        var query = db.Crm_Analise.
                       Where(x => x.cliente_CRM == Convert.ToInt32(Session["cod_cli"]));

        return View(await query.ToListAsync());
    }
    else
    {
        return Error();
    }
}

Answer 2

The problem is that you are running Where, but runs Tolistasync throughout Dbset, change your code to:

public async Task<ActionResult> Index()
{
    if(Session["cod_cli"] != null)
    {
        return View(await db.Crm_Analise.Where(x => x.cliente_CRM == Convert.ToInt32(Session["cod_cli"])).ToListAsync());
    }
    else
    {
        return Error();
    }
}