EF Core – Dynamically specify which table to query at runtime

  Kiến thức lập trình

I have many tables that can be fetched. Right now, for each table I have a Controller class that gets the request, applies the query params and paging, and finally returns the result. All of them are pretty much the same and only the table name is different, which resulted in many files and many duplications.

So, I was trying to create one endpoint and serve all of them. The goal was to detect the table from route parameter.

First I created this Attribute:

[AttributeUsage(AttributeTargets.Class, AllowMultiple = false, Inherited = false)]
public class ResourceAttribute : Attribute
{
    public ResourceAttribute(ResourceType resourceType)
    {
        ResourceType = resourceType;
    }

    public ResourceType ResourceType { get; }
}

public enum ResourceType
{
    Public = 1,
    User,
    Admin
}

Public means anyone can fetch the data, example: Product. User means the resource is available for each logged-in user and they should not see other users’ data, example: Review, Order. And Admin means this resource is only available in our admin panel and it is not public, example: AppSettings.

Next, I apply the attribute:

[Resource(ResourceType.Public)]
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    // ...
}

Then I created a singleton service to store all resource information:

public class ResourceService
{
    private readonly Dictionary<string, RoutableResource> _resourceMap;

    public ResourceService()
    {
        _resourceMap = AppDomain.CurrentDomain.GetAssemblies()
            .SelectMany(x => x.GetTypes())
            .Where(x => x.IsDefined(typeof(ResourceAttribute), false))
            .ToDictionary(
                GetResourceRouteName,
                x => new RoutableResource
                {
                    Name = GetResourceRouteName(x),
                    Type = x,
                    ResourceType = GetResourceRouteType(x)
                });

        // ...
    }

    public bool ResourceExists(string resourceName) => _resourceMap.ContainsKey(resourceName);
    public RoutableResource? GetResource(string resourceName) => _resourceMap.GetValueOrDefault(resourceName);
}

public class RoutableResource
{
    public string Name { get; set; }
    public Type Type { get; set; }
    public ResourceType ResourceType { get; set; }
}

And this is the controller method:

[HttpGet("{resourceName}/{id?}")]
public async Task<IActionResult> GetResource(string resourceName, string? id)
{
    if (!_resourceService.ResourceExists(resourceName))
    {
        return NotFound();
    }

    RoutableResource resource = _resourceService.GetResource(resourceName)!;
    
    // Check authorization

    // Get IQueryable of the selected resource ???? [I'm stuck here]

    // Get all query params and apply them to query with the help of reflection and expressions

    // Get the target ViewModel class from ResourceVM map and apply the projection

    // Finally return the result
}

There is a DbContext.Set<T>() but it requires the type at compile time. With the help of this answer I got an IQueryable but it was not useful.

LEAVE A COMMENT