CODEWITHSUNDEEP
c#linqlambdaentity-framework-6
Subliminal Hash
Subliminal Hash

Reputation: 13742

Entity Framework - LINQ - Dynamic Column Names

I know that this question was asked several times but the more answers I came accross the more I got confused.

Given I have a table Categories and within this table columns: Category1, Category2, Category3 and so on.. How can I write a method like below and use a parameter (in this case catno)

As simplified as I could get;

public List<string> GetProductCategories(int catno)
{
    using (var ctx = new myEntities())
    {
        return (from c in ctx.Categories
                select c.Category1).ToList();
               //1 being the catno parameter obviously
    }
}

I definitely DO NOT want to use the Dynamic Linq library as it is not type safe. And although (and obviously) a working answer would do fine, a detailed explanation to the problem and the solution is provided.

Thanks in advance

Upvotes: 1

Views: 3162

Answers (3)

roozbeh S
roozbeh S

Reputation: 1124

public List<string> GetProductCategories(int catno)
{
    string catName = string.Format("Category{0}", catno);
    using (var ctx = new myEntities())
    {
        var result = ctx.Categories.Select(x => {
                var prop = x.GetType().GetProperty(catName);
                if (prop == null)
                {
                    return double.NaN;
                }
                return double.Parse(prop.GetValue(x).ToString());
            }).ToList();
        return result.Where(x => !double.IsNaN(x)).ToList();
    }
}

Upvotes: 0

arekzyla
arekzyla

Reputation: 2898

You can define expression that selects the property based on string name. Then in IQueryable interface you can use it in Select argument. You would need to use method syntax for it though:

public List<string> GetProductCategories(int catno)
{
    var parameter = Expression.Parameter(typeof(Category));
    var property = Expression.Property(parameter, $"Category{catno}");
    var selector = Expression.Lambda<Func<Category, string>>(property, parameter);

    using (var ctx = new myEntities())
    {
        return ctx.Categories
            .Select(selector)
            .ToList();
    }
}

Expression.Property method is performing some type checks so if on type Category there is no given property defined it will thrown an excpetion. For example this will fail if there is no Category0 property:

var property = Expression.Property(parameter, "Category0");

Upvotes: 1

Shahin Dohan
Shahin Dohan

Reputation: 6882

If you want complete typesafety, I don't see any other way than to use a switch case to map the integer to its corresponding category:

public List<string> GetProductCategories(int catno)
{
    using (var ctx = new myEntities())
    {
        switch (catno)
        {
            case 1:
                return ctx.Categories.Select(c => c.Category1).ToList();
            case 2:
                return ctx.Categories.Select(c => c.Category2).ToList();
            default:
                throw new ArgumentException("Category number not supported!");
        }
    }
}

Another thing you can obviously do is to refactor your code so that you have a list of Category classes that contain your string property as well as the number, then you can write something like this:

return ctx.Categories.Where(c => c.CategoryNumber == catno)
    .Select(c => c.CategoryDescription)
    .ToList();

I think you should probably refactor your code. I smell bad design when I see "Category1" and "Category2", it sounds like these should be items in a list of Categories.

Also a comment about Dynamic Linq, there's nothing wrong with using dynamic linq if for example you get a request from a client that is out of your control. You can simply return an error if the property is not found.

Upvotes: 1

Related Questions