WHERE IN clause

Question

I'm working in MVC3 project. I was browsing for a while and trying several examples but I could not get it working.

I need to get a list of record from OrderForm table whose DeptID are in another list I already have got.

I'm aware that I need to use Contains() replacing IN SQL clause, but every example that I could read are doing this in the same way

.Where(ListOfDepartments.Contains(q.DeptID))

This is my method at the controller, which obviously is not working:

public ActionResult ValidOrders(string installation, string orderpriority, string stockclass, string validity)
{
    int instID = System.Convert.ToInt32(installation);
    int orderpriorityID = System.Convert.ToInt32(orderpriority);
    int stockclassID = System.Convert.ToInt32(stockclass);
    string period = validity;

    try
    {
        var departments = dba.Department
                            .Where (a => a.InstID == instID);

        var valid = dba.OrderForm
            .Where(q => q.FormType == 3 
                && q.FormStatus == 2 
                && q.OrderPriority.OrderPriorityID == orderpriorityID
                && q.StockClassID == stockclassID
                && departments.Contains(q.DeptID));              

        return View(valid.ToList());
    }
    catch (Exception)
    {
        return View("Error");
    }
}

What I'm doing wrong?

Answer 1

you need a list of int, not Department.

var departments = dba.Department
                            .Where (a => a.InstID == instID)
                            .Select(d => d.Id);//Id is a guess, it maybe another property name
                            //.ToList();