Different query generated in LLBLGen 5.8.2 than in LLBLGen 4.2

Posts   
 
    
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 13-Aug-2021 15:34:19   

This is the query (where Patron is a subtype of User)

            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,
                    UserFields.StartDate,
                    UserFields.EndDate,
                    UserFields.ManagementSystemID
                );
  [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],
  [LPA_L1].[StartDate],
  [LPA_L1].[EndDate],
  [LPA_L1].[ManagementSystemID] 
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].[DisplayName] ASC,
  [LPA_L1].[ID] ASC

The WHERE clause highlighted is not generated in LLBLGen 4.2 and is not wanted as it excludes Users who are not also Patrons.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 13-Aug-2021 18:25:40   

(previous answer deleted) You specify Patron fields, so the engine adds a type filter for Patron as it's something it has to do (as you sometimes do want to have that, and this situation is ambiguous). A long time ago we changed this behavior for a stricter type filter.

We have a test just like this one, I'll see what we did there.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 13-Aug-2021 18:51:08   

Otis wrote:

It's likely caused by a stricter join filter system that we've added in 5.0, as there are cases where you do want this and it's impossible to tell when. I think the workaround is that you specify PatronFields instead of UserFields in the orderby, as I think the filter is caused by the orderby (please check if without the order by it works ok)

1) I'm not a SQL expert as you know but if there is a LEFT JOIN plus an IS-NOT-NULL WHERE clause on the PK/Inheritance field of the other table then it may as well generated an INNER JOIN, no?

2) I really don't want to have to use non-clearly defined workarounds to make a query work. It makes sense to me for queries to use the Table name where the Field is defined. And I really don't want to be locating and checking all the queries in my app to see if their output has now changed.

3) If something is ambiguous then it needs to be unambiguated. In this case, using qf.Patron should be clear enough that Patrons are required whereas qf.User should indicate Users are required along with their Patron details if they are also a Patron. I guess an explicit JOIN would also do it.

4) You also mention a "stricter join filter system for v5.0" but my query does not specify joins. Whilst I know that a join is build by LLBLGen for the inheritance system surely that should be an implementation detail with a very simple LEFT JOIN or RIGHT JOIN between the tables and no filtering (strict or otherwise) required.

I'm convinced this is either a bug or a serious breaking change that IFAICT isn't documented.

simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 13-Aug-2021 19:27:14   

I've tried what you originally suggested - using Patron Fields in the OrderBy clauses (all permutations) and I've tried commenting out the OrderBy clauses altogether. The WHERE clause *always *appears in the query.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 13-Aug-2021 21:04:31   

simmotech wrote:

Otis wrote:

It's likely caused by a stricter join filter system that we've added in 5.0, as there are cases where you do want this and it's impossible to tell when. I think the workaround is that you specify PatronFields instead of UserFields in the orderby, as I think the filter is caused by the orderby (please check if without the order by it works ok)

1) I'm not a SQL expert as you know but if there is a LEFT JOIN plus an IS-NOT-NULL WHERE clause on the PK/Inheritance field of the other table then it may as well generated an INNER JOIN, no?

There are two things at play: 1) adding the joins so subtypes of a supertype can be fetched and 2) filter the resultset of 1) so it only results in the types requested. The join is a result of 1), the where clause is a result of 2). The left join is a standard join for joining subtypes.

The where clause is added because it sees at least 1 field from a subtype and therefore limits the fetch (as otherwise sibling rows of the subtype also match). This is the ambiguous part, see below.

2) I really don't want to have to use non-clearly defined workarounds to make a query work. It makes sense to me for queries to use the Table name where the Field is defined. And I really don't want to be locating and checking all the queries in my app to see if their output has now changed.

Your query fails because we adjusted the auto-type filter system we added for queries where there are fields from multiple types in the same direct inheritance hierarchy. In 4.2 it did a poor job, and it was requested to adjust this to make the auto-type filter adding system be more clever. See below.

3) If something is ambiguous then it needs to be unambiguated. In this case, using qf.Patron should be clear enough that Patrons are required whereas qf.User should indicate Users are required along with their Patron details if they are also a Patron. I guess an explicit JOIN would also do it.

This is exactly what we did: we made it non-ambiguous. The thing is that you describe your query from your PoV and then it's valid, but you can also look at it as: you specified a supertype but as you specified one or more fields from a subtype, the query really should fetch the subtypes the fields are from. That you specified user fields isn't really interesting for this: they're valid patron fields. This was in line what we already had but in this particular case didn't do a good job of, so we made this better so people didn't need to specify a type filter and the queries they already had kept working. This has the side effect that your query no longer works as it is now. There's sadly not a solution that works for everyone.

Now, before you jump up and try to convince me there is a solution that will everyone's query work with this problem, there's not. This problem is very old, it had many small changes and refinements over the years and there are many situations it affects. There's no chance this changes from how it is today. That's not to annoy you, we can't simply change it to something that works for everyone and all code that is using the code as it was intended keeps working. So, it's unfortunate you run into this. I'll give you a workaround that better describes what you're doing, and it's not likely you have a lot of these queries that run into this issue anyway.

Workaround: (For the record, no I don't like this either, it results in bigger SQL queries but it is removing the reliance on a side effect of a system we can't remove nor change)

The query that does what you want is this:

var query = qf.User
    .Where(PatronEntity.GetEntityTypeFilter(negate:true))
    .Select(
        UserFields.ID,
        UserFields.DisplayName,
        Functions.Constant(null).As("LegalBodyID"),
        UserFields.PrimaryPermissionGroupID,
        UserFields.AccountName,
        UserFields.AccountDisabled,
        UserFields.FirstLoginDateTime,
        UserFields.LastLoginDateTime,
        Functions.Constant(null).As("ClassGroupID"),
        UserFields.StartDate,
        UserFields.EndDate,
        UserFields.ManagementSystemID
    )
    .UnionAll(
        qf.Patron
            .Select(
                PatronFields.ID,
                PatronFields.DisplayName,
                PatronFields.LegalBodyID,
                PatronFields.PrimaryPermissionGroupID,
                PatronFields.AccountName,
                PatronFields.AccountDisabled,
                PatronFields.FirstLoginDateTime,
                PatronFields.LastLoginDateTime,
                PatronFields.ClassGroupID,
                PatronFields.StartDate,
                PatronFields.EndDate,
                PatronFields.ManagementSystemID
            )
    )
    .OrderBy(UserFields.DisplayName | SortOperator.Ascending)
    .OrderBy(UserFields.ID | SortOperator.Ascending);

It was quite similar to what we had in a test:

[Test]
public void DynamicListAutoTypeFilterAddTest5()
{
    var qf = new QueryFactory();
    using(var adapter = new DataAccessAdapter())
    {
        var q = qf.Employee
                  .Where(ClerkEntity.GetEntityTypeFilter(negate:true))
                  .Select(EmployeeFields.Name,
                          Functions.Constant(null).As("JobDescription"))
                  .UnionAll(qf.Clerk
                           .Select(ClerkFields.Name,
                                   ClerkFields.JobDescription))
                  .OrderBy(EmployeeFields.Name.Ascending());
        var results = adapter.FetchQuery(q);
        Assert.AreEqual(4, results.Count);
    }
}

which results in:

SELECT [LPA_L1].[Name],
       NULL AS [JobDescription]
FROM   ([InheritanceTwo].[dbo].[Employee] [LPA_L1]
        LEFT JOIN [InheritanceTwo].[dbo].[Clerk] [LPA_L2]
            ON [LPA_L1].[EmployeeID] = [LPA_L2].[ClerkID])
WHERE  ((([LPA_L2].[ClerkID] IS NULL)))
UNION ALL
SELECT [LPA_L3].[Name],
       [LPA_L4].[JobDescription]
FROM   ([InheritanceTwo].[dbo].[Employee] [LPA_L3]
        LEFT JOIN [InheritanceTwo].[dbo].[Clerk] [LPA_L4]
            ON [LPA_L3].[EmployeeID] = [LPA_L4].[ClerkID])
WHERE  ((([LPA_L4].[ClerkID] IS NOT NULL)))
ORDER  BY [LPA_L1].[Name] ASC 

Again, I understand your PoV, and even a reaction of 'why isn't there a simple switch to change this', but that has already been discussed some time ago, it won't help. Well, in this situation but not in others. I'm sorry, but this is what it is.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 02-Oct-2021 08:32:27   

For that application, I used the Union thing as you suggested - only one query - all fine. However, I'm now migrating my other project which is much larger, has deeper hierarchies and hundreds of queries. Just got it updated to compile under .NET 6 and on running it I've already I've noticed empty grids etc. due to the additional type filters.

I understand that the way things are in v5.8 is not going to change for the reasons you mentioned above. But I would prefer not to have to check and rewrite dozens of queries in code (not least because of problems I mentioned previously) so I'm trying to find a workaround where I can just mark the query as not wanting type filters.

I've started by adding this extension method to let me identify queries as requiring v4.2 behaviour:

        public static TQuery WithoutTypeFilters<TQuery>(this TQuery query) where TQuery: DynamicQuery
        {
            ArgumentVerifier.CantBeNull(query, nameof(query));

            ((HashSet<string>) typeof(QuerySpec).GetProperty("QueryHints", BindingFlags.Instance | BindingFlags.NonPublic)!.GetValue(query))!.Add(RemoveTypeFiltersHint);

            return query;
        }

I can't prevent the type filters being added so In my custom Query Creation Manager, I check for and remove it:-

        protected override IRetrievalQuery CreateSelectDQ(QueryParameters parameters)
        {
            if (parameters.QueryHints.Contains(Extensions.RemoveTypeFiltersHint))
            {
                parameters.QueryHints.Remove(Extensions.RemoveTypeFiltersHint);

                if (parameters.FilterSpecified && parameters.FilterToUseAsPredicateExpression.Count >= 3 && parameters.FilterToUseAsPredicateExpression[^2] is { Type: PredicateExpressionElementType.Operator, Contents: PredicateExpressionOperator.And })
                {
                    parameters.FilterToUse = (IPredicate) parameters.FilterToUseAsPredicateExpression[0].Contents;
                }
                else
                {
                    throw new InvalidOperationException($"'{Extensions.RemoveTypeFiltersHint}' was set but Filter didn't contain Type Filter");
                }
            }

It needs to take account of a type filter being the only filter but it worked for the query in my grid and everything is back as before. Can you see any issues in doing this?

Assuming the above workaround is valid, I then wanted to try to identify where type filters have been added so I can then add the WithoutTypeFilters() where necessary so this code is called at the bottom of CreateSelectDQ().

        [Conditional("DEBUG")]
        void CheckForPotentialTypeFilteredQuery(QueryParameters parameters, IRetrievalQuery preparedQuery)
        {
            if (parameters.FilterSpecified && parameters.FilterToUseAsPredicateExpression.Count >= 3 && parameters.FilterToUseAsPredicateExpression[^2] is { Type: PredicateExpressionElementType.Operator, Contents: PredicateExpressionOperator.And })
            {
                var containingObjectNames = parameters.FieldsForQueryAsArray.Select(f => f.ContainingObjectName).ToHashSet();

                if (containingObjectNames.Count > 1)
                {
                    var formattedQuery = new SqlQueryFormatter(DataAccessAdapter.QueryDumpPrefixToRemove).DumpFormattedQuery(preparedQuery);

                    Trace.Write(formattedQuery, null);

                    LogManager.GetLogger("** Potential Entity Type Filtered Query **").Debug(formattedQuery);
                }
            }
        }

So I can now see in the log the generated query just before it runs.

Am I right in thinking that I only need to check for DynamicQueries? If so, is there any way of detecting such from QueryParameters? Any help on this is appreciated.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 04-Oct-2021 09:33:53   

As far as I can see, the workaround you added might work ok, but I can't foresee all possible situations, so it might be a situation might pop up where the predicate you are removing is a valid one you added.

The code which produces the type filters is called in PersistenceCore.cs, line 1925:

// filter out entities in a hierarchy of TargetPerEntity, and also filter out supertypes if the subtype is also in the list. 
// grab the filter and set the proper object alias. This method will return no filter if the names are not part of the same inheritance hierarchy path, i.e. the list
// contains siblings, as that will cause 0 rows to be returned. The user in that case has to apply a type filter. If the names are part of the same hierarchy path,
// the deepest subtype will provide the type filter (all other type filters are redundant). 
filter.AddWithAnd(infoProvider.GetEntityTypeFilters(entityNamesToCheck));

The code above determines the amount of entities in the projection, it then passes that to the inheritanceinfoprovider and it gets the typefilters back. It's not enough to change the GetEntityTypeFilters method, as it's also used for entity fetches. If anything you might want to remove some names of the dictionary passed in, or you might want to alter the InheritanceInfoProvider method by signalling it's doing a dyn. list fetch so it should produce at most 1 type filter.

I think that should get you to the 4.2 feature level.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 04-Oct-2021 09:48:57   

Yes, I've been staring at that line over the weekend but being internal static and the methods that call it being internal/non virtual, I am having a lot of trouble trying to influence it.

I also looked at AliasesOfEntitiesAlreadyTypeFiltered to see if I could prepopulate that but that didn't seem to have any effect - I'll have a closer look to see if I cocked it up.

simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 04-Oct-2021 13:43:31   

What do you think to having an EntityTypeFilterPredicateExpression wrapper class?

Similar to CorrelationPredicateExpression where it doesn't do anything special itself but allows easier identification/debugging.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 05-Oct-2021 11:08:28   

Let's turn it around: with the queries like this: you can add your own predicates to a PredicateExpression subclass which contains all your predicates. In the queryparameters object it'll have that predicate expression and potentially other predicates. Remove these and your query will be the same as it was. (as type filters are 'the other predicates'. ). This will be some work, but it also gives you control over how to solve the problem for your app.

We don't want to introduce a type for no reason that's used likely only in your project (if it will at all, as the release isn't tomorrow). it's code we need to maintain that has no value for the framework (Contrary to the correlationpredicateexpression which does have value, hence we have introduced it). It also makes the problem our problem and that's also not something we look forward to.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 05-Oct-2021 11:59:50   

OK. I think I get your point - though I don't actually have any predicates to keep. I guess in this case you mean remove all predicates.

My biggest problem here is looking at the queries I have and saying Ah! That one will need the workaround. As you know, I'm not a genius with SQL and trying to remember exactly what they do when they were written years ago is not straightforward for me at the moment.

What happens with the added Type Filters if

a. Two sibling subtype fields are listed instead of one?

b. A field from a subtype of a subtype is included (is that possible?)?

I've got my head around why you changed things to include type filters - so they don't need to manually be included; that was the intended behaviour; 99% of users really meant to get the (single) subtype.

But my cases seem to be "create a single dynamic query to get information from all fields across the entity hierarchy returning nulls for fields that don't exist in a particular subtype". I happened to get that working by what turned out to be a fluke in 4.2.

Why isn't that considered a valid use case?

What I am trying to do in LLBLGen is a smaller way is similar to this database view:

CREATE VIEW [dbo].[LegalBodyReferenceData] AS
SELECT
    lb.ID,
    lb.DisplayName,
    lb.BirthDate,
    lb.DeathDateAndFlag as DeathDate,
    
    ti.Description AS Title,
    p.FirstNames,
    p.Surname,
    
    lb.Telephone,
    lb.Email,
    
    a.Street,
    a.Town,
    a.Region,
    a.Postcode,
    a.Country,
    
    COALESCE(cf1.ID, cf2.ID, cf3.ID) AS ClientFileID,
    COALESCE(cf1.FileStatusID, cf2.FileStatusID, cf3.FileStatusID) AS ClientFileStatusID,
    COALESCE(cf1.AdviserID, cf2.AdviserID, cf3.AdviserID) AS ClientFileAdviserID,
    COALESCE(cf1.DisplayName, cf2.DisplayName, cf3.DisplayName) AS ClientFileDisplayName,
    
    ISNULL(CAST(CASE WHEN p.ID IS NOT NULL THEN 1 ELSE 0 END AS bit), 0) AS IsPerson,
    ISNULL(CAST(CASE WHEN co.ID IS NOT NULL THEN 1 ELSE 0 END AS bit), 0) AS IsCorporate,
    ISNULL(CAST(CASE WHEN prov.ID IS NOT NULL THEN 1 ELSE 0 END AS bit), 0) AS IsProvider,
    ISNULL(CAST(CASE WHEN t.ID IS NOT NULL THEN 1 ELSE 0 END AS bit), 0) AS IsTrust,
    ISNULL(CAST(CASE WHEN cbp.ID IS NOT NULL THEN 1 ELSE 0 END AS bit), 0) AS IsCorporateBenefitProvider,
    (SELECT 
        '|' + CAST(t.RoleID AS varchar)
    FROM Tag t
    WHERE t.SourceID = lb.ID
    FOR XML PATH('') ) + '|'
        AS TagIDs,
    NULL AS Tags,
    
    c.MaritalStatusID,
    p.Gender,
    u.ID AS UserID,
    ISNULL(CAST(CASE WHEN ad.ID IS NOT NULL THEN 1 ELSE 0 END AS bit), 0) AS IsAdviser,
    prov.UniqueIdentifier AS ProviderCode,
    CAST(c.DisplayOrder AS tinyint) AS ClientDisplayOrder,

    p.InformalSalutation AS InformalSalutation,
    ISNULL(CAST(CASE WHEN t.IsSimpleTrust IS NOT NULL THEN 1 ELSE 0 END AS bit), 0) AS IsSimpleTrust,
    t.TrustTypeID AS TrustTypeID,
    lb.PrimaryAddressID AS PrimaryAddressID,

    (SELECT 
        '|' + CAST(ass.RoleID AS varchar) + CAST((CASE WHEN ass.SourceID = lb.ID THEN 'T' + CAST(ass.TargetID AS varchar) ELSE 'S' + CAST(ass.SourceID AS varchar) END) AS varchar)
    FROM Association ass
    WHERE ass.TargetID = lb.ID OR ass.SourceID = lb.ID
    FOR XML PATH('') ) + '|'
        AS AssociationIDs,
    a.Telephone AS AddressTelephone,
    p.MobileTelephone,
    p.WorkTelephone

FROM LegalBody lb
LEFT JOIN Address a ON a.ID = lb.PrimaryAddressID
LEFT JOIN Person p ON p.ID = lb.ID
LEFT JOIN Title ti ON ti.ID = p.TitleID
LEFT JOIN Client c ON c.ID = lb.ID
LEFT JOIN ClientFile cf1 ON cf1.ID = c.PrivateClientFileID
LEFT JOIN Corporate co ON co.ID = lb.ID
LEFT JOIN Provider prov ON prov.ID = co.ID
LEFT JOIN Trust t ON t.ID = lb.ID
LEFT JOIN CorporateBenefitProvider cbp on cbp.ID = lb.ID
LEFT JOIN CorporateClientFile ccf ON ccf.CorporateID = co.ID
LEFT JOIN ClientFile cf2 ON cf2.ID = ccf.ID
LEFT JOIN TrustClientFile tcf ON tcf.TrustID = lb.ID
LEFT JOIN ClientFile cf3 ON cf3.ID = tcf.ID
LEFT JOIN [User] u ON u.PersonID = p.ID
LEFT JOIN [Adviser] ad ON ad.ID = u.ID

One query from many entities (including hierarchies) providing everything about Persons, Companies, Addresses, Associations, Tags and more in one go that can be used to populate dropdowns, do local lookups, create filters etc. etc.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 05-Oct-2021 18:19:51   

I'd suggest creating a dB view for this, and a typedView mapped to it.

simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 05-Oct-2021 18:23:11   

That was just a sample showing why I want to get fields from across a hierarchy without maintaining a db View.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 06-Oct-2021 09:41:16   

simmotech wrote:

OK. I think I get your point - though I don't actually have any predicates to keep. I guess in this case you mean remove all predicates.

My biggest problem here is looking at the queries I have and saying Ah! That one will need the workaround. As you know, I'm not a genius with SQL and trying to remember exactly what they do when they were written years ago is not straightforward for me at the moment.

What happens with the added Type Filters if

a. Two sibling subtype fields are listed instead of one? b. A field from a subtype of a subtype is included (is that possible?)?

The engine will add type filters for all subtypes it finds. If you specify the fields of sibling subtypes, 0 rows will be returned as a type can't be 2 sibling types at once. If you specify 1 subtype, it'll get the subtype's instances. Including fields of subtype C of a subtype B will result in the same problem: it'll add a type filter for C, as C is also a B.

I've got my head around why you changed things to include type filters - so they don't need to manually be included; that was the intended behaviour; 99% of users really meant to get the (single) subtype.

But my cases seem to be "create a single dynamic query to get information from all fields across the entity hierarchy returning nulls for fields that don't exist in a particular subtype". I happened to get that working by what turned out to be a fluke in 4.2.

Why isn't that considered a valid use case?

I'm not going to re-do the debate again. I've explained why this is the case and also why it's an either/or situation. Yes, I know I can add a setting no-one but you would use but as I have already explained above (https://www.llblgen.com/tinyforum/Message/Goto/149927 ) the situation is complicated and therefore won't change. Make no mistake: I do realize this is a problem for you and I'm sorry you run into this, but also do realize I'm tired of debating things why they are they way they are. I already have explained things in detail and have spent time trying to look for workarounds. You now again try to dig up that debate, and I would like to ask you to stop doing that.

What you're effectively trying to do in your code is: you store data using inheritance hierarchies and then you use the data in a non-inheritance way; as the data is stored in database tables, you can read these tables like any other table and simply join the data and project that to types, like a typedlist or poco class or other. In an entity model it doesn't work that way: you have entities which are in an inheritance hierarchy and you have to stick with that. This works fine, except when you want to use the data as it is stored in the database, without inheritance hierarchies: the raw data in the tables.

So in that light you could use two models on the same database: one for CRUD where you edit entity instances, which uses inheritance, and one for read/only data for viewing where you project entities to typedlists etc. Simply the same entities but this time they're not in an inheritance hierarchy (in the r/o model, only keep the entities that are in an inheritance hierarchy). The friction comes from the fact you try to use a model with inheritance in a way that 'ignores' the inheritance hierarchies being there, for certain situations. This has been debated to death and we won't change how it works: we won't add a switch to make them go away.

To migrate your current code to the 'two' model system, one having the inheritance hierarchies and one not having the inheritance hierarchies, you can start with the same project file, copy it, rename the copy and remove the hierarchies from the entities you want to use in a non-inheritance fashion. Change the namespace for the generated code and with your grids use that code instead. You have to add more joins (as no entity is in an inheritance hierarchy), and therefore it might not be a workaround you want. The alternative is to remove the add inheritance filters based on entity fields in a projection functionality from the runtime and use a custom build of that.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 07-Oct-2021 18:33:39   

In an entity model it doesn't work that way: you have entities which are in an inheritance hierarchy and you have to stick with that.

Not when it is being converted to a DynamicQuery though - I see it just as a bunch of fields across Tables (all nicely pre-joined for me by the inheritance hierarchy - not 'ignored' as you suggested). And whilst limiting to a single entity is a perfectly valid use-case (and the default being the common case), so is just choosing the required fields across them all.

Using two models on the same database/custom builds: What a overcomplicated way of working around a simple thing.

I get you are not going to change anything and we will have to agree to disagree about whether it is a valid use-case, but can't you throw me a bone here? An EntityTypeFilterPredicateExpressionWrapper class would cost nothing - you would get a clearer predicate display in the debug window for free and I get to implement my original workaround.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 08-Oct-2021 09:15:45   

simmotech wrote:

In an entity model it doesn't work that way: you have entities which are in an inheritance hierarchy and you have to stick with that.`

Not when it is being converted to a DynamicQuery though - I see it just as a bunch of fields across Tables (all nicely pre-joined for me by the inheritance hierarchy - not 'ignored' as you suggested).

That's a misunderstanding: the entity fields are still part of an inheritance hierarchy, creating a query with them will need to pull in the inheritance info to make the query work. They are then projected into a flat list, but the query to get that projection is based on inheritance information. That's the crux of the matter: when you specify a subtype field, do you just want the field in the table it's mapped on, or do you mean the field of a subtype (so the row in a supertype table is required too)? that's an ambiguous aspect of these queries and why it is problematic to solve.

The only real solution here is to map another entity, e.g. Flattened, on the same table (e.g. the table Subtype) which thus acts like a 1:1 related table to the table Supertype, and you can just read rows from it like you would from any other table, without the inheritance hierarchy baggage, as that entity isn't in an inheritance hierarchy so specifying a field of Flattened in a dynamic query will not pull in inheritance information as the field isn't part of an entity in an inheritance hierarchy, there's nothing ambiguous there.

And whilst limiting to a single entity is a perfectly valid use-case (and the default being the common case), so is just choosing the required fields across them all.

Using two models on the same database/custom builds: What a overcomplicated way of working around a simple thing.

I get you are not going to change anything and we will have to agree to disagree about whether it is a valid use-case, but can't you throw me a bone here? An EntityTypeFilterPredicateExpressionWrapper class would cost nothing - you would get a clearer predicate display in the debug window for free and I get to implement my original workaround.

Simon, I've told you we won't do that, sorry. The workaround would be a shoddy one anyway. the real solution is to have additional entities for the purposes you want to use the readonly (as it's readonly data) for, like I've explained above. That's maintainable and logically sound.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 08-Oct-2021 10:12:06   

We are definitely at cross purposes here I think.

I definitely DO want the entity inheritance hierarchy - this automatically sorts out the joins etc. so I don't have to do them myself and I don't need to map another entity which I find non-intuitive and complicated. So I want the entity structure but then change to a Dynamic query to get the read-only data.

The *only *thing I don't want is the added EntityTypeFilter() for this particular type of DynamicQuery. When that is not there, I get nulls where a particular 'entity' doesn't have that particular field. This is exactly what I want. There is no longer any ambiguity here and It all works out of the box except for that added EntityTypeFilter().

You won't add an built-in option to toggle using an EntityTypeFilter or not - not surprising if you think it doesn't work or is not what originally intended/designed or is not a useful scenario; But I can't override anything to prevent it being added so the only solution I have is to remove it before use.

Are we still at cross-purposes?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 08-Oct-2021 13:23:11   

You get the inheritance type filter because you re-use the entity fields with inheritance information for a situation where they are used as if they don't have inheritance information. The best way to fix that is to map some new entities on the same tables but without inheritance information and use these for your readonly situation where you need this data in a non-inheritance fashion.

This is my final reply, Simon, I don't want to drag this debate any further as we won't add a type to our framework that is used only in your project for this particular situation. You are in this situation because you want to use types for the purpose they're not intended to be used for. So use types that are, I can't make it any different than this. Adding a type we won't use nor anyone else but you won't solve any of that, you still need to add code to remove instances of that type which also makes little sense as you should use types that don't need that in the first place.

I'm sorry, but you have to bite the bullet and introduce these other types. I know it sucks, as our framework too contains code that works around situations in the .net framework we won't be able to change, but that's life.

Frans Bouma | Lead developer LLBLGen Pro