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?

Seems to me your database design is wrong for this application — Sir Rufo, Jul 16 '19 at 04:43
No disagreement from me, this is in a way the core of our application and is pretty massive (in columns and data) — bla9x, Jul 16 '19 at 04:46
You need 80~ different queries which you need to choose via a string (name of the column or whatever is best). What about something like a Dictionary>>> ? — Sir Rufo, Jul 16 '19 at 05:06
http://docs.automapper.org/en/latest/Queryable-Extensions.html#explicit-expansion — Lucian Bargaoanu, Jul 16 '19 at 05:12

score 1 · Answer 1 · answered Jul 17 '19 at 21:12

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

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

1 Answers1