asp.net mvc 5 get related data from different tables

Question

I'm trying to get specific data from different tables based on some requirements, The program is supposed to take some columns of choosing from tables: Contatti, Contacts and Companies which are at different locations and bring them together.

Contact is connected to Companies through CompanyID

What i want is to display the company name basing on the CompanyID field in Contact table. The problem is that i iterate through a list to get the data in the view, and because of that i can't seem to get a company name based on the company ID attribute which is the Contact Foreign Key to the Companies table, what i get is all the companies names, because they're in the same list.

I'm sure there is an easy way to do this but this is a new world for me, thank you for any help.

Contact Model:

public class Contact
{
[Key]
public int ContactId { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string State { get; set; }
public string Zip { get; set; }
[DataType(DataType.EmailAddress)]
public string Email { get; set; }
[ForeignKey("Companies")]
public int CompanyId { get; set; }
public virtual ICollection<Companies> Companies { get; set; }
[Required]
//public virtual Contatti Contatti { get; set; }
public virtual ICollection<Contatti> Contatti { get; set; }
}

Companies model:

public class Companies
{
[Key]
public int CompanyId { get; set; }
public string CompanyName { get; set; }
public string CompanyAddress { get; set; }
public string CompanyCity { get; set; }
public string CompanyState { get; set; }
public string CompanyZip { get; set; }
public string CompanyArea { get; set; }
}

Controller:

public ActionResult Index(String Page)
    {

        ContactsUni2 CU = new ContactsUni2();
        CU.Contattis = db2.Contatti.ToList();
        CU.Contacts = db.Contacts.ToList();
        CU.Companies = db.Companies.ToList();
        List<ContactsUni2> contactlist = new List<ContactsUni2>();
        contactlist.Add(CU);
        return View(contactlist);
    }

View:

    @foreach (var item in Model)
    {
        for (int i = 0; i < item.Contacts.Count; i++)
        {
            <tr>
                <td>
                    @item.Contacts[i].ContactId
                </td>
                <td>
                    @item.Contacts[i].Name
                </td>
                <td>
                    @item.Contacts[i].Address
                </td>
                <td>
                    @item.Contacts[i].CompanyId
                </td>
                    @if (@item.Contacts[i].CompanyId == item.Companies[i].CompanyId)
                        {
                    <td>
                        @item.Companies[i].CompanyName
                    </td>
                    <td>
                        @item.Companies[i].CompanyCity
                    </td>
                    <td>
                        @item.Companies[i].CompanyArea
                    </td>
                        }
                    }
                </tr>
                <tr>
                    <td>
                        @item.Contattis[i].ContattoID
                    </td>
                    <td>
                        @item.Contattis[i].Nome
                    </td>
                    <td>
                        @item.Contattis[i].Citta
                    </td>
                    <td>
                        @item.Contattis[i].CodicePostale
                    </td>
                    <td>
                        @item.Contattis[i].Email
                    </td>
            </tr>


        }
    }

</table>

</body>
</html>

AarónBC. · Accepted Answer · 2016-11-22 17:28:37Z

1

You can try populating the company collection for each of your contacts and then fix the view to access its own member directly instead of looking for it in the Companies list.

public ActionResult Index(String Page)
{

    ContactsUni2 CU = new ContactsUni2();
    CU.Contattis = db2.Contatti.ToList();
    CU.Contacts = db.Contacts.ToList();
    CU.Companies = db.Companies.ToList();

    foreach(var contact in CU.Contacts)
    {
        contact.Companies = CU.Companies
                          .Where(com => com.CompanyId == contact.CompanyId)
                          .ToList();
    }

    List<ContactsUni2> contactlist = new List<ContactsUni2>();
    contactlist.Add(CU);
    return View(contactlist);
}

In the view, you can replace:

@if (@item.Contacts[i].CompanyId == item.Companies[i].CompanyId)
{
  <td>
   @item.Companies[i].CompanyName
 </td>
 <td>
   @item.Companies[i].CompanyCity
 </td>
 <td>
   @item.Companies[i].CompanyArea
 </td>
}

With something like this: (Notice that the if statement is removed)

<td>
   @item.Contacts[i].Companies[0].CompanyName
</td>
<td>
   @item.Contacts[i].Companies[0].CompanyCity
</td>
<td>
   @item.Contacts[i].Companies[0].CompanyArea
</td>

EDIT: Since ICollection does not support indexes, the companies collection should be changed from

public virtual ICollection<Companies> Companies { get; set; }

to

public virtual IList<Companies> Companies { get; set; }

Or any other type of collection supporting indexes.

edited Nov 22, 2016 at 17:28

answered Nov 22, 2016 at 4:13

AarónBC.

1,3309 silver badges14 bronze badges

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

4 Comments

Dantuzzo Over a year ago

Thank you for the suggestion, the view however does not work for me, doesn't allow me to cast Companies to Companies, what's the idea behind Companies[i].Companies[0]? Could you make the code a bit more specific? Sorry for the trouble.

AarónBC. Over a year ago

Oops!, i meant Contacts[i].Companies[0], going to fix it. I noticed that you use a collection in contact model to relate companies but it seems the intention is to only have one company related to each contact (that's the reason for .Companies[0]), at least that is what i tought when i looked at your CompanyId foreign key.

Dantuzzo Over a year ago

I see, it makes sense however it says that I cannot apply indexing with [] to an expression of type ICollection<Companies> at Contacts[i].Companies[0], this environment is a real primadonna

AarónBC. Over a year ago

My bad, ICollection doesn't support indexes, maybe you could change them to IList: public virtual IList<Companies> Companies { get; set; }

Collectives™ on Stack Overflow

asp.net mvc 5 get related data from different tables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related