The member 'TestMode' is not supported

[kccarter]

public List GetEmployeesByGroup(string group)
{
var value = group;

        return (from sge in Context.SecurityGroupEmployees
               join sg in Context.SecurityGroups on sge.SecurityGroupId equals sg.SecurityGroupId
               join lu in Context.Lookups on sg.SecurityGroupTypeId equals lu.LookupId
               where lu.Value == value
               select sge.Employee).ToList();

    }

for some reason TestMode Property makes it into protected override Expression VisitMemberAccess(MemberExpression m) in TSqlFormatter.

at this point it has writtent the following SQL

SELECT [t0].[Test], [t0].[EmployeeId] AS EmployeeId1, [t0].[EmployeeTypeId], [t0].[IsActive], [t0].[IsLockedOut], [t0].[Password], [t0].[PersonId], [t0].[UserName]
FROM [dbo].[SecurityGroupEmployee] AS t1
INNER JOIN [dbo].[SecurityGroup] AS t2
ON ([t1].[SecurityGroupId] = [t2].[SecurityGroupId])
INNER JOIN [dbo].[Lookup] AS t3
ON ([t2].[SecurityGroupTypeId] = [t3].[LookupId])
LEFT OUTER JOIN (
SELECT [t4].[EmployeeId], [t4].[EmployeeTypeId], [t4].[IsActive], [t4].[IsLockedOut], [t4].[Password], [t4].[PersonId], 1 AS Test, [t4].[UserName]
FROM [dbo].[Employee] AS t4
) AS t0
ON (([t0].[EmployeeId] = [t1].[EmployeeId]) AND (

the real issue is that the column TestMode does not exist and it is defined as MemberTypes.Field. so the unsupported exception is thrown. I am really not sure what is happening because LinqToSql does not have this much difficulty.

[kccarter]

I followed this through to the end and helped it along to find out what it was wanting to do.

SELECT [t0].[Test], [t0].[EmployeeId] AS EmployeeId1, [t0].[EmployeeTypeId], [t0].[IsActive], [t0].[IsLockedOut], [t0].[Password], [t0].[PersonId], [t0].[UserName]
FROM [dbo].[SecurityGroupEmployee] AS t1
INNER JOIN [dbo].[SecurityGroup] AS t2
ON ([t1].[SecurityGroupId] = [t2].[SecurityGroupId])
INNER JOIN [dbo].[Lookup] AS t3
ON ([t2].[SecurityGroupTypeId] = [t3].[LookupId])
LEFT OUTER JOIN (
SELECT [t4].[EmployeeId], [t4].[EmployeeTypeId], [t4].[IsActive], [t4].[IsLockedOut], [t4].[Password], [t4].[PersonId], 1 AS Test, [t4].[UserName]
FROM [dbo].[Employee] AS t4
) AS t0
ON (([t0].[EmployeeId] = [t1].[EmployeeId]) AND (TestMode = TestMode))

is what it wanted to format. the problem is that TestMode is not a column in any one of the tables and in fact this will create this error "Invalid column name 'TestMode'." once the sql is executed. What I need help with is tracking down and finding out why a Field is being brought into the SqlTranslator and stop it. Rob, if you have any pointers on where to look and why, I would appreciate it.

[kccarter]

This issue was found to have it's root in the method GetMappedMembers(Type rowType) in the QueryMapping.cs file. the issue was that it was merging Fields with properties and was trying to join two tables on the Field 'TestMode' This was causing invalid SQL to be generated.

[adam7]

Looks like there's a fix available but it needs an associated test: http://github.com/kccarter/SubSonic-3.0/commit/012e492fa16a81fae805b364ba666c46b1e4dbf8

I'll investigate.

[adam7]

Fixed by the following commit:

http://github.com/subsonic/SubSonic-3.0/commit/7101d3d6176a3233060c4333c7ad536bebc5246e

Closing