Hi all,

Since this is my first post here, let me first say that LLBLGen is a great and really helps us a lot. It helps us to understand that data structure better and define good business models on top of our database models!. Keep up the good work!

Now my question regarding ODATA and retrieving a hierarchy of entities via their navigation properties/collections via the $expand query option.

We are using LLBLGen v4 final released on May 15th, 2013. We created an odata service on top of the LinqMetadata class generated by our lbllgen project. (LLBLGenProODataServiceBase<LinqMetaData>) We are using Microsoft.Data.Services v5.3 from nuget. We have the following hierachical structure in the database: Projects => TestPrograms => Tests

If I post 'http /localhost/Service.svc/Project(2)?$expand=TestPrograms,TestPrograms/Test&$format=json' to the service. I get the following result (modified it to be readable in this post):

{ odata.metadata: "http://localhost/Service.svc/$metadata#Project/@Element" -TestPrograms: [5] 0: { Id: 6 ProjectsId: 2 ... more testprogram properties data ... } -1: { Id: 7 ProjectsId: 2 ... more testprogram properties data ... } -2: { Id: 8 ... more testprogram properties data ... } -3: { Id: 9 ProjectsId: 2 ... more testprogram properties data ... } -4: { Id: 10 ProjectsId: 2 ... more testprogram properties data ... } Id: 2 ProjectNumber: "prj number 2" ... more project properties data ...

}

I would expect that the result would also include the tests that are part of each testprogram, since I explicitly requested to include the Test for a TestProgram by the '$expand=TestPrograms,TestPrograms/Test' query. Now I know this didn't work with the previous version of LBLLGen, but I expected this to work with this version of LLBLGen.

Question is, am I doing something wrong, am I forgetting something or is this feature not available with lbllgen v4?

As a side note: If I explicitly implement a service operation to get an entitycollection with all the relevant information and return it as an IQueryable, the tests entities from the testprograms are also lost. So before the servicemethod returns its data still has al the underlying entities (all testprograms contain their tests), but in the (odata) response message this information is no longer available. So it seems that the LLBLGenProODataServiceBase is not correctly serializing our result.

koelvin wrote:

Hi all,

Since this is my first post here, let me first say that LLBLGen is a great and really helps us a lot. It helps us to understand that data structure better and define good business models on top of our database models!. Keep up the good work!

Now my question regarding ODATA and retrieving a hierarchy of entities via their navigation properties/collections via the $expand query option.

We are using LLBLGen v4 final released on May 15th, 2013. We created an odata service on top of the LinqMetadata class generated by our lbllgen project. (LLBLGenProODataServiceBase<LinqMetaData>) We are using Microsoft.Data.Services v5.3 from nuget. We have the following hierachical structure in the database: Projects => TestPrograms => Tests

If I post 'http /localhost/Service.svc/Project(2)?$expand=TestPrograms,TestPrograms/Test&$format=json' to the service. I get the following result (modified it to be readable in this post):

---

{ odata.metadata: "http://localhost/Service.svc/$metadata#Project/@Element" -TestPrograms: [5] 0: { Id: 6 ProjectsId: 2 ... more testprogram properties data ... } -1: { Id: 7 ProjectsId: 2 ... more testprogram properties data ... } -2: { Id: 8 ... more testprogram properties data ... } -3: { Id: 9 ProjectsId: 2 ... more testprogram properties data ... } -4: { Id: 10 ProjectsId: 2 ... more testprogram properties data ... } Id: 2 ProjectNumber: "prj number 2" ... more project properties data ...

}

I would expect that the result would also include the tests that are part of each testprogram, since I explicitly requested to include the Test for a TestProgram by the '$expand=TestPrograms,TestPrograms/Test' query. Now I know this didn't work with the previous version of LBLLGen, but I expected this to work with this version of LLBLGen.

$expand works, but I see it works only 1 level deep. If I do: localhost:3931/NorthwindService.svc/Customer('ALFKI')?$expand=Orders/OrderDetails

I get the Orders for the customer, but not the orderdetails for the orders. I'll look into why this is. The Odata support classes have an expand provider, which should handle expand directives.

Btw, the navigators at the same level should be separated with a ',' but if you want to go deeper into the hierarchy, you have to specify a '/', which fetches the parents also, at least that's what I understood from the syntax.

Question is, am I doing something wrong, am I forgetting something or is this feature not available with lbllgen v4?

As a side note: If I explicitly implement a service operation to get an entitycollection with all the relevant information and return it as an IQueryable, the tests entities from the testprograms are also lost.

I have no info about how things are related in your model so I can't give you an answer about this. Btw, please test everything without JSon, to be sure the service returns the right data at least and it's not due to the JSon.NET serializer or some other serializer related issue (we don't serialize any data)

So before the servicemethod returns its data still has al the underlying entities (all testprograms contain their tests), but in the (odata) response message this information is no longer available. So it seems that the LLBLGenProODataServiceBase is not correctly serializing our result.

Our odata service class doesn't serialize any data, it returns objects to the caller, which is the WCF Data service host process. It simply converts calls made to it into actions on the data, e.g. create a prefetch path or save data. It doesn't serialize anything, that's all done by the WCF Data services process itself and out of our hands. I'll see what the prefetch path is and what the data is that's passed into the expand provider and see if it does what it should or misses something. (which is easy to do, MS changes things a lot in OData code without documenting anything so making it a very frustrating and cumbersome experience )

Profiling the query

http://localhost:3931/NorthwindService.svc/Customer('ALFKI')?$expand=Orders/OrderDetails

it does fetch orders and order details in a prefetch path, they just don't appear in the results. (the orders do, the order details don't)

I have no idea why not. can be anything to do with OData or other things

I'll check at the linq provider level to see whether the results are what they have to be.

(edit) The 2nd level results are properly added to the entity graph. I.o.w.: the code does what it should, it fetches the customer, its orders and for each order the order details and returns that in 1 collection. I have no idea why OData thinks the 2nd level isn't interesting to return in the output.

I have no idea what's wrong. The data is properly fetched, the entity graph is properly setup, there's no error with the navigators, because if I do: localhost:3931/NorthwindService.svc/Order(10254)?$expand=OrderDetails/Products

it returns the orderdetails of the order, but not the products of the orderdetails, however they ARE fetched and merged into the graph.

Sadly I have no idea what to do, nor whom to ask for help or even what docs to read about this. I saw they just released v5.5 (a month after 5.4) but no word about an issue with multiple levels of data to be returned which failed...

I have no words to express the frustration I'm feeling with this at the moment. Sorry I can't help you at this point. I usually had a contact within MS to help me with these things, but they less and less seem to help 3rd party ORM developers to get the frameworks to work with what Microsoft produces

(edit) even if I set MaxExpandDepth to a high value, it makes no difference, the 2nd level is never returned, however it IS fetched and properly merged.

The IExpandProvider is deprecated in WCF Data Services, and although it still works somehow, it apparently makes WCF Data Services return just 1 level.

When there's no IExpandProvider, WCF Data Services will create hierarchical projections, which are dealt with efficiently in our linq provider. This isn't doing any prefetch paths, but the hierarchical projection handler of the linq provider performs the same actions as prefetch paths with respect to merging so that's ok.

To make the service not return any IExpandProvider, simply add to your service class:

protected override LLBLGenProODataServiceExpandProvider ExpandProvider
{
    get { return null; }
}

That should fix it. In the next build (next week) we'll simply not return the IExpandProvider implementing class anymore, so this line will then never be called by then. Could you add this line and see whether it fixes your problems regarding missing entities in the resultset?

Hi,

thnx, for the quick response.

I added the override to the service, and retried the query that should return multiple levels of the hierarchy. The problem is now that I get exceptions from the SQL Anywhere .Net data provider. A cast exception is returned, so I will need to fix/investigate that before I can give you a positive response on the fix you suggested.

I also retried to return the populated entitycollection from a service method and see if that works. I would expect the whole entityCollection (including prefetchpaths) to be returned, however again only the projectentity is returned and not the children and children children. The debugger shows all the data is present when i set a breakpoint on the 'return entityCollection' statement.

[WebGet]
public IEnumerable<ProjectEntity> GetHierarchy(int projectId)
{
    //build query
    //add relations and filer
    RelationPredicateBucket bucket = new RelationPredicateBucket();
    bucket.Relations.Add(ProjectEntity.Relations.TestProgramEntityUsingProjectsId);
    bucket.Relations.Add(TestProgramEntity.Relations.TestEntityUsingTestProgramsId);
    bucket.PredicateExpression.Add(ProjectFields.Id == projectId);

    //create prefetch paths
    IPrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.ProjectEntity);
    var path = ProjectEntity.PrefetchPathTestPrograms;
    prefetchPath.Add(path).SubPath.Add(TestProgramEntity.PrefetchPathTest);
    
    //retrieve data 
    var entityCollection = new EntityCollection<ProjectEntity>();
    DataAccessAdapter.FetchEntityCollection(entityCollection, bucket, prefetchPath);
    //return data
    return entityCollection;
}
--

I probably will get back on this tomorrow.

Cheers Kevin

koelvin wrote:

Hi,

thnx, for the quick response.

I added the override to the service, and retried the query that should return multiple levels of the hierarchy. The problem is now that I get exceptions from the SQL Anywhere .Net data provider. A cast exception is returned, so I will need to fix/investigate that before I can give you a positive response on the fix you suggested.

cast exception? Could you try in normal code whether a custom Select with a nested query works? (like: metaData.Customer.Select(c=>new { Customer = c, Orders = c.Orders});

I also retried to return the populated entitycollection from a service method and see if that works. I would expect the whole entityCollection (including prefetchpaths) to be returned, however again only the projectentity is returned and not the children and children children. The debugger shows all the data is present when i set a breakpoint on the 'return entityCollection' statement.

---

[WebGet]
public IEnumerable<ProjectEntity> GetHierarchy(int projectId)
{
    //build query
    //add relations and filer
    RelationPredicateBucket bucket = new RelationPredicateBucket();
    bucket.Relations.Add(ProjectEntity.Relations.TestProgramEntityUsingProjectsId);
    bucket.Relations.Add(TestProgramEntity.Relations.TestEntityUsingTestProgramsId);
    bucket.PredicateExpression.Add(ProjectFields.Id == projectId);

    //create prefetch paths
    IPrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.ProjectEntity);
    var path = ProjectEntity.PrefetchPathTestPrograms;
    prefetchPath.Add(path).SubPath.Add(TestProgramEntity.PrefetchPathTest);
    
    //retrieve data 
    var entityCollection = new EntityCollection<ProjectEntity>();
    DataAccessAdapter.FetchEntityCollection(entityCollection, bucket, prefetchPath);
    //return data
    return entityCollection;
}
--

I probably will get back on this tomorrow.

Cheers Kevin

This is very weird... IT should serialize all types.. Though, make sure that if Project is in an inheritance hierarchy or Testprogram is in an inheritance hierarchy, you set the OData protocol version to v3.

There are no inheritance hierarchies in the model. The protocol version is already set to version 3.

I tried out with a custom select with nested query and the result are populated without problem. Attached a screenshot of my dev enviroment while in a debug session, as you can see no problem populating the data structure created by the statement:

LinqMetaData metaData = new LinqMetaData(DataAccessAdapter);
var result = metaData.Project.Select(c => new { Project = c, TestPrograms = c.TestPrograms });

So the exception only occurs when querying the data via the expand statement.

Then we need a repro with stacktrace and exception info and what SQL was executed (enable tracing for that, see 'troubleshooting and debugging' in the manual), as we can't repro it with normal code here. The workaround I gave you executes the same linq query as you tried in the situation of your screenshot, so that works, which means I have no idea what exception might occur nor where, nor why.

Please keep the repro as small as possible. You use iAnywhere so if you can create a small repro on a small db file it would be great.

Btw, the service method you have returns IEnumerable<T>, please convert that to IQueryable<T> and see whether that changes things. IMHO it should return IQueryable, not IEnumerable.

Hi,

I already tried IQueryable<T> as the return type of the method and it does not change anything, result is the same.

I will create a repro for you and post it here. I will have time on thursday to create the repro, so will get back to you then.

Cheers.

Ok. It might also help already if you simply post the exception name, the exception message and the stacktrace, and what SQL is executed (tracing). We need this info anyway, as I'm not going to try a repro if I don't know what I'm looking for as it would mean looking for the famous 'speld in de hooiberg'

Btw, the method marked with WebGet, as the data is OK, there's nothing we can do there, as the caller is the WCF Data Service, and that code serializes the graph, using their own code, not ours.

But I think I know what happens with the service method: you have to call the method in the URL and add expands to it there, not inside the method. This requires IQueryable<T>, not IEnumerable<T>. I'll try to see if I can repro that here.

The person responsible for the LLBLGen project and thus the dataaccess layer is not available until thursday. I know we are using stored procedures for all that CUD operations on the data in the database, because we have to do some administration on these actions in the database. The data is read from views instead of directlye on tables.

Here's the data you requested. This exception is occuring when the ExpandProvider in the service is set to NULL. The odata request we send is:

 'http://wrk0874/Marin.TDMS.Services/TDMSService.svc/Project(2)?$expand=TestPrograms'

So this request is only 1 level deep.

I attached a trace file with the trace switches on llblgen enabled to this post.

Exception:

<m:error xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
<m:code />
<m:message xml:lang="en-US">An error occurred while processing this request.</m:message>
 <m:innererror>
<m:message>An exception was caught during the execution of a retrieval query: Cannot convert 'TestPrograms' to a integer. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.</m:message>
<m:type>SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException</m:type>
<m:stacktrace> at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in ..\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 155 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in ..\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1574 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary`2 typeConvertersToRun) in ..\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1735 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters) in ..\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1697 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteHierarchicalValueListProjection(QueryExpression toExecute, IRelationPredicateBucket additionalFilter, ITemplateGroupSpecificCreator frameworkElementCreator) in ..\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\LLBLGenProProvider2.cs:line 242 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute) in ..\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\LLBLGenProProvider2.cs:line 164 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) in ..\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\LLBLGenProProviderBase.cs:line 273 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) in ..\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\LLBLGenProProviderBase.cs:line 621 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() in ..\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\LLBLGenProQuery.cs:line 162 at System.Data.Services.Providers.BasicExpandProvider.ExpandedQueryable`1.GetEnumerator() at System.Data.Services.WebUtil.GetRequestEnumerator(IEnumerable enumerable) at System.Data.Services.QueryResultInfo.MoveNext() at System.Data.Services.Providers.DataServiceExecutionProviderWrapper.GetSingleResultFromRequest(SegmentInfo segmentInfo) at System.Data.Services.DataService`1.CompareETagAndWriteResponse(RequestDescription description, IDataService dataService, IODataResponseMessage responseMessage) at System.Data.Services.DataService`1.SerializeResponseBody(RequestDescription description, IDataService dataService, IODataResponseMessage responseMessage) at System.Data.Services.DataService`1.HandleRequest()</m:stacktrace>
 <m:internalexception>
<m:message>Cannot convert 'TestPrograms' to a integer</m:message>
<m:type>iAnywhere.Data.SQLAnywhere.SAException</m:type>
<m:stacktrace> at iAnywhere.Data.SQLAnywhere.SACommand._ExecuteReader(CommandBehavior commandBehavior, Boolean isExecuteScalar, Boolean isBeginExecuteReader) at iAnywhere.Data.SQLAnywhere.SACommand.ExecuteDbDataReader(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in ..\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 130</m:stacktrace>
 </m:internalexception>
 </m:innererror>
 </m:error>

Query: The query as being executed on the database (direct tracing on sqlanywhere):

select "LPLA_1"."id" as "Id","LPLA_1"."project_number" as "ProjectNumber","LPLA_1"."name" as "Name","LPLA_1"."purpose" as "Purpose","LPLA_1"."project_status" as "ProjectStatus","LPLA_1"."date_created" as "DateCreated","LPLA_1"."date_modified" as "DateModified",:? as "LPFA_5",:? as "LPFA_6",1 as "LPFA_7" from "TDMS_INT"."vw_projects" as "LPLA_1" where(((("LPLA_1"."id" = :?))))

Parameters passed are: hostvariable 0 - 'TestPrograms' hostvariable 1 - '' hostvariable 2 - 2

If I execute this query with substituted parameters directly on the database a result is returned and no exceptions are generated.

Hope this info helps. Cheers

Could you switch the tracer for linq to level 3 (now it's 4), and make the iAnywhere DQE tracer level 4 and attach that trace please? Now the parameters are not in the trace of the query (I assume you specified level 3, not 4 with the DQE trace)

Having the method as an IQueryable<T> works properly too. See below.

service method:

[WebGet]
public IQueryable<ProductEntity> GetProductByProductName(string productName)
{
    IQueryable<ProductEntity> q = this.CurrentDataSource.Product;
    if(!string.IsNullOrEmpty(productName))
    {
        q = q.Where(p => p.ProductName == productName);
    }
    return q;
}

url:

http://localhost:3931/NorthwindService.svc/GetProductByProductName?productName='Chang'&$expand=Category

result:

<?xml version="1.0" encoding="utf-8"?>
<feed xml:base="http://localhost:3931/NorthwindService.svc/" xmlns="http://www.w3.org/2005/Atom" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
    <id>http://localhost:3931/NorthwindService.svc/GetProductByProductName</id>
    <title type="text">GetProductByProductName</title>
    <updated>2013-06-04T10:31:29Z</updated>
    <link rel="self" title="GetProductByProductName" href="GetProductByProductName" />
    <entry>
        <id>http://localhost:3931/NorthwindService.svc/Product(2)</id>
        <category term="ServiceTester.ProductEntity" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
        <link rel="edit" title="ProductEntity" href="Product(2)" />
        <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/OrderDetails" type="application/atom+xml;type=feed" title="OrderDetails" href="Product(2)/OrderDetails" />
        <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Orders" type="application/atom+xml;type=feed" title="Orders" href="Product(2)/Orders" />
        <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Category" type="application/atom+xml;type=entry" title="Category" href="Product(2)/Category">
            <m:inline>
                <entry>
                    <id>http://localhost:3931/NorthwindService.svc/Category(1)</id>
                    <category term="ServiceTester.CategoryEntity" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
                    <link rel="edit" title="CategoryEntity" href="Category(1)" />
                    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Products" type="application/atom+xml;type=feed" title="Products" href="Category(1)/Products" />
                    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Suppliers" type="application/atom+xml;type=feed" title="Suppliers" href="Category(1)/Suppliers" />
                    <title />
                    <updated>2013-06-04T10:31:29Z</updated>
                    <author>
                        <name />
                    </author>
                    <content type="application/xml">
                        <m:properties>
                            <d:CategoryName>Beverages</d:CategoryName>
                            <d:Description>Soft drinks, coffees, teas, beers, and ales</d:Description>
                            <d:Id m:type="Edm.Int32">1</d:Id>
                            <d:Picture m:type="Edm.Binary">FRwvAAIAAAANAA4AFAAhAP////9CaXRtYXAgSW1hZ2UAUGFpbnQuUGlj
... (omitted the rest!)
QX+</d:Picture>
                        </m:properties>
                    </content>
                </entry>
            </m:inline>
        </link>
        <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Suppliers" type="application/atom+xml;type=entry" title="Suppliers" href="Product(2)/Suppliers" />
        <title />
        <updated>2013-06-04T10:31:29Z</updated>
        <author>
            <name />
        </author>
        <content type="application/xml">
            <m:properties>
                <d:CategoryId m:type="Edm.Int32">1</d:CategoryId>
                <d:Discontinued m:type="Edm.Boolean">false</d:Discontinued>
                <d:ProductId m:type="Edm.Int32">2</d:ProductId>
                <d:ProductName>Chang</d:ProductName>
                <d:QuantityPerUnit>24 - 12 oz bottles</d:QuantityPerUnit>
                <d:ReorderLevel m:type="Edm.Int16">25</d:ReorderLevel>
                <d:SupplierId m:type="Edm.Int32">1</d:SupplierId>
                <d:UnitPrice m:type="Edm.Decimal">19.0000</d:UnitPrice>
                <d:UnitsInStock m:type="Edm.Int16">17</d:UnitsInStock>
                <d:UnitsOnOrder m:type="Edm.Int16">41</d:UnitsOnOrder>
            </m:properties>
        </content>
    </entry>
</feed>

The query crash is odd, don't know why iAnywhere's ADO.NET provider tries to convert the string to an integer... But the trace will perhaps show what's wrong.

I only get this information if I set the switch to: <add name="SybaseAsaDQE" value="4" />

w3wp.exe Information: 0 : service endpoints 0
w3wp.exe Information: 0 : service endpoints Marin.TDMS.Services.TDMSService
w3wp.exe Information: 0 : service endpoints TDMSService
w3wp.exe Information: 0 : service endpoints http://services.marin.com/tdms/services
w3wp.exe Information: 0 : User 'kovermars' used as context for request 
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[Marin.TDMS.DB.EntityClasses.ProjectEntity]).Where(element => (element.Id == 2)).Select(p => new ExpandedWrapper`2() {ExpandedElement = p, Description = "TestPrograms", ReferenceDescription = "", ProjectedProperty0 = p.TestPrograms.OrderBy(p => p.Id).Take(1000)})
Method Enter: DataAccessAdapterBase.OpenConnection
: New connection created.
: Connection physically opened.
Method Exit: DataAccessAdapterBase.OpenConnection
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPLA_1].[id] AS [Id], [LPLA_1].[project_number] AS [ProjectNumber], [LPLA_1].[name] AS [Name], [LPLA_1].[purpose] AS [Purpose], [LPLA_1].[project_status] AS [ProjectStatus], [LPLA_1].[date_created] AS [DateCreated], [LPLA_1].[date_modified] AS [DateModified], ? AS [LPFA_5], ? AS [LPFA_6], 1 AS [LPFA_7]  FROM [TDMS_INT].[vw_projects]  [LPLA_1] WHERE ( ( ( ( [LPLA_1].[id] = ?))))
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.OpenConnection
Method Enter: DataAccessAdapterBase.CloseConnection
Method Exit: DataAccessAdapterBase.CloseConnection
w3wp.exe Error: 0 : Error handling request 'An exception was caught during the execution of a retrieval query: Cannot convert 'TestPrograms' to a integer. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.'
w3wp.exe Error: 0 : Error handling request 'Cannot convert 'TestPrograms' to a integer'

We are using sybase anywhere version 16.0.0.1342, might this be a problem?

Another trace, this time it includes detailed traces from the sqlanywhere data provider as well (I needed to upgrade to version 16.0.0.1535 to fix an issue with tracing on the dataprovider).

(removed the trace so the thread can be back to the main forums, as a blogpost points to this thread) -- Otis

(moved to helpdesk, as your connection string is in the last trace).

The parameters aren't listed in the query trace, which is very odd. Anyway, we'll setup a wcf data service on our iAnywhere testDB to see whether we can reproduce this. Odd error, as if the column value is expected to be integer (as the error is given by the ADO.NET provider, not our code)

I can reproduce the crash. I'm looking into it now.

Problem seems to be that a constant is presented as a parameter in the projection which makes the DB keel over. The parameter is properly setup, it has the right type and everything, but it appears that the column type the value has to be returned in isn't set as the type of the parameter.

My test query:

SELECT [LPLA_1].[CreatedDate],
       [LPLA_1].[CustomerId],
       [LPLA_1].[CustomerName],
       [LPLA_1].[ModifiedDate],
       ? AS [LPFA_5],
       ? AS [LPFA_6],
       1 AS [LPFA_7]
FROM   [dba].[Customer] [LPLA_1]
WHERE  (((([LPLA_1].[CustomerId] = ?)))) 

3 parameters, like you: @p2, string, value: "Orders" @p4, string, value: "" @p5, int, value: 1

As iAnywhere uses anonymized parameters, they're replaced with '?' in the SQL query. The first parameter, @p2, is placed as the value of [LPFA_5], so the resultcolumn should get the same type as the parameter, as the value is a string. However, it apparently defaults to 'int', and thus creates a problem.

I'm not sure whether Sybase will fix this, if they even can, because it might be the parameters are not known at that moment. We can try to fix this though, in a couple of ways:

1) emit the constant into the query, which isn't ideal, even though the constant is a constant in a linq query. or 2) try to add a cast and see if that helps. or 3) omit the constant in the DB query as it's in the projection lambda already. Not sure whether this is doable without a lot of work, so not my preferred option either.

I'll first try 2)

Hmm, directly executing a query with parameters does work:

BEGIN
DECLARE p1 varchar(50);
SET p1='Orders';
SELECT [LPLA_1].[CreatedDate],
       [LPLA_1].[CustomerId],
       [LPLA_1].[CustomerName],
       [LPLA_1].[ModifiedDate],
       p1 AS [LPFA_5],
       '' AS [LPFA_6],
       1 AS [LPFA_7]
FROM   [dba].[Customer] [LPLA_1]
WHERE  (((([LPLA_1].[CustomerId] = 1))));
END

so it is related to the ADO.NET provider...

(edit) option 2) is also cumbersome, as the constant is placed in the projection to the DB as a function call with 1 parameter. Will see if option 3) is feasible..

Thnx for taking the time, great support!

I can ofcourse work around the issue by removing the override for the expand provider on the service. Then do an explicit LoadProperty() on the property containing the child entities of an entity in the client code. This will do an extra request to the server, but thats currently not really a big issue.

So just so you know there is no hurry in fixing this for us.

Well, I hate having a bug in the code, so before someone else stumbles upon this issue, I'd rather have it fixed

And I have actually, I just now have to run the linq tests to see if this fix doesn't make something else break, but other than that, it's fixed and I'll upload a new ormsupportclasses dll for you shortly.

Of course, bugs are evil and must be dealt with swiftly :-)

Oh thats great news, that means you fixed it with option 3 or was there an option 4? :-) I am 'aan het duimen' that your fix doesn't break anything else.

Option 3), the constants are no longer send to the DB as parameters in the projection, but are left inside the lambda. We compile the lambda and execute it for each row, so the constants don't need to be inside the DB anyway, as they're used to set properties on the resulting type.

It's fixed, everything works, so I hope this solves the problems See attached dll.

Excellent!!

everything is fixed now.

The data is returned, no exception occurs, and also the hierarchy of entities can be retrieved via the $expand query option.

I assume this will be included in the next build?

Great work thnx for the effort.

Cheers.