Dynamically select columns in runtime using entity framework

Question

I have an existing function like this

public int sFunc(string sCol , int iId)
{
    string sSqlQuery = "  select  " + sCol + " from TableName where ID = " +  iId ;
    // Executes query and returns value in column sCol
}

The table has four columns to store integer values and I am reading them separately using above function.

Now I am converting it to Entity Framework .

public int sFunc(string sCol , int iId)
{
     return Convert.ToInt32(TableRepository.Entities.Where(x => x.ID == iId).Select(x => sCol ).FirstOrDefault());
}

but the above function returns an error

input string not in correct format

because it returns the column name itself.

I don't know how to solve this as I am very new to EF.

Any help would be appreciated

Thank you

Answer 1

Not going to be useful for the OP 8 years later, but this question has plenty of views, so I thought it could be helpful for others to have a proper answer.

If you use Entity Framework, you should do Linq projection (Select()), because that leads to the correct, efficient query on the db side, instead of pulling in the entire entity.

With Linq Select() you normally have to provide a lambda, though, so having your your column/property name in a string poses the main difficulty here.

The easiest solution is to use Dynamic LINQ (EntityFramework.DynamicLinq Nuget package). This package provides alternatives to the original Linq methods, which take strings as parameters, and it translates those strings into the appropriate expressions.

Example:

async Task<int> GetIntColumn(int entityId, string intColumnName)
{
    return await TableRepository.Entities
        .Where(x => x.Id == entityId)
        .Select(intColumnName) // Dynamic Linq projection
        .Cast<int>()
        .SingleAsync();
}

I also made this into an async call, because these days all database calls should be executed asynchronously. When you call this method, you have to await it to get the result (i.e.: var res = await GetIntColumn(...);).

Generic variation

Probably it's more useful to change it into an extension method on IQueryable, and make the column/property type into a generic type parameter, so you could use it with any column/property:

(Provided you have a common interface for all your entities that specifies an Id property.)

public static async Task<TColumn> GetColumn<TEntity, TColumn>(this IQueryable<TEntity> queryable, int entityId, string columnName)
    where TEntity : IEntity
{
    return await queryable
        .Where(x => x.Id == entityId)
        .Select(columnName) // Dynamic Linq projection
        .Cast<TColumn>()
        .SingleAsync();
}

This is called like this: var result = await TableRepository.Entities.GetColumn<Entity, int>(id, columnName);

Generic variation that accepts a list of columns

You can extend it further to support selecting multiple columns dynamically:

public static async Task<dynamic> GetColumns<TEntity>(this IQueryable<TEntity> queryable, int entityId, params string[] columnNames)
    where TEntity : IEntity
{
    return await queryable
        .Where(x => x.Id == entityId)
        .Select($"new({string.Join(", ", columnNames)})")
        .Cast<dynamic>()
        .SingleAsync();
}

This is called like this: var result = await TableRepository.Entities.GetColumns(id, columnName1, columnName2, ...);.

Since the return type and its members are not known compile-time, we have to return dynamic here. Which makes it difficult to work with the result, but if all you want is to serialize it and send it back to the client, it's fine for that purpose.

Answer 2

This might help to solve your problem:

public int sFunc(string sCol, int iId)
{
    var _tableRepository = TableRepository.Entities.Where(x => x.ID == iId).Select(e => e).FirstOrDefault();
    if (_tableRepository == null) return 0;

    var _value = _tableRepository.GetType().GetProperties().Where(a => a.Name == sCol).Select(p => p.GetValue(_tableRepository, null)).FirstOrDefault();

    return _value != null ? Convert.ToInt32(_value.ToString()) : 0;
}

This method now work for dynamically input method parameter sCol.

Update: This is not in context of current question but in general how we can select dynamic column using expression:

var parameter = Expression.Parameter(typeof(EntityTable));
var property = Expression.Property(parameter, "ColumnName");
//Replace string with type of ColumnName and entity table name.
var selector = Expression.Lambda<Func<EntityTable, string>>(property, parameter);

//Before using queryable you can include where clause with it. ToList can be avoided if need to build further query.
var result = queryable.Select(selector).ToList();

Answer 3

You have to try with dynamic LINQ. Details are HERE

Answer 4

Instead of passing the string column name as a parameter, try passing in a lambda expression, like:

sFunc(x => x.FirstColumnName, rowId);
sFunc(x => x.SecondColumnName, rowId);
...

This will in the end give you intellisense for column names, so you avoid possible errors when column name is mistyped.

More about this here: C# Pass Lambda Expression as Method Parameter

However, if you must keep the same method signature, i.e. to support other/legacy code, then you can try this:

public string sFunc(string sCol , int iId)
{
    return TableRepository.Entities.Where(x => x.ID == iId).Select(x => (string) x.GetType().GetProperty(sCol).GetValue(x)});
}

You might need to adjust this a bit, I didn't have a quick way of testing this.

Answer 5

You can do this:

        var entity = _dbContext.Find(YourEntity, entityKey);
        // Finds column to select or update
        PropertyInfo propertyInfo = entity.GetType().GetProperty("TheColumnVariable");