DataContext selects fields which doesn't exist

Question

I receive error

{"Invalid column name 'Role_Id'.\r\nInvalid column name 'User_Id'."}

when I try to get all permissions like this:

public IEnumerable<Permission> GetAllPermissions()
{
        return base.DataContext.Permissions;
}

What are these "Role_id" and "User_id"??? They don't exist neither in my model nor in database... What does it mean? In my entire solutions I don't have anything like "Role_id" and "User_id"

This is query from DataContext (what are those "Role_id" and "User_id"...?):

{SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Extent1].[Role_Id] AS [Role_Id], 
[Extent1].[User_Id] AS [User_Id]
FROM [dbo].[Permissions] AS [Extent1]}

Permission model looks like :

public class Permission : INotifyPropertyChanged
{
    private string name;

    public int Id
    {
        get;
        set;
    }


    public string Name
    {
        get
        {
            return this.name;
        }
        set
        {
            this.name = value;
            this.OnPropertyChanged("Name");
        }
    }

    public Permission()
    {

    }

    protected virtual void OnPropertyChanged(string propertyName)
    {
        PropertyChangedEventHandler propertyChangedEventHandler = this.PropertyChanged;
        if (propertyChangedEventHandler != null)
        {
            propertyChangedEventHandler(this, new PropertyChangedEventArgs(propertyName));
        }
    }

    public event PropertyChangedEventHandler PropertyChanged;
}

Table in database:

Context:

    public class MyContext : DbContext
{
    public DbSet<User> Users { get; set;}
    public DbSet<Role> Roles { get; set;}
    public DbSet<RolePermission> RolePermissions { get; set;}
    public DbSet<Permission> Permissions { get; set;}
    ...
    public MyContext()
        : base("name=my_Connection")
    {
        System.Data.Entity.Database.SetInitializer<MyContext>(new CreateDatabaseIfNotExists<MyContext>());
    }
}

Do you have tables named `Role` and `User`, Where you might be referencing the Permissions `Id`? — vendettamit, Aug 07 '15 at 14:25
Another question, which approach you are using codefirst, ModelFirst or DbFirst? — vendettamit, Aug 07 '15 at 14:28
Yes I have classes: Role (Id, PermissionId), User (Id, Name, ... _no PermissionId_) and RolePermissions (Id, RoleId, PermissionId) — tauri, Aug 07 '15 at 14:47
I took over the project when database and models were already created. Now I just modify according to the needs — tauri, Aug 07 '15 at 14:51
Tables (Roles, Permissions, RolePermissions) are added to the database as well I created manually models — tauri, Aug 07 '15 at 14:54

score 1 · Answer 1 · edited May 23 '17 at 12:06

Okay, so just wanted to make sure that you're not using Model first to generate your database from classes. The autogenerated names comes from the conventions that EF uses. It simply track for basic convention of database e.g. Pattern NameOfNavigationProperty_NameOfRelatedPK.

In your case EF is trying to discover the Foreign keys by references and that's why you're getting generated values. This convention can be disabled via DBModelBuilder. In the DbContext you can apply following override and disable a convention named NavigationPropertyNameForeignKeyDiscoveryConvention

public class SampleContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Permission> Permissions { get; set; }
    // ... goes other properties

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<NavigationPropertyNameForeignKeyDiscoveryConvention>();
    }
}

Note: Make sure you test your application thoroughly. This fix doesn't guarantee that this will not affect other existing queries.

Thank you. (I tried and it didn't change anything) I'm afraid I was wrong. I was working with ready database and I was not thinking what model is used. I edited my question, added MyContext. — tauri, Aug 07 '15 at 15:36
I know this the problem of Navigation property convention. Can you try turning off other [conventions](https://msdn.microsoft.com/en-us/library/system.data.entity.modelconfiguration.conventions(v=vs.103).aspx) related to foreignkey and navigation properties. specially `AssociationInverseDiscoveryConvention` one. — vendettamit, Aug 07 '15 at 16:56

DataContext selects fields which doesn't exist

1 Answers1