Using Editor .NET library for a WHERE inside a sub-select

htwyford

I want to include a WHERE condition inside a sub-select to fill the Options parameter of a table. I asked for the details of how to implement the SQL on Stack Overflow, where I said I would be able to translate a pure SQL answer to the Editor .NET library. Clearly, I overestimated my abilities....

Here is what I have so far:

The SQL recommended in the Stack Overflow answer is as follows:

SELECT DISTINCT [User].ID, [User].Name
FROM [User]
  JOIN User_Discipline
    ON [User].ID = User_Discipline.UserID
WHERE 
  User_Discipline.DisciplineID IN (
    SELECT DisciplineID
    FROM User_Discipline
    WHERE UserID = <<John Doe's userID>>
  )

My attempt in Editor's .NET tools is this:

var userOptions = new Options()
        .Table("User")
        .Value("ID")
        // I didn't cover this WHEREin the SO question, but it's just another related WHERE condition I need. I know this part works
        .Where(q => q.Where("User.Type", Models.User.UserType.Discipline, "!=")) 
        .Label(new string[] { "firstMidName", "lastName" });

if (!Roles.IsUserInRole("Admin"))
    userOptions.Where(
        q => {
            q.Join("UserDiscipline", "User.ID = UserDiscipline.User_ID");
            q.Where("UserDiscipline.Discipline_ID", "(SELECT Discipline_ID FROM UserDiscipline WHERE User_ID = [User].[ID])", "IN", false);
            q.Distinct(true);
        });

editor.Field(new Field("Schedule.UserID")
    .Options(userOptions)

The response returns the error
The multi-part identifier "User.ID" could not be bound.

The debug SQL:

SELECT 
-- the main table data is selected here. I'm concerned with the next part, which should select the Options() data
[User].[firstMidName] as 'User.firstMidName',
[User].[lastName] as 'User.lastName' 
FROM  [Schedule]  
JOIN [UserDiscipline] 
    ON [User].[ID] = [UserDiscipline].[User_ID]  
LEFT JOIN [User] 
    ON [User].[ID] = [Schedule].[UserID] 
    WHERE [Schedule].[JobID] = <<ID VALUE>>
        AND [Schedule].[week_ending] >= <<DATE VALUE>>
        AND [UserDiscipline].[Discipline_ID] 
    IN (SELECT Discipline_ID 
        FROM UserDiscipline 
           WHERE User_ID = [User].[ID])

Obviously, the debug SQL does not resemble the SO SQL. I would appreciate any help!

allan

Where is the week_ending and JobID coming from? Is that some part of the code that isn't shown?

Thanks,
Allan

htwyford

Yes, they're related to other SELECTs for the main table that I've omitted. I'm just trying to troubleshoot the SQL for the Options class. Those two fields can be safely ignored, I think.

I suppose that would make the SQL after the LEFT JOIN more like

LEFT JOIN [User]
    ON [User].[ID] = [Schedule].[UserID]
    WHERE [UserDiscipline].[Discipline_ID]
    IN (SELECT Discipline_ID
        FROM UserDiscipline
           WHERE User_ID = [User].[ID])

htwyford

I suppose that makes the SQL quite similar to the SO SQL...I feel a bit silly. I get the error nonetheless however. Any general troubleshooting techniques for errors like
The multi-part identifier "User.ID" could not be bound.?

tangerine

The multi-part identifier could not be bound

I googled this out of curiosity and it seems a pretty common problem - there appear to be some good answers out there.