Say I have two tables like this.

TBL_WORKREQUEST

ID - PK/INT/AUTOGENERATED TITLE - VARCHAR(30) GROUPID - INT (FK - TBL_GROUP.ID)

TBL_GROUP

ID - PK/INT/AUTOGENERATED TITLE - VARCHAR(30)

Now say I want to do a prefetch on a group part:

IPrefetchPath wrPreFetch = new PrefetchPath((int)EntityType.WorkRequestEntity); wrPreFetch.Add(WorkRequestEntity.PrefetchPathGroup);

WorkRequestCollection workCol = new WorkRequestCollection();

workCol.GetMulti(exp1,-1,null,null,wrPreFetch,-1,-1);

Say there are **ONLY **three groups ID (1,2,3), and say there are 500 work requests.

You expect the SQL generated to do something like GroupID IN (@GroupID1,@GroupID2,@GroupID3) @GroupID1 = 1 etc.. .

However it doesnt it does: GROUPID IN (@GroupID1,.............,@GroupID500)

In other words it pumping out massive SQL queries when it only need to retrieve the distinct group IDs. Do you follow?

In real scenario I have only 10 groups or so, however it's doing based on number of rows of main entity. Generated Sql query: Query: SELECT [sysdev].[tbl_Group].[GroupID] AS [GroupId], [sysdev].[tbl_Group].[GroupName], [sysdev].[tbl_Group].[Active], [sysdev].[tbl_Group].[DefaultDefTypeID] AS [DefaultDefTypeId] FROM [sysdev].[tbl_Group] WHERE ( ( [sysdev].[tbl_Group].[GroupID] IN (@GroupId1, @GroupId2, @GroupId3, @GroupId4, @GroupId5, @GroupId6, @GroupId7, @GroupId8, @GroupId9, @GroupId10, @GroupId11, @GroupId12, @GroupId13, @GroupId14, @GroupId15, @GroupId16, @GroupId17, @GroupId18, @GroupId19, @GroupId20, @GroupId21, @GroupId22, @GroupId23, @GroupId24, @GroupId25, @GroupId26, @GroupId27, @GroupId28, @GroupId29, @GroupId30, @GroupId31, @GroupId32, @GroupId33, @GroupId34, ... @GroupId1821, @GroupId1822, @GroupId1823, @GroupId1824, @GroupId1825, @GroupId1826, @GroupId1827, @GroupId1828, @GroupId1829, @GroupId1830, @GroupId1831, @GroupId1832))) Parameter: @GroupId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: <undefined value>. .... Parameter: @GroupId54 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: <undefined valu

Was it really necessary to post all the parameters? -- Otis

To which value did you set the DaoBase.ParameterisedPrefetchPathThreshold property? It seems like it was set to a very high value. Could you set it back to 50 please?

There was a bug in the prefetch path logic which didn't filter out duplicates in the IN clause. This bug was fixed in build 04292006. Please see: http://www.llblgen.com/pages/secure/ChangeLogBrowser.aspx and check llblgen pro 1.0.2005.1 -> runtime libraries 1.0.2005.1

Download the latest runtime libraries archive for 1.0.2005.1 to fix this issue.

Thanks Otis,

I now get this error (it previously worked with the bug of duplicates):

[ArgumentNullException: Key cannot be null. Parameter name: key] System.Collections.Hashtable.ContainsKey(Object key) +341 SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.FetchParameterisedPrefetchPath(IEntityCollection rootEntities, Int64 maxNumberOfItemsToReturn, IPrefetchPath prefetchPath, ITransaction containingTransaction) SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetMulti(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IEntityFactory entityFactoryToUse, IValidator validatorToUse, IPredicate selectFilter, IRelationCollection relations, IPrefetchPath prefetchPathToUse, Int32 pageNumber, Int32 pageSize) SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase.GetMulti(IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relations, IPrefetchPath prefetchPathToUse, Int32 pageNumber, Int32 pageSize) WRS.Web.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\wrsbeta\webform1.aspx.cs:83 System.Web.UI.Control.OnLoad(EventArgs e) System.Web.UI.Control.LoadRecursive() System.Web.UI.Page.ProcessRequestMain()

I think that's a bug in the runtime lib. I saw in your parameter values in the query that the values were NULL, so the code has to handle that but apparently doesn't. I'll check it out.

(btw, I don't know if the values should be NULL, if not, could you check that please (in the db)) ?

Hi Otis,

Yes there will be some values that will be null.

Thanks

Matt83uk wrote:

Hi Otis,

Yes there will be some values that will be null. Thanks

The code in the current routine is not taking into account null values, which is rather silly, as an PK can't be NULL, and an FK which is null will never result in a matching PK so they can always be excluded from the query.

I'll fix this in the runtime libs and it's then fixed in the next build, released in the coming days.

Hmm... the strangest thing is... I cant reproduce it in a unittest...

        [Test]
        public void PrefetchPathFetchTestWithNullFKFields()
        {
            PrefetchPath path = new PrefetchPath((int)EntityType.EmployeeEntity);
            path.Add(EmployeeEntity.PrefetchPathRegion_);

            EmployeeCollection employees = new EmployeeCollection();
            employees.GetMulti(null, path);

            Assert.AreEqual(9, employees.Count);
        }

This fetches employees and region entities, and 5 employees have NULL as value for RegionID, so this should crash the routine, but ... it doesn't. It passes for 4 regions the id's. Strange, I'll see if I made a mistake in my choice of test.

(edit). There's something odd going on in your code. In the routine, a check for valueField.IsNull is performed, which is true if the FK field is NULL in the DB. If the FK field is NULL in the DB, the CurrentValue property will be the default value for the type, in this case '0' because the type is an int. So even if for some obscure reason IsNull fails, it still wouldn't crash on ContainsKey as CurrentValue isn't null, it's 0.

So.. very strange. Could you elaborate a bit more about what you might have changed to the templates? (if applicable).