Select specific table columns to list async using EF Core

.net-core entity-framework-core sql-server

Question

I have a table called CarMakes with a number of columns:

  • Id
  • Name
  • DateCreated
  • etc..

I would like to retrieve all these records in the table, but only the Id and Name columns (such that the generated SQL only retrieves these specific columns). This must also be executed asynchronously.

However, I can see that it can be done async for single entities but I'm having some trouble figuring out how to do this as an async list.

I have the following generic method which returns a task of IEnumerable<T> where T is CarMake:

public async Task<IEnumerable<T>> GetAllAsync(params Expression<Func<T, object>>[] navigationProperties)
{
    IQueryable<T> query = Context.Set<T>();
    query = navigationProperties.Aggregate(query, (current, navigationProperty) => current.Include(navigationProperty));
    IEnumerable<T> entities = await query.ToListAsync();
    return entities;
}

How would I modify this method to only generate sql to get the specific fields I want?

1
0
3/22/2019 2:00:47 PM

Accepted Answer

I've expanded on ibrahimozgon's answer by using a dynamic return type when selecting specific columns:

public async Task<dynamic> GetDynamicAsync(Expression<Func<T, bool>> where, Expression<Func<T, dynamic>> columns,
    params Expression<Func<T, object>>[] navigationProperties)
{
    IQueryable<T> query = Context.Set<T>();
    query = navigationProperties.Aggregate(query, (current, navigationProperty) => current.Include(navigationProperty));
    dynamic entity = await query.Where(where).Select(columns).FirstOrDefaultAsync();
    return entity;
}

This will allow a generic implementation where you can map whatever fields need mapping to an actual DTO object or similar subclass:

public class CarMakeDtoConverter : ITypeConverter<object, CarMakeDto>
{
    public CarMakeDto Convert(object source, CarMakeDto destination, ResolutionContext context)
        => new CarMakeDto
        {
            // Because this method's class only takes in object and not dynamic, we need to access the properties via:
            // GetType().GetProperty().GetValue()
            Id = (int)source.GetType().GetProperty("Id").GetValue(source),
            Name = (string)source.GetType().GetProperty("Name").GetValue(source)
        };
}

So then you can use the following implementation in your upper layers:

public async Task<CarMakeDto> GetCarMakeDto(int id)
{
    var result = await GetDynamicAsync(x => x.Id == id, x => new { x.Id, x.Name});
    var carMake = Mapper.Map<CarMakeDto>(result);
    return carMake;
}
0
3/1/2019 11:35:16 PM

Popular Answer

I think you want something like this. I changed your GetAllAsync method a little bit. Just, for example, we have Model class.

class Model
{
    public string Val { get; set; }
}

I moved your method to a helper class and made method generic. It takes 2 generic type, 1 for IQueryable type, 1 for the return type. the first parameter of the method is expression, we will pass select expression here. the second one is your navigationProperties.

static class Helper
{
    public static async Task<IEnumerable<TY>> GetAllAsync<T, TY>(Expression<Func<T, TY>> selector, params Expression<Func<T, object>>[] navigationProperties)
    {
        IQueryable<T> query = new EnumerableQuery<T>(new List<T>());
        query = navigationProperties.Aggregate(query, (current, navigationProperty) => current.Include(navigationProperty));
        IEnumerable<TY> entities = await query.Select(selector).ToListAsync();
        return entities;
    }
}

And we will call the method here. Model for IQueryable and string for returning type.

private static async Task MainAsync(string[] args)
{
    var result = await Helper.GetAllAsync<Model, string>(s => s.Val,navigationProperties:...);
}

Hope this helps.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow