CODEWITHSUNDEEP
c#linqentity-framework
Patrick
Patrick

Reputation: 145

C# Linq Column Name as variable

I have a table where I want to make a query on variable columns. Like:

private void query(string column, string value) {

    using (var db = new myDB()) {

        var s1 = (from c in db.Components
                  where (**column** == **value**)
                  select new {c.id, **column**});
    }
}

lets say I want to look for a supplier then it would be like:

var s1 = (from c in db.Components
          where (c.supplier == "abc")
          select new {c.id, c.supplier});

is there a way to pass the column name as variable?

Upvotes: 8

Views: 21354

Answers (5)

Ashish Makhija
Ashish Makhija

Reputation: 11

Just use typeof to use the column name

public string columnName(string Id, string columnName, string columndata)
        {
            var story = _CidbContext.Stories.Where(m => m.Id.ToString() == Id).FirstOrDefault();
            var property = typeof(Story).GetProperty(columnName);
            if (property != null)
            {

                var convertedValue = Convert.ChangeType(columndata, property.PropertyType);

                property.SetValue(story, convertedValue);
                _CidbContext.SaveChanges();
                return "Data Saved";
            }
            return "No Column found";
        }

Upvotes: 0

Darkseal
Darkseal

Reputation: 9564

I'm resurrecting this old thread because I had to work around that issue with ASP.NET Core 2.2 today. I used the System.Linq.Dynamic.Core NuGet package to create the following extension method, which works beautifully if you need to check if multiple given string values are contained within multiple given columns.

public static IQueryable<TEntity> WhereContains<TEntity>(
    this IQueryable<TEntity> query,
    string field,
    string value,
    bool throwExceptionIfNoProperty = false,
    bool throwExceptionIfNoType = false)
    where TEntity : class
{
    PropertyInfo propertyInfo = typeof(TEntity).GetProperty(field);
    if (propertyInfo != null)
    {
        var typeCode = Type.GetTypeCode(propertyInfo.PropertyType);
        switch (typeCode)
        {
            case TypeCode.String:
                return query.Where(String.Format("{0}.Contains(@0)", field), value);
            case TypeCode.Boolean:
                var boolValue = (value != null
                    && (value == "1" || value.ToLower() == "true"))
                    ? true
                    : false;
                return query.Where(String.Format("{0} == @0", field), boolValue);
            case TypeCode.Int16:
            case TypeCode.Int32:
            case TypeCode.Int64:
            case TypeCode.UInt16:
            case TypeCode.UInt32:
            case TypeCode.UInt64:
                return query.Where(String.Format("{0}.ToString().Contains(@0)", field), value);

            // todo: DateTime, float, double, decimals, and other types.

            default:
                if (throwExceptionIfNoType)
                    throw new NotSupportedException(String.Format("Type '{0}' not supported.", typeCode));
                break;
        }
    }
    else
    {
        if (throwExceptionIfNoProperty)
            throw new NotSupportedException(String.Format("Property '{0}' not found.", propertyInfo.Name));
    }
    return query;
}

The code can be used with .NETStandard/.NETCore (using the aforementioned System.Linq.Dynamic.Core package) and also with ASP.NET 4.x (using the System.Linq.Dynamic package).

For further info regarding the WhereContains extension method and a full use-case info, check out this post on my blog.

Upvotes: 1

cnom
cnom

Reputation: 3241

Short answer is to add library System.Linq.Dynamic as a reference and do:

string columnName = "Supplier";
var s1 = Suppliers
    .Where(String.Format("{0} == \"abc\"", columnName))
    .Select(new {c.id, c.supplier};

Following is a complete working example of Dynamic Linq, where column-name is a parameter:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic;

public class Program
{
    public static void Main()
    {
        var lstContacts = new List<Contact>{
            new Contact{Id = 1, Active = true, Name = "Chris"}, 
            new Contact{Id = 2, Active = true, Name = "Scott"}, 
            new Contact{Id = 3, Active = true, Name = "Mark"}, 
            new Contact{Id = 4, Active = false, Name = "Alan"}};

        string columnName = "Active";
        List<Contact> results = lstContacts.Where(String.Format("{0} == true", columnName)).ToList();

        foreach (var item in results)
        {
            Console.WriteLine(item.Id.ToString() + " - " + item.Name.ToString());
        }
    }
}

public class Contact
{
    public int Id
    {
        get;
        set;
    }

    public bool Active
    {
        get;
        set;
    }

    public string Name
    {
        get;
        set;
    }
}

You can experiment with this .net-fiddle-here

Upvotes: 1

Burk
Burk

Reputation: 3067

This example can be useful i guess.

 void BindGridTypeSafe()
    {
        NorthwindDataContext northwind = new NorthwindDataContext();

        var query = from p in northwind.Products
                    where p.CategoryID == 3 && p.UnitPrice > 3
                    orderby p.SupplierID
                    select p;

        GridView1.DataSource = query;
        GridView1.DataBind();
    }

    void BindGridDynamic()
    {
        NorthwindDataContext northwind = new NorthwindDataContext();

        var query = northwind.Products
                             .Where("CategoryID = 3 AND UnitPrice > 3")
                             .OrderBy("SupplierID");

        GridView1.DataSource = query;
        GridView1.DataBind();
    }

Upvotes: 5

Dave Bish
Dave Bish

Reputation: 19646

A nice way is to use Dynamic Linq

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Something like:

var s1 = (from c in db.Components
    where(column + "=" + value)
    select new {c.id, **column**});

Upvotes: 3

Related Questions