Hi Team,

We're using LLBLGen 5.4 (Adapter) against MSSQL.

Computed columns seem to be causing an issue with the Plain SQL module.

For Example - we have a table called Employee, on which there is a persisted computed column called DisplayName which simply concatenates the first name and last name fields in the database.

If we fetch using the normal entity fetch everything works fine.

using(var adapter = new DataAccessAdapter())
{
      var emp = new EmployeeEntity(1);
      adapter.FetchEntity(emp);
}

If we fetch using the FetchQuery<> function and try to project onto the Employee Entity

using(var adapter = new DataAccessAdapter())
{
      var results = adapter.FetchQuery<EmployeeEntity>("SELECT * FROM Employee WHERE EmployeeId=@EmployeeId", new { EmployeeId = 1 });
}

I get a Linq error

ArgumentException: The property 'System.String DisplayName' has no 'set' accessor Parameter name: member

System.Linq.Expressions.Expression.ValidateSettableFieldOrPropertyMember(MemberInfo member, out Type memberType)
System.Linq.Expressions.Expression.Bind(MemberInfo member, Expression expression)
SD.LLBLGen.Pro.QuerySpec.ProjectionLambdaTransformer.CreateProjectorFunc<T, TDelegate>(string[] fieldPerOrdinal, Func<Type, int, Expression> indexerCreatorFunc, ParameterExpression parameterToUse)
SD.LLBLGen.Pro.QuerySpec.ProjectionLambdaTransformer.CreateDataReaderProjectorFunc<T>(string[] fieldPerOrdinal)
SD.LLBLGen.Pro.ORMSupportClasses.ProjectionUtils.FetchProjectionFromReader<T>(List<T> destination, IDataReader dataSource, IRetrievalQuery queryExecuted, bool performImplicitTypeConversions)
SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjection<T>(List<T> destination, IRetrievalQuery queryToExecute, bool performImplicitTypeConversions)
SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchQuery<T>(PlainSQLFetchAspects fetchAspects, string sqlQuery, object parameterValues)
SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase+<>c__DisplayClass2_0<T>.<FetchQuery>b__0()
SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchQuery<T>(string sqlQuery, object parameterValues)

If I go into the LLBLGen designer and edit the entity and set the DisplayName field to not be read only (LLBLGen correctly marks the field as read only by default) then the projection method works fine but of course that isn't an ideal situation to be in.

Feels like there is no set accessor because it is read only but it needs one when doing the projection. What's the best solution?

Note: Above example is just to replicate the error in a simple method. I'm not actually trying to load all my entities with the Plain SQL module by default, there is a good reason

Hmm, indeed.

We do specify the filter that we only want properties with a setter: var propertiesOfT = typeofT.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.SetProperty);

but apparently that still selects properties without a setter, we can reproduce this. (likely the flag is ignored by reflection, so we need to find another way)

When we solve this, the value however simply will be skipped so the property will never be set (as there's no setter). Likely not what you want.

I don't know your use case, but in general it's recommended to use the normal entity fetch route if the entity is used in a read/write scenario (with change tracking) as that sets things up properly without the entity becoming 'dirty'. With the query you're using, it will set a new entity instance' properties with values so the entity will be seen as 'new' and 'dirty' (changed), so that's often not preferable if you want it to be seen as a fetched entity.

If you want a readonly type, it's preferable to map a new typedview on the target table / view (and simply use read/write properties, you can't save a typedview anyway) so in that scenario you will get the computed column fetched and the property will be set.

Yes, I can understand the conundrum.

We need to expose a restful api for a significant number of entities. Part of this is facilitating odata filters. I'm using the moon.odata library to convert the Odata filter to an sql statement. Using the plain sql library projecting onto our entity objects works perfectly for every entity except those with computed columns. This allows our downstream developers to consume an API with industry standard support (mostly).

While I understand the technical issue, I feel as though simply saying that you won't support computed columns for your plain sql projections isn't representative of the excellence in engineering that LLBLGen has produced over the years. I've been using the platform since 2006 and this is the first time I've ever encountered a tough luck response from you.

Is there another api query specification we should look at that works better with LLBLGen? Maybe GraphQL? What is the official support of that?

I'm really impressed with how LLBLGen has integrated with .net core 2 webapi, using swagger to produce documentation, moon.odata to facilitate odata queries. There is some boilerplate code we've had to develop to deal with some serialization/deserialization issues but that is all pretty trivial.

We have over 200 tables, about 25 entities with approximately 45 computed columns. If this is a major issue I'd consider converting those columns to normal columns and using triggers (urgh) to keep them up to date to get around my issue there.

Happy to hear feedback if there is a better way

Anthony

hostileant wrote:

Yes, I can understand the conundrum.

We need to expose a restful api for a significant number of entities. Part of this is facilitating odata filters. I'm using the moon.odata library to convert the Odata filter to an sql statement. Using the plain sql library projecting onto our entity objects works perfectly for every entity except those with computed columns. This allows our downstream developers to consume an API with industry standard support (mostly).

While I understand the technical issue, I feel as though simply saying that you won't support computed columns for your plain sql projections isn't representative of the excellence in engineering that LLBLGen has produced over the years. I've been using the platform since 2006 and this is the first time I've ever encountered a tough luck response from you.

I'm sorry for that. We can't set properties that don't have a setter. Mind you this API is very low level: it builds a projection based on a type so it tries to match properties with the columns coming back from the database. So it's not about not supporting computed columns, it doesn't care about that, it's that there's no way to set a property that doesn't have a setter in this case as it simply has a set of properties and a set of columns and tries to match them, and builds property setter call code for each field-property pair. As the entity type has a readonly field, the field property doesn't have a setter so this fails in this case.

That said, we've fixed the issue in that it now doesn't try to include properties which don't have a setter. This however leads to the situation I already described, where the property without a setter isn't set with a value. (Hotfix build with fix has been uploaded)

Is there another api query specification we should look at that works better with LLBLGen? Maybe GraphQL? What is the official support of that?

You could use our OData support of course, if that's sufficient (however it's compatible with OData v5.7 (https://www.llblgen.com/Documentation/5.4/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_wcfdataservices.htm), as MS broke the api in v6.x and therefore we had to keep the support to the older odata classes.

But ultimately it depends on what you're doing on the client If the odata service is consumed in e.g. JS you might want to simply expose a DTO model modeled on top of entities, using denormalization, if necessary, using our Derived Model feature (https://www.llblgen.com/Documentation/5.4/Derived%20Models/index.htm). If you're consuming the service in a .NET system and use the entities in read/write fashion then you indeed need entity types and it would then be a good idea to e.g. the odata support classes we have or use webapi and linq queries (instead of converting to sql). Main plus here is that the entity instances are all fetched without become marked as 'changed' and you can simply pass them back (changed) and they're persisted as-is.

I'm really impressed with how LLBLGen has integrated with .net core 2 webapi, using swagger to produce documentation, moon.odata to facilitate odata queries. There is some boilerplate code we've had to develop to deal with some serialization/deserialization issues but that is all pretty trivial.

Interesting that you use an odata->sql converter first, and not simply use the linq queries produced by webapi (although these have edge cases too with 1:1 queries and the awkward eager loading system of odata which creates messy queries ... )

We have over 200 tables, about 25 entities with approximately 45 computed columns. If this is a major issue I'd consider converting those columns to normal columns and using triggers (urgh) to keep them up to date to get around my issue there.

Happy to hear feedback if there is a better way

Anthony

One thing you could do is simply uncheck the 'ReadOnly' checkbox for the fields. These fields likely won't get any new value set so it won't be problematic when saving (they're ignored anyway). But again it depends on what you're doing with the data on the client: deserialize them back to entity instances or project them from json to e.g. js objects...

Unchecking the ReadOnly checkbox for fields doesn't get reset when syncing with the database again.

To find all entities with readonly fields, in the designer's Element search do: (select 'Entity' as return type') return p.EntityModel.Vertices.Where(e=>e.Fields.Any(f=>f.IsReadOnly && !f.IsPartOfIdentifyingFields));

It's code, so you can easily alter the IsReadOnly value there with some C# before you return the set, making life a little easier when you have to edit 25 entities and 45 fields

I'm sorry for that. We can't set properties that don't have a setter. Mind you this API is very low level: it builds a projection based on a type so it tries to match properties with the columns coming back from the database. So it's not about not supporting computed columns, it doesn't care about that, it's that there's no way to set a property that doesn't have a setter in this case as it simply has a set of properties and a set of columns and tries to match them, and builds property setter call code for each field-property pair. As the entity type has a readonly field, the field property doesn't have a setter so this fails in this case.

Having not dived into the generated code deeply enough to know, if the property doesn't have a setter how is the property set upon fetching from the database using the FetchEntity/FetchEntityCollection routines?

You could use our OData support of course

Is there a solution for using your OData support in ASP.NET Core 2 in a rest api? It all appears to be for the traditional .net with WCF?

The moon.odata library is pretty basic and out of the box doesn't support all odata commands (such as expand, but it could if we extended that library) but works pretty nicely for the most common requirements - including the fact we can support typed views.

Our service is being consumed by a variety of clients, both internal and external and thus are looking to expose a meaningful rest api.

Have you thought about another set of templates for LLBLGen that provides a scaffold for developing a restful api?

Yes - I went through yesterday and unchecked the read only flag on all those fields and everything is working nicely.

Thanks for your help.

hostileant wrote:

Having not dived into the generated code deeply enough to know, if the property doesn't have a setter how is the property set upon fetching from the database using the FetchEntity/FetchEntityCollection routines?

FetchEntity/Collection are set through internal fields. However the** FetchQuery<YYY>("select ...")** approach works differently: you can pass any object in YYY so the routine compares the names and types to find matches and map the results to YYY objects. This is explained in the documentation:

docs wrote:

Here all rows from the table Customers are fetched which have USA as the value for Country and each row is projected to an instance of the class Customer. The engine will project each column to a property of the class with the same name, case-insensitive comparison. This means a property MyProperty in Customer with no equivalent column in the resultset won't receive a value. A column in the resultset with no equivalent property in Customer will be ignored.

hostileant wrote:

Is there a solution for using your OData support in ASP.NET Core 2 in a rest api? It all appears to be for the traditional .net with WCF?

You're rigth. The SD.LLBLGen.Pro.ODataSupportClasses assembly is only available for .NET Full.

hostileant wrote:

Have you thought about another set of templates for LLBLGen that provides a scaffold for developing a restful api?

I remember that there was a 3rd party contribution about this for old versions. I think it would be interesting for someone to develop open-source templates for this.

hostileant wrote:

Yes - I went through yesterday and unchecked the read only flag on all those fields and everything is working nicely.

Nice

To add some details to David's post

hostileant wrote:

Having not dived into the generated code deeply enough to know, if the property doesn't have a setter how is the property set upon fetching from the database using the FetchEntity/FetchEntityCollection routines?

Entities are fetched with the whole row stored as object[] inside their Fields property, which are then read/write (if a setter is there) through the class properties. So it doesn't go through the properties to set entity values when fetching.

You could use our OData support of course

Is there a solution for using your OData support in ASP.NET Core 2 in a rest api? It all appears to be for the traditional .net with WCF?

The OData classes are not WCF but a real OData service (the official name was wcf data services so hence it contains 'wcf', but indeed not for .net core. For .net core you should use webapi.

Have you thought about another set of templates for LLBLGen that provides a scaffold for developing a restful api?

This has crossed our minds but in the end it's a bridge too far for what we want to do, as the service likely would never meet anyone's needs (as you can create an api in many ways and it's impossible to define something for that that fits everyone's idea how to create such an api). Instead we invested a lot in derived models (for read only or read/write scenarios) and generate code for that. This means that creating the service is pretty straight forward and the DTO / document hierarchies are generated from the designer, based on your entity model, complete with projection code. This leaves you with just the API definition of the webapi and that's about it.

In general this is preferable as the object model exposed by the service is decoupled from the entity model behind the service and in line with e.g. what people use in MVVM or MVC scenarios with a service.

First class service chaps That's why I've been on the framework since 2006!