Entity Framework 7: creating an invalid column name

Question

using EF 7: 1.0.0-rc1-final,

I'm having trouble creating an EF query properly using the first database approach — using ef scaffolding to create some of the model properties listed in DbContext — since the tables contain a large number of columns, I only need to work a bit for webapi to they were displayed in columns

I have 3 objects, brands, events and sessions.

Brands contain many events and events containing 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 use the project as webapi, when I call 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]. The column name [EventId1] is invalid Please note that if I comment on the following code in DbContext, this error will disappear and the request will be generated correctly:

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

I tried to add the attributes [ForeignKey], [InverseProperty] during "fiddling" - this does not seem to matter

I also tried adding the column name explicitly, as mentioned here

I'm not sure what else to try - does this only start to happen when I determine the relation to sessions in FluidUI - sentences?

Answer 1

Just for fun. try to make this database. I removed a lot of the "mess" of EF, like ASP.NET MVC, based on the concept of conditional configuration.

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

Here are some basic rules for convention (conventions are not case sensitive for the case).

• To create a key (aka primary key), you must call it either Id or [classname] Id
• To make a foreign key, you will call it [foreighClassName (partA)] Id (you do not need this if you add the following instead (and ofc you can have both at the same time, but then the name should be the same (partA)
• To access the external body object, you simply add a property of type public Brand Brand { get; set; } type public Brand Brand { get; set; } public Brand Brand { get; set; } public Brand Brand { get; set; } , the name does not matter if there is only one "link".
• On the main side, you can use some kind of collection to wrap all the children, and yes public ICollection<Event> Events { get; set; } public ICollection<Event> Events { get; set; } public ICollection<Event> Events { get; set; } is the way to go. One may ask, what about IEnumerable or IList (think of it well this way, IEnumerable cannot do .Add() so that it is more or less read-only. IList Well, it does more and more, and it would be nice if it is not for doing things untranslated by SQL, so in the middle we have ICollection .

When do you use the virtual keyword? Well, in EF7 you do not use it, because to ensure lazy loading and EF7 this is not there yet, and we do not know whether to add it. Github EF7 function request without lazyload

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

Remember to do the migration and (for fun) try first without any “hard-coded” requirements in FluentAPI, and look at the migration code that you will see, PK / FK, indexes and a few other beans and pins 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 question - it looks like it could be a bug in EF 7 1.0.0-RC1

in object 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); }); 

Please note that it has 2 keys - which were created from the forest, the table has a composite primary key

However, for my requirements with the API, I only need a primary identifier - deleting the composite key is fixed by an incorrect 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); });