时间戳

首页 > 解决方案 > 带有等待的多个连接的 C# Linq 查询

c# - 带有等待的多个连接的 C# Linq 查询

问题描述

无论我尝试在查询中的哪个位置添加等待，我都会收到智能错误。只需要插入一个等待。这是带有 EntityFramework 核心的 dotnet core 2.x

 public async Task<IEnumerable<BalanceItemResource>> GetBalanceItems(int fyId)
 {
 IEnumerable<BalanceItemResource> lQuery = (IEnumerable<BalanceItemResource>)
            from r1 in _context.Requests
            join u1 in _context.Users
            on r1.ApproverId equals u1.Id
            join p1 in _context.Purchases
            on r1.PurchaseId equals p1.Id
            join o1 in _context.OfficeSymbols
            on u1.Office equals o1.Name
            where r1.FYId == fyId
            select new { r1.Id, 
                p1.PurchaseDate, 
                officeId = o1.Id, 
                officeName = o1.Name, 
                o1.ParentId, o1.Level, 
                total = r1.SubTotal + r1.Shipping

            };
            return lQuery;

    }

标签： c#linqasync-await

解决方案

C# Linq 代码只能await执行实现查询并加载它的操作，例如ToListAsync和ToDictionaryAsync。这些方法在命名空间中System.Data.Entity而不是System.Linq.

public async Task<List<BalanceItemResource>> GetBalanceItems(int fyId)
{
    var query = // Ensure `query` is `IQueryable<T>` instead of using `IEnumerable<T>`. But this code has to use `var` because its type-argument is an anonymous-type.
        from r1 in _context.Requests
        join u1 in _context.Users
        on r1.ApproverId equals u1.Id
        join p1 in _context.Purchases
        on r1.PurchaseId equals p1.Id
        join o1 in _context.OfficeSymbols
        on u1.Office equals o1.Name
        where r1.FYId == fyId
        select new { r1.Id, 
            p1.PurchaseDate, 
            officeId = o1.Id, 
            officeName = o1.Name, 
            o1.ParentId,
            o1.Level, 
            total = r1.SubTotal + r1.Shipp
        };

    var list = await query.ToListAsync().ConfigureAwait(false); // <-- notice the `await` here. And always use `ConfigureAwait`.

    // Convert the anonymous-type values to `BalanceItemResource` values:
    return list
        .Select( r => new BalanceItemResource() {
            PurchaseDate = p1.PurchaseDate, 
            officeId = o1.Id, 
            officeName = o1.Name, 
            ParentId = o1.ParentId,
            Level = o1.Level, 
            total = r1.SubTotal + r1.Shipp
        } )
        .ToList();
}

也就是说，看起来您正在使用实体框架 - 假设您设置了外键导航属性，您可以将查询简化为：

public async Task<List<BalanceItemResource>> GetBalanceItems(int fyId)
{
    var query = _context.Requests
        .Include( r => r.ApproverUser )       // FK: Users ApproverId        // <-- These `.Include` lines aren't necessary when querying using a projection to an anonymous type, but I'm using them for illustrative purposes.
        .Include( r => r.Purchase )           // FK: Purchases PurchaseId
        .Include( r => r.AproverUser.Office ) // FK: OfficeSymbols Name
        .Select( r => new
        {
            r.Purchase.PurchaseDate,
            officeId   = r.AproverUser.Office.Id,
            officeName = r.AproverUser.Office.Name,
            r.AproverUser.Office.ParentId,
            r.AproverUser.Office.Level,
            total      = r.SubTotal + r.Shipp
        } );

    var list = await query.ToListAsync().ConfigureAwait(false);

    return list
        .Select( r => new BalanceItemResource() {
            PurchaseDate = p1.PurchaseDate, 
            officeId = o1.Id, 
            officeName = o1.Name, 
            ParentId = o1.ParentId,
            Level = o1.Level, 
            total = r1.SubTotal + r1.Shipp
        } )
        .ToList();
}

或单线：

public async Task<List<BalanceItemResource>> GetBalanceItems(int fyId)
{
    return
        (
            await _context.Requests
                .Select( r => new
                {
                    r.Purchase.PurchaseDate,
                    officeId   = r.AproverUser.Office.Id,
                    officeName = r.AproverUser.Office.Name,
                    r.AproverUser.Office.ParentId,
                    r.AproverUser.Office.Level,
                    total      = r.SubTotal + r.Shipp
                } )
                .ToListAsync()
                .ConfigureAwait(false)
        )
        .Select( r => new BalanceItemResource() {
            PurchaseDate = p1.PurchaseDate, 
            officeId = o1.Id, 
            officeName = o1.Name, 
            ParentId = o1.ParentId,
            Level = o1.Level, 
            total = r1.SubTotal + r1.Shipp
        } )
        .ToList();
}

在这种特殊情况下，我们不能省略await（并简单地Task直接返回），因为转换BalanceItemResource发生在内存中的 Linq 中，而不是 Line-to-Entities。

推荐阅读