Dynamic Linq SearchFor from entities by passing a tab name, column name and value as parameter

Question

I need to write a function called IsExists(string TableName,string KeyColumnName,string ValueToCheck) in a DAL that checks if data exists in a specific table and in the specific column that I pass

Basically, I want to achieve something similar when I try to execute a sql query

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

But I can not use sql query ..

In my solution, I have a .edmx file, an entity class along with a repository class that has a 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 doesn't work again.

Someone help me with this. I tried for a long time. Suggestions are greatly appreciated.

Answer 1

You can execute the SQL query from dbcontext:

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

Answer 2

Here is how I would solve this type of problem. You can convert it to a function if you want.

 // Let 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(); }