Error using WHERE ... IN ... clause

4 posts, 0 answers
  1. Trevor
    Trevor avatar
    2 posts
    Member since:
    Sep 2011

    Posted 26 Sep 2011 Link to this post

    I'm using an OpenAccessDataSource:

    <telerik:OpenAccessDataSource ID="oadsPackages" runat="server"
    	ObjectContextProvider="NAMESPACE.VirtualOfficeContext, NAMESPACE"
    	TypeName="NAMESPACE.Package" 
    	EnableDelete="False"
    	EnableInsert="False" 
    	EnableUpdate="False" 
    	OrderBy="Description"
    	OnSelecting="oadsPackages_OnSelecting">
    </telerik:OpenAccessDataSource>

    In the "oadsPackages_OnSelecting" event, I have the following code:

    public void oadsPackages_OnSelecting(object sender, Telerik.OpenAccess.OpenAccessDataSourceSelectingEventArgs e)
    {
    	try
    	{
    		if(SelectedResourcePool != null)
    		{
    			string inClausePackageIds = "";
    			foreach(Guid AssetId in SelectedResourcePool.ResourcePoolPackages.Select(rpp => rpp.PackageId))
    								inClausePackageIds += "'" + AssetId + "',";
    			string whereClause = string.Format("AssetId IN ({0})", inClausePackageIds.TrimEnd(','));
    			e.DataSource.Where = whereClause;
    				}
    		else
    		{
    			e.DataSource.Where = string.Format("False");
    		}
    	}
    	catch(Exception ex)
    	{
    		Master.DisplayError(ex.Message);
    	}
    }

    Additionally, I have a RadGrid with it's "DataSourceID" property set to "oadsPackages".  When I call the "Rebind()" method on this RadGrid, the "OnSelecting" code of the "oadsPackages" is run but before the "RadGrid.Rebind()" method returns, an error is thrown which states:

    line 1:132: expecting "right parenthesis", found ','
    Original Query: DEFINE EXTENT xt FOR VirtualOffice.Model.Package; SELECT * FROM xt AS this WHERE AssetId IN ('a4f58729-b822-4f77-9ee6-a956123ab53a','877a1486-bfdc-46e9-a83f-a3a670835309','03fcb68f-8d1c-4705-8832-7ec89208ddb6') ORDER BY this.Description

    (emphasis added)

    I can't understand why I would get this error.  Perhaps the IN clause is formatted incorrectly?  When I run this exact WHERE...IN clause in MS SQL Management Studio, it runs just fine.  I imagine that OQL parses this different, somehow.  Any help would be much appreciated.

  2. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 29 Sep 2011 Link to this post

    Hello Trevor,

     Unfortunately, you can not use the IN statement in OQL to check against custom collections. You can only check against collections of persistent classes as shown in our OQL reference guide. At the moment we are working on a new Linq data source which as the name says, will be based on the LINQ query language and will allow such query expressions to be defined. We will include the new datasource in the following Q3 release of our product.

    Regards,
    Zoran
    the Telerik team

    Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>

  3. DevCraft banner
  4. Trevor
    Trevor avatar
    2 posts
    Member since:
    Sep 2011

    Posted 29 Sep 2011 Link to this post

    Thanks for your help; I look forward to the Q3 release.

    I am confused about one thing in your response:

    "you can not use the IN statement in OQL to check against custom collections. You can only check against collections of persistent classes"

    I'm not sure what you mean by "custom collections" and "persistent classes".  I would assume that "persistent classes" refers to database model classes, which is exactly what I'm querying.  That is, "AssetId" is a property on a persistent data model class and the primary key column in the database.  I be misunderstanding "persistent classes".
  5. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 03 Oct 2011 Link to this post

    Hi Trevor, 

    Yes you are understanding right, persistent classes is a notation we use for the classes that are generated by OpenAccess and their objects are persisted in the database.

    The part  that I have to clarify is about the collections in an IN statement:
    • The "AssetId" might be a property of a persistent class, but I was referring to the collection that you are checking against e.g. a collection of persistent classes would be order.OrderDetails - a collection that is represented in the database by a foreign key relationship and is populated by OpenAccess.
    •  On the other side a List<int>{1,2,3,4,5} is a custom collectino and the values are not managed by OpenAccess.
    • You can have a look in this help article where you can see some more examples in this direction.

    Regards,
    Zoran
    the Telerik team

    Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>

Back to Top