Dynamic Linq SearchFor from entities by Passing Tablename,ColumnName and value as parameter

Question

I need to write a function called IsExists(string TableName,string KeyColumnName,string ValueToCheck) in DAL which checks whether the data exists in the particular table and in the particular column which I am passing

Basically I want to achieve something like this when I try to put up in sql query

select count(id) from "+TableName+" where "+keyColumnName+"="+ValueToCheck+";

But I cant use sql query ..

In my solution I have an .edmx-file, an entity class along with a repository class, which has SearchFor method:

public class EntityRepository<C, TEntity> : IEntityRepository<TEntity>
        where TEntity : class
        where C : DbContext
{
    public IQueryable<TEntity> SearchFor(System.Linq.Expressions.Expression<Func<TEntity, bool>> predicate)
    {
        return _entities.Set<TEntity>().Where(predicate);
    }
}

I Tried something like this

public bool CheckIsExists<T>(string keyColumnName, string valueToCheck) where T : class
{
    bool isExist = false;

    using (var repository = ContextFactory.CreateEmployeeMasterRepository())
    {
        var repo = repository.GetEntityRepository<T>();
        object obj = (T)Activator.CreateInstance(type);
        repo.SearchFor(u => u.GetType().GetProperty(keyColumnName).GetValue(obj).ToString() == valueToCheck);
    }
    return isExist;
}

Which is again not working ..

Somebody help me in doing this. i have been trying from long time.. suggestions are greatly appreciated.

You should check my own question about dynamic LINQ: http://stackoverflow.com/questions/29234484/linq-dynamic-where-with-generic-property-and-value — W0lfw00ds, May 19 '15 at 10:43
1) It seems as if you have a framework here besides entityframework, I suggest that you first make it work with known types/objects before moving to generic. 2) Simulate this without a database first in a small console project — eugenekgn, Jun 09 '15 at 17:04
https://entityframework.net/knowledge-base/35307659/linq-select-dynamic-columns-and-values — Power Mouse, Feb 23 '21 at 21:08

score 0 · Answer 1 · answered Jan 08 '16 at 23:19

Here's how I would solve this type of problem. You could convert it to function if you like.

// Let's say I have a Customers table and want to search against its Email column
// First I get my data layer class that inherits from DbContext class
yourContextClass db = new yourContextClass();
// below I am getting valueToCheck from a view with GET method
public ActionResult Index(string valueToCheck)
{
 bool isExists = false;
 IEnumerable<Customers> customersList = (from cus in db.Customers select cus).ToList();
 int index = customersList.FindIndex(c => c.Email == valueToCheck);
 if (index >= 0) 
  isExists = True;
 // if index is -1 then the value to check does not exist
return View();
}

score -1 · Answer 2 · answered Jul 24 '15 at 20:03

-1

You can execute sql query from you dbcontext:

using (var ctx = new DBEntities())
{
   var itemsCount = ctx.Database.SqlQuery<int>("select count(id) from "+TableName+" where "+keyColumnName+" = "+ValueToCheck+").FirstOrDefault<int>();
}

answered Jul 24 '15 at 20:03

Wahid Bitar

13,776
13
78
106

1

this would be a very VERY bad solution. the query is passed to the database as-is without escaping, if the user queries for a table named `; truncate table Customers--` you're in for a ride. avoid this at all costs. – Stavm Feb 14 '19 at 15:13

Dynamic Linq SearchFor from entities by Passing Tablename,ColumnName and value as parameter

2 Answers2