CODEWITHSUNDEEP
c#asp.netsqlasp.net-mvc
user1300788
user1300788

Reputation: 181

asp.net 4.5 MVC SQL select like

So I have this code: 

 public ActionResult SearchIndex(string id)
    {
        string searchString = id;


        var members = from m in db.Members
                      select m;

        if (!String.IsNullOrEmpty(searchString))
        {
            members = members.Where(m => m.town.Equals(searchString));
        }

        return View(members);
    }

This selects all the town names in my database and I've got it to display in a drop down menu.

How can I change this so that when it's displaying the town names, it will only show each town name once.

For example, if I have 3 londons in the database it will show all 3 londons in the drop down menu. I want it to only show 1 london

Upvotes: 3

Views: 1956

Answers (2)

StuartLC
StuartLC

Reputation: 107277

I'm not sure that the accepted answer addresses your original question, which was to return an enumerable of Member from a Controller to a View, with an optional filter on town, restricting the list to a maximum of one member in each town:

var members = db.Members; // Or .AsQueryable();

if (!String.IsNullOrEmpty(searchString))
{
    // Apply the conditional filter, returning Members
    members = members.Where(m => m.town == searchString);
}

// First of each member only (randomly)
return View(members
             .GroupBy(m => m.town)
             .Select(grp => grp.First()));

It seems however that your real requirement is to

  • View with filtered IEnumerable<Member> as the @Model
  • As a secondary requirement, populate a dropdown with distinct towns

In this case, you can also project an IEnumerable<string>, e.g. into ViewBag, or better, create a custom ViewModel class containing both the filtered list of Member and the distinct towns for the drop down.

ViewBag.DistinctTowns = db.Members.Select(m => m.town).Distinct();

Upvotes: 2

crthompson
crthompson

Reputation: 15865

You want to use the Distinct method on IEnumerable

var towns = members.Where(m => m.town.Equals(searchString))
                  .Select(m => m.town).Distinct();

This will Select only the town value, then get the Distinct values, so London will only come up once.

Upvotes: 3

Related Questions