none
Problem modeling many-to-many relationship with table/colum names different than class/property names RRS feed

  • Question

  • i have tried to model a many-to-many relationship using entity framework 4 and the code first approach and PostgreSQL as underlying database with Npgsql as connector.

    Unfortunately my table and column configurations are not taken into account by entity framework. I might have something missconfigured, but i don't know what.

    Because tables already exist, I need to specify the exact table names (all lower case with underscore) and columnames. The mapping of Role and Person works good, but when accessing a navigation property, the SQL written below is generated and leads to an exception, because there is no table called PersonRoles but person_role, moreover the column names within the INNER JOIN are wrong.

    Here is the source.

     

    [Table("person", Schema = "domain")]
    public class Person
    {
        /// <summary>
        /// Identifier
        /// </summary>
        [Key]
        [Column("identifier", TypeName = "bpchar")]
        [StringLength(36, MinimumLength=36)]
        public string Identifier { get; private set; }
    
        /// <summary>
        /// Roles
        /// </summary>
        [InverseProperty("Persons")]
        public virtual ICollection<Role> Roles { get; set; }
    
    }
    
    
    [Table("role", Schema = "domain")]
    public class Role
    {
        #region Properties
        /// <summary>
        /// Identifier
        /// </summary>
        [Key]
        [Column("identifier", TypeName = "bpchar")]
        [StringLength(36, MinimumLength = 36)]
        public string Identifier { get; set; }
    
        /// <summary>
        /// Persons
        /// </summary>
        [InverseProperty("Roles")]
        public virtual ICollection<Person> Persons { get; set; }
    
    }
    
    
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Role>().HasMany(m => m.Persons).WithMany(p => p.Roles).Map(m => m.ToTable("person_role", "domain").MapLeftKey("person_identifier").MapRightKey("role_identifier"));
    
    
            base.OnModelCreating(modelBuilder);
        }
    

    This: Role.Persons.Count()

    Results in following SQL:

    SELECT "Extent2"."identifier" AS "identifier" FROM "dbo"."PersonRoles" AS "Extent1" INNER JOIN "domain"."person" AS "Extent2" ON "Extent1"."Person_Identifier"="Extent2"."identifier" WHERE "Extent1"."Role_Identifier"= (((E'334a5e5f-a2fe-4457-9428-2db487053255')))
    

    Thanks.

    Thursday, September 15, 2011 11:12 AM

Answers

  • Ok,

    I found another error, there was a misconfiguration with MapLeftKey / MapRightKey, and again there's no need of using them in this case, here's the right version:

    modelBuilder.Entity<Role>().HasMany(m => m.Persons).WithMany(p => p.Roles).Map(m => m.ToTable("person_role", "dbo").MapLeftKey("role_identifier").MapRightKey("person_identifier"));

     

    after this it'll work:

        using (var d = new DbContainer())

                {

                    var person = d.Persons.First();

                    var role = d.Roles.First();

                    //person.Roles.Add(role);

                    //d.SaveChanges();

                    Console.WriteLine(person.Roles.First().Identifier);

                    Console.WriteLine(role.Persons.First().Identifier);

                }

    etc

    compare it to the original code, I didn't upload source as there will be no other difference.

    Hope this solves your problem.

    Thanks for attention


    Clarity VS Precision
    • Marked as answer by asymetrixs Wednesday, September 21, 2011 3:30 PM
    Wednesday, September 21, 2011 11:48 AM

All replies

  • Hi,

    you should follow these rules when trying to map:

    http://blogs.msdn.com/b/adonet/archive/2010/12/14/ef-feature-ctp5-fluent-api-samples.aspx

     

    modelBuilder.Entity<ClassName>() 
        .Property(c => c.PropertyName) 
        .HasColumnName("collumn_name");

     

    Hope this helps

    Regards


    Clarity VS Precision
    Monday, September 19, 2011 7:09 AM
  • Hello,

    Could you please provide more detailed error information in your reply to help us analyze your question better?

    Thanks,


    Larcolais Gong[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, September 19, 2011 8:31 AM
  • Hi,

    the problem is not the mapping of a class property to a table column. This works fine. Selecting Person or Role works and the properties contain exactly the same values as they records in the table do.

    The problem is, that the ef-generated SQL for the mapping table "person_role" is wrong, because ef always names the mapping table "PersonRoles" instead (as configured in OnModelCreating) "person_role" with schema "domain".

    Monday, September 19, 2011 8:18 PM
  • Hello,

    as replied to Giorgi Zautashvili the problem is that my modelbuilder settigs for the many-to-many mapping are not used by ef.

    You can find a sample app here: http://asymetrixs.net/128937hiwehr723r82/ConsoleApplication1.zip

    I hope this helps. If not let me know.

    Thanks.

    Monday, September 19, 2011 8:35 PM
  • Ah, sorry, now I see,

    in many-to-many cases you'd use this expression (copying from my code):

     

     

    modelBuilder.Entity<Operation>().HasMany(o => o.Doctors).WithMany().Map(m => m.ToTable("inpatient_Operations_hr_Doctors"));

     

     

    this works and should be enough, but maybe you have something else misconfigured.

     

    Thank you

     

     

     


    Clarity VS Precision

    Tuesday, September 20, 2011 6:35 AM
  • Hi,

    I've tried it but it results in the same error.

    EF always tries to access table "dbo"."PersonRoles" and not  "domain"."person_role"

    This

    modelBuilder.Entity<Role>().HasMany(r => r.Persons).WithMany().Map(m => m.ToTable("person_role", "domain").MapLeftKey("person_identifier").MapRightKey("role_identifier"));
    

    or that

    modelBuilder.Entity<Role>().HasMany(r => r.Persons).WithMany().Map(m => m.ToTable("person_role", "domain"));
    


    It gives me this in both cases:
    SELECT "Project1"."identifier" AS "identifier","Project1"."name" AS "name","Project1"."created" AS "created","Project1"."enabled" AS "enabled","Project1"."deleted" AS "deleted" FROM (SELECT "Extent1"."identifier" AS "identifier","Extent1"."name" AS "name","Extent1"."created" AS "created","Extent1"."enabled" AS "enabled","Extent1"."deleted" AS "deleted",(SELECT CAST (count(1) AS int4) AS "A1" FROM "dbo"."PersonRoles" AS "Extent2" WHERE "Extent1"."identifier"="Extent2"."Role_Identifier") AS "C1" FROM "domain"."role" AS "Extent1") AS "Project1" WHERE "Project1"."C1">0
    
    

    Have a look into my sample project please. There you can see what is configured and what might be misconfigured. Try to run it and you should also get this error-message.

    Thanks.

    Tuesday, September 20, 2011 11:05 AM
  • I downloaded your project on my side, could you please mdf file or sql script to help me restore the real sql scenario like yours?

    based on my experience, I have some suggestions after i saw your project.

    1. In your module classes, normally I add the following code snippets.

    public class Person()
    {
          public Person()
          {
                Roles = new HashSet<Role>();
          }
    }
    //In the Role class vice versa.
    

    2.  Want to create join between two tables, normally I achieve it like:

            protected override void OnModelCreating(System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Person>().HasMany(a => a.Persons).WithMany(b => b.Roles).Map(m =>
                {
                    m.MapLeftKey(a => a.PersonId,"PersonId");
                    m.MapRightKey(b => b.RolesId, "RolesId");
                    m.ToTable("PersonRole");
                });
                base.OnModelCreating(modelBuilder);
            }
    

    Of course, glad to recieve your sharing sql script or mdf file.

    Thanks,

    Werewolf,


    Just a newbie for everything.
    Tuesday, September 20, 2011 1:12 PM
  • The syntax you use for mapping (m.MapLeftKey(a => a.PersonId, "PersonId")) is not available in the EntityFramework Release I use. MapLeftKey just accepts params string[] keyColumnNames as parameter(s).

    Here is the SQL create script with values: http://asymetrixs.net/128937hiwehr723r82/sql_test.sql

    Initializing the navigation properties does not make any changes to the underlying ef-sql generation.

    Tuesday, September 20, 2011 9:42 PM
  • Hi, 

    remove that InverseProperty attribute (there's no need of it) and it works :)

     

    Cheers


    Clarity VS Precision
    Wednesday, September 21, 2011 6:36 AM
  • Hi,

    when I remove the InversePropertyAttribute on Person.Roles and Role.Persons, than the NavigationProperties are always empty (I can find a person and I can find a role, but role.Persons and person.Roles are always 0.

    Can you please show me what you have changed exactly to get it running? Or upload the running project somewhere so that I can compare it myself?

     

    Wednesday, September 21, 2011 11:03 AM
  • Hi,

    I did nothing more than deleted that attribute and the database generated correctly, so the first part of the problem is solved I think.

    I didn't test it for retrieving data, sorry, I'll do it now, but in general,  I've never used InversePropertyAttribute for making many-to-many relationship work correctly.

     

    I'll upload the solution and post the link.

     

    Thanks


    Clarity VS Precision
    Wednesday, September 21, 2011 11:21 AM
  • Ok,

    I found another error, there was a misconfiguration with MapLeftKey / MapRightKey, and again there's no need of using them in this case, here's the right version:

    modelBuilder.Entity<Role>().HasMany(m => m.Persons).WithMany(p => p.Roles).Map(m => m.ToTable("person_role", "dbo").MapLeftKey("role_identifier").MapRightKey("person_identifier"));

     

    after this it'll work:

        using (var d = new DbContainer())

                {

                    var person = d.Persons.First();

                    var role = d.Roles.First();

                    //person.Roles.Add(role);

                    //d.SaveChanges();

                    Console.WriteLine(person.Roles.First().Identifier);

                    Console.WriteLine(role.Persons.First().Identifier);

                }

    etc

    compare it to the original code, I didn't upload source as there will be no other difference.

    Hope this solves your problem.

    Thanks for attention


    Clarity VS Precision
    • Marked as answer by asymetrixs Wednesday, September 21, 2011 3:30 PM
    Wednesday, September 21, 2011 11:48 AM
  • Thanks,

    i don't know how often I changed the source, but now it is working the way you posted.

     

    Thank you very much.

    Wednesday, September 21, 2011 3:30 PM