Hi,

I would like to know how can I create relations at runtime?

I have these relations but I need one that I do not have in database. I need to create a relation with table UserModule and Batch.

RelationCollection _relation = new RelationCollection();    _relation.Add(TbBatchClassEntity.Relations.TbUserBatchClassEntityUsingCiBatchClass);
_relation.Add(TbUserEntity.Relations.TbUserBatchClassEntityUsingCiUser);
_relation.Add(TbUserEntity.Relations.TbUserModuleEntityUsingCiUser);
_relation.Add(TbBatchEntity.Relations.TbBatchClassEntityUsingCiBatchClass);

This code generate this query:

SELECT DISTINCT [dbo].[tb_batch].[ci_batch_class] AS [CiBatchClass],
    [dbo].[tb_batch].[ci_batch] AS [CiBatch],[dbo].[tb_batch].[nm_batch] AS [NmBatch],
    [dbo].[tb_batch].[dc_batch] AS [DcBatch],[dbo].[tb_batch].[ci_status] AS [CiStatus],
    [dbo].[tb_batch].[qt_expected_sheets] AS [QtExpectedSheets],
    [dbo].[tb_batch].[qt_expected_docs] AS [QtExpectedDocs],
    [dbo].[tb_batch].[qt_sheets] AS [QtSheets],[dbo].[tb_batch].[qt_images] AS [QtImages],
    [dbo].[tb_batch].[qt_docs] AS [QtDocs],[dbo].[tb_batch].[priority] AS [Priority],[dbo].[tb_batch].[ci_module] AS [CiModule],
    [dbo].[tb_batch].[qt_expected_folders] AS [QtExpectedFolders],
    [dbo].[tb_batch].[qt_rejected_items] AS [QtRejectedItems],
    [dbo].[tb_batch].[nm_last_workstation] AS [NmLastWorkstation],[dbo].[tb_batch].[qt_folders] AS [QtFolders] 
FROM (((( [dbo].[tb_batch_class] (nolock) 
    INNER JOIN [dbo].[tb_user_batch_class] (nolock) ON  [dbo].[tb_batch_class].[ci_batch_class]=[dbo].[tb_user_batch_class].[ci_batch_class]) 
    INNER JOIN [dbo].[tb_user] (nolock) ON  [dbo].[tb_user].[ci_user]=[dbo].[tb_user_batch_class].[ci_user]) 
    INNER JOIN [dbo].[tb_user_module] (nolock) ON  [dbo].[tb_user].[ci_user]=[dbo].[tb_user_module].[ci_user]) 
    INNER JOIN [dbo].[tb_batch] (nolock) ON  [dbo].[tb_batch_class].[ci_batch_class]=[dbo].[tb_batch].[ci_batch_class]) 
WHERE ( [dbo].[tb_batch].[ci_status] <> 5 And [dbo].[tb_user].[ci_user] = 52) 
ORDER BY [dbo].[tb_batch].[nm_batch] ASC

I need to generate this query... _ SELECT DISTINCT [dbo].[tb_batch].[ci_batch_class] AS [CiBatchClass],[dbo].[tb_batch].[ci_batch] AS [CiBatch],[dbo].[tb_batch].[nm_batch] AS [NmBatch],[dbo].[tb_batch].[dc_batch] AS [DcBatch],[dbo].[tb_batch].[ci_status] AS [CiStatus],[dbo].[tb_batch].[qt_expected_sheets] AS [QtExpectedSheets],[dbo].[tb_batch].[qt_expected_docs] AS [QtExpectedDocs],[dbo].[tb_batch].[qt_sheets] AS [QtSheets],[dbo].[tb_batch].[qt_images] AS [QtImages], [dbo].[tb_batch].[qt_docs] AS [QtDocs],[dbo].[tb_batch].[priority] AS [Priority], [dbo].[tb_batch].[ci_module] AS [CiModule], [dbo].[tb_batch].[qt_expected_folders] AS [QtExpectedFolders], [dbo].[tb_batch].[qt_rejected_items] AS [QtRejectedItems], [dbo].[tb_batch].[nm_last_workstation] AS [NmLastWorkstation],[dbo].[tb_batch].[qt_folders] AS [QtFolders] FROM (((( [dbo].[tb_batch_class] (nolock) INNER JOIN [dbo].[tb_user_batch_class] (nolock) ON [dbo].[tb_batch_class].[ci_batch_class]=[dbo].[tb_user_batch_class].[ci_batch_class]) INNER JOIN [dbo].[tb_user] (nolock) ON [dbo].[tb_user].[ci_user]=[dbo].[tb_user_batch_class].[ci_user]) INNER JOIN [dbo].[tb_user_module] (nolock) ON [dbo].[tb_user].[ci_user]=[dbo].[tb_user_module].[ci_user]) INNER JOIN [dbo].[tb_batch] (nolock) ON [dbo].[tb_batch_class].[ci_batch_class]=[dbo].[tb_batch].[ci_batch_class] and [dbo].[tb_batch].[ci_module]=[dbo].[tb_user_module].[ci_module]) WHERE ( [dbo].[tb_batch].[ci_status] <> 5 And [dbo].[tb_user].[ci_user] = 52) ORDER BY [dbo].[tb_batch].[nm_batch] ASC _ Tks.

PS: I posted this again because I checked the other erroneous.

I've adjusted the title and removed the other thread. Our support team will look into your query shortly.

I've also moved the thread to 'general' as it's not really General Chat material

Ok tks.

I think I resolved that now.

I used after this code

RelationCollection _relation = new RelationCollection();
_relation.Add(TbBatchClassEntity.Relations.TbUserBatchClassEntityUsingCiBatchClass);
_relation.Add(TbUserEntity.Relations.TbUserBatchClassEntityUsingCiUser);
_relation.Add(TbUserEntity.Relations.TbUserModuleEntityUsingCiUser);
_relation.Add(TbBatchEntity.Relations.TbBatchClassEntityUsingCiBatchClass);

criterio.AddWithAnd(new FieldCompareExpressionPredicate(
    EntityFieldFactory.Create(TbBatchFieldIndex.CiModule),
    ComparisonOperator.Equal,
    new Expression(EntityFieldFactory.Create(TbUserModuleFieldIndex.CiModule))));

This is working but if you have another way to do this I will be very "thankfull" ... ;-)

Thank you very much.