none
create 1-N code first database w/attributes - getting all the instances of tables to be created RRS feed

  • Question

  • I am trying to generate a database - correctly, from code first.  For this project, I have to use constraints

    (square bracket notation) in the POCO classes.  The examples on 1-N code that I found by googling didn't

    completely show how to deal with setting up the 1-N relationship in the database so that an instance

    was created with a foreign key mapping to a primary key of another table.  This is a test at work, which we

    are allowed to get help on, and we're supposed to show how long it takes us to get code completed, even

    though help is allowed and we're not completely on our own. I have a 1-N relationship from Application to Individual

    (because a family can share an application) and a 1-N relationship from Individual to IndividualBenefits.

    I am wondering if I am putting in the correct properties for my database to map these relationships.  I am

    also wondering what the correct way in code is to create the database correctly, so that all the table instances are

    generated (the tables are generating, just the specific instances with data are not all being created), where one table may contain a

    foreign key to link to another table.  I have played around with

    this code, trying to add to the lists, not adding to the lists, etc.. (commented out parts may have been

    uncommented and experimented with at some point.. I am just trying different things to see if I can figure

    out) but the best I could do was generate one

    table instance - the Application_ table, I cannot generate all three with one table containing a foreign key to another

    table so the 1-N relationships are properly mapped in the database.

    Here are my poco classes:

     [Table("Application_")]
        public class Application_
        {
            [Key]
            public Guid ApplicationID { get; set; }
            public DateTime SubmittedDate { get; set; }
           //  public virtual List<Individual> Individuals { get; set; }
        }
        [Table("Individual")]
        public class Individual
        {
            [Key]
            public Guid IndividualID { get; set; }
            [ForeignKey("Application_")]
            public Guid ApplicationID { get; set; }
            public virtual Application_ Application_ { get; set; }
            [Required]
            [StringLength(15, ErrorMessage = "Name must be no more than 15 characters long.")]
            public string FirstName { get; set; }
            [Required]
            [StringLength(21, ErrorMessage = "Name must be no more than 21 characters long.")]
            public string LastName { get; set; }
            public bool IsHeadOfHousehold { get; set; }
            [Required(ErrorMessage = "SSN required.")]
            public string SSN { get; set; }
            public DateTime DOB { get; set; }
            [StringLength(1)]
            public string Gender { get; set; }
            //public virtual List<IndividualBenefit> IndividualBenefits { get; set; }
    
        }
        [Table("IndividualBenefit")]
        public class IndividualBenefit
        {
            [Key]
            public Guid BenefitID { get; set; }
            [ForeignKey("Individual")]
            public Guid IndividualID { get; set; }
            public virtual Individual Individual { get; set; }
            [Required]
            [StringLength(3)]
            public string BenefitCode { get; set; }
        }
        public class BenefitsContext : DbContext
        {
            public BenefitsContext() : base("ApplicationServices4") { }
            public DbSet<Application_> Applications { get; set; }
            public DbSet<Individual> Individuals { get; set; }
            public DbSet<IndividualBenefit> IndividualBenefits { get; set; }
        }

    Here is my codefirst database generation code:

      
    using (var db = new BenefitsContext())
                {
                    string firstName = ASPXTBFirst.ToString();
                    string lastName = ASPXTBLast.ToString();
                    string sSN = ASPXTBSSN.ToString();
                    DateTime dOB = ASPXDEDOB.Date;
                    string gender = DDLGender.SelectedValue.ToString();
                    string genderChar = "M";
                    if (gender == "Female") genderChar = "F";
                    string hOH = DDLHeadOfHousehold.SelectedValue.ToString();
                    bool hOHBool = false;
                    if (hOH == "Yes") hOHBool = true;
                    string selectedBenefits = "";
                    switch (DDLBenefits.SelectedValue.ToString())
                    {
                        case "Cash Assistance":
                            selectedBenefits = "CA";
                            break;
                        case "Food Stamps":
                            selectedBenefits = "FS";
                            break;
                        case "Child Care":
                            selectedBenefits = "CI";
                            break;
                    }
                    
                    Guid individualID = Guid.NewGuid();
                    Guid applicationID = Guid.NewGuid();
                    Guid benefitID = Guid.NewGuid();
                    
                    Application_ newApplication = new Application_ { ApplicationID = applicationID, SubmittedDate = DateTime.Today };
                    
                    Individual newIndividual = new Individual
                    {
                        IndividualID = individualID,
                        ApplicationID = applicationID,
                        Application_ = newApplication,
                        FirstName = firstName,
                        LastName = lastName,
                        IsHeadOfHousehold = hOHBool,
                        SSN = sSN,
                        DOB = dOB,
                        Gender = genderChar
                    };
    
                    IndividualBenefit newIndividualBenefit = new IndividualBenefit { BenefitID = benefitID, 
                        Individual = newIndividual, 
                        IndividualID = individualID, 
                        BenefitCode = selectedBenefits };
    
                  
                    db.IndividualBenefits.Add(newIndividualBenefit);
                    db.Individuals.Add(newIndividual);
                    db.Applications.Add(newApplication);
                   
    
    
                    try
                    {
    
                        int numb = db.SaveChanges();
                        LBDatabase.Items.Add(numb.ToString());
                    }
                    catch (System.Data.Entity.Validation.DbEntityValidationException er)
                    {
                        LBDatabase.Items.Add(er.EntityValidationErrors.ToString());
                    }
    	}

    Friday, November 9, 2012 6:46 PM

Answers

  • Hi Jamesiw,

    Welcome to the MSDN forum.

    Please check if follow code works (replace your code):

                    Application_ newApplication = new Application_ { ApplicationID = applicationID, SubmittedDate = DateTime.Today, Individuals = new List<Individual>() };//Add List initialization
    
                    Individual newIndividual = new Individual
                    {
                       IndividualID = individualID,
                        ApplicationID = applicationID,
                        Application_ = newApplication,
                        FirstName = firstName,
                        LastName = lastName,
                        IsHeadOfHousehold = hOHBool,
                        SSN = sSN,
                        DOB = dOB,
                        Gender = genderChar,
                        IndividualBenefits = new List<IndividualBenefit>()
                    };//Add List initialization
    
                    IndividualBenefit newIndividualBenefit = new IndividualBenefit
                    {
                        BenefitID = benefitID,
                        Individual = newIndividual,
                        IndividualID = individualID,
                        BenefitCode = selectedBenefits
                    };
    
                    newApplication.Individuals.Add(newIndividual);//Add item into list
                    newIndividual.IndividualBenefits.Add(newIndividualBenefit);//Add item into list
    
                    db.IndividualBenefits.Add(newIndividualBenefit);
                    db.Individuals.Add(newIndividual);
                    db.Applications.Add(newApplication);
    

    Best Regards,


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Alexander Sun Thursday, November 22, 2012 7:03 AM
    Tuesday, November 13, 2012 8:34 AM