locked
How can I get a value from this navigation property? RRS feed

  • Question

  • User315142987 posted

    New to Entity Framework and this is confusing me.  I have 3 classes "Candidate", "Website" and "WebsiteType".  Candidate has a one to many relationship with Website.  WebsiteType is purely a lookup table to store the type of site being accessed.  I can return the collection of websites for a candidate but cannot figure out how to get the Name property stored in WebsiteType.

    Here are the 3 classes:

    Candidate.cs
    public partial class Candidate
        {
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
            public Candidate()
            {
                this.Websites = new HashSet<Website>();
            }
        
            public int Id { get; set; }
            public string LastName { get; set; }
            public string FirstName { get; set; }
            public string MiddleInitial { get; set; }
            public string NickName { get; set; }
            public string PhotoUrl { get; set; }
            public string Address { get; set; }
            public string Phone { get; set; }
            public string Email { get; set; }
            public Nullable<int> NumberOfSignatures { get; set; }
            public Nullable<bool> IncludesSupplementalFiling { get; set; }
            public string Bio { get; set; }
            public Nullable<int> NumberOfVotes { get; set; }
            public Nullable<int> Percentage { get; set; }
            public Nullable<int> CategoryId { get; set; }
            public Nullable<int> OfficeId { get; set; }
            public Nullable<int> PoliticalPartyId { get; set; }
        
            public virtual Category Category { get; set; }
            public virtual Office Office { get; set; }
            public virtual PoliticalParty PoliticalParty { get; set; }
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
            public virtual ICollection<Website> Websites { get; set; }
        }
    
    Website.cs
    public partial class Website
        {
            public int WebsiteId { get; set; }
            public string Url { get; set; }
            public Nullable<int> WebsiteTypeId { get; set; }
            public Nullable<int> CandidateId { get; set; }
        
            public virtual Candidate Candidate { get; set; }
            public virtual WebsiteType WebsiteType { get; set; }
        }
    
    WebsiteType.cs
     public partial class WebsiteType
        {
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
            public WebsiteType()
            {
                this.Websites = new HashSet<Website>();
            }
        
            public int WebsiteTypeId { get; set; }
            public string SiteName { get; set; }
        
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
            public virtual ICollection<Website> Websites { get; set; }
        }

    Below is the code I am using to access the data and project to my DTO.  Again my goal is to return the "Name" property from WebsiteType along with my Website collection.  Could anyone point me in the right direction?

     public CandidateDetailsDTO GetCandidateDetails(int id)
            {
                db.Configuration.LazyLoadingEnabled = false;
                var candidate = (from w in db.Websites
                                 join c in db.Candidates on w.CandidateId equals c.Id
                                 join wt in db.WebsiteTypes on w.WebsiteTypeId equals wt.WebsiteTypeId
                                 select new CandidateDetailsDTO
                                {
                                    Id = c.Id,
                                    LastName = c.LastName,
                                    FirstName = c.FirstName,
                                    Bio = c.Bio,
                                    Address = c.Address,
                                    CategoryName = c.Category.CategoryName,
                                    PoliticalParty = c.PoliticalParty.PartyName,
                                    Office = c.Office.OfficeName,
                                    Websites = w.Candidate.Websites
                                }).FirstOrDefault(c => c.Id == id);

    Monday, March 12, 2018 11:02 PM

All replies

  • User-707554951 posted

    Hi ShaunM

    I use the following to test, it works well, you could refer to it:

     public class WebContext: DbContext
        {
            public WebContext (): base("name=WebDBConnectionString")
            {
            }
            public DbSet<Candidate> Candidates { get; set; }
            public DbSet<Website> Websites { get; set; }
            public DbSet<WebsiteType> WebsiteTypes { get; set;}
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            }
        }
        public class Candidate
        {
            public int Id { get; set; }
            public string CandidateName { get; set; }
            public virtual ICollection<Website> Websites { get; set; }
        }
        public class Website
        {
            public int WebsiteId { get; set; }
            public string WebsiteName { get; set; }
            public Nullable<int> CandidateId { get; set; }
            public virtual Candidate Candidate { get; set; }
            public Nullable<int> WebsiteTypeId { get; set; }
            public virtual WebsiteType WebsiteType { get; set; }
        }
        public class WebsiteType
        {
            public int WebsiteTypeId { get; set; }
            public string WebsiteTypeName { get; set; }
            public virtual ICollection<Website> Websites { get; set; }
        }
    
    public class DBInitializer : System.Data.Entity.DropCreateDatabaseIfModelChanges<WebContext>
        {
            protected override void Seed(WebContext context)
            {
                var Website1 = new List<Website>
                {
                new Website{ WebsiteId=1, WebsiteName="WebsiteName2",  WebsiteType=new WebsiteType {  WebsiteTypeId=1, WebsiteTypeName="WebsiteTypeName1", }  },
                new Website {  WebsiteId=2, WebsiteName="WebsiteName2", WebsiteType=new WebsiteType {  WebsiteTypeId=2, WebsiteTypeName="WebsiteTypeName2", }
                }
                };
                var Website2 = new List<Website>
                {
                new Website{ WebsiteId=3, WebsiteName="WebsiteName3", WebsiteTypeId=1},
                new Website {  WebsiteId=4, WebsiteName="WebsiteName4", WebsiteTypeId=2}
                };
                var Candidates = new List<Candidate>
                {
                new Candidate{ Id=1, CandidateName="CandidateName1", Websites=Website1},
                new Candidate{Id=2, CandidateName="CandidateName2", Websites=Website2},
             
                };
                Candidates.ForEach(s => context.Candidates.Add(s));
                context.SaveChanges();
            }
        }

    In web config:

     <add name="WebDBConnectionString" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Web.mdf;Integrated Security=True"
          providerName="System.Data.SqlClient" />
     <entityFramework>
    
        <contexts>
          <context type="CaseTest.March.WebContext, CaseTest">
            <databaseInitializer type="CaseTest.March.DBInitializer, CaseTest" />
          </context>
        </contexts>
      </entityFramework>

    in codebehind:

     protected void Page_Load(object sender, EventArgs e)
            {
            
                WebContext db = new WebContext();
                var candidate = (from w in db.Websites
                                 join c in db.Candidates on w.CandidateId equals c.Id
                                 join wt in db.WebsiteTypes on w.WebsiteTypeId equals wt.WebsiteTypeId
                                 select new CandidateDetailsDTO
                                 {
                                      id = c.Id,
                                       candidatename=c.CandidateName,
                                      websitetypename=wt.WebsiteTypeName
                                 }).FirstOrDefault(c => c.id == 1);
              
            }

    Output whe debuging:

    Best regards

    Cathy

    Tuesday, March 13, 2018 9:12 AM
  • User315142987 posted

    Thanks Cathy but this doesn't fully help my issue.  This will return one website name for each candidate but is there a way to return a collection of them and include the SiteName property?  Here is the DTO class that I am trying to return:

        public class CandidateDetailsDTO
        {
            public int Id { get; set; }
            public string LastName { get; set; }
            public string FirstName { get; set; }
            public string MiddleInitial { get; set; }
            public string NickName { get; set; }
            public string PhotoUrl { get; set; }
            public string Address { get; set; }
            public string Phone { get; set; }
            public string Email { get; set; }
            public string Bio { get; set; }
            public string CategoryName { get; set; }
            public string PoliticalParty { get; set; }
            public string Office { get; set; }
    
            public ICollection<Website> Websites { get; set; }
        }

    Wednesday, March 14, 2018 7:02 AM