I tried to execute a custom SQL query. However I get the following error when I execute my method.
Source: Entity Framework
Message: The data reader is incompatible with the specified 'KPMG.DE.Impact.Data.Entities.Config'. A member of the type, 'Id', does not have a corresponding column in the data reader with the same name.
This is my method:
private readonly IRepositoryAsync<Config> _configRepository;
private IEnumerable<Config> getConfig()
{
var sqlCommand = "select * from tbl_Config";
return this._configRepository.SelectQuery(sqlCommand);
}
This is my entity class:
public class Config : Entity
{
public int Id { get; set; }
public string Key { get; set; }
public string Value { get; set; }
public string ValueType { get; set; }
public string Description { get; set; }
}
This is my IRepositoryAsync
:
public interface IRepositoryAsync<TEntity> : IRepository<TEntity> where TEntity : class, ITrackable
{
Task<TEntity> FindAsync(params object[] keyValues);
Task<TEntity> FindAsync(CancellationToken cancellationToken, params object[] keyValues);
Task<bool> DeleteAsync(params object[] keyValues);
Task<bool> DeleteAsync(CancellationToken cancellationToken, params object[] keyValues);
Task<int> ExecuteCustomSQL(string sqlCommand, params object[] parameters);
}
This is my .SqlQuery method within the repository:
public virtual IQueryable<TEntity> SelectQuery(string query, params object[] parameters)
{
return this.Set.SqlQuery(query, parameters).AsQueryable();
}
Here is the mapping:
// Table & Column Mappings
this.ToTable("db.TBL_CONFIG");
this.Property(t => t.Id).HasColumnName("PK_CONF");
this.Property(t => t.Key).HasColumnName("KEY");
this.Property(t => t.Value).HasColumnName("VALUE");
this.Property(t => t.ValueType).HasColumnName("VALUETYPE");
this.Property(t => t.Description).HasColumnName("DESCRIPTION");
The SQL command I have chosen is a simple one since I wanted to test the .SqlQuery
method first. It says that it can't find the the column name for "Id" although I checked the mapping and it is definitely there.
Did I miss something else?
Thanks to @IvanStoev I have solved it now. Indeed the method does not take mappings into account. Therefore it expects sql to contain all entity property names (not column names).
The mapping:
// Table & Column Mappings
this.ToTable("db.TBL_CONFIG");
this.Property(t => t.Id).HasColumnName("PK_CONF");
this.Property(t => t.Key).HasColumnName("KEY");
this.Property(t => t.Value).HasColumnName("VALUE");
this.Property(t => t.ValueType).HasColumnName("VALUETYPE");
this.Property(t => t.Description).HasColumnName("DESCRIPTION");
As you see in the line this.Property(t => t.Id).HasColumnName("PK_CONF");
there is a difference between the entity property name and the column name.
The solution sql command:
private readonly IRepositoryAsync<Config> _configRepository;
private IEnumerable<Config> getConfig()
{
var sqlCommand = "select PK_CONF AS Id, Key, Value, ValueType, Description from TBL_CONFIG";
return this._configRepository.SelectQuery(sqlCommand);
}