entity.ToListAsync().Where or entity.Where()?

asp.net-web-api async-await c# entity-framework-6

Question

I have trouble to decide which way is the best to query my SQL Server on my WebAPI 2 Backend

I am trying to use async/await as often as possible, but i found that when i return the whole collection, there is no async option available.

Which way would be the best one?

[ResponseType(typeof(List<ExposedPublisher>))]
[HttpGet]
public async Task<IHttpActionResult> GetPublisher()
{
    var list = new List<PublisherWithMedia>();
    foreach (var publisher in _db.Publisher.Where(e => e.IsDeleted == false))
    {
        var pub = new PublisherWithMedia()
            {
                Id = publisher.Id,
                Name = publisher.Name,
                Mediae = new List<WebClient.Models.Media>()
            };
            foreach (var media in publisher.Media)
            {
                pub.Mediae.Add(ApiUtils.GetMedia(media));
            }
        list.Add(pub);
    }
    return Ok(list);
}

or

[ResponseType(typeof(List<PublisherWithMedia>))]
[HttpGet]
public async Task<IHttpActionResult> GetPublisher()
{
    var list = new List<PublisherWithMedia>();
    var entity = await _db.Publisher.ToListAsync();
    foreach (var publisher in entity.Where(e => e.IsDeleted == false))
    {
        var pub = new PublisherWithMedia()
            {
                Id = publisher.Id,
                Name = publisher.Name,
                Mediae = new List<WebClient.Models.Media>()
            };
            foreach (var media in publisher.Media)
            {
                pub.Mediae.Add(ApiUtils.GetMedia(media));
            }
        list.Add(pub);
    }
    return Ok(list);
}

The operation could potentially result in a very large resultset, so it would make sense to filter directly on the database, especially since with time, the amount of deleted records could surpass the amount of undeleted ones. However, with the large result and the query of children (Media), it makes also sense to have the operation work asynchronously, as it should be quite time-consuming. Sadly there is no async Where() in this context.

Or is there a third way i am not aware of?

1
1
5/28/2018 3:42:27 PM

Accepted Answer

You can have the best of both worlds: filter in the database and asynchronously execute the query:

var publishers = await _db.Publisher
    .Where(e => !e.IsDeleted)
    .ToListAsync();

That said, depending on what ApiUtils.GetMedia(media) does, you could even perform the projection on the query:

var publishers = await _db.Publisher
    .Where(e => !e.IsDeleted)
    .Select(e => new PublisherWithMedia()
    {
        Id = e.Id,
        Name = e.Name,
        Mediae = e.Mediae.Select(m => ApiUtils.GetMedia(m)).ToList()
    };
    .ToListAsync();
4
5/28/2018 3:50:46 PM


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