Joining "TT" records to regular DB records in BPMs

Experts' Corner
9

I re-wrote the BPM making sure to make ttTable the first and to drag the arrow from tt to the actual table

image
image.png687×648 33.9 KB

The LINQ (Code behind generated looks like this)

 private bool C001_QuerySizeCondition()
        {
            var query =
                from dbQuery in (
                    from rowABCCode in this.Db.ABCCode
                    where ((rowABCCode.Company == null
                        || rowABCCode.Company == ""
                        || rowABCCode.Company == (this.Session.CompanyID)))
                    select new
                        {
                            rowABCCode_Company = rowABCCode.Company,
                            rowABCCode_ABCCode1 = rowABCCode.ABCCode1,
                        }).AsEnumerable()
                join rowttABCCode in ds.ABCCode on new { Key0 = dbQuery.rowABCCode_Company.ToLowerInvariant(), Key1 = dbQuery.rowABCCode_ABCCode1.ToLowerInvariant() } equals new { Key0 = rowttABCCode.Company.ToLowerInvariant(), Key1 = rowttABCCode.ABCCode.ToLowerInvariant() }
                select 1;
            return query.Take(2).Count() >= (1);
        }

Which using SQL Profiler generated the following SQL on the server side

SELECT 
1 AS [C1], 
[Extent1].[Company] AS [Company], 
[Extent1].[ABCCode] AS [ABCCode]
FROM [Erp].[ABCCode] AS [Extent1]
WHERE (((N'' = [Extent1].[Company]) OR (([Extent1].[Company] = @p__linq__0) AND (@p__linq__0 IS NOT NULL)))

Note that the above will still return 1 record per item in the ABCCode table (1,1,1,) if this was PartTran with a million lines you’d get a million rows returned.

This was a test in 10.1.600.16 I will re-run this test in 10.2 shortly just in case that they addressed these in 10.2