Entity Framework ToListAsync() with Select()

Question

I have the following ActionResult in my controller

public async Task<ActionResult> NewTickets()
{
    // Show tickets for all divisions a agent is in

    var user = "abcdefg";
    var company = "company1";

    var tickets = (from a in db2.Ticket
        join c in db2.Division on a.DivisionId equals c.DivisionId
        join dp in db2.DivisionParticipator on c.DivisionId equals dp.DivisionId
        where c.CompanyId == company.CompanyId && a.Status == "New" && dp.ApplicationUserId == user.Id
        select new
        {
            Id = a.Id,
            DivisionId = a.DivisionId,
            Name = a.Name,
            TicketDate = a.TicketDate,
            NewPosts = a.NewPosts,
            Status = a.Status,
            Type = a.Type
         })
         .ToList().Select(x => new Ticket
         {
             Id = x.Id,
             DivisionId = x.DivisionId,
             Name = x.Name,
             TicketDate = x.TicketDate,
             NewPosts = x.NewPosts,
             Status = x.Status,
             Type = x.Type
          });            

    return PartialView(tickets.ToList());
}

What i want to achieve is running this async, however if adding the ToListAsync()before Select()the Select() method becomes unavailable. The same goes for the return PartialView(tickets.ToList()).

I'm fairly new to working with async but reading about it's advantages I have been convering regular ActionResult methods with async ones. This has been going fine so far with easier LINQ-based queries. However for this ActionResult-method the query is a bit more advanced and the syntax/way to implement it seems to differ.

Any help to send me in the right direction would be greatly appreciated!

Solution 1

Split in into two statements:

var tickets0 = await (from ...).ToListAsync();

var tickets = tickets0.Select(...);

The .ToListAsync() returns a Task, so it doesn't accept the .Select extension method, which will need some IEnumerable. Only when you await that Task, you will get the List.

Another way (less readable IMO) would be:

 var tickets = (await (from ...).ToListAsync()).Select(...);

Note the extra ( ) around the await clause, which mean that the Select will work on the result of the awaited code, instead of the Task.

Solution 2

You will need to await the query before calling select, but given that the query is simply selecting a model out of the query you can create the model within the query and just await that.

public async Task<ActionResult> NewTickets()
{
    // Show tickets for all divisions a agent is in

    var user = "abcdefg";
    var company = "company1";

    var tickets = await (from a in db2.Ticket
        join c in db2.Division on a.DivisionId equals c.DivisionId
        join dp in db2.DivisionParticipator on c.DivisionId equals dp.DivisionId
        where c.CompanyId == company.CompanyId && a.Status == "New" && dp.ApplicationUserId == user.Id
        select new Ticket
        {
            Id = a.Id,
            DivisionId = a.DivisionId,
            Name = a.Name,
            TicketDate = a.TicketDate,
            NewPosts = a.NewPosts,
            Status = a.Status,
            Type = a.Type
         })
         .ToListAsync();

    return PartialView(tickets);
}

Solution 3

You also need to import System.Data.Entity rather than just System.Linq. That may seem silly, but I ended up on this question due to this. ToList is part of Linq but the async methods are specific to EF.

Comments

[Subtractive]

This, together with what @Ant P suggested it's working flawlessly! Thank you!

[rism]

This might work but it will bring back every column from the table query to memory as a result of the toList and then do the projection there rather than only bringing back the projected columns.