Hi,

We use version 4.1 of LLBLGen, SelfServicing adapter on a 2012 SQL Server database for an OData service project.

Querying the data works as expected. But filtering the text columns as contains throws an SQL exception: 'An exception was caught during the execution of a retrieval query: An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.'

Details: 1. I attached a sample solution 2. Please run the sql scripts in Scripts folder to create the ODataTest database, the User table and insert a couple of records in that table 3. After opening the solution in VS >= 2012, please use nuget to restore missing packages. Then build the solution and stat debugging the DataService project. 4. Run 'http /localhost:63938/odata/user?$filter=contains(LastName,%27Doe%27)' and you should see the problem. 5. Debugging this with SQL Profiler reveals a malformed SQL dynamic script that is executed when resolving the contains filter. 6. We have tried to change OData version to 3 and use the version 5.1 of LLBLGen but with no success.

Let us know if you are aware of this issue or if there is anything we can do to about it.

Thanks, Alin

At least first try the latest 4.1 runtime build first. (you didn't specify which version you are using)

Hi,

As I mentioned in the original post, we are using version 4.1 runtime and the sample project I attached uses runtime 4.1.

We have tried on out end both 4.1 and a trial version of 5.1 with no success. We still get that error that comes from SQL Server as the dynamic script is malformed.

We welcome any suggestion. Thanks!

For OData you really have to use the OData support classes we ship with the runtime.

http://www.llblgen.com/Documentation/4.1/LLBLGen%20Pro%20RTF/hh_goto.htm#Using%20the%20generated%20code/gencode_wcfdataservices.htm#tutorial

The linq query issued is:

value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource`1[ODataTest.LLBLGen.EntityClasses.UserEntity])
    .Select(user => new UserData() {
        UserId = user.UserId, 
        FirstName = user.FirstName, 
        LastName = user.LastName})
    .Where($it => (IIF(
                (($it.LastName == null) Or 
                 (value(System.Web.OData.Query.Expressions.LinqParameterContainer+TypedLinqParameterContainer`1[ System.String]).TypedProperty == null))
                 , null, 
                 Convert($it.LastName.Contains(value(System.Web.OData.Query.Expressions.LinqParameterContainer+ TypedLinqParameterContainer`1[System.String]).TypedProperty))
        ) == True))

The SQL generated is:

SELECT  [LPA_L1].[UserId], [LPA_L1].[FirstName], [LPA_L1].[LastName] 
FROM (
    SELECT [LPLA_1].[UserID] AS [UserId], [LPLA_1].[FirstName], [LPLA_1].[LastName] 
    FROM [ODataTest].[dbo].[User]  [LPLA_1]  ) [LPA_L1] 
    WHERE ( ( ( (CASE WHEN CASE WHEN [LPFA_1] THEN 1 ELSE 0 END=1 THEN @p4 ELSE CASE WHEN ( [LPA_L1].[LastName] LIKE @p5) THEN 1 ELSE 0 END END)=1
)))

Which is caused by the in-memory mess OData created with the IIF statement. I think this is done because it doesn't know anything about the types and reflects over them, as there's no OData service helper available to help it with the actual entity metadata (which is what the OData support classes are for. )

So this won't work unless you use the OData support classes.

Hi,

Thank you for getting back on this so quickly! And thanks for the WCF article you shared.

Can you please help me understand the code changes I need to do with the OData service we have?

Can you point out an article about this? I found nothing on the web.

Thanks!

alinzen wrote:

Hi,

Thank you for getting back on this so quickly! And thanks for the WCF article you shared.

Can you please help me understand the code changes I need to do with the OData service we have?

Can you point out an article about this? I found nothing on the web.

Thanks!

The link I posted above is of our own documentation which contains an example of the odata service you need to create, complete with tutorial. I'd say follow that tutorial and you'll end up with the odata service you need

Thank you for you patience.

The tutorial is about creating WCF services and I am trying to create an ASP.NET Web API OData service (see this https://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/odata-v4/create-an-odata-v4-endpoint). I need to create an ODataController that returns something IQueryable.

I was wondering if here is a similar article for OData services using ASP.NET Web API.

WCF Data Services == Odata

They renamed it to OData services, and then merged it into ASP.NET. There are 2 main versions of OData: v1-v3 (the ones we support) and v4+. MS broke everything with v4+'s classes (OData services classes v6.0+) and as it's now merged into ASP.NET we support OData v1-v3 only. This will give you a service which exposes an IQueryable using the OData v3 protocol.

If you need OData v4 protocol, we can't help you as we don't support that, however in general v3 of the protocol is what one needs anyway, so it's not a big problem. If you really want to use v4 of the OData protocol (but again, there's no need to do that unless your clients can only work with v4) you have to compile the OData support classes source code for the OData services classes v6+ and fix the breaking changes MS made. As this is a black hole of undocumented Microsoft mess, I wouldn't lose sleep over that.

The tutorial you refer to is a bit misleading: MS cheats as they silently have implemented Entity Framework support into OData services so it looks like it's automatic but it's not: the ORM has to provide meta-data to the OData services to make things work (Hence our OData support classes ).

So just try the tutorial, it will give you a service you can query through OData and which will give you xml or json (see our docs how to switch that)

Thanks for the feedback.

While I do understand the article and that there is a way to get my OData service through WCF, we already have in production an entire OData (with ASP.NET API service) with 20 endpoints that has the filtering problem I originally presented.

Changing everything at this point to WCF approach is not something I fancy. We could at minimum change OData version from 4 to 3 since the code changes are rather minimum.

Could you please help me with the sample project I attached to my original post and let me know how could I change it to fix the filtering issue?

Thank you, Alin

The main issue is that Microsoft moves the core element around (the core system which provides the EDM to the OData system) like it's some kind of small thing but it's not: creating an EDM from mapping/metadata isn't simple. Looking at this page: https://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/odata-v3/creating-an-odata-endpoint

I see they changed everything as they require an ODataModelBuilder class, to produce the EDM in this particular framework (which is already outdated, as asp.net core again changes everything).

Your example simply passes a Poco with no relationship to the entity to the model builder and of course that doesn't results in proper queries: there's no metadata available at all.

Your route leads to a method 'Get' on a controller which performs a query, but OData is about exposing a model through IQueryable methods, which you can append on with service methods which perform an action, but performing the query you specified in your example, is not how it works: make it a service method (but I have no freaking clue how to do it in the ASP.NET frameworks of today, which might be outdated tomorrow, and it likely already is as the documentation linked above is from 2014) and call that or simply traverse the model exposed by OData, no need for a method like you made (as that looks like a mix of WebAPI and OData)

Sad thing is, the odata support classes model builder we wrote (I think it was the 5th one we had to create for Microsoft's framework-of-the-day) isn't compatible with this one, so can't produce the EDM you want.

So the OData setup you want isn't supported, I'm afraid. The only out-of-the-box support MS delivers is with Entity Framework, as they ship the model builder for that with asp.net.

We analyzed what we should do, as we can't chase after every framework MS releases (as I said, we wrote at least 5 model builders for various frameworks they released during the years, all of them now defunct) and we concluded people will mostly create WebAPI methods, which query the DB based on input and return a resultset, and for the occasional situation where people need OData, they can use WCF Data Services. Other than that, we won't support this OData specific modelbuilder.

I did try to make your example work though, even with DataMember/DataContract attributes, but then it dies in weird errors that it can't serialize parameters (wtf...). So I have no idea to use this. the odata support classes modelbuilder we have can't expose the model so this new service can use it (if only that was true) so that's also not a route to solve it. So unless WCF data services are used, I'm afraid you can't do what you want with our runtime.

I'd like to add that I'm sorry you ran into this, even though we can do little about it. Microsoft's docs make it look like it will just work, but under the hood it only works with EF, all other ORMs have to provide a very complex system to produce the model data. If they for once simply design one modelbuilder (as EDM is defined once anyway, it makes no sense to require different model builders for all these kind of services) we can invest time once, and re-use these modelbuilders but they're all different (in every way, sadly).