Inheritance and id fetch query

Posts   
 
    
Findev
User
Posts: 103
Joined: 08-Dec-2014
# Posted on: 25-Oct-2016 12:50:18   

Hi,

I have inheritance: base abstract Entities table, Users table inherit from it, CompanyUsers table inherit from Users table. There are other tables inheriting from Entities, like Companies table etc.

was working on a query in LinqPad 5, decided to get Id field first and noticed the following:


var qf = new QueryFactory();

    var q = qf.User.Select(
    () => new ServiceRequestGenericUserHelper {
        Id = UserFields.Id.ToValue<Guid>(),
        //Type = UserFields.Type.ToValue<byte>()
    });
    
    var result = this.AdapterToUse.FetchQuery(q);
    result.Dump();

this generates the following SQL:


 SELECT [LPA_L2].[Id] FROM ( [XXX].[dbo].[Entities] [LPA_L1]  LEFT JOIN [XXX].[dbo].[Users] [LPA_L2]  ON  [LPA_L1].[Id]=[LPA_L2].[Id])

because it is a left join and nothing else I obviously get wrong result set. Now if I uncomment Type assignment then SQL changes to:


SELECT [LPA_L2].[Id], [LPA_L1].[Type] FROM ( [XXX].[dbo].[Entities] [LPA_L1]  LEFT JOIN [XXX].[dbo].[Users] [LPA_L2]  ON  [LPA_L1].[Id]=[LPA_L2].[Id]) WHERE ( ( ( [LPA_L2].[Id] IS NOT NULL)))

now result is correct because of the WHERE clause. I can also generate similar query by replacing Id with Id_EntityBase


var q = qf.User.Select(
    () => new ServiceRequestGenericUserHelper {
        Id = UserFields.Id_EntityBase.ToValue<Guid>(),
        //Type = UserFields.Type.ToValue<byte>()
    });

SQL:

 SELECT [LPA_L1].[Id] FROM ( [XXX].[dbo].[Entities] [LPA_L1]  LEFT JOIN [XXX].[dbo].[Users] [LPA_L2]  ON  [LPA_L1].[Id]=[LPA_L2].[Id]) WHERE ( ( ( [LPA_L2].[Id] IS NOT NULL)))

Why the first query behaves like that?

Thank you!

P.S.: using the 5.0.7

Findev
User
Posts: 103
Joined: 08-Dec-2014
# Posted on: 25-Oct-2016 12:59:40   

Actually there is more, as I mentioned earlier, uncommenting the Type assignment "fixed" it, it was fixed maybe because the Type field was being inherited. If I try to fetch User table specific value, like user type:


Type = UserFields.UserType.ToValue<byte>(),

I get unfiltered LEFT JOIN again...


SELECT [LPA_L2].[Id], [LPA_L2].[Type] AS [UserType] FROM ( [XXX].[dbo].[Entities] [LPA_L1]  LEFT JOIN [XXX].[dbo].[Users] [LPA_L2]  ON  [LPA_L1].[Id]=[LPA_L2].[Id])

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 25-Oct-2016 20:43:57   

Trying to reproduce it...

Base (SuperType/Abstract)

BaseId (PK) BaseName BaseType

User (SubType) :Base

UserId (PK, FK) UserName UserType

User is the qf source.

Fetching BaseName or BaseType => type filter is generated. (success) Fetching UserName or UserType => type filter is **not **generated. (fail)

Trying mix of fields from SuperType and SubType, **as long as there is a field from the SuperType => => type filter is generated. (success) ** Fetching UserFields.Id_Base => type filter is generated. (success) Fetching UserFields.Id (UserId is mapped to Id) => type filter is **not **generated. (fail)

We will look further into it.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 27-Oct-2016 12:07:49   

In your query with just the id field, it doesn't add a type filter, as ID is inherited from the base type. This means all subtypes including the base type match, so no type filter is added (as it will always result in true).

The 'type' field is a user field, so it needs to filter on user, which it does. As you haven't specified the hierarchy for 'type' field, I assume this is a field in the User table?

The other queries are odd though.

Please use code in a normal .net project, as the linqpad driver might contain an outdated ormsupportclasses dll (if in doubt, copy the latest ormsupportclasses dll into the driver folder of linqpad.

We'll look into why the type filter isn't always added at the right spot or is negated. You can filter on type by using a Where() clause with _EntityName_Entity.GetTypeFilter() as argument.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 27-Oct-2016 13:36:03   

I can reproduce it with a query in the low level api too: (Employee is root, has as subtypes Clerk and Manager. Manager has as subtype Boardmember. 4 entities are in the DB, 2 clerks, 1 manager and 1 boardmember. ID, Name fields are inherited from Employee)


[Test]
public void DynamicListAutoTypeFilterAddTest4()
{
    using(var adapter = new DataAccessAdapter())
    {
        var toDelete = new EntityCollection();
        UnitOfWork2 uow = CreateTestBatchForSave2(ref toDelete);
        try
        {
            uow.Commit(adapter, true);

            var fields = new ResultsetFields(1);
            fields.DefineField(ClerkFields.Id, 0);

            var results = new DataTable();
            adapter.FetchTypedList(fields, results, null);
            Assert.AreEqual(2, results.Rows.Count);
        }
        finally
        {
            adapter.DeleteEntityCollection(toDelete);
        }
    }
}

This results in 4 rows (manager, boardmember ID's are also fetched), no filter is applied


SELECT [LPA_L2].[ClerkID] AS [Id]
FROM   ( [InheritanceOne].[dbo].[Employee] [LPA_L1]
         LEFT JOIN [InheritanceOne].[dbo].[Clerk] [LPA_L2]
             ON [LPA_L1].[EmployeeID] = [LPA_L2].[ClerkID]) 

Using a non-pk field:


[Test]
public void DynamicListAutoTypeFilterAddTest4()
{
    using(var adapter = new DataAccessAdapter())
    {
        var toDelete = new EntityCollection();
        UnitOfWork2 uow = CreateTestBatchForSave2(ref toDelete);
        try
        {
            uow.Commit(adapter, true);

            var fields = new ResultsetFields(1);
            fields.DefineField(ClerkFields.Name, 0);

            var results = new DataTable();
            adapter.FetchTypedList(fields, results, null);
            Assert.AreEqual(2, results.Rows.Count);
        }
        finally
        {
            adapter.DeleteEntityCollection(toDelete);
        }
    }
}

Results in 2 rows, even though 'Name' is inherited from Employee, it applies the type filter here:


SELECT [LPA_L1].[Name]
FROM   ( [InheritanceOne].[dbo].[Employee] [LPA_L1]
         LEFT JOIN [InheritanceOne].[dbo].[Clerk] [LPA_L2]
             ON [LPA_L1].[EmployeeID] = [LPA_L2].[ClerkID])
WHERE  ((([LPA_L2].[ClerkID] IS NOT NULL))) 

this is the same in QuerySpec & linq. It's indeed strange that this happens, also when I refer to the base type field in queryspec/low level API it does apply the filter while it shouldn't do that.

Looking into it.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 27-Oct-2016 16:34:19   

It took a bit of work, but we fixed it. There are quite a few edge cases to take care of, but we think we have them covered now. Funny how fixing this bug shows a unit test testing something else actually had the wrong values to test for. The issue was in the code since before 2008, and it's one of these 'this has never worked properly, why has never anyone including ourselves run into this!?' issues, but it's all working OK now simple_smile

We uploaded a new 5.0.8 hotfix build which contains the fixed runtime. This should give the proper queries you were after.

Frans Bouma | Lead developer LLBLGen Pro
Findev
User
Posts: 103
Joined: 08-Dec-2014
# Posted on: 28-Oct-2016 09:31:02   

Hi,

I'm glad we managed to address this issue simple_smile I think I've run into it before a couple of times and just "tweaked" the query to work without going much into details why it happened in the first place simple_smile

Thank you!