为什么我要分配子查询到我的主查询之外的变量

本文关键字：查询 变量 我要 分配 为什么 我的 | 更新日期: 2023-09-27 18:08:22

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

{系统。NotSupportedException: LINQ to Entities不能识别方法'System.Linq.IQueryable ' 1[XCBusinessLogic.Presentation]。分配]GetAllocations()方法，并且该方法不能转换为store表达式。

这个查询有效:

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;
    }

为什么我要分配子查询到我的主查询之外的变量

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

Linq to Entities不能弄清楚像GetAllocations()这样的。net方法如何工作。它应该怎么做呢?方法中可能有任何形式的疯狂代码。它怎么能把它变成SQL呢?

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

所以保持你现有的解决方案。

您可以通过使用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
            };

我也遇到过类似的问题，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;

我张贴这个解决方案，因为我的情况与上面不同(需要在哪里不选择子查询)。如果有人偶然发现这个线程与我有相同的问题，希望这将节省他们一些搜索周围。

这个让我很紧张，因为GetServicesQuery有很多条件，我不想重复。

相关文章：