Invalid Entity Framework column

Question

I have considered many other questions on SO, but I cannot get an answer. I have a column in the table: Sex - Male

I would like to access those who named it, as this gives me problems with EF. If I use this:

[Column("Sex - Male")] public bool Sex { get;set; } 

This gives me the error of being incompatible with the model, because the Sex field cannot be found. So I changed this:

 [Column("[Sex - Male]")] public bool Sex { get;set; } 

Then I get the message Invalid Column Name [Sex - Male]. Does EF rename columns with spaces somehow since this field exists and is not some kind of FK?

EDIT

I found this to be done in modelBuilder:

 modelBuilder.Entity<Student>().Property(x => x.Sex).HasColumnName("Sex - Male"); 

It causes the same error, which says that it is incompatible, because there is no column called "Sex with the same name! I noticed that this happens in everything that I use the annotation of the column data not only for this field!

EDIT 2

I created a new application and used Model Designer to see how he interpreted the column and showed it in the designer as “Sex___Male”, however changing the class before even using [] around it still allows me not to find the column Paul ___ ??

EDIT 3

It seems that the error is not exactly what I thought, I found that the mapping configuration works fine when I just use db.Students; and the column is there as expected. Turns out the wrong area is the line:

 var students = (db as IObjectContextAdapter).ObjectContext.ExecuteStoreQuery<Student>(sql); 

It is therefore clear that ExecuteStoreQuery, which I assume will not use the same display configuration, therefore sees that the column is missing. Not sure why posting a column annotation on a property in a class does not work though ??

Answer 1

It seems that the Entity Framework structure itself did not have a problem displaying this column relative to normal use, however the problem I came across is where I used the ExecuteStoreQuery method to map the model.

It turns out that this means that everything that you match it should have the same name, regardless of any data annotations that you add for the column (they seem to be simply ignored). Instead, I made a small class with the required fields and changed the sql of the request to Select StudentID as ID, [Sex - Male] As Sex, ... other FROM fields ... etc., i.e.

 public class StudentReadOnly { public int ID { get; set; } public bool Sex { get;set; } ... other properties } 

And then change the line to:

 var students = (db as IObjectContextAdapter).ObjectContext.ExecuteStoreQuery<StudentReadOnly>(sql); 

And there were no problems. I also found that any properties that you put in a class MUST exist in a sql request, unlike a regular ef request.

Answer 2

I updated your situation in a test configuration. I was able to successfully insert and query data using the following configuration

• SQL Server 2012
• Visual studio 2013
• Entity Framework 6.0.1

If you are using an outdated version of Entity Framework, I would consider updating; which is most likely the reason, however I cannot reproduce your environment, so this answer is just a guess. I used this code:

Created a table:

 create table MyTable2 ( [pk] int not null identity primary key, [Sex - Male] bit not null); 

Grade:

 public class MyTable2 { public int pk { get; set; } public bool Sex { get; set; } } 

Display Configuration:

 this.HasKey(t => t.pk); this.Property(t => t.Sex).HasColumnName("Sex - Male");