Entity Framework Many to Many Relationships Accessing Data

Question

I am doing my first project with many to many relationships and I am still newbie with asp.net, so please forgive the basic question. The project that I am working on has many entities (person, business, etc.) and each entity can have many phone numbers (home, work, cell, etc.). I have to do a phone number search that will return all of the entities (in this instance only people) that have that phone number associated with them. I cannot figure out how to access the entityID when I have the NumberID!

Models:

    namespace CaseManagement.Models
{
    using System;
    using System.Collections.Generic;

    public partial class IdentityNumber
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public IdentityNumber()
        {
            this.IdentityEntities = new HashSet<IdentityEntity>();
        }

        public int NumberID { get; set; }
        public System.DateTime DateCreated { get; set; }
        public string TenDigitNumber { get; set; }
        public bool Preferred { get; set; }
        public byte[] RowVersion { get; set; }
        public string ChangedBy { get; set; }
        public int NumberTypeID { get; set; }

        public virtual IdentityNumberType IdentityNumberType { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<IdentityEntity> IdentityEntities { get; set; }
    }
}


    namespace CaseManagement.Models
{
    using System;
    using System.Collections.Generic;

    public partial class IdentityEntity
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public IdentityEntity()
        {
            this.ContactLogs = new HashSet<ContactLog>();
            this.IdentityEntityCasePartyJoins = new HashSet<IdentityEntityCasePartyJoin>();
            this.Files = new HashSet<File>();
            this.IdentityAddresses = new HashSet<IdentityAddress>();
            this.IdentityBusinesses = new HashSet<IdentityBusiness>();
            this.IdentityEmails = new HashSet<IdentityEmail>();
            this.IdentityNumbers = new HashSet<IdentityNumber>();
            this.IdentityPersonNames = new HashSet<IdentityPersonName>();
            this.IdentityPersonPositions = new HashSet<IdentityPersonPosition>();
            this.Notes = new HashSet<Note>();
        }

        public int EntityID { get; set; }
        public int EntityTypeID { get; set; }
        public System.DateTime DateCreated { get; set; }
        public byte[] RowVersion { get; set; }
        public string ChangedBy { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<ContactLog> ContactLogs { get; set; }
        public virtual IdentityEntityType IdentityEntityType { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<IdentityEntityCasePartyJoin> IdentityEntityCasePartyJoins { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<File> Files { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<IdentityAddress> IdentityAddresses { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<IdentityBusiness> IdentityBusinesses { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<IdentityEmail> IdentityEmails { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<IdentityNumber> IdentityNumbers { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<IdentityPersonName> IdentityPersonNames { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<IdentityPersonPosition> IdentityPersonPositions { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Note> Notes { get; set; }
    }
}

Controller:

    namespace CaseManagement.Controllers
{
    public class IdentityPersonSelectController : Controller
    {
        private CaseMGMTEntities db = new CaseMGMTEntities();
        // GET: IdentityPersonSelect
        public ActionResult Index(int id)
        {
            var phoneLogs = db.PhoneLogs.Include(p => p.ContactInquirySource).Include(p => p.ContactReason).Include(p => p.EthicsEmployee);
            var recordFromPhoneLog = (from x in phoneLogs
                          where x.PhoneLogID == id
                          select x).First();
            var person = db.IdentityEntities.Include(p => p.IdentityAddresses).Include(p => p.IdentityEmails).Include(p => p.IdentityNumbers).Include(p => p.IdentityPersonNames).Include(p => p.IdentityPersonPositions);



            var sameName = (from x in db.IdentityPersonNames
                            where x.FirstName == recordFromPhoneLog.FirstName.ToLower() &&
                            x.LastName == recordFromPhoneLog.LastName.ToLower()
                            select x).ToList();


            var samePhone = (from x in db.IdentityNumbers
                             where x.TenDigitNumber == recordFromPhoneLog.Phone
                             select x).ToList();

            //going to add the people to this list in the for loop once I can figure out how to access the entityID
            var people = new List<IdentityPersonName>();
            for (int i = 0; i < samePhone.Count; i++ )
            {
                //get the numberID of the current phone number
                var numID = samePhone[i].NumberID;
                //get entityID of that number
                var entID = (from x in person
                             where x.//this is where I am stuck and cannot figure out how to get the entityID from the join table based on the numberID 

                             );
            }



            ViewBag.FN = recordFromPhoneLog.FirstName;
            ViewBag.LN = recordFromPhoneLog.LastName;
            return View(sameName);
        }
    }
}

Answer 1

Simply:

var entities = db.IdentityEntities.Where(m => m.IdentityNumbers.Any(n => n.NumberID == numberID));

In short, this selects any IdentityEntity instances that have a related IdentityNumber instance whose id is numberID.