Using AutoMapper and Entity Framework to change what columns are fetched from a database table

Question

We have a new requirement where we need to dynamically fetch a list of columns from a table. We have 141 columns in this table, but say we only want to return 2 or 3 of them, we just don't know which 3 until run-time.

These columns are passed in as a List<string>, with the hopes I could use reflection to access the property on the source object.

We have support for this already by being able to pass a TObjectDto into our service which our IQueryable is projected to before hitting the database, it just doesn't handle the dynamic columns part of the equation.

So far I have unable to do something similar using MapFrom (no luck because reflection is not supported in LINQ, and it must be LINQ because otherwise it's not supported by queryable)

http://docs.automapper.org/en/stable/Queryable-Extensions.html#supported-mapping-options

Is there any reason I should keep going down this rabbit hole and instead just use what I have to generate some raw SQL?

Answer 1

I found a solution that I am pretty happy with

I declared a Dto class that inherits directly from my model class

    public class MyObjectDto : MyObject
    {
    }

Then configured a AutoMapper profile like so

CreateMap<MyObject, MyObjectDto>()
            .ForAllMembers(s => s.ExplicitExpansion());

I then curated the list of fields I do want to retrieve and

                result = await myObjects
                    .ProjectTo<TMyObjectDto>(_mapperConfiguration, null, fieldsToRetrieve.ToArray())
                    .ToListAsync().ConfigureAwait(false);

This then only queries the database values for the properties in fieldsToRetrieve and leaves the rest as null/default values, perfect for what I need

Thanks to Lucian for pointing me in the right direction in the comments