Dynamically select columns in runtime using entity framework

dynamic column name in linq query
entity framework select specific columns
entity framework dynamic model
entity framework dynamic query
entity framework dynamic linq
entity framework create table dynamically
dynamic linq select multiple columns
ef core dynamic select

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

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.

dynamically pass column names to be selected at runtime in entity , Popular Answer​​ The simplest way is just to drop back down to plain parametrized SQL: var fields = new[] { "ContentId", "ContentName" }; var q = "SELECT " + string. Join(", ", fields) + " WHERE CatalogId = @Id"; var result = context. Database. We can use the below query to retrieve specific columns using Entity Framework: var result = context.Contents.Where(c => c.CatalogId == "ABC") .Select(c => new {c.ContentId, c.ContentName});

You have to try with dynamic LINQ. Details are HERE

Dynamically select columns in runtime using entity framework, This might help to solve your problem: public int sFunc(string sCol, int iId) { var _tableRepository = TableRepository.Entities.Where(x => x. Obviously, you will need to refactor this method to make it more suitable for your application. But as you can see, you can easily use even async method from Entity Framework. If you customize the select also (the return type) you may need to either get the async result using reflection or use ExecuteAsync instead with ToList().

You can do this:

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

Reading user defined columns from database with Entity Framework, Reading user defined columns from database with Entity Framework. entity-​framework-dynamic-columns-fig1 Lambda Expression with a projection from T type to the Runtime type; Invoke the Select method using that Lambda Expression. Dynamically select column name using Linq to Entities (EF6) So I'm thinking on selecting dynamically the column using "propertyName". No Entity Framework

You could use Reflection, something like this (not tested code):

public string sFunc(string sCol , int iId)
{
  var row = TableRepository.Entities.Where(x => x.ID == iId);
  var type = typeof(row);
  var propInfo = type.GetProperty(sCol);

  if (propInfo != null)
  {
    string value = (string)propInfo.GetValue(row);

    return value;
  }

  return null;
}

https://social.msdn.microsoft.com/Forums/en-US/2fb, entity framework select columns entity framework dynamic where clause entity framework select single column entity framework exclude column from select DBAs just don’t like developers using Entity Framework and with good reason. Entity Framework can make SQL Server work awfully hard if the developer isn’t careful. No, it’s not April Fool’s Day , we’re really going to go over some Entity Framework code.

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.

How to create table columns dynamically in entityframework, Entity Framework works on the basis of Models. You cannot create dynamic properties at runtime and register to EF. If you are forced to use EF,  Obviously, you will need to refactor this method to make it more suitable for your application. But as you can see, you can easily use even async method from Entity Framework. If you customize the select also (the return type) you may need to either get the async result using reflection or use ExecuteAsync instead with ToList().

Entity Framework: How to pass column name dynamically to where , most of the time i mention column name when search with EF like this way. Hide Copy Code. var query = from s in db.Customers select s; if (ColumnName  Most likely it would be a string representation of these columns. If you need to use the Entity Framework, here is how it's done. Jumping ahead you will be able to use this syntax: using (var ctx = new MyContext()) { return ctx.Customers.SelectProperties(new [] { "LastName", "Phone1" }); }

Retrieve and Update Entity Framework with Dynamic Queries , Entity SQL and ObjectQuery will let you generate queries at runtime You don't have to give up on creating dynamic queries just because you're using Entity Framework. In an earlier column, I looked at using plain old SQL with EF -- an Obviously, eSQL looks very much like plain old SQL with Select,  Use dynamic select clause with string expression; Use dynamic order by with string expression; Use dynamic where clause with string expression; Google Related Searches. Entity Framework Dynamic LINQ; Entity Framework Dynamic Where Clause; Entity Framework OrderBy String; StackOverflow Related Questions. Building dynamic where clauses in LINQ to EF queries

ASP.NET C#, NET Core + Entity Framework Core projects, I've stumbled upon a rather typical scenario: being able to make a dynamic query – a query on  There was a time, before LINQ and Entity Framework, when developers concatenated SQL statements together and passed them to ADO.NET to be executed. This was a more flexible system than LINQ with EF because it let you generate a query at runtime from the user's input.

Comments
  • So, you want at runtime to determine which columns to select? Do I have that correctly?
  • @TiesonT. yes exactly
  • Thank you for your help , but this gives me an error (TableRepository.Entities.Entity does not contain a definition for 'sCol' )
  • That perfectly did the trick and got me exactly what I wanted.Thank you
  • @ArekBal What are you trying to say? Yes it is related to Entity Framework since I am providing Linq on collection TableRepository.Entities which is collection of EntityObject, Please explain your problem or provide me link for to your question so that I can find something for you. And yes it is not dynamic linq plz ref Naveen answer for same.
  • This one still hits the database asking all columns.
  • Note that this is a really terrible answer if you're dealing with Entity Framework. This is still going to retrieve all data from the database and also requires that you have a fixed entity type.
  • This answer could be greatly improved if you provided a summary of the linked webpage (in case the link breaks for some reason) or if you added a code sample using the tool detailed on the webpage.
  • There is no explanation about subject on the link you provided.
  • Welcome to Stack Overflow! Please note you are answering a very old and already answered question. Here is a guide on How to Answer.
  • As bad as Ankush Madankar answer. Doesn't do dynamic select clause at all. Just reflection. First line of function tells it all about how bad it is. I am only glad that you used GetProperty and checked null there...
  • Thank you for your help ,right now I cannot change my function parameters. So your second option is suitable for me. I have tried that and got an error (LINQ to Entities does not recognize the method 'System.Object GetValue) .
  • I removed the second parameter from GetValue. Try updated code. BTW, you might want to add some error handling, i.e. what happens is the property by the given name is not found - a misspelled column name, perhaps.