Error using WHERE ... IN ... clause

[Trevor]

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.

[Zoran]

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

[Trevor]

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".

[Zoran]

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