C# EF Core Select Specific Column via Condition

Question

I have a Table in the Database which has around 100+ columns.

I have a query which will select specific columns from a Database. But I want to do in a dynamic way. From the 100+ columns 2 Columns will always be there and there will be one more column from the 100+ column.

For now I am using a Select Expression

Expression<Fun<MyTable,MyDto>> Select = p => new MyDto() {
 Column1 = p.Column1,
 Column2 = p.Column2,
 Extra = p.(on of the 100 Column)
}

But I want to set this Extra Property to one of the column dynamically.

Something like

if (SomeCondition) // This  can an Enum or a String which will determine which column to chose
{

 Select.Extra = (one of the 100 Column)
}
else {
 Select.Extra = (one of the 100 Column)

}

Is there a way to update the selected columns on Runtime?

Show briefly how your entity looks like and DTO class. Do you need just one additional column? — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Aug 30, 2023 at 9:38

Svyatoslav Danyliv · Accepted Answer · 2023-08-30 13:56:00Z

This is helper extension method which allows to append column to existing projection. It is useful when you need to add column to projection dynamically. For example, if you have projection like this:

Expression<Fun<MyTable,MyDto>> projection = p => new MyDto() 
{
    Column1 = p.Column1,
    Column2 = p.Column2,
};

var newProjection = projection.AppendProperty(p => p.Extra, "Column3");

As result you will get projection like this:

Expression<Fun<MyTable,MyDto>> newProjection = p => new MyDto() 
{
    Column1 = p.Column1,
    Column2 = p.Column2,
    Extra = p.Column3,
};

Dynamic property can be nested, path is separated by dot:

var newProjection = projection.AppendProperty(p => p.Extra, "Navigation.Column");

Function works with nested properties as well:

var newProjection = projection.AppendProperty(p => p.ExtraNested.Value, "Column3");

With nested property it will create nested object:

Expression<Fun<MyTable,MyDto>> newProjection = p => new MyDto() 
{
    Column1 = p.Column1,
    Column2 = p.Column2,
    ExtraNested = new NestedDto 
    { 
        Value = p.Column3 
    },
};

It is also possible to replace existing column:

var newProjection = projection.AppendProperty(p => p.Extra, "Column3");
newProjection = newProjection.AppendProperty(p => p.Extra, "Column4");

And extension method itself:

public static class ProjectionExtensions
{
    public static Expression<Func<TSource, TDest>> AppendProperty<TSource, TDest>(this Expression<Func<TSource, TDest>> projection, Expression<Func<TDest, object>> destColumn, string propPath)
    {
        if (projection.Body is not MemberInitExpression mi)
            throw new NotImplementedException($"Support of {projection.NodeType} is not implemented.");

        var entityParam = projection.Parameters[0];

        var destBody = UnwrapConvert(destColumn.Body);

        var path = new List<MemberExpression>();

        if (destBody is not MemberExpression me)
            throw new InvalidOperationException($"Expected MemberExpression as {nameof(destColumn)} argument.");

        var current = me;
        while (true)
        {
            path.Insert(0, current);
            if (current.Expression is MemberExpression subMember)
                current = subMember;
            else if (current.Expression == destColumn.Parameters[0])
                break;
            else
                throw new InvalidOperationException($"Unexpected expression {current.Expression}");
        }

        var newMemberInit = mi.Update(mi.NewExpression, MergeBinding(mi.Bindings, path, 0, MakePropPath(entityParam, propPath)));

        var newProjection = Expression.Lambda<Func<TSource, TDest>>(newMemberInit, projection.Parameters);

        return newProjection;
    }

    static List<MemberBinding> MergeBinding(IEnumerable<MemberBinding> bindings, List<MemberExpression> path, int index, Expression withExpression)
    {
        var currentMember = path[index];

        var result = new List<MemberBinding>(bindings);

        MemberBinding? foundBinding = null;

        for (int i = 0; i < result.Count; i++)
        {
            var binding = result[i];
            if (binding.Member == currentMember.Member)
            {
                foundBinding = binding;

                if (path.Count - 1 == index)
                {
                    result[i] = Expression.Bind(currentMember.Member, withExpression);
                }
                else
                {
                    if (binding is not MemberAssignment ma)
                        throw new InvalidOperationException("Only MemberAssignment is supported.");

                    if (ma.Expression is not MemberInitExpression mi)
                        throw new InvalidOperationException("Only MemberInit in binding is supported.");

                    var assignExpression = Expression.MemberInit(Expression.New(currentMember.Type), 
                        MergeBinding(mi.Bindings, path, index + 1, withExpression));

                    result[i] = Expression.Bind(currentMember.Member, assignExpression);
                }
                
                break;
            }
        }

        if (foundBinding == null)
        {
            if (path.Count - 1 == index)
            {
                result.Add(Expression.Bind(currentMember.Member, withExpression));
            }
            else
            {
                var assignExpression = Expression.MemberInit(Expression.New(currentMember.Type), 
                    MergeBinding(Enumerable.Empty<MemberBinding>(), path, index + 1, withExpression));

                result.Add(Expression.Bind(currentMember.Member, assignExpression));
            }
        }

        return result;
    }

    [return: NotNullIfNotNull(nameof(ex))]
    public static Expression? UnwrapConvert(Expression? ex)
    {
        if (ex == null)
            return null;

        switch (ex.NodeType)
        {
            case ExpressionType.ConvertChecked :
            case ExpressionType.Convert        :
            {
                if (((UnaryExpression)ex).Method == null)
                    return UnwrapConvert(((UnaryExpression)ex).Operand);
                break;
            }
        }

        return ex;
    }
    static Expression MakePropPath(Expression objExpression, string path)
    {
        return path.Split('.').Aggregate(objExpression, Expression.PropertyOrField);
    }
}

Do you have a resource link to learn Expressions so I could understand what you have written?
Sorry, no. I have just ten years of experience while developing LINQ provider. But this extension ReadableExpressions.Visualizers helps to understand what you have built on the fly.

Hussain Murtaza · Accepted Answer · 2023-08-30 11:28:17Z

-1

So I was able to figure things out based on the comment of smoksnes.

First use this Library:

using System.Linq.Dynamic.Core;

But I want to share the solution I made up plus what I chose over the other

My Solution:

Expression<Fun<MyTable,MyDto> Select = p => new MyDto() {
   Column1 = p.Column1,
   Column2 = p.Column2,
  Extra = p.GetType().GetProperty("ColumnNameToSelect").GetValue(p).ToString()
}

Now the above solution works but performance wise it sucks when going for large numbers of rows 10000+ as compared to the solution below.

Solution I went with

_context.MyTable.Where(p => p.IsDeleted == false)
.Select<MyDto>($"new (Column1 as Column1,Column2 as Column2, Extra as {"ColumnNameToSelect"})")
.ToList()

The above solution has better performance so go with that and only go with the first if that satisfies you use case.

These both solution work with EF Core and this is Tested on NET 6. Don't know about other Versions.

edited Aug 30, 2023 at 11:28

answered Aug 30, 2023 at 10:48

Hussain Murtaza

4075 silver badges18 bronze badges

1 Comment

Hussain Murtaza Over a year ago

@GertArnold using System.Linq.Dynamic.Core; This is what I am using

Collectives™ on Stack Overflow

C# EF Core Select Specific Column via Condition

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related