Join concatinating table names

May 28, 2013 at 3:27 AM
Hey Tom,

Might have to give you a sample program to track this one down.

If I do a Join via Odata\EF the table name in the second visitor call tries to get an invalid table name.
ie If I Http\site\odata\table1(3)\table2

I can see the first call fire for SELECT Fields from TABLE1.dbf where x=3 order by x

then

I get an error on a SELECT with the Join FROM TABLE1TABLE2.dbf Where.....

The final error is a File not found TABLE1TABLE2.dbf

I've been debugging through your source for the last hour and see that the edm name is actually CodeFirstDatabaseSchemaTable1Table2 but cant seem to see where the true table name is, and where you push it onto the parameters for the Sql build.

Any Clues?

Thanks,
Mike
May 28, 2013 at 1:16 PM
Here's a bit more of the config
//// Controller
   public class Table1Controller : ODataController 
    {
        private WebApiContext db = new WebApiContext();

        [Queryable]
        public  IQueryable<Table1> GetTable1s()
        {
            return db.Table1.AsQueryable();
        }

        public Ohlog GetTable1([FromODataUri] string key)
        {
            return db.Table1.FirstOrDefault(p => p.Serialno == key);
        }

        [Queryable]
        public ICollection<Hhlog> GetTable2([FromODataUri]  string key)
        {
            return db.Table1.FirstOrDefault(p => p.Serialno == key).Table2;    //////  <------ File Not Found error thrown here TABLE1TABLE2.DBF 
        }
   }

//// Model
    public  class Table1
    {
        public string OrderNo{ get; set; }
        public string Serialno { get; set; }
        public virtual ICollection<Table2> Table2 { get; set; }
    }


//// Model Map

    public class Table1Map : EntityTypeConfiguration<Table1>
    {
        public Table1Map()
        {
            this.HasKey(t => t.Serialno);

            this.Property(t => t.Orderno)
                .IsFixedLength()
                .HasMaxLength(7);

            this.Property(t => t.Serialno)
                .IsFixedLength()
                .HasMaxLength(10);

           this.HasMany(t => t.Hhlog).WithMany();//.WithOptionalDependent();

         }
    }

    public class Table2Map : EntityTypeConfiguration<Tabkle2>
    {
        public Table2Map()
        {
            this.HasKey(t => t.UniqId);

            this.Property(t => t.UniqId)
                .IsFixedLength()
                .HasMaxLength(7);

            this.Property(t => t.Serialno)
                .IsFixedLength()
                .HasMaxLength(10);
    }
    }


//// Context
    public class WebApiContext : DbContext
    {
         static WebApiContext()
        {
            Database.SetInitializer<WebApiContext>(null);
        }

        public WebApiContext() : base("name=WebApiContext")
        {
        }

        public DbSet<Table1> Table1s { get; set; }
        public DbSet<Table2> Table2s { get; set; }    

          protected override void OnModelCreating(DbModelBuilder modelBuilder)
          {
            modelBuilder.Configurations.Add(new Table1Map());
            modelBuilder.Configurations.Add(new Table2Map());
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
          }
    }

I'm going to step through your EF Provider code again today and see if I can see where the wrong tablename came from. I dont have anything in my code that explicitly defines"TABLE1TABLE2" and don't know why EF would concatenate them except for some sort of internal identifier. I almost feel like creating the bogus dbf and see what EF does next.

Regards,
Mike
Coordinator
May 28, 2013 at 3:36 PM
You have a many to many mapping between Table1 and Table2 defined but you aren't explicitly identifying the joiner table information. In this case, EF assumes that your joiner table's name is a combination of the two table names.
Example: Table1 + Table2 = Table1Table2 (as the joiner name)

You can override the default name by specifying the mapping information.
Example:
        HasMany(t => t.Table2)
            .WithMany()
            .Map(x => { 
                x.ToTable("Table1XTable2"); 
                x.MapLeftKey("Table1SerialNo");
                x.MapRightKey("Table2SerialNo");
            });
May 28, 2013 at 5:39 PM
Ahhhh!

Alright Back on Track - I had the .Map command in there, but had removed it because I got the following error:
(57,6) : error 0019: The EntitySet 'Table1Table2' with schema 'dbo' and table 'Table2' was already defined. Each EntitySet must refer to a unique schema and table.
Now since its a Free table (no DBF), I tried it as
        HasMany(t => t.Table2)
            .WithMany()
            .Map(x => { 
                x.ToTable("Table2.dbf");  <- needed ".dbf" since no DBC
                x.MapLeftKey("Table1SerialNo");
                x.MapRightKey("Table2SerialNo");
            });
Not sure why the ".dbf" suffix is needed, but seems the Join is now returning data.

If you think you should be appending it in the provider to keep things natural let me know.

Thanks again for all your help

Kind Regards,
Mike
Coordinator
Jun 2, 2013 at 2:51 PM
I'm not sure why you need to include ".dbf" either. I created an example (MvcODataExampleFreeTables) using free tables and I was able to get it work without the file extension.