I'm using OData V4, EF6 and MySql 5.6/5.7 With below models and tables. I get the result of the Application resource fine with this call odata/Applications but I get this error when I expand on roles, as follows odata/Applications?$expand=roles.
Error: An error occurred while executing the command definition. See the inner exception for details. Unknown column 'Project3.ApplicationId' in 'where clause'
I know it's something with mapping, but I can't see what.
public class Role
{
public int Id { get; set; }
public string Name { get; set; }
public int ApplicationId { get; set; }
//public virtual Application Application { get; set; }
}
public class Application
{
public int Id { get; set; }
public string Name { get; set; }
public bool IsDeleted { get; set; }
public virtual ICollection<Role> Roles { get; set; }
public Application()
{
Roles = new List<Role>();
}
}
public class ApplicationView
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<RoleView> Roles { get; set; }
public ApplicationView()
{
Roles = new List<RoleView>();
}
}
public class RoleView
{
public int Id { get; set; }
public string Name { get; set; }
public int ApplicationId { get; set; }
}
create table IF NOT EXISTS Application (
ApplicationId int not null auto_increment primary key,
Name varchar(255) not null,
IsDeleted bool not null default false,
CreatedBy varchar(255) not null,
CreatedOn Datetime not null,
UpdatedBy varchar(255) not null,
UpdatedOn DateTime not null
) ENGINE=INNODB;
create table IF NOT EXISTS Role (
RoleId int not null auto_increment primary key,
Name varchar(255) not null,
ApplicationId int not null,
CreatedBy varchar(255) not null,
CreatedOn Datetime not null,
UpdatedBy varchar(255) not null,
UpdatedOn DateTime not null,
index app_index (ApplicationId),
foreign key (ApplicationId) references Application(ApplicationId) on delete cascade
) ENGINE=INNODB;
This is the OData action method.
[HttpGet]
[ODataRoute("Applications")]
public IQueryable<ApplicationView> Get()
{
var result = IdentityRepository.Applications
.Include("Role")
.ProjectTo<ApplicationView>();
return result;
}
The mapping classes:
public class RoleMap : EntityTypeConfiguration<Role>
{
public RoleMap()
{
ToTable("Role");
HasKey(x => x.Id);
Property(x => x.Id).HasColumnName("RoleId");
// Tried with/without, no change.
//HasRequired(x => x.Application).WithMany(x => x.Roles).HasForeignKey(x => x.ApplicationId);
}
}
public class ApplicationMap : EntityTypeConfiguration<Application>
{
public ApplicationMap()
{
ToTable("Application");
HasKey(x => x.Id);
Property(x => x.Id).HasColumnName("ApplicationId");
// Tried with/without no change.
HasMany(x => x.Roles).WithRequired().HasForeignKey(x => x.ApplicationId);
}
}
I tried the repository with both code first and database first edmx and keep getting the same error.