none
Invalid object name RRS feed

  • Question

  • I need help.

    I am rather new to the Entity Framework and I have worked the past days on a Many-to-Many table-relationship construct that I would like to insert some records into.

    Unfortunatly this turned out to be much harder then I expected. i Do not understand what is going on here.

    The project purpose is to take an existing desktop applications database and add an web interface to it by using ASP.Net MVC 3.

    It is my understanding that there are three ways how you can work with the Entity Framework, 1: Code-First, 2: Datebase-First and 3: Code-First with an existing database. The last aproach is the one I am using. I have an existing database but I have not created an EDMX file that imports all the needed tables. Instead I just created the POCO tables myself.

    I should note here that I am able to select/insert/update/delete data with this construct.

    The problem I have is as meantioned above, with the Many-To-Many relationship. For some reason the Entity Framework insist to use the class name of a POCO entity as table reference.

    Here is the snippet of the POCO classes

    [Table("OENSKER_OMRAADER", Schema="dbo")]
    public class WishArea
    {
      [Key, Column(Order = 0)]
      public Int32 OEO_OE_LBNR { get; set; }
      [Key, Column(Order = 1)]
      public Int32 OEO_O_LBNR { get; set; }
    }
    
    [Table("OENSKER")]
    public class Wish
    {
      [Key]
      public Int32 OE_LBNR { get; set; }
      public Byte OE_UGE { get; set; }
      public Byte OE_PRIORITET { get; set; }
      public Boolean OE_RYGER { get; set; }
      public Boolean OE_HUSDYR { get; set; }
      public Boolean OE_HANDICAP { get; set; }
      public Byte OE_ANTALVOKSEN { get; set; }
      public Byte OE_ANTALBOERN { get; set; }
      public Int32 OE_K_LBNR { get; set; }
    
      public virtual ICollection<Area> Areas { get; set; }
    }
    
    [Table("OMRAADER")]
    public class Area
    {
      [Key]
      public Int32 O_LBNR { get; set; }
      public String O_NAVN { get; set; }
    
      public virtual ICollection<Wish> Wishes { get; set; }
    }

    As you can see, I am mapping the class name explicit to a table in the database.

    The relationship is: Wish -> WishArea <- Area

    Where WishArea is a pure join table.

    I have turned pluralization off and added a relational definition into the OnModelCreating eventhandler as you can see in the following snippet.

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      /// We do not care about pluarlization
      modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
      base.OnModelCreating(modelBuilder);
    
     /// Defines a many-to-many relationship through a join-table
      modelBuilder.Entity<Wish>()
       .HasMany(c => c.Areas)
       .WithMany(i => i.Wishes)
       .Map(t => 
         t.MapLeftKey("OEO_OE_LBNR")
          .MapRightKey("OEO_O_LBNR")
          .ToTable("WishArea"));
    }

    And here are the dataset I declare

    public DbSet<Area> Area { get; set; }
    public DbSet<Wish> Wish { get; set; }
    public DbSet<WishArea> WishArea { get; set; }

    What I had hoped now was to use an initialized wish object and add this one through the dbcontext, and at the same time being able to insert a record into the join table. Like this: 

    context.Wish.Add(wish);
    context.SaveChanges();

    When the code cursor reaches the SaveChanges statement an Exception is thrown, telling me: Invalid object name 'dbo'.'WishArea'. It should use the defined table in the database OENSKER_OMRAADER, but this is just not happening.

    I have tried several combination now to see what might trigger this. I even removed all references (by renaming the WishArea POCO class to the original table name) so the name WishArea or WishAreas would not be found in the project at all. But somehow It keeps insisting to fail because it can't find a table by that name.

    Can someone please tell me what I am doing wrong?

    Tuesday, February 5, 2013 12:19 AM

Answers

  • Hi Søren-ssv,

    Welcome to the MSDN forum.

    Did you use Entity Framework Power Tool to Generates POCO classes?

    If didn’t, I recommend you try to use this tool: http://visualstudiogallery.msdn.microsoft.com/72a60b14-1581-4b9b-89f2-846072eff19d

    I tested this tool with some many to many tables, and it works great. Please check it.

    Good day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Søren-ssv Wednesday, February 6, 2013 9:22 AM
    Wednesday, February 6, 2013 5:40 AM
  • Hi Alexander,

    Thanks for the reply. I actually found an answer from this link: http://stackoverflow.com/questions/7987221/fluent-api-many-to-many-mapping-error

    My error was to declare

    public DbSet<WishArea> WishArea { get; set; }

    A JOIN table is not meant to be declared at all.

    Never the less I am certainly going to check the tool out Alexander, it might save my but another time :)

    Thanks for the help!

    • Marked as answer by Søren-ssv Wednesday, February 6, 2013 9:22 AM
    Wednesday, February 6, 2013 9:22 AM

All replies

  • Hi Søren-ssv,

    Welcome to the MSDN forum.

    Did you use Entity Framework Power Tool to Generates POCO classes?

    If didn’t, I recommend you try to use this tool: http://visualstudiogallery.msdn.microsoft.com/72a60b14-1581-4b9b-89f2-846072eff19d

    I tested this tool with some many to many tables, and it works great. Please check it.

    Good day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Søren-ssv Wednesday, February 6, 2013 9:22 AM
    Wednesday, February 6, 2013 5:40 AM
  • Hi Alexander,

    Thanks for the reply. I actually found an answer from this link: http://stackoverflow.com/questions/7987221/fluent-api-many-to-many-mapping-error

    My error was to declare

    public DbSet<WishArea> WishArea { get; set; }

    A JOIN table is not meant to be declared at all.

    Never the less I am certainly going to check the tool out Alexander, it might save my but another time :)

    Thanks for the help!

    • Marked as answer by Søren-ssv Wednesday, February 6, 2013 9:22 AM
    Wednesday, February 6, 2013 9:22 AM