Problem updating from v4.2 to v5.5

Posts   
 
    
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 20-Feb-2019 14:52:39   

I have copied my app into a new folder and replaced the 2 LLBLGen projects with copies that use LLBLGen v5.5.

Just about got it compilable - that DbGeneric/DbSpecific project move was a real pain! Thank goodness its a one-off (it recreated the project from scratch so all my custom code was removed!)

The first query I ran produced different code from v4.2

This is the v5.5 query

SELECT [LPA_L1].[ID], [LPA_L1].[DisplayName], [LPA_L2].[LegalBodyID], [LPA_L1].[PrimaryPermissionGroupID], [LPA_L1].[AccountName], [LPA_L1].[AccountDisabled], [LPA_L1].[FirstLoginDateTime], [LPA_L1].[LastLoginDateTime], [LPA_L2].[ClassGroupID] FROM ([User] [LPA_L1] LEFT JOIN [Patron] [LPA_L2] ON [LPA_L1].[ID] = [LPA_L2].[ID]) **WHERE ( ( ( [LPA_L2].[ID] IS NOT NULL))) ** ORDER BY [LPA_L1].[ID] ASC

And this is the v4.2 query

SELECT [LPA_L1].[ID], [LPA_L1].[DisplayName], [LPA_L2].[LegalBodyID], [LPA_L1].[PrimaryPermissionGroupID], [LPA_L1].[AccountName], [LPA_L1].[AccountDisabled], [LPA_L1].[FirstLoginDateTime], [LPA_L1].[LastLoginDateTime], [LPA_L2].[ClassGroupID] FROM ( [User] [LPA_L1]
LEFT JOIN [Patron] [LPA_L2] ON [LPA_L1].[ID] = [LPA_L2].[ID]) ORDER BY [LPA_L1].[DisplayName] ASC, [LPA_L1].[ID] ASC

Patron inherits from User but I want to fetch User-only results too but the highlighted WHERE is messing that up. I can't see a breaking change for that.

Here is the query code:

            var qf = new QueryFactory();

            var query = qf.User
                .OrderBy(UserFields.DisplayName | SortOperator.Ascending)
                .OrderBy(UserFields.ID | SortOperator.Ascending)
                .Select(
                    UserFields.ID,
                    UserFields.DisplayName,
                    PatronFields.LegalBodyID,
                    UserFields.PrimaryPermissionGroupID,
                    UserFields.AccountName,
                    UserFields.AccountDisabled,
                    UserFields.FirstLoginDateTime,
                    UserFields.LastLoginDateTime,
                    PatronFields.ClassGroupID
                );

            return query;

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 20-Feb-2019 20:56:30   

that DbGeneric/DbSpecific project move was a real pain! Thank goodness its a one-off (it recreated the project from scratch so all my custom code was removed!)

As a best practice, always generate to a new folder when upgrading. Also you should keep your custom code in partial class files so it is maintained across code regenerations.

Patron inherits from User but I want to fetch User-only results too but the highlighted WHERE is messing that up. I can't see a breaking change for that.

For entity fetches, like the one you're doing, all subtypes are fetched as well that's by design, as subtypes are of the supertype as well. If you just want to fetch the supertypes, you have to fetch a projection and only specify the supertype's fields.

simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 20-Feb-2019 22:07:20   

Walaa wrote:

that DbGeneric/DbSpecific project move was a real pain! Thank goodness its a one-off (it recreated the project from scratch so all my custom code was removed!)

As a best practice, always generate to a new folder when upgrading. Also you should keep your custom code in partial class files so it is maintained across code regenerations.

Patron inherits from User but I want to fetch User-only results too but the highlighted WHERE is messing that up. I can't see a breaking change for that.

For entity fetches, like the one you're doing, all subtypes are fetched as well that's by design, as subtypes are of the supertype as well. If you just want to fetch the supertypes, you have to fetch a projection and only specify the supertype's fields.

My custom code was in partial class files but v5.5 created a new project in the new location without referencing them.

I do want it to fetch subtypes as well but also where there is no subtype - in the this case there is a User that isn't a Patron. This worked in v4.2 but now doesn't in v5.5

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 21-Feb-2019 10:37:34   

The move of the csproj code is documented with a remark that warns you right at the top of the migration page for the version where it was introduced. If you migrate from 4.2 to 5.5, you do have to read the migration docs from 4.2->5.0 and up. https://www.llblgen.com/Documentation/5.5/LLBLGen%20Pro%20RTF/migratingcode.htm#migrating-generated-code-from-v5.3-to-v5.4

Sadly this was missed by some. It had to be a manual move because you also have to update the sln file, and we hoped that with the warning it would be sufficient. I'm sorry you missed it.

That it now generates an 'is not null' predicate is a bugfix for the situation where you want subtype fields. It can't make a different decision, because you specify PatronFields.LegalBodyID, in the projection so it has to assume you want subtype fields. It's ambiguous and before it didn't do this, which was wrong (no, please no argument, it is wrong). We couldn't introduce it during a minor version as it's a change of behavior but the fix was necessary.

That you relied on it and that it doesn't work is sadly unfortunate but we can't change it back as the same mechanism breaks other queries where it is expected it adds the filter (as subtype fields are specified). It's a cumbersome mechanism and sadly there's no other way as it's assumed if you specify subtypes the filters are added automatically. Would it have been the case the type filters would have been specified explicitly then things would have been better, but that's not the case (same thing as for aliasing btw)

What you could do is try a union query where you fetch 2 queries: one with and one without the subtype fields. (and use null as value for the PatronFields.LegalBodyID field) as 5.5 supports union queries now.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 21-Feb-2019 11:40:44   

I'm not too bothered about the moving csproject files since this was just a test project. Its a lot more than just changing the .sln file BTW - all the custom files need to be moved and adjusted too and then there is source control. But it is a one-off so not too concerned.

I am bothered about the breaking change though.

You said that it is a bugfix for the situation where you want subtype fields. But it already did this!!! It returned the subtype fields where they were available and returned null where they weren't. Just what I wanted and exactly what the query describes IMHO.

And what would happen if there was another subtype and the fields from that were also included? You can't have two IS NOT NULL checks for different subtypes - nothing would be returned surely? The way it was in v4.2 would have returned rows containing data for all subtypes (nulls where not available). This is a good feature!

If a specific subtype was required then using qf.<subtype> would indicate that and the WHERE clause makes sense but where qf.<roottype> is specified (and maybe when that roottype is not abstract?) then surely it would make sense to keep the original behaviour and turn what used to be a good feature - albeit seemingly unofficial - into an official feature.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 21-Feb-2019 13:22:45   

simmotech wrote:

I am bothered about the breaking change though.

You said that it is a bugfix for the situation where you want subtype fields. But it already did this!!! It returned the subtype fields where they were available and returned null where they weren't. Just what I wanted and exactly what the query describes IMHO.

That's why it's ambiguous. Fetching Clerk.Name from Employees where Clerk derives from Employee returns NULLs for every non-clerk. While I just asked for names of clerks.

There are multiple interpretations possible, we have to make a choice, we think it's best to add the filter automatically here.

And what would happen if there was another subtype and the fields from that were also included? You can't have two IS NOT NULL checks for different subtypes - nothing would be returned surely?

The way it was in v4.2 would have returned rows containing data for all subtypes (nulls where not available). This is a good feature!

If a specific subtype was required then using qf.<subtype> would indicate that and the WHERE clause makes sense but where qf.<roottype> is specified (and maybe when that roottype is not abstract?) then surely it would make sense to keep the original behaviour and turn what used to be a good feature - albeit seemingly unofficial - into an official feature.

You specify 'user' as the source (supertype) and then specify subtype fields at will and assume things are joined in. Subtypes are joined using left-joins. The values from the supertype table therefore are always filled.

If I fetch User.Name, Patron.SomeField and SiblingType.SomeOtherField, and I get 3 rows, what does that even mean? Patron and SiblingType are siblings. Fetching them in the same projection makes no sense unless you join somehow the data together describing the relationship between these types.

It's like I fetch Clerk.JobDescription (field in subtype table) and Manager.Name. Clerk and Manager are both subtypes of Employee. That makes no sense, there's no relationship between these two types other than they share the same supertype. What should the join be with clerk when the employeerow is a manager?

Hence the filter.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 21-Feb-2019 14:31:27   
Fetching Clerk.Name from Employees where Clerk derives from Employee returns NULLs for every non-clerk. While I just asked for names of clerks. 

Why would you use qf.Employee? You would use qf.Clerk.

It didn't have to be ambiguous. The qf.<type> (IsHierachyRoot) gives a clue as to the intention as does the number of different subtype fields included in the list (IsForSingleSubType - exclude supertype fields).

This simple truth table would reduce the ambiguity: IsHierachyRoot IsForSingleSubType Result N N Ambiguous - throw error N Y Single Left Join as per v5.5 (your Clerk example) Y N Multiple Left Joins as per v4.2 (return with nulls - my scenario) Y Y Single Left Join as per v5.5

Also, see attached image (this is a Target per entity hierarchy but pretend it is a Target-Per Entity for now). These Resources are not related to each other other than sharing the same supertype but is this not a reasonable query to run to get the resource plus IsFiction if it is a book/null otherwise?

            var query = qf.Resource
                .OrderBy(ResourceFields.Title | SortOperator.Ascending)
                .Select(
                    ResourceFields.ID,
                    ResourceFields.Title,
                    BookFields.IsFiction
                );

Attachments
Filename File size Added on Approval
Resources.png 124,704 21-Feb-2019 14:31.37 Approved
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 22-Feb-2019 10:27:17   

Please see this thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=24041

It's sadly not as simple as you describe. We can't please both situations, and as the situation that's currently implemented makes more sense (sorry) we opt for that one.

The root cause is that the low-level API doesn't have a typed projection system based on the types defined in the source. What I mean by that is that if you want to define these kind of queries with linq you can't: you have to cast the root type to a subtype, then pull a field from that, and that cast automatically adds the type filter. With the low-level API (and queryspec with the same projection system using a bucket of fields) you didn't need to. That 'didn't need to' is the root cause of this problematic situation: we should have added a forced 'type filter' requirement (which would have resulted in the same end result btw) so things would have been clear. Alas, water under the bridge.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 22-Feb-2019 13:50:06   

I seem to always be on the wrong side of ambiguity. disappointed

I think I'll just stick with LBLLGen v4.2.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Feb-2019 09:41:17   

I think that's a bit overly dramatic, there are things which can't be solved for all. Sometimes you're the beneficiary sometimes others. I do my best to make it work for everyone, but sometimes I can't and have to make a choice. You might not like the choice, that's fine. Trying to make it sound like I pick the choice that's negative for you on purpose is not, however. Please keep that in mind in your future posts on this forum. Thanks.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 25-Feb-2019 09:38:07   

I didn't mean to indicate the decisions were deliberate - that's ridiculous - I meant I was unlucky. In particular, I tried v5.5 and found two big features I have used extensively are now gone.

Over the weekend, I had decided not to give up on v5.5 so easily: The string stuff cannot be worked around so I'll just leave that at the river - we'll just have to disagree about memory usage scenarios.

But this one might be worked around by adding an extension method in my DataAccessAdapter partial class, say ForHierarchy(). Can this be done? Would you help?

Also, I have a couple of other features on my DataAccessAdapter that I don't _think _would be affected by the later version but would you be willing to glance over them to see if you think there could be a problem?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 25-Feb-2019 12:22:58   

simmotech wrote:

But this one might be worked around by adding an extension method in my DataAccessAdapter partial class, say ForHierarchy(). Can this be done? Would you help?

Currently it's not possible to override something and make it not add automatic type filters, as the code which does that is called from private methods and is located in a static class.

What you could do however is the following (I don't know how complex your queries are but for the one you showed here this should be doable). Override the following method in a DataAccessAdapter partial class:


        /// <summary>
        /// Creates a new Select DQ for the fields passed in using the parameters specified.
        /// </summary>
        /// <param name="parameters">The parameters.</param>
        /// <returns>
        /// ready to use query to use.
        /// </returns>
        protected virtual IRetrievalQuery CreateSelectDQ(QueryParameters parameters)

This is the method which creates the query using the DQE. The passed in 'parameters' object contains the set of objects which will be used for creating the SQL string. It's complete, all processing and inheritance stuff has been added. In the FilterToUse property, the FieldCompareNull predicates are present inside a predicate expression. If you don't have a predicate, simply reset this by setting it to null IF some boolean was set in your partial class through some ForHierarchy() method, otherwise do nothing and leave the parameters object alone.

You could also replace the FieldCompareNullPredicates with an IPredicate implementation that emits 1=1 when ToQueryText() is called, using a derived class of QueryApiObjectGraphTraverser where you override public virtual void TraversePredicateExpression(IPredicateExpression objectToTraverse) and manually scan that object (can be null!) if it contains a FieldCompareNullExpression and toss it out if it does or replace it (operators are between them, so if you have ((A IS NULL) OR (B IS NULL)) then the predicate expression contains FieldCompareNullExpression OR FieldCompareNullExpression, so removing the or the latter leaves a dangling OR so you have to keep that in mind, but as all type filters are in a single predicate expression it should be OK (as in: you end up with an empty one)

that's what you can do now, otherwise I don't know what to do other than rewriting the query to a union query (which I think is preferable, but I don't know how much of these queries you have).

Also, I have a couple of other features on my DataAccessAdapter that I don't _think _would be affected by the later version but would you be willing to glance over them to see if you think there could be a problem?

Sure, if you have a question regarding them just post a new thread and we'll have a look. Tho with porting things, it's often first a matter of trying, then seeing if it's easy to adapt and if not, ask what to do.

Frans Bouma | Lead developer LLBLGen Pro