Hello Support, I have a Question about dynamic List!

Here is my try

public static DataTable dynamiclist(EntityField2 Feld, int BenuterID)
       {
           DataAccessAdapter adapter = new DataAccessAdapter();
           ResultsetFields fields = new ResultsetFields(2);
           fields.DefineField(TblanlagehauptFields.Anlage, 0, Feld.Alias, "Anlage");
           fields.DefineField(TblanlagehauptFields.Anlage, 1, "Anzahl", "Anlage", AggregateFunction.Count);
           IRelationPredicateBucket bucket = new RelationPredicateBucket();
           //bucket.PredicateExpression.Add(TblabteilungFields.Fid  == BenuterID);
           IGroupByCollection groupByClause = new GroupByCollection();
           groupByClause.Add(fields[0]);
           DataTable dynamicList = new DataTable();
           adapter.FetchTypedList(fields, dynamicList, bucket, 0, null, true, groupByClause);
           return dynamicList;
}

I use dynamic list because I use dynamic group by

I need this Filter //bucket.PredicateExpression.Add(TblabteilungFields.Fid == BenuterID); How I'm doing wrong.

Christof

To be able to filter on a related entity, you must add the corresponding relation, i.e. the relations that links TblanlagehauptEntity to TblabteilungEntity. It should be (guess) something like this:

bucket.Relations.Add(TblanlagehauptEntity.Relations.TblabteilungUsing Fid);

Me isn't clearly how can I build the relation between TblanlagehauptEntity and TblabteilungEntity in the LLBLGen Designer , there is the TstamAbteilungEntity between them.

In the Attachment you can see the relations in SQL-Server Management Studio and in LLBLGen Designer

Here are the three Tables

CREATE TABLE [dbo].[TBLANLAGEHAUPT](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ANLAGE] [nvarchar](50) NULL,
    [STANDORT] [nvarchar](50) NULL,
    [BESCHREIBUNG] [nvarchar](250) NULL,
    [STATUS] [int] NULL,
    [UNTERLAGENBEFINDENSICH] [nvarchar](100) NULL,
    [Verantwortlicher] [nvarchar](50) NULL,
    [EMAILADRESSE] [nvarchar](100) NULL,
    [TELEFON] [nvarchar](50) NULL,
    [Baujahr] [nvarchar](10) NULL,
    [IDENTNUMMER] [nvarchar](150) NULL,
    [FREIENUMMER1] [int] NULL,
    [FREIENUMMER2] [int] NULL,
    [FREIERTEXT1] [nvarchar](255) NULL,
    [FREIERTEXT2] [nvarchar](255) NULL,
    [HistorieDrucken] [bit] NOT NULL,
    [Kategorie] [nvarchar](50) NULL,
    [GruppierungAus] [nvarchar](20) NOT NULL,
    [fKategorie] [smallint] NULL,
    [ABTEILUNG] [varchar](50) NULL,
    [UnterKategorie] [varchar](50) NULL,
    [FREIERTEXT3] [nvarchar](255) NULL,
    [FREIERTEXT4] [nvarchar](255) NULL,
    [FREIERTEXT5] [nvarchar](255) NULL,
    [FREIERTEXT6] [nvarchar](255) NULL,
    [FABTEILUNG] [int] NOT NULL,
 CONSTRAINT [PK_TBLANLAGEHAUPT] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[TBLABTEILUNG](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Abteilung] [nvarchar](50) NOT NULL,
    [fid] [smallint] NOT NULL,
    [ABTEILUNGID] [int] NOT NULL,
 CONSTRAINT [PK_TBLABTEILUNG] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[TSTAM_ABTEILUNG](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Abteilungsname] [nchar](200) NOT NULL,
    [AufAm] [date] NOT NULL,
    [AufVon] [nchar](100) NOT NULL,
 CONSTRAINT [PK_STAM_ABTEILUNG] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

I hope you can help me Thanks Christof

Hi Christof,

To be able to filter on a related entity, you must add the relations that make possible to reach from your fetched entity to your filtered entity. In this case you have a m:n intermediate entity (TstamAbteilung), so you should add it to the relations, then add the final entity from TstamAbteilung. Here is an approximate code:

...
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(TblangehauptEntity.Relations.TstamAbteilungEntityUsingFaistenlung);
bucket.Relations.Add(TstamAbteilungEntity.Relations.TblabteilungEntityUsingId);

bucket.PredicateExpression.Add(TblabteilungFields.Fid == BenuterID);
...

I have created an m:n Relation and then I use your Code

            
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(TblanlagehauptEntity.Relations.TstamAbteilungEntityUsingFabteilung);
bucket.Relations.Add(TstamAbteilungEntity.Relations.TblabteilungEntityUsingAbteilungid);
bucket.PredicateExpression.Add(TblabteilungFields.Fid == BenuterID);

The sql query is now

    Query: SELECT [Baujahr].[ANLAGE] AS [Anlage], COUNT([Baujahr].[ANLAGE]) AS [Anzahl] FROM (( [pruefbbm].[dbo].[TSTAM_ABTEILUNG]  INNER JOIN [pruefbbm].[dbo].[TBLANLAGEHAUPT]  ON  [pruefbbm].[dbo].[TSTAM_ABTEILUNG].[ID]=[pruefbbm].[dbo].[TBLANLAGEHAUPT].[FABTEILUNG]) INNER JOIN [pruefbbm].[dbo].[TBLABTEILUNG]  ON  [pruefbbm].[dbo].[TSTAM_ABTEILUNG].[ID]=[pruefbbm].[dbo].[TBLABTEILUNG].[ABTEILUNGID]) WHERE ( ( [pruefbbm].[dbo].[TBLABTEILUNG].[fid] = @p1)) GROUP BY [Baujahr].[ANLAGE]
    Parameter: @p1 : Int16. Length: 0. Precision: 5. Scale: 0. Direction: Input. Value: 4.

In the Query is always pruefbbm].[dbo].[TSTAM_ABTEILUNG] as Table. I can't make a Query with Table TBLANLAGEHAUPT.

Please help me again

Chrisof

For the relations to work, you need to either re-use the entity alias ("Anlage") in the relations.

Or you can get rid of it in the fields definition (You don't need it anyway)

fields.DefineField(TblanlagehauptFields.Anlage, 0, Feld.Alias);
fields.DefineField(TblanlagehauptFields.Anlage, 1, "Anzahl", AggregateFunction.Count);

By the way, is this realy an m:n relation. As I think it's not from what you have drawn in the attached diagram. It seems to me that both entities reference another in-between entity. not the other way around. If so and if the FKs at both sides should match, then you can use one custom relation as follows (I guess):

var relation = new EntityRelation(TblanlagehauptFields.Fabteilung, TblabteilungFields.AbtielungId, RelationType.OneToMany);
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(relation);

Thank you, now I'm happy I use custom relation that work for me.

Regards Christof