Print Page | Close Window

PassthruEsqlQuery - how to

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2009
Forum Discription: For .NET 3.5
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=942
Printed Date: 27-Oct-2025 at 12:46am


Topic: PassthruEsqlQuery - how to
Posted By: pokaragat
Subject: PassthruEsqlQuery - how to
Date Posted: 19-Sep-2008 at 9:27am

Per documentation, this is how we should code pass thru query:
 
var query1 = new PassthruEsqlQuery(typeof(DATA_SOURCE_TYPE), paramEsql);
var res = manager.ExecuteQuery<DATA_SOURCE_TYPE>( query1 );
 
However, that has compiler error or even intellisense does not let you do that.
 
I modified the code as below to compile however it threw an exception:
 
var query1 = new PassthruEsqlQuery(typeof(DATA_SOURCE_TYPE), paramEsql);
var res = manager.ExecuteQuery( query1 );
 
The specified cast from a materialized 'System.Data.Objects.MaterializedDataRecord' type to the 'VCLData.DATA_SOURCE_TYPE' type is not valid."
 
Pleas help.


Replies:
Posted By: kimj
Date Posted: 19-Sep-2008 at 12:37pm
First, sorry about the documentation error with the ExecuteQuery<T> call.  This gives you a compiler error because a PassthruEsqlQuery is not an IEntityQuery<T>, which is what the method expects as its argument. 
 
As for the runtime exception, what does your paramEsql look like?  It needs to return objects of DATA_SOURCE_TYPE.

Posted By: pokaragat
Date Posted: 19-Sep-2008 at 2:02pm
Here's the whole code:
 

var param = new QueryParameter("DATA_SOURCE_TYPE_ID", 3);

string fields = "T.DATA_SOURCE_TYPE_ID, T.DATA_SOURE_TYPE_FROM_DATE, T.NAME, T.DESCRIPTION, T.USERNAME, T.THROUGH_DATE, T.DELETE_USERNAME";

var paramEsql = new ParameterizedEsql(string.Format(" SELECT {0} FROM DATA_SOURCE_TYPEs AS T WHERE T.DATA_SOURCE_TYPE_ID = @DATA_SOURCE_TYPE_ID", fields), param);

var query1 = new PassthruEsqlQuery(typeof(DATA_SOURCE_TYPE), paramEsql);

var res = manager.ExecuteQuery(query1);

----------

The sql "SELECT {0} FROM DATA_SOURCE_TYPEs AS T WHERE T.DATA_SOURCE_TYPE_ID = @DATA_SOURCE_TYPE_ID", has DATA_SOURCE_TYPEs as the collection definition of DATA_SOURCE_TYPE entity. All the fields defined in 'fields' string are valid fields of DATA_SOURCE_TYPE.

Posted By: kimj
Date Posted: 19-Sep-2008 at 2:37pm
Try something like:
 
"SELECT VALUE T FROM DATA_SOURCE_TYPEs AS T WHERE T.DATA_SOURCE_TYPE_ID = @DATA_SOURCE_TYPE_ID"
 
for the SQL string.  It's possible that because you're using a field list the result is really an anonymous type rather than an instance of DATA_SOURCE_TYPE.

Posted By: pokaragat
Date Posted: 19-Sep-2008 at 2:43pm
It worked. Thanks for the quick response.

Posted By: pokaragat
Date Posted: 19-Sep-2008 at 3:31pm

I expect a pass thru query to be "pass thru", which imply that what ever that database underneath the query you pass will be supported as long as it complies with the database. With the query below, it took me a while to build it to pass all the exception that would allow it to almost be executed. But at the end I got below:

SELECT VALUE T FROM DATA_ENTRYs AS T WHERE T.RUN_NUMBER = @RUN_NUMBER AND T.THROUGH_DATE IS NULL AND T.DEVICE_ALIAS IN ( SELECT Q.NAME FROM DEVICESs AS Q WHERE Q.NAME = \"AABB - Bill\"

Exception:

The element type 'Edm.String' and the CollectionType 'Transient.collection[Transient.rowtype[(NAME,Edm.String(Nullable=True,DefaultValue=,MaxLength=Max,Unicode=True,FixedLength=False))](Nullable=True,DefaultValue=)]' are not compatible. The IN expression only supports entity, primitive, and reference types. , near IN set expression, line 1, column 117
 
So, this is kinda pass thru query. I think what I need is a documentation on the limitations and what are the supported operators, etc. that DevForce EF can understand.

Posted By: kimj
Date Posted: 19-Sep-2008 at 4:43pm
You probably need to use something like "... T.DEVICE IN (SELECT VALUE Q FROM ...)".  Here's a link to a good Entity SQL reference from Microsoft:  http://msdn.microsoft.com/en-us/library/bb399560.aspx - http://msdn.microsoft.com/en-us/library/bb399560.aspx


Print Page | Close Window