Entity Framework 7 : Generating Invalid Column Name

Question

using EF 7 : 1.0.0-rc1-final,

I'm having trouble with EF generating the query properly, using database first approach - using ef scaffolding to to generate some model properties listed in the DbContext - as the tables contain a large number of columns I only need a few to work for the webapi so they are column mapped

I have 3 entities, Brands, Events and Sessions

Brands contains many Events and Events contains many Sessions

my models:

[Table("tblBranding")]
public class Brand 
{
    [Key]
    [Column("brandingId")]
    public int BrandId { get; set; }
    [Column("BrandingActive")]
    public bool Active { get; set; }
    [JsonIgnore]
    [Column("DeadBrand")]        
    public bool DeadBrand { get; set; }
    [Column("BrandingSiteTitle")]
    public string Name { get; set; }

    //navigation properties
    public virtual ICollection<Event> Events { get; set; }
}

[Table("tblEvents")]
public class Event
{           
    public int EventId { get; set; }
    [Column("eventActive")]
    public bool Active { get; set; }
    [Column("eventName")]
    public string Name { get; set; }        
    public DateTime EventCloseDate {get;set;}        
    public int PaxAllocationLimit { get; set; }

    //navigation properties             
    [JsonIgnore]        
    [Column("brandingId")]

    public virtual int BrandId { get; set; }
    [JsonIgnore]
    [ForeignKey("BrandId")]
    public virtual Brand Brand { get; set; }
    public virtual ICollection<Session> Sessions { get; set; }
}

[Table("tblEventsDates")]
public class Session
{       
    [Column("EventDateID")]
    public int SessionId { get; set; }
    [Column("EventDateName")]
    public string Name { get; set; }               
    [Column("EventDate")]
    public DateTime SessionDate { get; set; }        
    [Column("EventDateTime")]
    public DateTime SessionTime { get; set; }
    [Column("EventDateMinutes")]
    public decimal? SessionDurationInMinutes { get; set; }
    [Column("EventDateArrival")]
    public DateTime? ArrivalTime { get; set; }
    [Column("EventCapacity")]
    public int SessionCapacity { get; set; }

    //navigation properties        
    [JsonIgnore]        
    public virtual int EventId { get; set; }
    [JsonIgnore]        
    public virtual Event Event { get; set; }            
}

My DbContext

protected override void OnModelCreating(ModelBuilder modelBuilder) 
{       
    modelBuilder.Entity<Event>()
        .HasOne(e => e.Brand)
        .WithMany(b => b.Events).HasForeignKey(e=>e.BrandId);

    modelBuilder.Entity<Event>()
        .HasMany(s => s.Sessions)
        .WithOne(e => e.Event).HasForeignKey(s => s.EventId);

    modelBuilder.Entity<Event>(entity=> {
        entity.Property(e => e.EventId).HasColumnName("EventID");
        entity.HasKey(e => new{ e.EventId, e.EventCloseDate});
        entity.HasIndex(e => e.EventId).HasName("For Full Text Indexing").IsUnique();
        entity.Property(e => e.Active).HasDefaultValue(false);
        entity.Property(e => e.EventCloseDate)
            .HasColumnType("datetime")
            .HasDefaultValueSql("'1/1/2038'");
        entity.Property(e => e.Name).HasMaxLength(1024);
        entity.Property(e => e.PaxAllocationLimit).HasDefaultValue(10000);
    });

    modelBuilder.Entity<Brand>(entity => {
        entity.HasKey(e => e.BrandId);                

        entity.Property(e => e.Active).HasDefaultValue(false);               

        entity.Property(e => e.Name)
            .IsRequired()
            .HasMaxLength(150)
            .HasColumnType("varchar");                
    });

    modelBuilder.Entity<Session>(entity => {
        entity.HasKey(e => e.SessionId);

        entity.Property(e=>e.Name)
          .HasMaxLength(250)
            .HasColumnType("varchar")
            .HasDefaultValue("");

        entity.Property(e => e.SessionDurationInMinutes)
            .HasColumnType("numeric")
            .HasDefaultValue(0m);                
        });
    }

    public virtual DbSet<Brand> Brands { get; set; }
    public virtual DbSet<Event> Events { get; set; }        
    public virtual DbSet<Session> Sessions { get; set; }
}

I'm using the project as a webapi, when I call up Brands, it generates the following SQL:

SELECT [e].[brandingId], [e].[BrandingActive], [e].[DeadBrand], [e].[BrandingSiteTitle]
FROM [tblBranding] AS [e]
WHERE [e].[BrandingActive] = 1
ORDER BY [e].[BrandingSiteTitle], [e].[brandingId]
Microsoft.Data.Entity.Storage.Internal.RelationalCommandBuilderFactory: Information: Executed DbCommand (75ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [t].[EventId], [t].[EventCloseDate], [t].[eventActive], [t].[brandingId], [t].[EventId1], [t].[eventName], [t].[PaxAllocationLimit]
FROM [tblEvents] AS [t]
INNER JOIN (
    SELECT DISTINCT [e].[BrandingSiteTitle], [e].[brandingId]
    FROM [tblBranding] AS [e]
    WHERE [e].[BrandingActive] = 1
) AS [e] ON [t].[brandingId] = [e].[brandingId]
ORDER BY [e].[BrandingSiteTitle], [e].[brandingId]

where [t].[EventId1] column name is invalid note that If I comment out the following code in DbContext - this error goes away and the query is generated correctly:

modelBuilder.Entity<Event>()
            .HasMany(s => s.Sessions)
            .WithOne(e => e.Event).HasForeignKey(s => s.EventId);

I have tried adding [ForeignKey], [InverseProperty] attributes while 'fiddling' - it doesn't seem to make a difference

I also tried adding the column name explicitly as mentioned here

I'm not sure what else to try - it starts happening only when I define the relationship to Sessions in FluidUI - suggestions ?

Answer 1

Just for fun. try making this database. I removed a lot of the "clutter" EF is like ASP.NET MVC based on the concept of convention over configuration.

You have done the configuration up to 2 times (attributes and/or FluentApi) , while in reality you could have done it 0 times.

Here are some base rules on convention (conventions are not case sensitive to trigger).

• To make a Key (aka primary key) you must name it either Id or [classname]Id
• To make a foreign key you name it [foreighClassName(partA)]Id (you dont need this if you add the next instead (and ofc you can have both at the same time but then the name must be the same (partA)
• To access the foreign "body" object, you just add a property of the type like this public Brand Brand { get; set; }, the name is not important if there is only one "link".
• On the primary side you might want to use some sort of collection to wrap all the children, and yes public ICollection<Event> Events { get; set; } is the way to go. One might ask what about IEnumerable or IList (well think of it this way, IEnumerable cannot do .Add() so it is more or less read only. IList well it does all and more, and would be a nice fit if it where not for it doing stuff that's untranslatable to SQL. so in the middle we have ICollection.

When you use the virtual keyword? Well in EF7 you don't use it, as its for enabling lazy loading, and EF7 don't have that (yet) and we don't know if they are to add it. Github EF7 feature request lacking lazyload

Why did I remove [JsonIgnore] attributes? NEVER send the entity models to the client. Create a proper DTO (popularly called a model in ASP.NET MVC)

Remember to do Migration, and (for fun) try first without any "hardcoded" requirements in the FluentAPI and look at the mig code you will see PK/FK are done, Indexes and several other bobs and pins are added for you.

public class Brand 
{
    public int Id { get; set; }
    public bool Active { get; set; }
    public bool DeadBrand { get; set; }
    public string Name { get; set; }

    //navigation properties
    public ICollection<Event> Events { get; set; }
}

public class Event
{           
    public int Id { get; set; }
    public bool Active { get; set; }
    public string Name { get; set; }        
    public DateTime EventCloseDate {get;set;}        
    public int PaxAllocationLimit { get; set; }

    //navigation properties             
    public Brand Brand { get; set; }
    public ICollection<Session> Sessions { get; set; }
}

public class Session
{       
    public int Id { get; set; }
    public string Name { get; set; }   
    //Datetime contains date and time        
    public DateTime Time { get; set; } 
    //TimeSpan is for duration, allowing access to seconds, minutes, hours etc.
    public TimeSpan Duration { get; set; } 
    public DateTime? ArrivalTime { get; set; }
    public int SessionCapacity { get; set; }

    //navigation properties        
    public Event Event { get; set; }            
}

Context class

class DbContex{

    public virtual DbSet<Brand> Brands { get; set; }
    public virtual DbSet<Event> Events { get; set; }        
    public virtual DbSet<Session> Sessions { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder) 
    {       
    //Personally I would not have many requirements in the database unless I              
    //was completely sure it had to be that way.
    //They will ALWAYS bite you in the ass in the long run.

        modelBuilder.Entity<Event>(entity=> {
            entity.Property(e => e.Active).HasDefaultValue(false);
            entity.Property(e => e.EventCloseDate)
                .HasColumnType("datetime")
                .HasDefaultValueSql("'1/1/2038'");
            entity.Property(e => e.Name).HasMaxLength(1024);
            entity.Property(e => e.PaxAllocationLimit).HasDefaultValue(10000);
        });

        modelBuilder.Entity<Brand>(entity => {
            entity.Property(e => e.Active).HasDefaultValue(false);    
            entity.Property(e => e.Name)
                .IsRequired()
                .HasMaxLength(150)
                .HasColumnType("varchar");                
        });

        modelBuilder.Entity<Session>(entity => {
            entity.Property(e=>e.Name)
                .HasMaxLength(250)
                .HasColumnType("varchar")
                .HasDefaultValue("");    
            entity.Property(e => e.SessionDurationInMinutes)
                .HasColumnType("numeric")
                .HasDefaultValue(0m);                
            });
        }
    }
}

Answer 2

Answering my own question - it seems like it might be a bug in EF 7 1.0.0-RC1

in the entity properties for Event in DbContext

 modelBuilder.Entity<Event>(entity=> {
    entity.Property(e => e.EventId).HasColumnName("EventID");
    entity.HasKey(e => new{ e.EventId, e.EventCloseDate});
    entity.HasIndex(e => e.EventId).HasName("For Full Text Indexing").IsUnique();
    entity.Property(e => e.Active).HasDefaultValue(false);
    entity.Property(e => e.EventCloseDate)
        .HasColumnType("datetime")
        .HasDefaultValueSql("'1/1/2038'");
    entity.Property(e => e.Name).HasMaxLength(1024);
    entity.Property(e => e.PaxAllocationLimit).HasDefaultValue(10000);
});

Note that it has 2 keys - which was generated from scaffolding, the table has a composite primary key

However for my requirements with the API I only need a primary identifier - removing the composite key fixed the invalid column generation error

updated code:

modelBuilder.Entity<Event>(entity=> {
    entity.Property(e => e.EventId).HasColumnName("EventID");
    entity.HasKey(e => e.EventId);
    entity.HasIndex(e => e.EventId).HasName("For Full Text Indexing").IsUnique();
    entity.Property(e => e.Active).HasDefaultValue(false);
    entity.Property(e => e.EventCloseDate)
        .HasColumnType("datetime")
        .HasDefaultValueSql("'1/1/2038'");
    entity.Property(e => e.Name).HasMaxLength(1024);
    entity.Property(e => e.PaxAllocationLimit).HasDefaultValue(10000);
});