Assign DbSet and query result to same variable

Question

I am using the following code to allow for users to search for other registered users in order to add them to a friends list:

[Authorize(Roles = "RegisteredUsers")]
public ActionResult Index(string searchString)
{
    //list of registered users
    var regUsers = db.Users;

    if (!String.IsNullOrEmpty(searchString))
    {
        regUsers = regUsers.Where(s => s.User.LastName.Contains(searchString));
    }

    return View(regUsers.ToList());
}

Yet I am getting an error on line 9 at regUsers = regUsers.Where(..), stating:

"cannot implicitly convert type System.Linq.IQueryable<ETLToolKit.Models.User> to System.Data.entity.DbSet<ETLToolKit.Models.User>

How can I reuse the regUsers variable?

Answer 1

How about

IQueryable<User> regUsers = db.Users;

db.Users is of Type DbSet, so when you do your Where call, it tries to implicitly assign a value of different type to regUsers, which doesn't work - hence the error.

EDIT: As others have pointed out, the call to AsQueryable can be omitted. regUsers is now explicitly of type IQueryable.

Answer 2

Write as below:

regUsers = regUsers.Where(s => s.User.LastName.Contains(searchString)).AsQueryable();

This will convert into your desired result!

For more details:

What is the purpose of AsQueryable()?

EDIT:

As per the comment better approach would be as below:

IQueryable<User> users = db.Users;

if (!String.IsNullOrEmpty(searchString))
{
    users = users.Where(s => s.User.LastName.Contains(searchString));
}

return View(users.ToList());

Answer 3

As an alternative, omit the reuse of the variable which is of conflicting types and pass the list directly into the View:

[Authorize(Roles = "RegisteredUsers")]
public ActionResult Index(string searchString)
{   
    if (!String.IsNullOrEmpty(searchString))
    {
        return View(db.Users.Where(s => s.User.LastName.Contains(searchString)).ToList);
    }

    return View(new List<User>());
    //or if you want to return all users:
    return View(db.Users.ToList()); //you might want to consider `Skip` and `Take`
}

With special thanks to @CodeCaster: If your query becomes more complex:

[Authorize(Roles = "RegisteredUsers")]
public ActionResult Index(string searchString)
{   
    IQueryable<User> query = db.Users;
    if (!String.IsNullOrEmpty(searchString))
    {
        query = query.Where(s => s.User.LastName.Contains(searchString));
    }

    //additional filtering can be applied to `query`

    return View(query.ToList());    
}

Answer 4

The error is pretty clear.

You have two different types in your code:

• db.Users: a DbSet<User>
• regUsers.Where(s => s.User.LastName.Contains(searchString)): an IQueryable<User>

By using this statement:

var regUsers = db.Users;

You're declaring regUsers to be of the type of the right-hand side of the assignment: DbSet<User>.

You cannot assign the result of Where() to that same variable, because the result of Where() is not a DbSet<User>.

Declare the variable explicitly to be of a common ancestral type (IQueryable<User>, which both types implement):

IQueryable<Users> users = db.Users;

Now you can assign the result of Where() to the same variable:

users = users.Where(s => s.User.LastName.Contains(searchString));

So the code will look like this:

IQueryable<User> users = db.Users;

if (!String.IsNullOrEmpty(searchString))
{
    users = users.Where(s => s.User.LastName.Contains(searchString));
}

return View(users.ToList());