Entity Framework + ODATA + Dynamic column mapping

Question

I have two OData Controllers:

PersonsController
PersonsUnrestrictedController

The only way in which they will differ is that a couple of properties has to get their values from different columns in the persons table depending on the controller.

The PersonsController will send back a list of Persons where the persons givenname, familyname etc are alias names whereas PersonsUnrestrictedController will send back a list of Persons with the persons real names. All other properties will be exactly the same, including navigation properties and their relationships with other tables.

It is extremely important that PersonsController under no circumstances reveals a persons real name.

Is it possible to dynamically switch between:

[Column("AltGivenName")]
public string GivenName { get; set; }

and

[Column("GivenName")]
public string GivenName { get; set; }

depending on controller?

Or alternatively have two properties GivenName and AltGivenName and dynamically hide/reveal 1 of them depending on controller:

[DataMember(Name="GivenName")] //Either should this one be ignored
public string AltGivenName { get; set; }

public string GivenName { get; set; } //or this one, depending on controller

Or are there any other possible workarounds?

Edit: Added my code

Startup.cs

    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<PersonContext>(options => { options.UseSqlServer(Configuration.GetConnectionString("MyConnection")); });
            services.AddOData();
        }

        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseRouting();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapODataRoute("odata", "odata", GetEdmModel());
                endpoints.Select().Expand().MaxTop(null).Count();
            });
        }

        private static IEdmModel GetEdmModel()
        {
            var builder = new ODataConventionModelBuilder();
            var persons = builder.EntitySet<Person>("Persons");
            return builder.GetEdmModel();
        }
    }

PersonContext.cs

    public class PersonContext : DbContext
    {
        public DbSet<DbPerson> Persons { get; set; }

        public PersonContext(DbContextOptions<PersonContext> options) : base(options)
        {
        }
    }

DbPerson.cs

    [Table("Person")]
    public class DbPerson
    {
        [Key]
        public int Id { get; set; }

        public string GivenName { get; set; }

        public string AltGivenName { get; set; }
    }

Person.cs

    public class Person
    {
        [Key]
        public int Id { get; set; }

        public string GivenName { get; set; }
    }

MappingHelper.cs

    public static class MappingHelper
    {
        public static Person ToPerson(this DbPerson dbPerson)
        {
            return new Person
            {
                Id = dbPerson.Id,
                GivenName = dbPerson.GivenName,
            };
        }

        public static Person ToAnonymousPerson(this DbPerson dbPerson)
        {
            return new Person
            {
                Id = dbPerson.Id,
                GivenName = dbPerson.AltGivenName,
            };
        }
    }

PersonsController.cs

    public class PersonsController : ODataController
    {
        private readonly PersonContext _context;

        public PersonsController(PersonContext context)
        {
            _context = context;
        }

        [EnableQuery]
        public IActionResult Get()
        {
            return new ObjectResult(_context.Persons.Select(MappingHelper.ToPerson));
        }
    }

Running the following query takes 5-10 seconds http://localhost:4871/odata/persons?$top=10

If I instead change:

            return new ObjectResult(_context.Persons.Select(MappingHelper.ToPerson));

to

            return new ObjectResult(_context.Persons);

and change

var persons = builder.EntitySet<Person>("Persons"); 

to

var persons = builder.EntitySet<DbPerson>("Persons");

the same query takes 50-100 ms

There are about 150k persons in the person table.

Answer 1

Configure PersonsUnrestrictedController to return the standard set of DB operations, this is effectively your internal DbPerson api, and to define PersonsController as a dedicated controller to serve access to a Data Transfer Object called Person.

You already have most of the elements defined, all we need to change is the controller implementation.

No changes to the following:

• DbPerson.cs
• Person.cs
• PersonContext.cs

Define the two controllers in your EdmModel:

private static IEdmModel GetEdmModel()
{
    var builder = new ODataConventionModelBuilder();
    var persons = builder.EntitySet<Person>("Persons");
    var unrestricted = builder.EntitySet<DbPerson>("PersonsUnrestricted");
    return builder.GetEdmModel();
}

Rather than creating a Mapping method to map the objects, a simpler pattern would be to add a method into your controller to provide the base query that all operations in that controller should use.

In this way you could enforce common filter criteria, includes or sorting without having to declare the same query in each action. Its an easier pattern to maintain long term, you'll thank me when you have 20 actions or functions all with the same query that you need to refactor, or when you have to refactor a similar condition across multiple controllers.

Public Persons Controller:

public class PersonsController : ODataController
{
    private readonly PersonContext _context;

    public PersonsController(PersonContext context)
    {
        _context = context;
    }
    
    private IQueryable<Person> GetQuery() 
    {
        return from p in _context.Persons
               select new Person 
               { 
                   Id = p.Id,
                   GivenName = p.AltGivenName 
               };
    }

    [EnableQuery]
    public IActionResult Get()
    {
        return Ok(GetQuery());
    }

    [EnableQuery]
    public IActionResult Get(int key)
    {
        return Ok(GetQuery().Single(x => x.Id == key));
    }
}

Unrestricted Controller:

public class PersonsUnrestrictedController : ODataController
{
    private readonly PersonContext _context;

    public PersonsUnrestrictedController(PersonContext context)
    {
        _context = context;
    }

    private IQueryable<DbPerson> GetQuery() 
    {
        return _context.Persons;
    }

    [EnableQuery]
    public IActionResult Get()
    {
        return Ok(GetQuery());
    }

    [EnableQuery]
    public IActionResult Get(int key)
    {
        return Ok(GetQuery().Single(x => x.Id == key));
    }
}

Answer 2

The other answers here have focussed specifically on your request for 2 separate controllers mapped to the same table, but what it sounds like all you really need is a customised read-only feed from an OData Entity, that still has query support.

In OData this commonly implemented by defining a Function endpoint on the standard controller that returns a queryable set of DTOs.

No changes to the following:

• DbPerson.cs
• Person.cs
• PersonContext.cs

In this solution however we will have a single PersonsController that will have the standard Get() endpoint and a Function View() endpoint.

private static IEdmModel GetEdmModel()
{
    var builder = new ODataConventionModelBuilder();
    var persons = builder.EntitySet<DbPerson>("Persons");
    persons.EntityType.Collection.Function("View").ReturnsCollection<Person>();
    return builder.GetEdmModel();
}

PersonsController.cs

public class PersonsController : ODataController
{
    private readonly PersonContext _context;

    public PersonsController(PersonContext context)
    {
        _context = context;
    }
    
    [HttpGet]
    [EnableQuery]
    public IActionResult Get()
    {
        return Ok(_context.Persons);
    }

    [HttpGet]
    [EnableQuery]
    public IActionResult View()
    {
        return Ok(from p in _context.Persons
                  select new Person 
                  { 
                      Id = p.Id,
                      GivenName = p.AltGivenName 
                  });
    } 

}

OP has specifically asked about asp.net-core however this response will work in both EF6 & asp.net and asp.net-core

Answer 3

In cases like this (as a matter of fact, as a general rule) I would suggest that you separate your database models from your business models and have some mapping logic in-between.

This way you can have two business models Person and AnonymousPerson and one database model DbPerson (call them what you will). You then implement some mapping logic to convert DbPerson to Person and DbPerson to AnonymousPerson and use the proper mapping logic based on the situation you're in.

As an example, check out this fiddle. For a little more details, consider we have the following database model and two business object models:

public class DbPerson
{
    public string GivenName { get; set; }
    public string FamilyName { get; set; }
    public string AltGivenName { get; set; }
}

public class Person
{
    public string GivenName { get; set; }
    public string FamilyName { get; set; }
}

public class AnonymousPerson
{
    public string Nickname { get; set; }
}

Then we need some logic to convert the database model into either of the two business objects. I'm using extension methods here, but you can also just do normal methods if you prefer:

public static class MappingHelper
{
    public static Person ToPerson(this DbPerson dbPerson)
    {
        return new Person
        {
            GivenName = dbPerson.GivenName,
            FamilyName = dbPerson.FamilyName
        };
    }
    
    
    public static AnonymousPerson ToAnonymousPerson(this DbPerson dbPerson)
    {
        return new AnonymousPerson
        {
            Nickname = dbPerson.AltGivenName
        };
    }
}

Now, to get the data in the right format, you simply use the mapping method you need (using an Entity Framework-like approach):

// From PersonsController (or a service beneath)
var persons = myDatabase.Persons.Select(MappingHelper.ToPerson);
// Same as                       Select(p => p.ToPerson())
// or                            Select(p => MappingHelper.ToPerson(p))


// And from PersonsUnrestrictedController
var anonPersons = myDatabase.Persons.Select(MappingHelper.ToAnonymousPerson);