SQL cannot be converted to LINQ: Field Not Found

[isaac]

I am unable to build LINQ for below SQL, I get the error stating

SQL cannot be converted to LINQ: Field [tblAction.LeadID ON tblBusinessLead].[LeadID = tblAction.LeadID] not found in the current Data Context.

SELECT tblBusinessLead.InitiativeName, tblBusinessLead.Description, tblBusinessLead.PendingCode, tblBusinessLead.LeadID,
tblBusinessLead.CreativeNeeds, tblBusinessLead.IdeationNeeds, tblBusinessLead.NewBusNeeds, tblBusinessLead.ActionNeeded,
tblBusinessLead.Comments, tblBusinessLead.LossReasons, tblBusinessLead.OriginDate, tblBusinessLead.DateReceivedAssignment,
tblBusinessLead.DueDate, tblBusinessLead.EstStartDate, tblBusinessLead.EstEndDate, tblBusinessLead.ExeStartDate,
tblBusinessLead.ExeEndDate, tblBusinessLead.Probable80Total, tblBusinessLead.Possible50Total, tblBusinessLead.Emerging25Total,
tblBusinessLead.NoBudget0Total, tblBusinessLead.BizDevContactUserID, tblBusinessLead.BizDevContact2UserID, tblBusinessLead.SVPContactUserID,
tblBusinessLead.ClientMgmtContactUserID, tblBusinessLead.CMAdditionalContactUserID, tblBusinessLead.OfficeID, tblBusinessLead.ClientID,
tblBusinessLead.LeadTypeID, tblStatus.StatusDate, tblStatus.StatusNotes, tblStatusType.StatusName,
tblAction.NewBusDesc,tblAction.CreativeDesc,tblAction.IdeationDesc, tblAction.ActionDate, tblClient.ClientName, tblClient.ClientDesc,
tblLeadType.LeadName, tblLeadType.LeadDescription, tblUsers_1.LastName + ', ' + tblUsers_1.FirstName AS AccountLeadName,
tblUsers.LastName + ', ' + tblUsers.FirstName AS SalesLeadName, tblUsers_3.LastName + ', ' + tblUsers_3.FirstName AS AdditionalLeadName,
tblUsers_4.LastName + ', ' + tblUsers_4.FirstName AS SVPLeadName, tblUsers_5.LastName + ', ' + tblUsers_5.FirstName AS SalesLeadName2,
tblUpdateHistory.UpdateDate, tblUpdateHistory.UpdateAction, tblUpdateHistory.UpdateSection, tblUsers_2.LastName + ', ' + tblUsers_2.FirstName AS UpdateUser
FROM tblClient
INNER JOIN tblBusinessLead ON tblClient.ClientID = tblBusinessLead.ClientID
INNER JOIN tblLeadType ON tblBusinessLead.LeadTypeID = tblLeadType.LeadTypeID
LEFT JOIN tblUsers ON tblBusinessLead.BizDevContactUserID = tblUsers.UserID
LEFT JOIN tblUsers tblUsers_1 ON tblBusinessLead.ClientMgmtContactUserID = tblUsers_1.UserID
LEFT JOIN tblUsers tblUsers_5 ON tblBusinessLead.BizDevContact2UserID = tblUsers_5.UserID
Left JOIN tblUsers tblUsers_3 ON tblBusinessLead.CMAdditionalContactUserID = tblUsers_3.UserID
Left JOIN tblUsers tblUsers_4 ON tblBusinessLead.SVPContactUserID = tblUsers_4.UserID
INNER JOIN tblUpdateHistory ON tblBusinessLead.LeadID = tblUpdateHistory.LeadID
INNER JOIN (SELECT MAX(UpdateID) AS UpdateID, LeadID FROM tblUpdateHistory WHERE UpdateSection <> 'DOLLAREST' GROUP BY LeadID) MaxUpdateID ON tblUpdateHistory.UpdateID = MaxUpdateID.UpdateID AND tblUpdateHistory.LeadID = MaxUpdateID.LeadID
LEFT OUTER JOIN tblUsers tblUsers_2 ON tblUpdateHistory.UserID = tblUsers_2.UserID
LEFT OUTER JOIN (SELECT tblAction.LeadID, MAX(tblAction.ActionID) ActionID FROM tblAction, (SELECT MAX(ActionDate) AS ActionDate, LeadID FROM tblAction GROUP BY LeadID) MaxDate WHERE MaxDate.ActionDate = tblAction.ActionDate And MaxDate.LeadID = tblAction.LeadID GROUP BY tblAction.LeadID) MaxAction INNER JOIN tblAction ON MaxAction.ActionID = tblAction.ActionID AND MaxAction.LeadID = tblAction.LeadID ON tblBusinessLead.LeadID = tblAction.LeadID
LEFT OUTER JOIN tblStatusType INNER JOIN tblStatus ON tblStatusType.StatusTypeID = tblStatus.StatusTypeID
INNER JOIN (SELECT tblStatus.LeadID, MAX(tblStatus.StatusID) StatusID FROM tblStatus,
(SELECT MAX(tblStatus.StatusDate) AS StatusDate, tblStatus.LeadID FROM tblStatus
GROUP BY tblStatus.LEADID) MaxDate
WHERE MaxDate.StatusDate = tblStatus.StatusDate And MaxDate.LeadID = tblStatus.LeadID
GROUP BY tblStatus.LeadID) MaxStatus ON tblStatus.StatusID = MaxStatus.StatusID AND tblStatus.LeadID = MaxStatus.LeadID ON tblBusinessLead.LeadID = tblStatus.LeadID
WHERE tblBusinessLead.Deleted = 0 AND ( tblStatusType.StatusTypeID not IN (3, 8, 7) )
Order By ActionNeeded Desc, UpdateDate desc



There are no keywords in the sql that are unsupported. The SQL runs fine in the command window

[Mikhail Oumantsev]

Please send me your dbml and designer.* files. It will help me to identify the problem.

[Cetin Basoz]

Sorry to jump in. I am not a current user of Linqer but was checking it as a big fan Linq itself I saw your message.

I glanced at the error message and then SQL itself and IMHO your SQL is faulty here:


... INNER JOIN tblAction ON MaxAction.ActionID = tblAction.ActionID AND MaxAction.LeadID = tblAction.LeadID ON tblBusinessLead.LeadID = tblAction.LeadID  ...

[Mikhail Oumantsev]

It doesn't matter whether you current Linqer user or not. I would like to improve Linqer engine based on all users feedbacks. The SQL itself doesn't look to be complicated and supposed to be converted. Your dbml and designer files would help to debug.

[Cetin Basoz]

I think you misunderstood me. I was trying to say that the SQL has syntax error in it and thus that wouldn't even work with MS SQL. If it is an invalid SQL then I expected Linqer would not be able to convert it either, no?

[Mikhail Oumantsev]

Oh No!
Thanks.