Invalid SQL generated from LINQ query

[All Web Leads]

I have the following LINQ query:

//  Given:  IQueryable<Agent> Agents
//  Given:  IQueryable<AgentLead> AgentLeads
var agentsQuery = Agents.Where(a => AgentLeads.Any(al => al.Agentid == a.Id));
var count = agentsQuery.Count();

When I execute it, I get an InvalidOperationException with the message:

An exception occured during the execution of '
Extent<Il.Data.Agent>.Where(a => value(AgentMigrationApp.IlFeatureDefinitions+<>c__DisplayClass64).dc.AgentLeads.Any(al => (Convert(a.Id) = al.Agentid)))'. See InnerException for more details.

...which has a Telerik.OpenAccess.Exceptions.DataStoreException inner exception with the following message:

{"Count(*) query failed: Telerik.OpenAccess.RT.sql.SQLException: Incorrect syntax near '='.\r\n   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery()\r\n   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery()\r\n   at OpenAccessRuntime.Relational.RelationalStorageManager.executeCount(RelationalCompiledQuery cq, QueryParameters parameters)\nSELECT COUNT(1) FROM ( SELECT  (CASE WHEN COUNT(1) > 0 THEN 1 ELSE 0 END)  AS  EXPR1                  FROM [agent] a WHERE  (SELECT  (CASE WHEN COUNT(1) > 0 THEN 1 ELSE 0 END)                   FROM [agent_leads] b WHERE b.[id] = a.[id] = b.[agentid])   ) AS TMP_COUNT\n(set event logging to all to see parameter values) Telerik.OpenAccess.RT.sql.SQLException: Incorrect syntax near '='.\r\n   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery()\r\n   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery()\r\n   at OpenAccessRuntime.Relational.RelationalStorageManager.executeCount(RelationalCompiledQuery cq, QueryParameters parameters)"}

Here's a formatted version of the generated SQL above (for readability):

SELECT COUNT(1)
FROM (
  SELECT  (CASE WHEN COUNT(1) > 0 THEN 1 ELSE 0 END)  AS  EXPR1
  FROM [agent] a
  WHERE  (
    SELECT  (CASE WHEN COUNT(1) > 0 THEN 1 ELSE 0 END)
    FROM [agent_leads] b
    WHERE b.[id] = a.[id] = b.[agentid]
  )
) AS TMP_COUNT


Note that the innermost WHERE clause is incorrect.  I have absolutely no idea where the "b.[id] = " is coming from; there is nothing like that in my original LINQ query.  What's going on here?

[Thomas]

Hello Stephen,

this looks like something I have to look into... Can you try with a Count(al => al.AgentId == a.Id) > 0 instead of the .Any() ?

We are sorry for the inconvenience this might cause. 

Greetings,
Thomas
the Telerik team

[All Web Leads]

I tried what you recommended, and I'm happy to say that Yes, that works!  I suppose that using "Count({predicate}) > 0" is semantically equivalent to "Any({predicate})", so I'm happy with this result.  (I guess I really should have thought of this alternative.)  Thanks for the suggestion!

[All Web Leads]

Now that I've taken the example one more very small step further, I'm now running into what appears to be a different problem with an equally mysterious error message.  I tried to do something like this...

//  Given:  IQueryable<Agent> Agents

//  Given:  IQueryable<AgentLead> AgentLeads

var agentsQuery = Agents.Where(a => a.Active == 1 && AgentLeads.Count(al => al.Agentid == a.Id) > 0);
var ids = agentsQuery.Select(ag => ag.Id).ToList();

When the ToList method executes, the following exception is thrown...  (Wherever you see "[...]", I've removed sensitive information.)

InvalidOperationException: An exception occured during the execution of '
Extent<[...].Agent>.Where(a => ((Convert(a.Active) = Convert(1)) && (value([...]).[...].AgentLeads.Count(al => (al.Agentid = Convert(a.Id))) > 0))).Select(ag => ag.Id)'. See InnerException for more details.

   at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQuery(Type type, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single, Boolean checkOid)
   at Telerik.OpenAccess.Query.ExpressionExecution.PerformDatabaseQuery[T](Piece`1 piece, Object[] grpVals)
   at Telerik.OpenAccess.Query.Piece`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   [...]
Parameter expression has unexpected type.
Parameter name: ag
Actual value was [...].Agent.
   at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQueryImpl(Type type, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single, Boolean checkOid)
   at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQuery(Type type, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single, Boolean checkOid)


I've looked over this very carefully, and I absolutely cannot see anything wrong with what I've done.  The major difference between this and the other fluent LINQ queries I'm using is that this one uses the Count expression inside of it.  As for the details of the inner exception, I am expecting that the parameter ag will be of type Agent, and that appears to be exactly what the message is telling me, except that the message also indicates that the "Parameter expression has unexpected type".  Really?  If it's not supposed to be of type Agent, then what behavior should I expect here?  I've verified that agentsQuery is of type IQueryable<Agent>, so I fully expect that if I do a simple projection like agentsQuery.Select(ag => ag.Id)that ag will be of type Agent.  Am I missing something?  Perhaps the error message is a bit misleading?  I don't know.  I'm truly mystified!  Can anyone help?

[Thomas]

Hi All Web Leads,

I can reproduce the issue now and will let you know once the problem is fixed.

Best wishes,
Thomas
the Telerik team

[Thomas]

Hello All Web Leads,

 this problem has been fixed now and the changes are incorporated into the upcoming 2011Q2 release.

Greetings,
Thomas
the Telerik team