Hi there,

I am implementing the Adapter template group, and am running LLBLGen Pro Version 2.6 Final.

I am working with a SQL2000 database, which include two tables Tran01 – a list of transactions - and TranDet – a list of transaction details corresponding to a particular entry in Tran01. There is a 1:n relationship between the tables with the PK being Tran01.DocNo, and the FK being TranDet.DocNo. The data in the database is in quite a state - there is a considerable amount of white space in the entries indicated (and in most other entries!).

I am trying to retrieve a collection of TranDet entities that correspond to a specific Tran01entity. The result is achieved simply in SQL using a straightforward SQL statement without implementing trimming of any sort. In my solution, however, there is a mixed bag of confusion! Running the following Linq statement (an ideal situation because it retrieves all required data at once) returns nothing per Transaction01 entity:

            var transaction01List = (from tran in metaData.Tran01
                                 where tran.DocTot != 0
                                 select tran).WithPath(p => p.Prefetch(tran => tran.TranDetCollectionViaDocNo));

            foreach (var tran in transaction01List)
            {  
              System.Console.WriteLine(tran.TranDetCollectionViaDocNo.Count);
            }

The output is a list of 0s, even though there definitely are corresponding data in the database. (Before prefetching, I tried using a nested query which was also unsuccessful.)

The following however returns data, but the result is achieved after a much longer wait and seems inefficient:

            var transaction01List = (from tran in metaData.Tran01
                                 where tran.DocTot != 0
                                 select tran);

            foreach (var tran in transaction01List)
            {
                var check = from n in metaData.TranDet
                            where n.Tran01ViaDocNo.Equals(tran)
                            select n;

                System.Console.WriteLine(check.Count());
            }

This returns the required data, but as I mentioned takes considerably longer. I know that the corresponding entries in the two databases have differing amounts of white space (in Tran01.DocNo, there are fewer trailing spaces than in TranDet.DocNo) and the actual DocNo fields (substring ignoring the white space) in each table are identical.

Are the corresponding SQL statements generated in each of these cases executed differently? Why would it not work in the first case and work in the second? Do you have any suggestions or advice for me?

Thank you in advance

Ps. I've attached a copy of my naming construction preferences to clear up any confusion*

Could you also post the SQL that gets generated in each case ? SQL Profiler will capture this for you.

Thanks

Matt

Hi there,

Sorry about that!

In the first case, ie. using the prefetch path the following two SQL queries are generated:

exec sp_executesql N'SELECT [LPLA_1].[TRAN_NO] AS [TranNo], [LPLA_1].[DOC_NO] AS [DocNo], [LPLA_1].[T_DATE] AS [TDate], [LPLA_1].[LINK_DOC] AS [LinkDoc], [LPLA_1].[VIA] AS [Via], [LPLA_1].[GOODS_NO] AS [GoodsNo], [LPLA_1].[TH_REF] AS [ThRef], [LPLA_1].[OUR_REF] AS [OurRef], [LPLA_1].[ORDER_NO] AS [OrderNo], [LPLA_1].[DEL_NO] AS [DelNo], [LPLA_1].[DEL_DATE] AS [DelDate], [LPLA_1].[SALES_MAN] AS [SalesMan], [LPLA_1].[CUST_CODE] AS [CustCode], [LPLA_1].[CUST_NAME] AS [CustName], [LPLA_1].[ADD1] AS [Add1], [LPLA_1].[ADD2] AS [Add2], [LPLA_1].[ADD3] AS [Add3], [LPLA_1].[ADD4] AS [Add4], [LPLA_1].[VAT_NO] AS [VatNo], [LPLA_1].[DEL_CODE] AS [DelCode], [LPLA_1].[DEL1] AS [Del1], [LPLA_1].[DEL2] AS [Del2], [LPLA_1].[DEL3] AS [Del3], [LPLA_1].[DEL4] AS [Del4], [LPLA_1].[EXC_TOT] AS [ExcTot], [LPLA_1].[VAT_TOT] AS [VatTot], [LPLA_1].[DOC_TOT] AS [DocTot], [LPLA_1].[DIS_PER] AS [DisPer], [LPLA_1].[SET_DIS] AS [SetDis], [LPLA_1].[CR_NOTE] AS [CrNote], [LPLA_1].[PAY_TOT] AS [PayTot], [LPLA_1].[DIS_TOT] AS [DisTot], [LPLA_1].[REBATES] AS [Rebates], [LPLA_1].[PRINT] AS [Print], [LPLA_1].[POST] AS [Post], [LPLA_1].[DONE_BY] AS [DoneBy], [LPLA_1].[INV_REM] AS [InvRem], [LPLA_1].[ROWID] AS [Rowid] FROM [ATLAS_DEVELOPMENT].[dbo].[TRAN01] [LPLA_1]  WHERE ( ( ( ( ( [LPLA_1].[DOC_TOT] <> @DocTot1)))))',N'@DocTot1 float',@DocTot1=0

exec sp_executesql N'SELECT [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[DOC_NO] AS [DocNo], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[LINE_TYP] AS [LineTyp], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[ITEM_CODE] AS [ItemCode], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[DESC1] AS [Desc1], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[REM1] AS [Rem1], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[UNITS] AS [Units], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[QTY1] AS [Qty1], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[QTY2] AS [Qty2], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[TOT_QTY] AS [TotQty], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[SER_FROM] AS [SerFrom], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[SER_TO] AS [SerTo], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[COST] AS [Cost], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[PRICE] AS [Price], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[DIS_PER] AS [DisPer], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[DIS_AMT] AS [DisAmt], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[EXC_TOT] AS [ExcTot], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[VAT_CODE] AS [VatCode], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[VAT_PER] AS [VatPer], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[VAT_AMT] AS [VatAmt], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[LINE_TOT] AS [LineTot], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[SUPPLIER] AS [Supplier], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[SUP_INV] AS [SupInv], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[ORDER_NO] AS [OrderNo], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[NUM1] AS [Num1], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[NUM2] AS [Num2], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[STR1] AS [Str1], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[STR2] AS [Str2], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[DATE1] AS [Date1], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[DATE2] AS [Date2], [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[ROWID] AS [Rowid] FROM [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET]  WHERE ( [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET].[DOC_NO] IN (SELECT [LPLA_1].[DOC_NO] AS [DocNo] FROM [ATLAS_DEVELOPMENT].[dbo].[TRAN01] [LPLA_1]  WHERE ( ( ( ( ( ( [LPLA_1].[DOC_TOT] <> @DocTot1))))))))',N'@DocTot1 float',@DocTot1=0

In the second case - with the second query within the foreach loop - the following two SQL queries are the first two generated. Thereafter, there are multiple queries generated identical to the second query with the value of @DocNo1 changing in each case to account for the specific Tran01.DocNo.

exec sp_executesql N'SELECT [LPLA_1].[TRAN_NO] AS [TranNo], [LPLA_1].[DOC_NO] AS [DocNo], [LPLA_1].[T_DATE] AS [TDate], [LPLA_1].[LINK_DOC] AS [LinkDoc], [LPLA_1].[VIA] AS [Via], [LPLA_1].[GOODS_NO] AS [GoodsNo], [LPLA_1].[TH_REF] AS [ThRef], [LPLA_1].[OUR_REF] AS [OurRef], [LPLA_1].[ORDER_NO] AS [OrderNo], [LPLA_1].[DEL_NO] AS [DelNo], [LPLA_1].[DEL_DATE] AS [DelDate], [LPLA_1].[SALES_MAN] AS [SalesMan], [LPLA_1].[CUST_CODE] AS [CustCode], [LPLA_1].[CUST_NAME] AS [CustName], [LPLA_1].[ADD1] AS [Add1], [LPLA_1].[ADD2] AS [Add2], [LPLA_1].[ADD3] AS [Add3], [LPLA_1].[ADD4] AS [Add4], [LPLA_1].[VAT_NO] AS [VatNo], [LPLA_1].[DEL_CODE] AS [DelCode], [LPLA_1].[DEL1] AS [Del1], [LPLA_1].[DEL2] AS [Del2], [LPLA_1].[DEL3] AS [Del3], [LPLA_1].[DEL4] AS [Del4], [LPLA_1].[EXC_TOT] AS [ExcTot], [LPLA_1].[VAT_TOT] AS [VatTot], [LPLA_1].[DOC_TOT] AS [DocTot], [LPLA_1].[DIS_PER] AS [DisPer], [LPLA_1].[SET_DIS] AS [SetDis], [LPLA_1].[CR_NOTE] AS [CrNote], [LPLA_1].[PAY_TOT] AS [PayTot], [LPLA_1].[DIS_TOT] AS [DisTot], [LPLA_1].[REBATES] AS [Rebates], [LPLA_1].[PRINT] AS [Print], [LPLA_1].[POST] AS [Post], [LPLA_1].[DONE_BY] AS [DoneBy], [LPLA_1].[INV_REM] AS [InvRem], [LPLA_1].[ROWID] AS [Rowid] FROM [ATLAS_DEVELOPMENT].[dbo].[TRAN01] [LPLA_1]  WHERE ( ( ( ( [LPLA_1].[DOC_TOT] <> @DocTot1))))',N'@DocTot1 float',@DocTot1=0

exec sp_executesql N'SELECT DISTINCT TOP 1 COUNT(*) AS [LPAV_] FROM ( [ATLAS_DEVELOPMENT].[dbo].[TRAN01] [LPA_L1]  RIGHT JOIN [ATLAS_DEVELOPMENT].[dbo].[TRAN_DET] [LPA_L2]  ON  [LPA_L1].[DOC_NO]=[LPA_L2].[DOC_NO]) WHERE ( ( ( ( ( [LPA_L1].[DOC_NO] = @DocNo1)))))',N'@DocNo1 nvarchar(50)',@DocNo1=N'CDN01001                                        '

Thanks again!

Might sound like a daft question, but does running the second query from the first example in query analyser actually return the resultset that you expect - ie all of tran_det records for all of the tran records where doc_tot <> 0 ?

Just want to make sure the SQL is working as expected first...

Matt

Yes, running the query in query analyzer returns the expected resultset. This confused us this morning, because the query definitely returns the required data

Thanks for the prompt replies!

Are TRAN01.DOC_NO and TRAN_DET.DOC_NO the same data type in SQL...? Is the data consistently cased and trimmed in both tables...?

Just wondering if the SQL query is managing to join successfully but the LLBLGen code is failing to macth the DOC_NO's when it tries to construct the TRAN_DET collections.

Matt

The data in database is not in the best shape, so when comparing entries - in this case, the document numbers of each table - we definitely get differences. For example, in one case TRAN01.DOC_NO ='RET03422 ' and the corresponding TRAN_DET.DOC_NO ='RET03422 '. We received the database in quite a state, and most entries have this trailing white space.

Otherwise, the respective columns are of the same data types. The data is cased , but not trimmed (trimming is not likely to happen because changing the data could affect current systems in place).

Even though there is this difference with the length of the whitespace, the query within the foreach loop (detailed in the second part of my first post) completes successfully and returns the required data. So the LLBLGen code possibly failing to match the DOC_NOs in the construction of the TRAN_DET collections is specific to the prefetch query only?

Any suggestions?

Thanks again.

String comparison differs when done in SQL than in .NET. PrefetchPaths, does string comparing (.NET string comparing), which of-course doesn't trim trailing spaces.

Please check the following threads: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=5405 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14364

The last thread contains a work around.

Thanks. I'll look into the options that you've given me.

Thanks again for the prompt replies