asp.net 4.5 MVC SQL select like

Question

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

crthompson · Accepted Answer · 2013-12-12 03:55:45Z

3

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.

edited Dec 12, 2013 at 3:55

answered Dec 12, 2013 at 3:49

crthompson

15.9k6 gold badges64 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

crthompson Over a year ago

@user1300788 Sorry, thats because you are trying to overwrite the members variable with something that isnt the same type. Create a new variable called towns (which is more meaningful anyway) and set that.

user1300788 Over a year ago

I still get the same error :/ Can you edit your answer to show how you do this?

user1300788 Over a year ago

I feel really silly but I posted the wrong bit of my code to where the distinct would go.

@foreach (var item in Model) {       string mytown = item.town.*enter distinct here*   <option value="@mytown"> @mytown</option>

how would I fit distinct into this?

crthompson Over a year ago

It would be something like @foreach (var item in Model.Select(m => m.Town).Distinct()) ... Then you would just use item, instead of mytown.

StuartLC · Accepted Answer · 2013-12-12 04:45:08Z

2

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();

answered Dec 12, 2013 at 4:45

StuartLC

108k18 gold badges225 silver badges294 bronze badges

2 Comments

user1300788 Over a year ago

How can I add an orderBy alphabetical to this bit(m => m.town).Distinct(); ? Code works great btw thanks

crthompson Over a year ago

@user1300788 .Distinct().OrderBy(t => t); (and +1 StuartLC)

Collectives™ on Stack Overflow

asp.net 4.5 MVC SQL select like

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related