DevForce Community Forum : PassthruEsqlQuery - how to

PassthruEsqlQuery - how to : You probably need to use something...

Fri, 19 Sep 2008 16:43:20 -700

Author: kimj
Subject: 942
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

PassthruEsqlQuery - how to : I expect a pass thru query to...

Fri, 19 Sep 2008 15:31:08 -700

Author: pokaragat
Subject: 942
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.

PassthruEsqlQuery - how to : It worked. Thanks for the quick...

Fri, 19 Sep 2008 14:43:14 -700

Author: pokaragat
Subject: 942
Posted: 19-Sep-2008 at 2:43pm

It worked. Thanks for the quick response.

PassthruEsqlQuery - how to : Try something like: "SELECT...

Fri, 19 Sep 2008 14:37:43 -700

Author: kimj
Subject: 942
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.

PassthruEsqlQuery - how to : Here's the whole code: var...

Fri, 19 Sep 2008 14:02:38 -700

Author: pokaragat
Subject: 942
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.

PassthruEsqlQuery - how to : First, sorry about the documentation...

Fri, 19 Sep 2008 12:37:40 -700

Author: kimj
Subject: 942
Posted: 19-Sep-2008 at 12:37pm

First, sorry about the documentation error with the ExecuteQuery call. This gives you a compiler error because a PassthruEsqlQuery is not an IEntityQuery, 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.

PassthruEsqlQuery - how to : Per documentation, this ishow...

Fri, 19 Sep 2008 09:27:16 -700

Author: pokaragat
Subject: 942
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.