How to combine two query results together in C# & ASP.NET MVC controller

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:

CompanyName	ContactPerson
Company A	Bob Smith
Company B	Fred Stevens
Company A	Rick Moore
Company C	Bob Smith

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:

Customer	Occurrences
Company A	2
Bob Smith	2

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!

Solution

In your case, you can generate a generic model to hold the values from your grouped lists. First create a generic view model that we shall pass to your View:

public class GenericModel
{
    public GenericModel() { }

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

Now using the exact same piece of code logic that you have, you do a Union on the two grouped lists when using this GenericModel:

public ActionResult CustomerAlerts()
{
    List<GenericModel> myModel=new List<GenericModel>();
    // 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 GenericModel()
                      {
                          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 GenericModel()
                      {
                          Customer = g.Key,
                          Occurrence = g.Count(),
                      };

    var resultForCompany = company.Where(x => x.Occurrence > 1).ToList();
    var resultForContact= contact.Where(x => x.Occurrence > 1).ToList();
    
    //Now merge the two lists since they have are of the same type:
    myModel=resultForCompany.Union(resultForContact).ToList();
    
    //Here you are sending to your partial view a model of type List<GenericModel>
    return PartialView(myModel);
}

Now since we have generic model, your View will expect a Model of type List<GenericModel>. The rest of the display to show the data will be the same that you have posted in your question.