SELECT result map to entity in Dynamic Linq in Entity Framework Core

Question

I have a Linq query which is selecting 2 columns(that can be any 2 from all columns) dynamically based on some condition.I need to map the query result in to below model irrespective of selected column names

public class FinalModel
{ 
    public string Text { get; set; }
    public string Id { get; set; }
}

Currently I am using reflection to map the result in to that model because i am getting some anonymous list of objects and it is working fine, But I want to remove that reflection and need to add the mapping in the select itself, my current implementation is like below

 string column1 = "Name" //can be other columns also
 string column2 = "Age"
 var result = _context.table1
                      .Select("new ("+ column1 +","+ column2 +")")
                      .Distinct()
                      .Take(10) // having more records in table
                      .ToDynamicList()
                      .Select(x => new FinalModel()
                       {
                           Id = x.GetType().GetProperty(column1).GetValue(x).ToString(),
                           Text = x.GetType().GetProperty(column2).GetValue(x).ToString(),
                       });
                      

The above code is working fine but I need to remove the below section

       .Select(x => new FinalModel()
              {
                 Id = x.GetType().GetProperty(column1).GetValue(x).ToString(),
                 Text = x.GetType().GetProperty(column2).GetValue(x).ToString(),
              });

Is there any way to remove the refletion and add that model mapping directly inside Select("new (column1,column2)")

2. Is there any way to add orderBy with Column2 variable?

Answer 1

You can use generic versions of Select and ToDynamicList and OrderBy($"{column2}") for sorting:

var result = _context.table1
    .Select<FinalModel>($"new ({column1} as Id, {column2} as Text)")
    .Distinct()
    .OrderBy("Text")
    .Take(10)
    .ToDynamicList<FinalModel>();

Or if you want to stick with dynamic:

var result = _context.table1
    .Select($"new ({column1}, {column2})")
    .Distinct()
    .OrderBy($"{column2}")
    .Take(10) 
    .ToDynamicList()
    .Select(d => new FinalModel()
    {
        Id = d[column1].ToString(),
        Text = d[column2].ToString(),
    })
    .ToList();

Answer 2

1. You need to use .Select<T> instead of just .Select() to make sure that the selected entity the correct type. So in your case you need .Select<FinalModel>.

2. Use the as cast operator to "rename" the properties from the source-entity to the destination entity (FinalModel)

3. If you want the result to be typed, also use .ToDynamicList<FinalModel>().

Full example code below:

using System.Linq.Dynamic.Core;

class Program
{
    static void Main(string[] args)
    {
        var myQuery = new[] { new XModel { Age = "1", Name = "n" } }.AsQueryable();

        string column1 = "Name";
        string column2 = "Age";

        var result = myQuery
            .Select<FinalModel>("new (" + column1 + " as Id, " + column2 + " as Text)")
            .Distinct()
            .Take(10)
            .ToDynamicList<FinalModel>();

        Console.WriteLine(result[0].Id + "  " + result[0].Text);
    }


    public class XModel
    {
        public string Name { get; set; }
        public string Age { get; set; }
    }

    public class FinalModel
    {
        public string Text { get; set; }
        public string Id { get; set; }
    }
}