Invalid SQL generated from LINQ query

6 posts, 0 answers
  1. All Web Leads
    All Web Leads avatar
    4 posts
    Member since:
    Oct 2008

    Posted 23 May 2011 Link to this post

    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 ENDAS  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?

  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 26 May 2011 Link to this post

    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
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  3. DevCraft banner
  4. All Web Leads
    All Web Leads avatar
    4 posts
    Member since:
    Oct 2008

    Posted 27 May 2011 Link to this post

    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!

  5. All Web Leads
    All Web Leads avatar
    4 posts
    Member since:
    Oct 2008

    Posted 28 May 2011 Link to this post

    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?
  6. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 03 Jun 2011 Link to this post

    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
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  7. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 08 Jul 2011 Link to this post

    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

    Register for the Q2 2011 What's New Webinar Week. Mark your calendar for the week starting July 18th and book your seat for a walk through of all the exciting stuff we will ship with the new release!

Back to Top
DevCraft banner