1

I am new in ASP.NET so I have problem with duplicate records in my search enginee. When I search for the towns, some records are duplicate some of them are tree-douple.

Picture 1

Picture 2

Controller.cs

[Authorize(Roles = "Attorney")]
public ActionResult CreateNext(int? id)
{
    var currentUser = manager.FindById(User.Identity.GetUserId());
    var nfDoc = db.NFDOCUMENTS.Find(id);


    if (nfDoc.UserId == currentUser.Id && nfDoc.FacilityId == null)
    {
        var contacts = db.Contacts.Where(x => x.ContactCategory.Name == "Facility");
        List<string> towns = new List<string>();

        foreach (var item in contacts)
        {
            if (!towns.Contains(item.City))
            {
                towns.Add(item.City);  


            }
        }



        ViewData["towns"] = towns;


        var medProviders = db.Contacts.Where(x => x.ContactCategory.Name == "Facility" && x.Firstname != null).ToList();
        ViewData["medProviders"] = medProviders;

        var pat = db.Patients.Where(x => x.Id == nfDoc.PatientId).FirstOrDefault();
        ViewBag.Address = pat.Address1 + ", " + pat.City + ", " + pat.State + ", " + pat.Zip;
        ViewBag.InsuranceId = new SelectList(db.Contacts.Where(s => s.ContactCategory.Name == "Insurance Carrier"), "Id", "Firstname");
        ViewBag.AdjusterId = new SelectList(db.Contacts.Where(s => s.ContactCategory.Name == "Adjuster"), "Id", "Firstname");
        ViewBag.FacilityId = new SelectList(db.Contacts.Where(s => s.ContactCategory.Name == "Facility"), "Id", "Firstname");
        ViewBag.DoctorId = new SelectList(db.Contacts.Where(s => s.ContactCategory.Name == "Doctor"), "Id", "Firstname");

        ViewBag.PatientId = pat.Id;
        ViewBag.PatientName = pat.Firstname + " " + pat.Lastname;

        return View();
    }

    else
    {
        return RedirectToAction("Create");
    }
}

View

SO I expect after searchin engine filted towns, I want to avoid duplicates

  <div class="input-group col-md-12">
   <input id="search" type="text" class="form-control input-lg" placeholder="Search towns" />
   </div>
<ul class="list-group nav nav-pills nav-stacked" style="height: 200px; overflow-x: hidden; overflow-y: auto">
<li><a class="reload-towns"><i class="icon-location4"></i> ALL TOWNS</a></li>
  @foreach (var item in towns)
  {
 <li><a class="town" data-town="@item"><i class="icon-location3"></i> @item</a></li>
}
    </ul>
          </div>
                 </div>
                       </div>
Improve this question
10
  • ASP.NET is a web framework. It has nothing to do with data access. Most likely you want to ask about Entity Framework. You'll have to post the relevant code and explain what's wrong. What duplicates are you talking about? Which queries? What does the table contain, what did you expect, what did you get? Commented Aug 7, 2018 at 8:50
  • Btw if you load towns from your Contacts table, you'll always have at least two contacts from the same town. Commented Aug 7, 2018 at 8:51
  • @PanagiotisKanavos I edited my questions. SO I am looking for towns, after I filter towns, I get duplicate ones. I want to avoid that Commented Aug 7, 2018 at 8:55
  • If you want to select distinct items, use .Select() to select what you want and .Distinct() to select only distinct entries. You don't need to fill lists manually either, just call .ToList() or .ToArray() Commented Aug 7, 2018 at 8:55
  • I'll repeat it, ASP.NET MVC has nothing to do with data access. The view code doesn't explain anything. The code that loads towns though is rather inefficient and probably wrong Commented Aug 7, 2018 at 8:57

2 Answers 2

Reset to default
4

ASP.NET is a web framework. It has nothing to do with data access. Data access is the job of Entity Framework.

You don't explain which query returns duplicates, but I suspect that you have more than one contact per city. This loop :

    foreach (var item in contacts)
    {
        if (!towns.Contains(item.City))
        {
            towns.Add(item.City);  
        }
    }

uses .NET comparison rules which means casing and whitespace matters.

It could be rewritten as 

var towns=db.Contacts.Where(x => x.ContactCategory.Name == "Facility")
                     .Select(x=>x.City)
                     .Distinct()
                     .ToList();

This will generate a query that would look like :

SELECT DISTINCT City
FROM Contacts inner join ContactCategory on ContactCategory.ID=Contacts.CategoryID
Where ContactCategory.Name='Facility';

This will only unique city names and returns the result in a list. Whether the string's case matters depends on the City column's collation but the most common option is to use case-insensitive collations.

This could still fail if the City column contains dirty data, eg with leading or trailing spaces.

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

2 Comments

This looks like the best way to do it!
Both of the solution works fine for me :) Thank you guys :)
0

If you want to fix this on the c# side you can always use HashSet (hashset collection add another string to collection only when it's not duplicated).

So instead of using this:

List<string> towns = new List<string>();

Use:

HashSet<string> towns = new HashSet<string>();

From my point of view it's better to solve this problem on SQL side (entity framework):

var towns = db.Contacts.Where(x => x.ContactCategory.Name == "Facility").Select(m => m.City).Distinct().ToList();
Improve this answer

3 Comments

I get error when I use HasSet<> Additional information: Object reference not set to an instance of an object.
@Dennix you just doing something wrong. As I sad in post the best way is to use entity framework to get distinct values, but if you're interested how to do it on the c# side and how to use HashSet collection just show me how you tried to use this hashset and I will try to fix it ;)
Ok, just to check solution how does it work :) If I get any error I will write it here :) Thank you :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.