Inconsistant Data Returned using FetchUsingPK

Posts   
 
    
MJ_01
User
Posts: 17
Joined: 16-Jun-2017
# Posted on: 06-Jan-2018 00:30:59   

// Code is generated using LLBLGen Pro version: 3.5 // Code is generated using templates: SD.TemplateBindings.SharedTemplates.NET20 // Templates vendor: Solutions Design.

I've come across a very frustrating bug. I believe it to be a bug at least. When running the attached code, I get the following results:

**True :: 34 True :: null True :: 34

True :: null

True :: 34 True :: 34 True :: 34 True :: 34**

Is there a fix for this? Is this an issue in LLBLGen Pro v5.3? Any help is much appreciated.

Here is my code:

            UserEntity user = new UserEntity();
            user.FetchUsingPK(4);

            IncludeFieldsList userFields = new IncludeFieldsList();
            userFields.Add(UserFields.FirstName);
            userFields.Add(UserFields.LastName);
            userFields.Add(UserFields.IsPriorityProcessor);


            IncludeFieldsList billTypeIncludes = new IncludeFieldsList();
            billTypeIncludes.Add(BillTypeFields.BillTypeDescription);

            IncludeFieldsList clientIncludes = new IncludeFieldsList();
            clientIncludes.Add(ClientFields.ClientName);

            IncludeFieldsList vendorRegionsInclude = new IncludeFieldsList();
            vendorRegionsInclude.Add(VendorRegionFields.VendorRegionDescription);

            PrefetchPath userPaths = new PrefetchPath(EntityType.UserEntity);
            userPaths.Add(UserEntity.PrefetchPathBillType, billTypeIncludes);
            userPaths.Add(UserEntity.PrefetchPathClientAssignment, clientIncludes);
            userPaths.Add(UserEntity.PrefetchPathClientToExclude, clientIncludes);
            userPaths.Add(UserEntity.PrefetchPathVendorRegion, vendorRegionsInclude);

            Console.Write(user.FetchUsingPK(user.IdUser, userPaths, null, userFields) + " :: ");
            Console.WriteLine(user.IdClient?.ToString() ?? "null");

            Console.Write(user.FetchUsingPK(user.IdUser, userPaths, null, userFields) + " :: ");
            Console.WriteLine(user.IdClient?.ToString() ?? "null");

            Console.Write(user.FetchUsingPK(user.IdUser, userPaths, null, userFields) + " :: ");
            Console.WriteLine(user.IdClient?.ToString() ?? "null");

            Console.Write(user.FetchUsingPK(user.IdUser, userPaths, null, userFields) + " :: ");
            Console.WriteLine(user.IdClient?.ToString() ?? "null");

            Console.WriteLine("======================================");

            Console.Write(user.FetchUsingPK(user.IdUser, null, null, userFields) + " :: ");
            Console.WriteLine(user.IdClient?.ToString() ?? "null");

            Console.Write(user.FetchUsingPK(user.IdUser, null, null, userFields) + " :: ");
            Console.WriteLine(user.IdClient?.ToString() ?? "null");

            Console.Write(user.FetchUsingPK(user.IdUser, null, null, userFields) + " :: ");
            Console.WriteLine(user.IdClient?.ToString() ?? "null");

            Console.Write(user.FetchUsingPK(user.IdUser, null, null, userFields) + " :: ");
            Console.WriteLine(user.IdClient?.ToString() ?? "null");
daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 06-Jan-2018 08:03:48   

Hi MJ,

I tried various combinations, using Northwind and I can't reproduce what your problem. All following tests passed:

[TestMethod]
public void FetchUsingPKAndPaths()
{
    var order = new OrderEntity();

    var path = new PrefetchPath(EntityType.OrderEntity);
    path.Add(OrderEntity.PrefetchPathCustomer);
    path.Add(OrderEntity.PrefetchPathEmployee);

    order.FetchUsingPK(10248, path);
    Assert.IsNotNull(order.CustomerId, "Try #1");

    order.FetchUsingPK(10248, path);
    Assert.IsNotNull(order.CustomerId, "Try #2");

    order.FetchUsingPK(10248, path);
    Assert.IsNotNull(order.CustomerId, "Try #3");
}

[TestMethod]
public void FetchUsingPKAndPaths2()
{
    var order = new OrderEntity();

    var orderIncludeFields = new IncludeFieldsList();
    orderIncludeFields.Add(OrderFields.OrderDate);
    orderIncludeFields.Add(OrderFields.ShipCountry);
    orderIncludeFields.Add(OrderFields.ShipCity);

    var path = new PrefetchPath(EntityType.OrderEntity);
    path.Add(OrderEntity.PrefetchPathCustomer);
    path.Add(OrderEntity.PrefetchPathEmployee);

    order.FetchUsingPK(10248,  path, null, orderIncludeFields);
    Assert.IsNotNull(order.CustomerId, "Try #1");

    order.FetchUsingPK(10248, path);
    Assert.IsNotNull(order.CustomerId, "Try #2");

    order.FetchUsingPK(10248, path);
    Assert.IsNotNull(order.CustomerId, "Try #3");
}

[TestMethod]
public void FetchUsingPKAndPaths3()
{
    var order = new OrderEntity();

    var orderIncludeFields = new IncludeFieldsList();
    orderIncludeFields.Add(OrderFields.OrderDate);
    orderIncludeFields.Add(OrderFields.ShipCountry);
    orderIncludeFields.Add(OrderFields.ShipCity);

    var customerIncludeFields = new IncludeFieldsList();
    customerIncludeFields.Add(CustomerFields.CompanyName);

    var path = new PrefetchPath(EntityType.OrderEntity);
    path.Add(OrderEntity.PrefetchPathCustomer, customerIncludeFields);          

    order.FetchUsingPK(10248, path, null, orderIncludeFields);
    Assert.IsNotNull(order.CustomerId, "Try #1");

    order.FetchUsingPK(10248, path);
    Assert.IsNotNull(order.CustomerId, "Try #2");

    order.FetchUsingPK(10248, path);
    Assert.IsNotNull(order.CustomerId, "Try #3");
}

[TestMethod]
public void FetchUsingPKAndPaths4()
{
    var order = new OrderEntity();

    var orderIncludeFields = new IncludeFieldsList();
    orderIncludeFields.Add(OrderFields.OrderDate);
    orderIncludeFields.Add(OrderFields.ShipCountry);
    orderIncludeFields.Add(OrderFields.ShipCity);

    var customerIncludeFields = new IncludeFieldsList();
    customerIncludeFields.Add(CustomerFields.CompanyName);

    var employeeIncludeFields = new IncludeFieldsList();
    employeeIncludeFields.Add(EmployeeFields.FirstName);

    var path = new PrefetchPath(EntityType.OrderEntity);
    path.Add(OrderEntity.PrefetchPathCustomer, customerIncludeFields);
    path.Add(OrderEntity.PrefetchPathEmployee, employeeIncludeFields);

    order.FetchUsingPK(10248, path, null, orderIncludeFields);
    Assert.IsNotNull(order.CustomerId, "Try #1");

    order.FetchUsingPK(10248, path);
    Assert.IsNotNull(order.CustomerId, "Try #2");

    order.FetchUsingPK(10248, path);
    Assert.IsNotNull(order.CustomerId, "Try #3");
}

[TestMethod]
public void FetchUsingPKAndPaths5()
{
    var order = new OrderEntity();
    order.FetchUsingPK(10248);

    var orderIncludeFields = new IncludeFieldsList();
    orderIncludeFields.Add(OrderFields.OrderDate);
    orderIncludeFields.Add(OrderFields.ShipCountry);
    orderIncludeFields.Add(OrderFields.ShipCity);

    var customerIncludeFields = new IncludeFieldsList();
    customerIncludeFields.Add(CustomerFields.CompanyName);

    var employeeIncludeFields = new IncludeFieldsList();
    employeeIncludeFields.Add(EmployeeFields.FirstName);

    var path = new PrefetchPath(EntityType.OrderEntity);
    path.Add(OrderEntity.PrefetchPathCustomer, customerIncludeFields);
    path.Add(OrderEntity.PrefetchPathEmployee, employeeIncludeFields);

    order.FetchUsingPK(order.OrderId, path, null, orderIncludeFields);
    Assert.IsNotNull(order.CustomerId, "Try #1");

    order.FetchUsingPK(order.OrderId, path);
    Assert.IsNotNull(order.CustomerId, "Try #2");

    order.FetchUsingPK(order.OrderId, path);
    Assert.IsNotNull(order.CustomerId, "Try #3");
}

I'm using LLBLGen RTL 3.5.14.0113.

  • What LLBLGen build are you using?
  • What is the setup of your tables? (DDL script?)
  • Is inheritance hierarchy involved?
  • Can you reproduce it using Northwind?
  • What is user.ClientId and how it relates to the prefetched entities?
David Elizondo | LLBLGen Support Team
MJ_01
User
Posts: 17
Joined: 16-Jun-2017
# Posted on: 08-Jan-2018 22:52:36   

- What LLBLGen build are you using? 3.5.0.0.01082013

- What is the setup of your tables? (DDL script?) CREATE TABLE [dbo].[User]( ... [IdClient] [int] NULL, ... ) ... ALTER TABLE [dbo].[User] WITH CHECK ADD CONSTRAINT [FK_User_Client] FOREIGN KEY([IdClient]) REFERENCES [dbo].[Client] ([IdClient]) GO

ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Client] GO ...

- Is inheritance hierarchy involved? no, I think

- Can you reproduce it using Northwind? not sure. I'll look into.

- What is user.ClientId and how it relates to the prefetched entities? client table to user table = 1 to many. User.IdClient is nullable.

Hope I got those right. I'm not the best at sql.

MJ_01
User
Posts: 17
Joined: 16-Jun-2017
# Posted on: 08-Jan-2018 23:09:58   

I did run a test and it runs as expected when the foreign is not nullable. We do expect to remove all nullable foreign keys from our database before the end of the year.

EDIT: I did find a work around.

            Console.Write(user.FetchUsingPK(user.IdUser, userPaths, null, userFields) + " :: ");
            Console.WriteLine(user.IdClient?.ToString() ?? "null");
            Console.WriteLine(user.AlreadyFetchedClientAssignment);
            
            user.AlreadyFetchedClientAssignment = false;

            Console.Write(user.FetchUsingPK(user.IdUser, userPaths, null, userFields) + " :: ");
            Console.WriteLine(user.IdClient?.ToString() ?? "null");
            Console.WriteLine(user.AlreadyFetchedClientAssignment);

With the addition of

user.AlreadyFetchedClientAssignment = false;

before any subsequent call, I get the results that I expect. Kind of a bummer but I'll take it till we update to the latest version of llblgen. Even if this issue is not resolved in the latest version, we are moving away from nullable foreign keys which seem to be the only variables affect by this.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 09-Jan-2018 15:02:19   

for buildversion, you should look at the runtime build version. https://www.llblgen.com/Documentation/3.5/LLBLGen%20Pro%20RTF/hh_goto.htm#Using%20the%20generated%20code/gencode_compiling.htm#requestionversion (at the bottom), can be done in windows explorer.

It's not recommended to re-use prefetch paths in different queries, at least rebuild them every time.

Frans Bouma | Lead developer LLBLGen Pro