New Posts New Posts RSS Feed: PassthruEsqlQuery - how to
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

PassthruEsqlQuery - how to
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
pokaragat View Drop Down
Newbie
Newbie


Joined: 15-Sep-2008
Location: US
Posts: 13 		Post Options Post Options   Quote pokaragat Quote  Post ReplyReply Direct Link To This Post Topic: PassthruEsqlQuery - how to
    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.
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
pokaragat View Drop Down
Newbie
Newbie


Joined: 15-Sep-2008
Location: US
Posts: 13 		Post Options Post Options   Quote pokaragat Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
pokaragat View Drop Down
Newbie
Newbie


Joined: 15-Sep-2008
Location: US
Posts: 13 		Post Options Post Options   Quote pokaragat Quote  Post ReplyReply Direct Link To This Post Posted: 19-Sep-2008 at 2:43pm
It worked. Thanks for the quick response.
Back to Top
pokaragat View Drop Down
Newbie
Newbie


Joined: 15-Sep-2008
Location: US
Posts: 13 		Post Options Post Options   Quote pokaragat Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down