Entity Framework Invalid Column

Question

I've looked at lots of other questions on SO but can't get the answer. I have a column in a table called: Sex - Male

I would like to get my hands on whoever named it as it's giving 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 as the field "Sex" could not be found. So I changed to this:

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

I then get the message Invalid Column Name [Sex - Male]. Does EF rename columns with spaces in some way as the field does exist and is not any kind of FK?

EDIT

I have found that doing this in the modelBuilder:

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

Causes the same error to appear saying it's incompatible as there is no column called Sex with the same name! I've noticed it occurs on anything I use the Column data annotation for not just this field!

EDIT 2

I created a new application and used a Model Designer to see how it interpreted the column and showed it in the designer as "Sex___Male", however, changing the class to this even with []'s around it still gives me could not find column Sex___Male??

EDIT 3

It appears the error isn't quite what I thought, I found the mapping config works fine when I just use db.Students; and the column is there as expected. It turns out the area going wrong is this line:

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

So it's clearly the ExecuteStoreQuery that I'm guessing won't use the same mapping configuration therefore sees the column as missing. Not sure why putting the Column annotation on the property in the class doesn't work though??

Answer 1

I have recreated your situation in a test configuration. I was able to succesfully insert and query data using the following configuration

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

If you are using an older version of Entity Framework I would consider updating; that's most likely the cause, however I'm not able to reproduce your environment so this answer is only a guess. I used this code:

Created a table:

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

Class:

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

Mapping configuration:

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

Answer 2

It appears that Entity Framework itself had no issue mapping this column regarding it's normal use however the issue I had is where I was using the ExecuteStoreQuery method to map the model.

It turns out using this means anything you map it to has to have the same names regardless of any data annotations you add on for column (they appear to just get ignored). What I did instead was make a small class with just the fields I needed and changed the sql of the query to Select StudentID As ID, [Sex - Male] As Sex, ...other fields FROM ...etc i.e.

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

And then changed line to:

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

And had no problems. I also found that any properties you put in the class MUST exist in the sql query unlike a usual ef query.