C# repository pattern SQL query with Entity Framework 6 exception data reader is incompatible with specified entity

c# entity-framework-6 repository-pattern sql

Question

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?

1
2
9/3/2018 4:08:52 PM

Accepted Answer

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);
}
1
9/4/2018 9:39:18 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow