How to return Task list using LINQ or lambda in WinForm-CodePudding

I want to use async and await function in my LINQ function, but I don't how. I have two tables Order and Product I want join them and popout in DataGridView by using async.
Here is my async function.
My list definition is:

List<OrderDTO> transactionByDate;

and my function:

private async Task<List<OrderDTO>> GetTransactionByDate(DateTime today)
{
  return await Task.Factory.StartNew(() =>
   {
    using (Db db = new Db())
    {
       var totalOrder = (from u in db.Orders
                         join p in db.Product on u.ProductId equals p.ProductId
                         where u.OrderDate == today
                         select new OrderDTO
                         {
                           OrderId = u.OrderId,
                           ProductName = p.ProductName,
                           Date = u.OrderDate,
                           Price = u.Price,
                         }).OrderByDescending(x => x.Date).ToList();
                   return totalOrder;
                }
            });
        }

And then in my button event:

private async void button1_Click(object sender, EventArgs e)
{
  transactionByDate = await GetTransactionByDate(today);
  dgvTransactions.DataSource = transactionByDate;
}

Error I'm getting is:

The entity or complex type 'OrderDTO' cannot be constructed in a LINQ to Entities query.

I really don't know how to return from my async GetTransactionByDate(today) function. I would appreciate if you could help me, I'am new for this kind of coding.

CodePudding user response：

I couldn't test this because it doesn't compile locally (obviously) but I think you could do it like this:

private async Task<List<OrderDTO>> GetTransactionByDate(DateTime today)
{
    return await Task.Factory.StartNew(() =>
    {
        using (Db db = new Db())
        {
            var totalOrder = (from u in db.Orders
                              join p in db.Product on u.ProductId equals p.ProductId
                              where u.OrderDate == today
                              select new
                              {
                                  OrderId = u.OrderId,
                                  ProductName = p.ProductName,
                                  Date = u.OrderDate,
                                  Price = u.Price
                              })
                              .ToList();

            var result = totalOrder.Select(ano =>
                           new OrderDTO
                           {
                               OrderId = ano.OrderId,
                               ProductName = ano.ProductName,
                               Date = ano.OrderDate,
                               Price = ano.Price,
                           })
                           .OrderByDescending(x => x.Date)
                           .ToList();

            return result;
        }
    }
}

CodePudding user response：

The key is to separate what happens on the database and what happens in memory.

Before you start, though, I'd make a nice helper function that takes out some of the boilerplate code.

private async Task<List<T>> GetDtosAsync<T>(Func<Db, List<T>> getDtos) =>
    await Task.Run(() =>
    {
        using (Db db = new Db())
        {
            return getDtos(db);
        }
    });

Note that I'm using Task.Run as Task.Factory.StartNew is outdated and no longer recommended.

Now you can write your method like this:

private async Task<List<OrderDTO>> GetTransactionByDate(DateTime today) =>
    await GetDtosAsync(db =>
    {
        var query =
            from u in db.Orders
            join p in db.Product on u.ProductId equals p.ProductId
            where u.OrderDate == today
            select new
            {
                u.OrderId,
                p.ProductName,
                u.OrderDate,
                u.Price,
            };
                    
        var totalOrder =
            query
                .ToList()
                .Select(x => new OrderDTO
                {
                    OrderId = x.OrderId,
                    ProductName = x.ProductName,
                    Date = x.OrderDate,
                    Price = x.Price,
                })
                .ToList();
        
        return totalOrder;
    });

It's the query.ToList() that takes the data from the database and brings it in to memory before you construct you final list.