Anyone got a better way of doing this?

SELECT TOP 20 a.ApplicationId ,l.SysLastUpdateDt as 'ProcessedDt' ,c.Title + ' ' + c.Firstname + ' ' + c.Surname as 'CustomerName' ,l.VehicleYear + ' ' + lvm.VehicleMakeID + ' ' + lvm.Descr as 'Vehicle' ,COALESCE((SELECT TOP 1 1 FROM LoanFinanceContracts WHERE LoanFinanceId IN (SELECT LoanFinanceId FROM LoanFinance WHERE ApplicationId = a.ApplicationId)), 0) as 'ContractIssued' ,COALESCE(p.Released, 0) as 'PayoutReleased' FROM [Application] a INNER JOIN Loan l ON a.ApplicationId = l.ApplicationId INNER JOIN dbo.Customer c ON a.CustomerID = c.CustomerID LEFT JOIN dbo.LuVehicleModel lvm ON l.VehicleModelID = lvm.VehicleModelID LEFT JOIN Payout p ON a.ApplicationId = p.ApplicationId WHERE DealerID = 4918 AND l.FinanceInstResultID = 1 -- Approved ORDER BY a.ApplicationId DESC

Reproduced as the following (which works)...

            ResultsetFields fields = new ResultsetFields(6);
            fields.DefineField(ApplicationFields.ApplicationId, 0);
            fields.DefineField(LoanFields.SysLastUpdateDt, 1, "ProcessedDt");

            fields.DefineField(LoanFields.SysLastUpdateDt, 2, "CustomerName");
            DbFunctionCall exp = new DbFunctionCall("{0} + ' ' + {1} + ' ' + {2}",
                                 new object[] { CustomerFields.Title, CustomerFields.FirstName, CustomerFields.Surname });
            fields[2].ExpressionToApply = exp;

            fields.DefineField(LoanFields.VehicleYear, 3, "Vehicle");
            DbFunctionCall exp2 = new DbFunctionCall("{0} + ' ' + {1} + ' ' + {2}",
                                 new object[] { LoanFields.VehicleYear, LuVehicleModelFields.VehicleMakeId, LuVehicleModelFields.Descr });
            fields[3].ExpressionToApply = exp2;

            fields.DefineField(LoanFields.ApplicationId, 4, "ContractIssued");
            DbFunctionCall exp3 = new DbFunctionCall("COALESCE((SELECT TOP 1 1 FROM [Foundation].[dbo].[LoanFinanceContracts] WHERE LoanFinanceId IN (SELECT LoanFinanceId FROM [dbo].[LoanFinance] WHERE ApplicationId = {0})), 0)",
                                 new object[] { ApplicationFields.ApplicationId });
            fields[4].ExpressionToApply = exp3;

            fields.DefineField(PayoutFields.Released, 5, "PayoutReleased");
            DbFunctionCall exp4 = new DbFunctionCall("COALESCE({0}, 0)", new object[] { PayoutFields.Released });
            fields[5].ExpressionToApply = exp4;

            IRelationPredicateBucket filterBucket = new RelationPredicateBucket();
            filterBucket.Relations.Add(ApplicationEntity.Relations.LoanEntityUsingApplicationId);
            filterBucket.Relations.Add(ApplicationEntity.Relations.CustomerEntityUsingCustomerId);
            filterBucket.Relations.Add(LoanEntity.Relations.LuVehicleModelEntityUsingVehicleModelId, JoinHint.Left);
            filterBucket.Relations.Add(ApplicationEntity.Relations.PayoutEntityUsingApplicationId, JoinHint.Left);

            filterBucket.PredicateExpression.Add(LoanFields.DealerId == dealerId);
            filterBucket.PredicateExpression.Add(LoanFields.FinanceInstResultId == 1);

            ISortExpression sort = new SortExpression();
            sort.Add(ApplicationFields.ApplicationId | SortOperator.Descending);

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            { 
                DataTable list = new DataTable();

                adapter.FetchTypedList(fields, list, filterBucket, 20, sort, false);

                return list;
            }

I have previously used the expression below for customer name but it's not very readable.

            Expression exp = new Expression(
                        EntityFieldFactory.Create(CustomerFieldIndex.Title)
                        , ExOp.Add
                        , new Expression(" ", ExOp.Add
                        , new Expression(EntityFieldFactory.Create(CustomerFieldIndex.FirstName), ExOp.Add
                        , new Expression(" ", ExOp.Add, EntityFieldFactory.Create(CustomerFieldIndex.Surname)))));

fields[2].ExpressionToApply = exp;

Anyway... one thing I don't like is I have to fully qualify the item in the subselect or it throws an exception - "[Foundation].[dbo].[LoanFinanceContracts] ". Is there a way to dynamically add the db name and owner - even if it's just by adding it to the string from some available global variable?

Thanks Sean

You can grab any entityField of any entity and use its SourceCatalogName and SourceSchemaName properties.