none
Using Flent API to return related records with an existing database. Gets primary record does not get related records. RRS feed

  • Question

  • The following code executes without error and while the correct subject is returned, the addresses are not,  The Addresses propterty on subject has a count of 0.  Any help with this is appreciated.  Below this line is the repository implementation that has this line of code, followed by POCO classe and DbContex and configuration classes.

     Subject subject =  context.Subjects.Include("Addresses").Where(p => p.UserName == userName && p.Password == passWord).Single();
    
    

     

    using CompositeAppointment.Domain.Entities;
    using CompositeAppointment.Domain.Abstract;
    
    namespace CompositeAppointment.Domain.Concrete 
    {
        public class SubjectRepository : ISubjectRepository
        {
    
           private EFDbContext context = new EFDbContext();
    
           public Subject GetSubject(string userName, string passWord) 
            {
                Subject subject =  context.Subjects.Include("Addresses").Where(p => p.UserName == userName && p.Password == passWord).Single();
                foreach (Address a in subject.Addresses) // <-- just test code
                {
                    string s = a.Address1;
                }
                return subject;
            }
    
           public void SaveSubject(Subject subject)
           {
               if (subject.SubjectID == 0)
               {
                   context.Subjects.Add(subject);
               }
               else
               {
                   context.Entry(subject).State = EntityState.Modified;
               }
               context.SaveChanges();
           }
     
        }
    }
    
    

    POCO classes

    namespace CompositeAppointment.Domain.Entities
    {
        public class Subject
        {
            public int SubjectID { get; set; }
            public int OrgID { get; set; }
            public DateTime? ApppointmentDay { get; set; }
            public string AppointmentTime { get; set; }
            public string UserName { get; set; }
            public string Password { get; set; }
            public string OfficerTitle { get; set; }
            public string SchoolClass { get; set; }
            public string FirstName { get; set; }
            public string LastName { get; set; }
    
            public List<Associate> Associates { get; set; }
            public List<Address> Addresses { get; set; }
    
        }
    }
    
    namespace CompositeAppointment.Domain.Entities
    {
        public class Address
        {
            public int AddressID { get; set; }
            public string FirstName { get; set; }
            public string LastName { get; set; }
            public string Address1 { get; set; }
            public string Address2 { get; set; }
            public string City { get; set; }
            public string State { get; set; }
            public string Zip { get; set; }
            public string Country { get; set; }
            public string Phone { get; set; }       
            
            public virtual Subject Subject { get; set; }
        }
    }
    

    DBContext with configuration classes using Fluent API

    using CompositeAppointment.Domain.Entities;
    
    namespace CompositeAppointment.Domain.Concrete
    {
        public class EFDbContext : DbContext
        {
            public DbSet<Subject> Subjects { get; set; }
            public DbSet<Address> Addresses { get; set; }
    
            public EFDbContext(): base() // since I am not specifying a connection string, defaults to connection string of same name in web.config
            {
                Database.SetInitializer<EFDbContext>(null);
            }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
               modelBuilder.Configurations.Add(new SubjectConfiguration());
               modelBuilder.Configurations.Add(new AddressConfiguration());
               modelBuilder.Entity<Subject>().Map(m => { m.ToTable("tblPersonComp"); });
               modelBuilder.Entity<Address>().Map(m => { m.ToTable("tblAddress"); });
            }
        }
    }
    
    namespace CompositeAppointment.Domain.Entities
    {
        public class SubjectConfiguration : EntityTypeConfiguration<Subject>
        { 
            public SubjectConfiguration()
                : base()
            {
                HasKey(p => p.SubjectID);
                Property(p => p.SubjectID).HasColumnName("Person_ID").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).IsRequired();
                Property(p => p.OrgID).HasColumnName("Org_ID").HasDatabaseGeneratedOption(DatabaseGeneratedOption.None).IsRequired();
                Property(p => p.ApppointmentDay).HasColumnName("DayDT").IsOptional();
                Property(p => p.AppointmentTime).HasColumnName("StartTime24").IsRequired();
                Property(p => p.UserName).HasColumnName("Email").IsRequired();
                Property(p => p.Password).HasColumnName("WebPassword").IsRequired();
                Property(p => p.OfficerTitle).HasColumnName("Officer").IsOptional();
                Property(p => p.SchoolClass).HasColumnName("PledgeClass").IsRequired();
                Property(p => p.FirstName).HasColumnName("FirstName").IsRequired();
                Property(p => p.LastName).HasColumnName("LastName").IsRequired();
                HasMany(p => p.Addresses).WithRequired(x => x.Subject).Map(m => m.MapKey("CompPerson_ID"));
             }
        }
    }
    
    namespace CompositeAppointment.Domain.Entities
    {
        public class AddressConfiguration : EntityTypeConfiguration<Address>
        {
    
            public AddressConfiguration()
                : base()
            {
                HasKey(p => p.AddressID);
                Property(p => p.AddressID).HasColumnName("Address_ID").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).IsRequired();;
                Property(p => p.FirstName).HasColumnName("FName").IsRequired();
                Property(p => p.LastName).HasColumnName("LName").IsRequired();            
                Property(p => p.Address1).HasColumnName("Address1").IsRequired();
                Property(p => p.Address2).HasColumnName("Address2").IsOptional();
                Property(p => p.City).HasColumnName("City").IsRequired();
                Property(p => p.State).HasColumnName("USStateCode").IsRequired();
                Property(p => p.Zip).HasColumnName("PostalCode").IsRequired();
                Property(p => p.Country).HasColumnName("Country").IsOptional();
                Property(p => p.Phone).HasColumnName("PhoneVoice").IsRequired();
            }
        }
    }
    

     

     


    WhiskeyRomeoLima
    Sunday, October 23, 2011 3:58 PM

Answers

  • William,

    I can't recall issues with namespaces off the top of my head, but I will try to do what you did, and put the objects and the context in different namespaces. The only namespace realted issue that I can think of is this:

    https://connect.microsoft.com/data/feedback/details/694281/dbentityentry-getdatabasevalues-method-crashes-when-db-context-has-different-namespace-than-the-data-objects

    Please, keep us updated on your progress and I will let you know if I find something relevant to your problem.

    thank you,

    Julia


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, October 25, 2011 6:58 AM
    Moderator
  • Let me know if I misunderstood your problem. But if you want to explose a foreign key property on your dependent class you could use annotations to specify the foreign key and to which class it is related, like this:

     

        public class Address
        {
            public int AddressID { get; set; }
            public string AddressFirstName { get; set; }
            public string AddressLastName { get; set; }
    
            [ForeignKey("Subject")]
            public int CompPerson_ID { get; set; }
            public Subject Subject { get; set; }
        }
    

     

    you would then need to remove the fluent API configuration line:  (remove - HasMany(p => p.Addresses).WithRequired(x => x.Subject).Map(m => m.MapKey("CompPerson_ID")))

        public class SubjectConfiguration : EntityTypeConfiguration<Subject>
        {
            public SubjectConfiguration()
                : base()
            {
                HasKey(p => p.SubjectID);
                Property(p => p.SubjectID).HasColumnName("Person_ID").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).IsRequired();
                Property(p => p.SubjectFirstName).HasColumnName("SubjFirstName").IsRequired();
                Property(p => p.SubjectLastName).HasColumnName("SubjLastName").IsRequired();
                //HasMany(p => p.Addresses).WithRequired(x => x.Subject).Map(m => m.MapKey("CompPerson_ID"));
            }
        }

    you can then display the foreign key value:

                Subject subject = context.Subjects.Include(s => s.Addresses).Where(p => p.SubjectID == 1).FirstOrDefault();
                foreach (Address a in subject.Addresses) // <-- just test code
                {
                    Console.WriteLine(a.AddressID + " " + a.CompPerson_ID);
                }


    Please, let me know if that is not what you were looking for and I will try again:)

    thank you,

    Julia


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, October 27, 2011 6:56 PM
    Moderator

All replies

  • Hi,

    I'm not sure why it does work according to your code, I think you can try to use "Eagerly Loading":

    Context.Entry(subject).Collection(s=>s.Addresses).Load();

    // You can use "SQL Profiler" to watch if the T-SQL for Address has been ran.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, October 24, 2011 3:55 AM
    Moderator
  • That code runs but does not return the address.  So as in previous post, I don't think this forum is the place to learn EF.  I am sure it is helpful to many who have experience with EF and just help.
    WhiskeyRomeoLima
    Monday, October 24, 2011 3:43 PM
  • Hi, I tried to repro your problem using your code with a few modifications (added address and well as subject, used default connection, used lambda instead of string in the Include method) and the following code works fine.
    using System.Data.Entity.ModelConfiguration;
    using System.ComponentModel.DataAnnotations;
    using System.Data.Entity;
    using System.Data;
    
    
    
        public class Subject
        {
            public  int SubjectID { get; set; }
            public  string SubjectFirstName { get; set; }
            public  string SubjectLastName { get; set; }
          
            public List<Address> Addresses { get; set; }
        }
    
        public class Address
        {
            public  int AddressID { get; set; }
            public string AddressFirstName { get; set; }
            public string AddressLastName { get; set; }
            
            public Subject Subject { get; set; }
        }
    
     
    
    
        public class EFDbContext : DbContext
        {
            public DbSet<Subject> Subjects { get; set; }
            public DbSet<Address> Addresses { get; set; }
    
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
               modelBuilder.Configurations.Add(new SubjectConfiguration());
               modelBuilder.Configurations.Add(new AddressConfiguration());
               modelBuilder.Entity<Subject>().Map(m => { m.ToTable("tblPersonComp"); });
               modelBuilder.Entity<Address>().Map(m => { m.ToTable("tblAddress"); });
            }
        }
    
    
    
        public class SubjectConfiguration : EntityTypeConfiguration<Subject>
        { 
            public SubjectConfiguration()
                : base()
            {
                HasKey(p => p.SubjectID);
                Property(p => p.SubjectID).HasColumnName("Person_ID").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).IsRequired();
                Property(p => p.SubjectFirstName).HasColumnName("SubjFirstName").IsRequired();
                Property(p => p.SubjectLastName).HasColumnName("SubjLastName").IsRequired();
                HasMany(p => p.Addresses).WithRequired(x => x.Subject).Map(m => m.MapKey("CompPerson_ID"));
             }
        }
    
    
    
        public class AddressConfiguration : EntityTypeConfiguration<Address>
        {
    
            public AddressConfiguration()
                : base()
            {
                HasKey(p => p.AddressID);
                Property(p => p.AddressID).HasColumnName("Address_ID").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).IsRequired();;
                Property(p => p.AddressFirstName).HasColumnName("AddrFirstName").IsRequired();
                Property(p => p.AddressLastName).HasColumnName("AddrLastName").IsRequired();            
            }
        }
    
        public class SubjectRepository
        {
            private EFDbContext context = new EFDbContext();
    
            public void GetSubject()
            {
                Subject subject = context.Subjects.Include(s=>s.Addresses).Where(p => p.SubjectID == 1).FirstOrDefault();
                foreach (Address a in subject.Addresses) // <-- just test code
                {
                    Console.WriteLine(a.AddressID);
                }
            }
    
            public void SaveSubject(Address address)
            {
                if (address.AddressID == 0)
                {
                    context.Addresses.Add(address);
                }
                else
                {
                    context.Entry(address).State = EntityState.Modified;
                }
                context.SaveChanges();
            }
    
        }
    
        class Program
        {
            static void Main(string[] args)
            {
                Subject subject = new Subject();
                subject.SubjectFirstName = "SubjectFirstName";
                subject.SubjectLastName = "SubjectLastName";
                
                Address address = new Address();
                address.AddressID = 0;
                address.AddressFirstName = "AddressFirstName";
                address.AddressLastName = "AddressLastName";
                address.Subject = subject;
    
    
                SubjectRepository rep = new SubjectRepository();
                rep.SaveSubject(address);
                rep.GetSubject();
            }
        }


    thank you,

    Julia

     

    Please, let me know if you were able to make your code work.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, October 24, 2011 10:49 PM
    Moderator
  • Julia,

    Thanks for replying and taking the time to try and reproduce this problem.  My code will not let me use a lamba expression in the Include method.  That tells there is something wrong (from the beginning) because I expected to be able to use a lamda expression there.

    The question is what did you do differently that allowed you to use a lamada expression?  Did you let EF generate the database or did you create it by hand?  EF always works when it generates the database so there is something missing when EF doesn't generate the database and that is the answer.  But what is it???

    Also was everything in the same namespace?

     


    WhiskeyRomeoLima
    Monday, October 24, 2011 11:05 PM
  • I created a console application. Added a reference to EntityFramework version 4.1.0.0, System.Data, and System.Data.Entity and pasted the code above.

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, October 25, 2011 12:02 AM
    Moderator
  • Julia,

    My project structure is completely different because it is intended to be productional upon completion and I am using dependency injection.  But you have given me an idea.  I will roll back the complexity of the project structure until I am able to do want you have done.  However, I hope it doesn't come to all the code being in one file and one namespace.  But we will see.

    If you are interested I will post results here.  From what I could tell from your post the only things you changed was simplifying the classes and using the lamba expression in the Include method.  Let me know if I have missed something.

    William


    WhiskeyRomeoLima
    Tuesday, October 25, 2011 12:27 AM
  • William,

    I can't recall issues with namespaces off the top of my head, but I will try to do what you did, and put the objects and the context in different namespaces. The only namespace realted issue that I can think of is this:

    https://connect.microsoft.com/data/feedback/details/694281/dbentityentry-getdatabasevalues-method-crashes-when-db-context-has-different-namespace-than-the-data-objects

    Please, keep us updated on your progress and I will let you know if I find something relevant to your problem.

    thank you,

    Julia


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, October 25, 2011 6:58 AM
    Moderator
  • I copied and pasted your code in a console applicaton, changed names to match my environment, picked a real SubjectID and it worked.  So this led me to question my ASSumptions.

    First, I couldn't use a lambda expression in the Include method because I didn't have the System.Data.Entity using statement.

    Now (with red face) I have to admit of the nearly 63,000 records in the table, I chose 1 of the 3 who did not have addresses.  So the application was working all along.  When tested with a Subject record that actually has an Address record, it works.  I can used either the Navigation Property name in quotes or use a lamda express as you did.  Both work.

    I apologize to everyone for wasting their time.

    William

    PS:  My original code with the product level stucture is the applicaton that is working not just the console application.


    WhiskeyRomeoLima
    Tuesday, October 25, 2011 6:12 PM
  • It is not a problem at all. Thank you so much for letting us know that the code did work :) Please let us know if you come across other issues.

    You can also contact me by email: juliako@microsoft.com.

     

    Have a great day!

    -Julia


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, October 25, 2011 6:19 PM
    Moderator
  • Yes one more issue.  The Fluent API samples and tutorials didn't help me. I want to display CompPerson_ID at least the value of it in the Address class.  However, when I try to set it up in the AddressConfiguration class as a normal property the configuration fails complaing that CompPerson_ID is already defined.

    I added SubjectID as property in the Address classs.  I know this must be clashing with the configuration of the relationship in the Subject class but i do not know what to do about it.

    Property(p => p.SubjectID).HasColumnName("CompPerson_ID").IsRequired();

     

     


    WhiskeyRomeoLima
    Wednesday, October 26, 2011 12:15 AM
  • Let me know if I misunderstood your problem. But if you want to explose a foreign key property on your dependent class you could use annotations to specify the foreign key and to which class it is related, like this:

     

        public class Address
        {
            public int AddressID { get; set; }
            public string AddressFirstName { get; set; }
            public string AddressLastName { get; set; }
    
            [ForeignKey("Subject")]
            public int CompPerson_ID { get; set; }
            public Subject Subject { get; set; }
        }
    

     

    you would then need to remove the fluent API configuration line:  (remove - HasMany(p => p.Addresses).WithRequired(x => x.Subject).Map(m => m.MapKey("CompPerson_ID")))

        public class SubjectConfiguration : EntityTypeConfiguration<Subject>
        {
            public SubjectConfiguration()
                : base()
            {
                HasKey(p => p.SubjectID);
                Property(p => p.SubjectID).HasColumnName("Person_ID").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).IsRequired();
                Property(p => p.SubjectFirstName).HasColumnName("SubjFirstName").IsRequired();
                Property(p => p.SubjectLastName).HasColumnName("SubjLastName").IsRequired();
                //HasMany(p => p.Addresses).WithRequired(x => x.Subject).Map(m => m.MapKey("CompPerson_ID"));
            }
        }

    you can then display the foreign key value:

                Subject subject = context.Subjects.Include(s => s.Addresses).Where(p => p.SubjectID == 1).FirstOrDefault();
                foreach (Address a in subject.Addresses) // <-- just test code
                {
                    Console.WriteLine(a.AddressID + " " + a.CompPerson_ID);
                }


    Please, let me know if that is not what you were looking for and I will try again:)

    thank you,

    Julia


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, October 27, 2011 6:56 PM
    Moderator
  • Julia,

    You did get my intent exactly right but it is kind of disappointing to hear because:

    I would like to avoid any annotation in the Class because they are supposed to be simple, simple, database unaware POCOs. The beauty of the configuration classes it keeps the POCOs nice and clean.

    I thought with 4.0 and higher one can have both a Navigation property (in this case Addresses) and FK displayed.  Since I have not generated any kind of model, how does: Subject subject = context.Subjects.Include(s => s.Addresses) + (other chained methods) work if EF has no knowledge of how to match Subjects and Addresses?

    Again, thank you for your valuable time and knowledge.

    Bill


    WhiskeyRomeoLima
    Thursday, October 27, 2011 7:48 PM