Field not found in the current Data Context

[crackedpepper]

The following SQL works fine when I click the Run SQL button:

SELECT [Composite Membership], COUNT([Composite Membership]) AS MembershipCount
FROM vwEOQFirmAUM
WHERE ([As Of Date] = CONVERT(DATETIME, '2010-06-30 00:00:00', 102))
GROUP BY [Composite Membership]

However, when I try to convert the SQL to LINQ, I get the following error:

SQL cannot be converted to LINQ: Field [As Of Date] not found in the current Data Context.

The CONVERT statement is an issue as well. Using a "new System.DateTime" in place of the CONVERT as suggested in the FAQ:

SELECT [Composite Membership], COUNT([Composite Membership]) AS MembershipCount
FROM vwEOQFirmAUM
WHERE ([As Of Date] = new System.DateTime(2010,6,30))
GROUP BY [Composite Membership]

This change yields the following error:

Incorrect syntax near 'System'.

Can you point me in the right direction please? Thanks!

CP

[Mikhail Oumantsev]

Hello,
Your sql looks very simple and I cannot tell you what goes wrong without having your *.dbml and *.designer.cs files.
Please send these files to [e-mail] and I will debug your query.
About CONVERT function :
new System.DateTime(2010,6,30) cannot be part of SQL statement. But you need to put it manually in your LINQ statement.

Thanks,
Mikhail.

[crackedpepper]

Thank you - I have mailed you the files.

[Mikhail Oumantsev]

I checked your dbml file and realized, that it has slightly different format, which Linqer doesn't support. I am going to fix it in the next Linqer build. As a workaround try to generate dbml and designer.cs files using Linqer.