本地序列不能在 LINQ to SQL 实现中使用

Question

这就是我要做的。我有来自数据库的表和 1 个成员列表。我从 asp.net 成员资格控件中获取成员，它是一个组合的成员资格和配置文件对象。我有一个函数将返回以下 Mems 对象的列表：

Public Property FirstName As String = ""
Public Property LastName As String = ""
Public Property Address1 As String = ""
Public Property Address2 As String = ""
Public Property City As String = ""
Public Property State As System.Int32 = "0"
Public Property Zip As String = ""
Public Property Title As String = ""
Public Property Phone As System.Decimal = "0"
Public Property Id As System.Int32 = "0"
Public Property UserType As System.Int32 = "0"
Public Property SSN As System.Int32 = "0"
Public Property Email As String = ""
Public Property UserName As String = ""
Public Property IsLockedOut As Boolean = False
Private Loaded As Boolean = False

然后我有 2 个工作和帐户表：

工作

JobId   numeric(18,0)
AccountId   numeric(18,0)
Name    varchar(50)
Frequency   int
Modifier    varchar(90)
Active  bit
sDate   date
eDate   date

帐户

AccountId   numeric(18,0)
CompanyId   numeric(18,0)
ContactId   varchar(256)
Type    int
Name    varchar(256)
Address1    varchar(50)
Address2    varchar(50)
City    varchar(50)
State   int
Zip varchar(5)
AuthCode    varchar(10)
Comments    text

我试图实现的输出是特定公司ID帐户的作业列表，其中包含来自mems的信息。我让它在其他地方为帐户工作，但是当我将工作混合在一起时，它给了我以下错误：

Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.

这是我目前对该功能的了解：

    <System.Web.Services.WebMethod(EnableSession:=True)> _
Public Shared Function JobSearch(jSon As String) As String
    Dim DamnSer As New JavaScriptSerializer
    Dim Search As SearchString = DamnSer.Deserialize(Of SearchString)(jSon)
    Dim myDB As New MyDbDataContext
    Dim pro As New Mems(HttpContext.Current.User.Identity.Name)

    Dim acc = From a In myDB.Accounts Where a.CompanyId = pro.Id Select a
    Dim accList = acc.Where(Function(r) r.AccountId).[Select](Function(r) r.AccountId.ToString()).ToArray()
    Dim q = From u In Util.GetMems Join a In acc On a.ContactId Equals u.UserName
            Where u.FirstName.ToLower.Contains(Search.FirstName.ToLower) And
            u.LastName.ToLower.Contains(Search.LastName.ToLower) And
            a.Name.ToLower.Contains(Search.Name.ToLower) And
            a.ContactId.ToLower.Contains(Search.Email.ToLower) And
            u.Phone.ToString.Contains(Search.Phone.ToLower) And
            a.AccountId.ToString.Contains(Search.AccountId.ToLower)
            Select New With {.FirstName = u.FirstName,
                                      .LastName = u.LastName,
                                      .Name = a.Name,
                                      .Email = u.Email,
                                      .Phone = u.Phone,
                                      .AccountId = a.AccountId,
                                      .City = a.City,
                                      .State = (From s In myDB.States Where s.StateId = a.State Select s.StateAbbr).Single
                            }
    'This is the only line i added between what I have working and this new section and it is where the error happens
    Dim qq = From j In myDB.Jobs Where accList.Contains(j.AccountId) Select New With {j.JobId, j.Name, (From z In q Where z.AccountId = j.AccountId).Single}
    Return DamnSer.Serialize(qq)
End Function

Answer 1

我决定走一条不同的路线

    <System.Web.Services.WebMethod(EnableSession:=True)> _
Public Shared Function JobSearch(jSon As String) As String
    Dim DamnSer As New JavaScriptSerializer
    Dim Search As SearchString = DamnSer.Deserialize(Of SearchString)(jSon)
    Dim myDB As New MyDbDataContext
    Dim pro As New Mems(HttpContext.Current.User.Identity.Name)
    Dim acc = From a In myDB.Accounts Where a.CompanyId = pro.Id Select a
    Dim q = From u In Util.GetMems Join a In acc On a.ContactId Equals u.UserName
            Where u.FirstName.ToLower.Contains(Search.FirstName.ToLower) And
            u.LastName.ToLower.Contains(Search.LastName.ToLower) And
            a.Name.ToLower.Contains(Search.Name.ToLower) And
            a.ContactId.ToLower.Contains(Search.Email.ToLower) And
            u.Phone.ToString.Contains(Search.Phone.ToLower) And
            a.AccountId.ToString.Contains(Search.AccountId.ToLower)
            Select New With {.FirstName = u.FirstName,
                                      .LastName = u.LastName,
                                      .Name = a.Name,
                                      .Email = u.Email,
                                      .Phone = u.Phone,
                                      .AccountId = a.AccountId,
                                      .City = a.City,
                                      .State = (From s In myDB.States Where s.StateId = a.State Select s.StateAbbr).Single,
                                      .zip = a.Zip,
                                      .Locations = (From l In myDB.Locations Where l.AccountId = a.AccountId Select New With {l.AccountId, l.Address1, l.Address2, l.City, l.Comments, l.LocationId, .State = (From s In myDB.States Where s.StateId = l.State Select s.StateAbbr).Single, l.Zip,
                                          .JobCount = (From j In myDB.Jobs Where j.LocationId = l.LocationId Select j).Count}
                                    )
                            }
    Return DamnSer.Serialize(q)
End Function