Query returns wrong result after I reworked inheritance

Posts   
 
    
Findev
User
Posts: 103
Joined: 08-Dec-2014
# Posted on: 12-Jan-2015 19:08:03   

Hi,

let me clarify I don't have a test suite for that, it's just that I mentioned that old functionality is not working properly anymore.

I had a Users table which had "Target Per Hierarchy" also for CompanyUser.


public bool Exists( Guid id )
        {
            if (id == Guid.Empty)
            {
                throw new ArgumentException( "'id' is required" );
            }

            return
                QuerySpecHelper.QueryScalarViaFactory<bool>( qf => qf.Create().Select( qf.User.Select( UserFields.Id ).Where( UserFields.Id == id ).Any() ) );
        
        }

this was the method that checked if user exists. A week ago I redesigned the db structure. I introduced Entities table. Removed all inheritance from Users table (had to because LLBLGen doesn't support mixed hierarchy for the single chain, that's fine). Added new table CompanyUsers. Now CompanyUsers inherits from Users and Users inherits from Entities (obviously everything via Target Per Entity). After that change all methods similar to the aforementioned return "false" in UserRepository, always. I assume this affects all other entities that now also inherit from Entities. Entity is made abstract in the designer. Other repositories for entities that don't have hierarchy work just fine. If I remember correctly when I copied the generated SQL statement from ORM Profiler into SQL Management Studio it actually returned the user's id. It felt like LLBLGen's runtime just didn't pick it up.

So I fixed that method like that:


public bool Exists( Guid id )
        {
            if (id == Guid.Empty)
            {
                throw new ArgumentException( "'id' is required" );
            }

            return QuerySpecHelper.QueryScalarViaFactory<bool>( qf => qf.Create().Select( qf.User.Any( UserFields.Id == id ) ) );
        }

Could you please clarify if my original method was wrong or is there anything special I need to know since I have >2 levels of inheritance?

Also I noticed that the way I used to count products (which now inherits from Entities) also changed (Product entity just like EntityEntity is also abstract. Product's concrete types are Furniture etc.):


return QuerySpecHelper.QueryScalarViaFactory<int>(
                qf =>
                {
                    var q = GetForQuery( qf, companyId, publishedFrom, publishedUntil, isDeletedCondition );

                    return qf.Create().Select( q.OfEntityType<T>().CountDistinct( ProductFields.Id_EntityBase ) );
                } );


I've never had to use anything like xxx.Id_EntityBase, but if I use just xxx.Id I get 0.

If you are wondering what is QueryScalarViaFactory, I made custom wrappers over QueryFactory and DataAccessAdaptor for most use cases like:


public T QueryScalarViaFactory<T>(Func<QueryFactory, DynamicQuery> selector)
        {
            using (var adapter = new TDataAccessAdapterBase())
            {
                var q = selector(new QueryFactory());
                return adapter.FetchScalar<T>(q);
            }
        }
.....

P.S.: LLBLGen 4.2, SQL Server 2012

P.S.S.: as I said I'm writing this after I've changed those methods because I needed to publish ASAP thus I don't really have an isolated test suite. Just wanted to confirm that this is not a bug and/or that I'm not doing wrong queries when it comes to inherited types.

Thank you!

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 12-Jan-2015 23:59:02   

Could you please share the SQL produced for each query?

Findev
User
Posts: 103
Joined: 08-Dec-2014
# Posted on: 13-Jan-2015 01:39:51   

Hi,

thank you for replying! It is late and I hope I'm getting this correctly simple_smile

After I introduced the hierarchy changes, the old method now produces:


SELECT TOP(1 /* @p7 */) CASE
                  WHEN CASE
                         WHEN EXISTS
                              (SELECT [LPA_L2].[Id]
                               FROM   ( [XXX].[dbo].[Entities]
                                        LEFT JOIN [XXX].[dbo].[Users]
                                            ON [XXX].[dbo].[Entities].[Id] = [XXX].[dbo].[Users].[Id])
                               WHERE  (([LPA_L2].[Email] = 'some.email@company.com' /* @p1 */))) THEN 1
                         ELSE 0
                       END = 1 THEN 1 /* @p3 */
                  ELSE 0 /* @p5 */
                END AS [LLBLV_1]
FROM   ( [XXX].[dbo].[Entities] [LPA_L1]
         LEFT JOIN [XXX].[dbo].[Users] [LPA_L2]
             ON [LPA_L1].[Id] = [LPA_L2].[Id])


which returns 0 - and that's wrong.

After refactoring, the generated and correctly working SQL is:


SELECT TOP(1 /* @p7 */) CASE
                  WHEN CASE
                         WHEN EXISTS
                              (SELECT [LPA_L1].[F2_0]
                               FROM   (SELECT [XXX].[dbo].[Entities].[DateCreated]   AS [F2_0],
                                              [XXX].[dbo].[Entities].[DateModified] AS [F2_1],
                                              [XXX].[dbo].[Entities].[Description]   AS [F2_2],
                                              [XXX].[dbo].[Entities].[ClusteredId]   AS [F2_3],
                                              [XXX].[dbo].[Entities].[Id]             AS [F2_4],
                                              [XXX].[dbo].[Entities].[Ingress]       AS [F2_5],
                                              [XXX].[dbo].[Entities].[MediaFilename]   AS [F2_6],
                                              [XXX].[dbo].[Entities].[Type]         AS [F2_7],
                                              [XXX].[dbo].[Users].[Id]               AS [F8_8],
                                              [XXX].[dbo].[Users].[Birthday]           AS [F8_9],
                                              [XXX].[dbo].[Users].[City]               AS [F8_10],
                                              [XXX].[dbo].[Users].[Country]         AS [F8_11],
                                              [XXX].[dbo].[Users].[Culture]         AS [F8_12],
                                              [XXX].[dbo].[Users].[DateLastActivity]   AS [F8_13],
                                              [XXX].[dbo].[Users].[DateLastLogin]     AS [F8_14],
                                              [XXX].[dbo].[Users].[Email]             AS [F8_15],
                                              [XXX].[dbo].[Users].[FirstName]         AS [F8_16],
                                              [XXX].[dbo].[Users].[Gender]           AS [F8_17],
                                              [XXX].[dbo].[Users].[IsActivated]     AS [F8_18],
                                              [XXX].[dbo].[Users].[IsBlocked]         AS [F8_19],
                                              [XXX].[dbo].[Users].[LastName]           AS [F8_20],
                                              [XXX].[dbo].[Users].[LoginAttempts]     AS [F8_21],
                                              [XXX].[dbo].[Users].[PasswordHash]       AS [F8_22],
                                              [XXX].[dbo].[Users].[Phone]             AS [F8_23],
                                              [XXX].[dbo].[Users].[PostalCode]       AS [F8_24],
                                              [XXX].[dbo].[Users].[School]           AS [F8_25],
                                              [XXX].[dbo].[Users].[StreetAddress]     AS [F8_26],
                                              [XXX].[dbo].[Users].[TimeZoneId]       AS [F8_27],
                                              [XXX].[dbo].[Users].[ClusteredId]     AS [F8_28],
                                              [XXX].[dbo].[Users].[Type]               AS [F8_29],
                                              [XXX].[dbo].[CompanyUsers].[Id]         AS [F7_30],
                                              [XXX].[dbo].[CompanyUsers].[CompanyId]   AS [F7_31],
                                              [XXX].[dbo].[CompanyUsers].[ClusteredId] AS [F7_32]
                                       FROM   (( [XXX].[dbo].[Entities]
                                                 LEFT JOIN [XXX].[dbo].[Users]
                                                     ON [XXX].[dbo].[Entities].[Id] = [XXX].[dbo].[Users].[Id])
                                               LEFT JOIN [XXX].[dbo].[CompanyUsers]
                                                   ON [XXX].[dbo].[Users].[Id] = [XXX].[dbo].[CompanyUsers].[Id])
                                       WHERE  (([XXX].[dbo].[Users].[Email] = 'some.email@company.com' /* @p1 */))) [LPA_L1]) THEN 1
                         ELSE 0
                       END = 1 THEN 1 /* @p3 */
                  ELSE 0 /* @p5 */
                END AS [LLBLV_1]
FROM   [XXX].[dbo].[Entities]


you can actually see that it fetches all the fields, whereas my original one was fetching only Id field. I saw it generating similar big query, so back then I decided that EXISTS can be optimized and limited to just 1 field. Apparently after hierarchy changes this "optimization" wants more simple_smile

While writing this I decided to change the query and once more test:


return QuerySpecHelper.QueryScalarViaFactory<bool>(qf => qf.Create().Select(qf.User.Select(UserFields.Id_EntityBase).Where(UserFields.Email == email).Any()));

and this works correctly and produces:


SELECT TOP(1 /* @p7 */) CASE
                  WHEN CASE
                         WHEN EXISTS
                              (SELECT [XXX].[dbo].[Entities].[Id]
                               FROM   ( [XXX].[dbo].[Entities]
                                        LEFT JOIN [XXX].[dbo].[Users]
                                            ON [XXX].[dbo].[Entities].[Id] = [XXX].[dbo].[Users].[Id])
                               WHERE  (([XXX].[dbo].[Users].[Email] = 'some.email@company.com' /* @p1 */))) THEN 1
                         ELSE 0
                       END = 1 THEN 1 /* @p3 */
                  ELSE 0 /* @p5 */
                END AS [LLBLV_1]
FROM   [XXX].[dbo].[Entities]


Apparently if I replace .Id with .Id_EntityBase, similarly to the issue I had with counting products - everything resolves. Well, I'd definitely like to know more about that xxx_EntityBase, apparently old queries may stop working correctly after hierarchy changes, I'd rather be aware of that...

Thank you!

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 13-Jan-2015 02:17:19   

What's the exact runtime library version/build no. you are using? Please refer to the forum guidelines to know how to get the correct number.

The Id_EntityBase, is a rename for the Id field of the super entity, renamed to avoid conflict with the Id field of the sub entity.

Findev
User
Posts: 103
Joined: 08-Dec-2014
# Posted on: 13-Jan-2015 02:22:22   

Walaa wrote:

What's the exact runtime library version/build no. you are using? Please refer to the forum guidelines to know how to get the correct number.

The Id_EntityBase, is a rename for the Id field of the super entity, renamed to avoid conflict with the Id field of the sub entity.

SD.LLBLGen.Pro.ORMSupportClasses.dll File version: 4.2.14.701 Product version: 4.2.14.0701

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 13-Jan-2015 02:30:46   

Could you please try the latest build?

Findev
User
Posts: 103
Joined: 08-Dec-2014
# Posted on: 13-Jan-2015 22:00:39   

Walaa wrote:

Could you please try the latest build?

Will do later this week and report back. Thank you!

Findev
User
Posts: 103
Joined: 08-Dec-2014
# Posted on: 07-Mar-2015 15:37:50   

Walaa wrote:

Could you please try the latest build?

Sorry for replying so late. I just updated the LLBLGen Pro to: File version: 4.2.15.218 Product version: 4.2.15.0218

it generates exactly the same query as the old 4.2.14.701, so upgrading didn't resolve anything simple_smile

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 10-Mar-2015 07:14:36   

Could you please provide/attach a small repro solution?