I have a table called Complaint_Log and the relevant column names in it are CompanyName and ContactPerson.

What I’m trying to do is generate a partial view for my dash page of a table that loops through the Complaint_Log data and if there is duplicate values of the company name or the contact person, then have it get generated to a list. I also need it to get a count of how many duplicates there are and get rid of the duplicates.

I currently have a query I’m using that works perfect, but it’s only for the CompanyName. I can’t figure out how to get it to loop through the list and include the ContactPerson (as the CompanyName) IF there is a duplicate of a ContactPerson, but doesn’t have the same CompanyName.

For example, lets say this is my list data below that’s in my table:

So as you can see, there are 2 Company A‘s with a different ContactPerson and 2 Bob Smith’s with a different CompanyName.

In this example I’d like my table generated to show:

If both of the Company A entries had Bob Smith as the ContactPerson, then the above table should only show Company A. I want it set up this way in case there’s a person that maybe switches companies or something.

I’m totally stuck on how to achieve this, I literally just cannot figure this out. Maybe the way I’m currently going about it is not the way I should be, but as of right now, I created a model called CompanyAlertModel, a model called ContactAlertModel and a model called CustomerAlertModel. They are all identical except for the name of the models. I’m sure I probably don’t need that many models, but since my query worked for the CompanyName like I wanted it to, I made another one doing the same query except I did it for the ContactName.

My goal was to create that third model and combine the queries for the CompanyName and for the ContactName and add it into the third model which is what I’d use for my table I’d generate, but I can’t seem to figure out how to combine them.

This is my model (like I said the other ones are the same except for the names):

public class CompanyAlertModel
{
    public CompanyAlertModel() { }

    public string Customer { get; set; }
    public Nullable<int> Occurrence { get; set; }
    public Complaint_Log Complaints { get; set; }
}

Here is my controller:

public ActionResult CustomerAlerts()
{
    // Get list of all the companies with more than 1 occurrence
    var company = from c in db.Complaint_Log
                  group c by c.CompanyName into g
                  orderby g.Key
                  select new CompanyAlertModel()
                      {
                          Customer = g.Key,
                          Occurrence = g.Count(),
                      };

    // Get list of all the contact names with more than 1 occurrence
    var contact = from c in db.Complaint_Log
                  group c by c.ContactPerson into g
                  orderby g.Key
                  select new ContactAlertModel()
                      {
                          Customer = g.Key,
                          Occurrence = g.Count(),
                      };

    var result = company.ToList();

    return PartialView(result.Where(x => x.Occurrence > 1).ToList());
}

And my table on the view page:

 <table>
    <tr>
        <th>
            Customer Name
        </th>
        <th>
            Occurrences
        </th>
    </tr>

    @foreach (var item in Model)
    {
        <tr>
            <td>
                @item.Customer
            </td>
            <td>
                @item.Occurrence
            </td>                
        </tr>
    }
</table>

Any help would be greatly appreciated!