为什么要将子查询赋值给主查询之外的变量

Question

在下面的GetTransfers()方法中，我必须将GetAllocations()的结果赋给我的主查询之外的一个变量，否则查询将失败。为什么我要这么做呢？有没有更好的方法？当查询失败时，我会得到这个错误：

{ 'System.Linq.IQueryable`1XCBusinessLogic.Presentation.Allocation :System.NotSupportedException to Entities无法识别方法LINQ GetAllocations()‘方法，并且此方法无法转换为存储表达式。

下面的查询是有效的：

public IQueryable<Transfer> GetTransfers()
    {
        IQueryable<Allocation> wxyz = GetAllocations();

        IQueryable<Transfer> query =
            from transfer in Context.XC_TRANSFERS
            //let wxyz = GetAllocations()
            join trader in Context.MGRS on transfer.TRADER_ID equals trader.MGR_NO
            join ssm in Context.SSM_CORES on transfer.SSM_ID equals ssm.SSM_ID
            join desk in Context.XC_DESKS on transfer.DESK_ID equals desk.DESK_ID

            select new Transfer
            {
                // snip
                _AllocationList = wxyz.Where(x => x.TRANSFER_ID == transfer.TRANSFER_ID)
            };
        return query;

    }

此查询失败：

public IQueryable<Transfer> GetTransfers()
    {
        //IQueryable<Allocation> wxyz = GetAllocations();

        IQueryable<Transfer> query =
            from transfer in Context.XC_TRANSFERS
            let wxyz = GetAllocations()
            join trader in Context.MGRS on transfer.TRADER_ID equals trader.MGR_NO
            join ssm in Context.SSM_CORES on transfer.SSM_ID equals ssm.SSM_ID
            join desk in Context.XC_DESKS on transfer.DESK_ID equals desk.DESK_ID

            select new Transfer
            {
                // snip
                _AllocationList = wxyz.Where(x => x.TRANSFER_ID == transfer.TRANSFER_ID)
            };
        return query;

    }

此查询失败：

public IQueryable<Transfer> GetTransfers()
    {
        //IQueryable<Allocation> wxyz = GetAllocations();

        IQueryable<Transfer> query =
            from transfer in Context.XC_TRANSFERS
            //let wxyz = GetAllocations()
            join trader in Context.MGRS on transfer.TRADER_ID equals trader.MGR_NO
            join ssm in Context.SSM_CORES on transfer.SSM_ID equals ssm.SSM_ID
            join desk in Context.XC_DESKS on transfer.DESK_ID equals desk.DESK_ID

            select new Transfer
            {
                // snip
                _AllocationList = GetAllocations().Where(x => x.TRANSFER_ID == transfer.TRANSFER_ID)
            };
        return query;

    }

GetAllocations方法：

public IQueryable<Allocation> GetAllocations()
    {
        IQueryable<Allocation> query =
            from alloc in Context.XC_ALLOCATIONS
            join acm in Context.ACMS on alloc.ACCT_NO equals acm.ACCT_NO
            join b in Context.BUM_DETAILS.Where(x => x.FIRM_NO == 1 && x.CATEGORY_ID == 1937) on acm.ACCT_NO equals b.ACCT_NO into bumDetails
            from bumDetail in bumDetails.DefaultIfEmpty()
            where acm.FIRM_NO == 1
            select new Allocation
            {
                AccountName = acm.ACCT_NAME
                // snip

            };
        return query;
    }

Answer 1

您可以通过将join与您的方法一起使用，然后再使用into来解决此问题

IQueryable<Transfer> query =
            from transfer in Context.XC_TRANSFERS
            join allocation in GetAllocations() on transfer.TRANSFER_ID equals allocation.TRANSFER_ID into allocationList
            join trader in Context.MGRS on transfer.TRADER_ID equals trader.MGR_NO
            join ssm in Context.SSM_CORES on transfer.SSM_ID equals ssm.SSM_ID
            join desk in Context.XC_DESKS on transfer.DESK_ID equals desk.DESK_ID

            select new Transfer
            {
                // snip
                _AllocationList = allocationList
            };

Answer 2

Linq to Entities将查询from transfer in Context.XC_TRANSFERS ...中的所有内容转换为SQL。因此，该查询中唯一允许的表达式是那些可以很容易地转换为SQL的表达式。

Linq to Entities无法弄清楚像GetAllocations()这样的.NET方法是如何工作的。它应该怎么做呢？在一个方法中可能有任何形式的疯狂代码。它怎么能把它变成SQL呢？

在本例中，该方法实际上包含另一个Linq to Entities查询。也许您可以将一个查询复制粘贴到另一个查询的内部。但我不认为这会改善你的代码！

因此，只需保留您已有的工作解决方案。

Answer 3

我有一个非常类似的问题，Aducci的答案为我做了。这就是我想要做的：

query = from x in query
        where GetServicesQuery(db, options).Any(service => /*my criteria*/)
        select x;

按照Aducci的建议，解决了这个问题：

query = from x in query
        join service in GetServicesQuery(db, localOptions) on x.ID equals service.ID into services
        where services.Any(service => /*my criteria*/)
        select x;  

我之所以发布这个解决方案，是因为我的案例与上面的不同(需要where中的子查询，而不是select)。如果有人偶然发现了这个和我有相同问题的帖子，希望这能省去他们四处寻找的麻烦。

这个问题让我压力很大，因为GetServicesQuery有很多我不想重复的标准。