Can't find a certain item in DB if it's name is duplicated

Question

I'm going to speak about ASP.NET MVC 4 Web Application.

I have a database of users (Entity Framework). I can add, delete or search for user by his name or email, or both. It works fine if, for example, I search for "George" and there is only one George in the table. But if there are more users with these name, I need to show them all. Insted I have an exception.

Here is my controller's action for Search:

[HttpPost]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public ActionResult Search(User userinfo) 
{
    const string noResult = "Search Result Not Found";
    var itemforSearch = new User();
    using (var uc = new UserContext()) 
    {
        if (userinfo.Name == null && userinfo.Email == null)
        {
            List<User> users;
            users = uc.UserList.ToList();
            return new JsonResult { Data = null };
        }
        if (userinfo.Name != null) 
        {
            itemforSearch = uc.UserList.SingleOrDefault(o => o.Name == userinfo.Name);
            return GetSearchedItem(noResult, itemforSearch);
        } 
        else 
        {
            if (userinfo.Email != null) 
            {
                itemforSearch = uc.UserList.SingleOrDefault(o => o.Email == userinfo.Email);
                return GetSearchedItem(noResult, itemforSearch);
            }
        }
    }
    return View(itemforSearch);
}

private ActionResult GetSearchedItem(string noResult, Models.User itemforSearch) 
{
    if (itemforSearch != null) 
    {
        return new JsonResult { Data = itemforSearch };
    } 
    else 
    {
        throw new Exception("User with provided information was not find");
    }
}

Here is my script that works after clicking "Search" button:

<script type="text/javascript">
function DbSearch() {
    // Get some values from elements on the page:
    var serchedName = $("input[name='SearchName']").val(),
        searchedEmail = $("input[name='SearchEmail']").val();

    var user = { Name: serchedName, Email: searchedEmail };

    $.ajax(
    {
        type: "POST",
        url: "Search",
        data: JSON.stringify({ userinfo: user }),
        dataType: "json",
        contentType: "application/json; charset=utf-8",
        success: OnSearchSuccess,
        error: OnError
    });
}

function OnSearchSuccess(data) {

    var tableContent = "<tr>" +
        "<td>" + data.UserId + "</td>" +
        "<td>" + data.Name + "</td>" +
        "<td>" + data.Email + "</td>" +
        "</tr>";
    $('#UserTable').empty();
    $("#UserTable").append(tableContent);
    $("#UserTable").fnDraw();
}
function OnError(data) { }
</script>

Actually, I think the problem is in my LINQ expression, when I use SingleOrDefault (I got {System.InvalidOperationException} but how can i fix it?

Answer 1

When using SingleOrDefault you will get an exception when the dataset has multiple rows that match your condition, This property is also explained in the documentation http://msdn.microsoft.com/en-us/library/vstudio/bb342451.aspx

If you want to return a list of users that match your request you should use the .where() method.

Like so: itemforSearch = uc.UserList.Where(o => o.Name == userinfo.Name).ToList();

Or if you just want a single matching row you can use .firstOrDefault().

Like this: itemforSearch = uc.UserList.FirstOrDefault(o => o.Name == userinfo.Name);

---

EDIT further explanation on returning multiple user result The controller and the GetSearchedItem function would need to use List<User>() to return multiple users as result, and in the view, the function OnSearchSuccess(data) would need to loop through the list of users.

Below I have modified the controller to use List but I'm not sure how to use JsonResult with a list so you would have to look that up if it doesn't work.

[HttpPost]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public ActionResult Search(User userinfo) 
{
    const string noResult = "Search Result Not Found";
    var itemforSearch = new List<User>();
    using (var uc = new UserContext()) 
    {
        if (userinfo.Name == null && userinfo.Email == null)
        {
            List<User> users;
            users = uc.UserList.ToList();
            return new JsonResult { Data = null };
        }
        if (userinfo.Name != null) 
        {
            itemforSearch = uc.UserList.Where(o => o.Name == userinfo.Name).ToList();
            return GetSearchedItem(noResult, itemforSearch);
        } 
        else 
        {
            if (userinfo.Email != null) 
            {
                itemforSearch = uc.UserList.Where(o => o.Email == userinfo.Email).ToList();
                return GetSearchedItem(noResult, itemforSearch);
            }
        }
    }
    return View(itemforSearch);
}

private ActionResult GetSearchedItem(string noResult, List<Models.User> itemforSearch) 
{
    if (itemforSearch.Count > 0) 
    {
        // Not sure how JsonResult works with lists but try take a look at this post http://stackoverflow.com/questions/9110724/serializing-a-list-to-json
        return new JsonResult { Data = itemforSearch };
    } 
    else 
    {
        throw new Exception("User with provided information was not find");
    }
}