嵌套LINQ方法抛出“不支持...”异常 [英] Nested LINQ Method throwing a `Not Supported...` Exception

问题描述

这是从这里的跟进 - > 多重排序在线-nested-method 。

This is a follow up from here -->multiple-sorting-on-linq-nested-method .

基本上，在 let memberName = ... 它正在抛出这个例外方法'System.String MemberName（Int32）'没有支持的SQL转换。，我不知道解决方案。

Basically, on let memberName = ... it is throwing this exception Method 'System.String MemberName(Int32)' has no supported translation to SQL. and I am not figuring out the solution.

另外， BLLCmo 和 BLLConnect 实际上使用 TWO 不同的数据库间。原来的应用程序（不是我的）使用4个分支数据库，所以我试图到期。

Also, BLLCmo and BLLConnect actually use TWO different DataBases. The original app(not mine) uses 4 Seperate DB's so I am trying to make due.

BLLCmo.cs

    public static DataTable GetAllMembers(Guid workerID)
    {
        var AllEnrollees = from enrollment in context.tblCMOEnrollments
                               where enrollment.CMOSocialWorkerID == workerID || enrollment.CMONurseID == workerID
                               join supportWorker in context.tblSupportWorkers on enrollment.EconomicSupportWorkerID equals supportWorker.SupportWorkerID into workerGroup
                               from worker in workerGroup.DefaultIfEmpty()
                               let memberName = BLLConnect.MemberName(enrollment.ClientID)
                               orderby enrollment.DisenrollmentDate ascending, memberName ascending 
                               select new
                                       {
                                           enrollment.ClientID,
                                           MemberName = memberName,
                                           NurseName = BLLAspnetdb.NurseName(enrollment.CMONurseID),
                                           SocialWorkerName =BLLAspnetdb.SocialWorkerName(enrollment.CMOSocialWorkerID),
                                           enrollment.DisenrollmentDate,
                                           enrollment.EnrollmentDate,
                                           ESFirstName = worker.FirstName,
                                           ESLastName = worker.LastName,
                                           ESPhone = worker.Phone
                                       };

        var dataTable = AllEnrollees.CopyLinqToDataTable();

        return dataTable;
    }

---

BLLConnect.cs

    public static String MemberName(Int32 personID)
    {
        var memberName = from person in context.tblPersons
                         where person.PersonID == personID
                         select person.FirstName + " " + person.LastName;

        return memberName.SingleOrDefault();
    }

推荐答案

问题是LINQ to SQL正在尝试将您的方法转换为SQL。由于 MemberName 无效SQL，所以放弃。相反，您需要从SQL中下载所需的数据，然后在单独的LINQ to Objects查询中调用方法（和排序）：

The problem is that LINQ to SQL is trying to translate your method into SQL. Since MemberName isn't valid SQL, it gives up. Instead, you'll need to pull down the data you need from SQL and then call your methods (and sort) in a separate LINQ to Objects query:

public static DataTable GetAllMembers(Guid workerID)
{
    var AllEnrollees =
        from enrollment in context.tblCMOEnrollments
        where enrollment.CMOSocialWorkerID == workerID || enrollment.CMONurseID == workerID
        join supportWorker in context.tblSupportWorkers on enrollment.EconomicSupportWorkerID equals supportWorker.SupportWorkerID into workerGroup
        from worker in workerGroup.DefaultIfEmpty()
        select new
        {
            enrollment.ClientID,
            enrollment.CMONurseID,
            enrollment.CMOSocialWorkerID,
            enrollment.DisenrollmentDate,
            enrollment.EnrollmentDate,
            ESFirstName = worker.FirstName,
            ESLastName = worker.LastName,
            ESPhone = worker.Phone
        };

    var result =
        from enrollee in AllEnrollees.AsEnumerable()
        let memberName = BLLConnect.MemberName(enrollee.ClientID)
        orderby enrollee.DisenrollmentDate ascending, memberName ascending 
        select new
        {
            enrollee.ClientID,
            MemberName = memberName,
            NurseName = BLLAspnetdb.NurseName(enrollee.CMONurseID),
            SocialWorkerName = BLLAspnetdb.SocialWorkerName(enrollee.CMOSocialWorkerID),
            enrollee.DisenrollmentDate,
            enrollee.EnrollmentDate,
            enrollee.ESFirstName,
            enrollee.ESLastName,
            enrollee.ESPhone
        };

    return result.CopyLinqToDataTable();
}

这篇关于嵌套LINQ方法抛出“不支持...”异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！