Dynamic LINQ query to get Field value from Database

Question

is it possible?

Public String Get_Filed_By_Id(string table_Name,String Field_Name,string PK_val)
{
    string strRes="";
    using(mydbcontext db=new mydbcontext())
    {
      var x=db.table_Name.Where(p=>p.Id=PK_val).FirstOrDefault().Field_Name;
      strRes=Convert.Tostring(x);
    }
 return strRes;
}

OR

var x=(from o in db.table_Name where o.Id=PK_val select o.Field_Name).FirstOrDefault();

Here, i'm passing Table_Name,Column_Name and the Condition value(PK_val) to Get the Column_Name from Table_Name within a Certain Condition(Id=Pk_val).

Is it possible??

Bountiful

well if you need that kind of functionality the first thing on mind is reflection (but not inside IQueryable): var tableItem= db.table_Name.Where(p=>p.Id=PK_val).FirstOrDefault(); var x=tableItem.GetType().GetProperty(FieldName).GetValue(tabelItem, null); – Easterling

have you considered using repository pattern? – Decennary

@Arashjo no, can you show me the way, make an answer?? – Bountiful

user3540365 ,just a moment plz – Decennary

Answer 1

Is it possible??

Yes, it is.

First, some helpers:

using System;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace YourNamespace
{
    internal static class DbHelpers
    {
        public static object GetColumnById(this object dbContext, string tableName, string columnName, object id)
        {
            var table = (IQueryable)dbContext.GetType().GetProperty(tableName).GetValue(dbContext, null);
            var row = Expression.Parameter(table.ElementType, "row");
            var filter = Expression.Lambda(Expression.Equal(Expression.Property(row, "Id"), Expression.Constant(id)), row);
            var column = Expression.Property(row, columnName);
            var selector = Expression.Lambda(column, row);
            var query = Call(Where.MakeGenericMethod(row.Type), table, filter);
            query = Call(Select.MakeGenericMethod(row.Type, column.Type), query, selector);
            var value = Call(FirstOrDefault.MakeGenericMethod(column.Type), query);
            return value;
        }
        private static readonly MethodInfo Select = GetGenericMethodDefinition<
            Func<IQueryable<object>, Expression<Func<object, object>>, IQueryable<object>>>((source, selector) =>
            Queryable.Select(source, selector));
        private static readonly MethodInfo Where = GetGenericMethodDefinition<
            Func<IQueryable<object>, Expression<Func<object, bool>>, object>>((source, predicate) =>
            Queryable.Where(source, predicate));
        private static readonly MethodInfo FirstOrDefault = GetGenericMethodDefinition<
            Func<IQueryable<object>, object>>(source =>
            Queryable.FirstOrDefault(source));
        private static MethodInfo GetGenericMethodDefinition<TDelegate>(Expression<TDelegate> e)
        {
            return ((MethodCallExpression)e.Body).Method.GetGenericMethodDefinition();
        }
        private static object Call(MethodInfo method, params object[] parameters)
        {
            return method.Invoke(null, parameters);
        }
    }
}

and now your function:

public string Get_Field_By_Id(string table_Name, string field_Name, string PK_val)
{
    using (var db = new mydbcontext())
        return Convert.ToString(db.GetColumnById(table_Name, field_Name, PK_val));
}

Promycelium

Answer 2

It is not really possible with EntityFramework actually(as far as I know). If you only needed the field by its name, then you could have used @Den's proposed solution. But you want to specify the table name too as a parameter. So I suggest you to use standard Sql Connector api, and build the query string with the parameters you provide.

Check this link for usage of standard sql connector api.

Hominid

Answer 3

I had this question too ,I know this is not exactly what you want and you need write more code but it's much cleaner than those you want to write.
Using repository pattern
For every table you should have a model class and Repository class.
Consider this code(this code from one of my project)
This is my comment table(this can be anything with or without navigation property)

  public sealed class Comment
{
    public string CommentText { get; set; }
    public DateTime PostDate { get; set; }
    public int PostId { get; set; }
    public int? PageId { get; set; }
    public Page Page { get; set; }
    public User User { get; set; }
    public string UserId { get; set; }
    public int? ParentId { get; set; }
    public Comment[] ChildComments { get; set; }
}  

RepositoryComment

  public sealed class CommentRepository : BaseRepository<Comment>
{
    public CommentRepository(BabySitterContext context)
        : base(context)
    {
    }
}  

and a base class that you send your query with table name(here model) and field(you can extend clas for more functionality)

public class BaseRepository<T> where T : class
{
    protected BabySitterContext Context;
    private readonly PluralizationService _pluralizer = PluralizationService.CreateService(CultureInfo.GetCultureInfo("en"));
    public BaseRepository(BabySitterContext context)
    {
        this.Context = context;
    }
    public bool Add(T t)
    {
        Context.Set<T>().Add(t);
        Context.SaveChanges();
        return true;
    }
    public bool Update(T t)
    {
        var entityName = GetEntityName<T>();

        object originalItem;
        var key = ((IObjectContextAdapter)Context).ObjectContext.CreateEntityKey(entityName, t);
        if (((IObjectContextAdapter)Context).ObjectContext.TryGetObjectByKey(key, out originalItem))
        {
            ((IObjectContextAdapter)Context).ObjectContext.ApplyCurrentValues(key.EntitySetName, t);
        }
        Context.SaveChanges();
        return true;
    }
    public void Attach(T t)
    {
        if (t == null)
        {
            throw new ArgumentNullException("t");
        }

        Context.Set<T>().Attach(t);
        Context.SaveChanges();
    }
    public void Remove(T t)
    {
        if (t == null)
        {
            throw new ArgumentNullException("t");
        }
        Context.Set<T>().Remove(t);
        Context.SaveChanges();
    }
    public IEnumerable<T> Get(Expression<Func<T, bool>> filter = null, Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null, string includeProperties = "")
    {
        IQueryable<T> query = Context.Set<T>();

        if (filter != null)
        {
            query = query.Where(filter.Expand());
        }

        foreach (var includeProperty in includeProperties.Split
            (new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
        {
            query = query.Include(includeProperty);
        }

        if (orderBy != null)
        {
            return orderBy(query).ToList();
        }
        else
        {
            return query.ToList();
        }
    }
    private string GetEntityName<TEntity>() where TEntity : class
    {
        return string.Format("{0}.{1}", ((IObjectContextAdapter)Context).ObjectContext.DefaultContainerName, _pluralizer.Pluralize(typeof(TEntity).Name));

    }

    public virtual IEnumerable<T> GetByBusinessKey(T entity)
    {
        return null;
    }
}    

For any other table just make model class and reposiotry then inherite from base class

Using code

 var context = new BabySitterContext();
 var  _commentRepository = new CommentRepository(context);
 var comment = _commentRepository.Get(x => x.PostId == id).FirstOrDefault();

Decennary

Answer 4

No, but in this way

Public String Get_Filed_By_Id(string table_Name,String Field_Name,string PK_val)
{
    string strRes="";
    using(mydbcontext db=new mydbcontext())
    {
      var x=db.table_Name.Where(p=>p.Id=PK_val).Select(b=>b.Field_Name).FirstOrDefault();
      strRes=Convert.Tostring(x);
    }
 return strRes;
}

Diphthongize

Not gonna Work, i'm trying to pass the Table_Name and 'Field_value' as a Parameter – Bountiful

then use query, like db.database.ExecuteSQLQuery or something like this which takes a string (query) as parameter. – Diphthongize

i believe this way possible, but guess it would be broad, but i want to see the way. – Bountiful