LLBLGen Pro Version: 2.6 Final (April 15th, 2009) SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll - 2.6.9.511 .NET 3.5 MSSQL 2005 Adapter Template

I have defined a typed list in the designer. I have included a table and a view in the typed list. In the Entity Selection tab, both entities are listed in the "Entities select for this Typed List". In the "Relation Description" section, lists my m:1 relationship between the two entities. On the "Fields mapped on entity fields" tab, I have selected fields from both entities.

I am using the following code to retrieve the dataset:

....
                    Filter.PredicateExpression.Add(VOracleClusterDetailFields.RacEnvId % FilterExpression);
                    if (Sorter == null)
                    {
                        ISortExpression sorter = new SortExpression();
                        sorter.Add(VOracleClusterDetailFields.RacEnvId | SortOperator.Ascending);
                        sorter.Add(VOracleClusterDetailFields.MachineId | SortOperator.Ascending);
                        sorter.Add(VOracleClusterDetailFields.ProductNm | SortOperator.Ascending);
                        Sorter = sorter;
                    }
                    _GV.DataSource = ddh.GetOracleClusterNodesDistinct(Filter, Sorter);

......



        public TLOracleClusterDetailTypedList GetOracleClusterNodesDistinct(IRelationPredicateBucket irpb, ISortExpression ise)
        {
            TLOracleClusterDetailTypedList _list = new TLOracleClusterDetailTypedList();
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchTypedList(_list.GetFieldsInfo(), _list, irpb, 0, ise, false);
            }
            return _list;
        }

When the code runs, the query is correct except for the FROM clause. Only 1 entity is included. It is whatever entity I add first in the designer to the TypedList.

What magic do I need to do to get the SQL to generate properly?

Thanks....

Hi Jester,

Could you please post the .lgp file to see the TypedList? (you can open a HelpDesk thread if you want privacy).

You have to make sure that you pass the Relationinfo of the typed list as well. I.o.w.: you have to add the filter to the bucket you get returned from typedlist.GetRelationInfo()

This might sound cumbersome, so you can also use one of the overloads of FetchTypedList which accept a typed list and additional filters/relations to make this less error prone.

Adding the relationships using typedlist.GetRelationInfo() solved the issue.

Thanks...

Follow up to the same topic. I now have the correct SQL being generated and when I run the SQL against the database, I get back the correct number of rows. I converted my code to use a Dynamic List instead of a TypedList, BUT, when I execute the FetchTypedList, my datatable is not being populated with any rows. Here is my code:

...
            RelationPredicateBucket Filter = new RelationPredicateBucket();
            Filter.Relations.Add(VOracleClusterDetailEntity.Relations.ApplicationEntityUsingAppId);
            switch (QTR)
            {
                default:
                    ResultsetFields _rs = new ResultsetFields(7);
                    _rs.DefineField(VOracleClusterDetailFields.RacEnvId, 0);
                    _rs.DefineField(VOracleClusterDetailFields.MachineId, 1);
                    _rs.DefineField(VOracleClusterDetailFields.ProductNm, 2);
                    _rs.DefineField(VOracleClusterDetailFields.InstanceNm, 3);
                    _rs.DefineField(VOracleClusterDetailFields.Version, 4);
                    _rs.DefineField(VOracleClusterDetailFields.AppId, 5);
                    _rs.DefineField(ApplicationFields.ApplicationShortNm, 6);
                    Filter.PredicateExpression.Add(VOracleClusterDetailFields.RacEnvId % FilterExpression);
                    if (Sorter == null)
                    {
                        ISortExpression sorter = new SortExpression();
                        sorter.Add(VOracleClusterDetailFields.RacEnvId | SortOperator.Ascending);
                        sorter.Add(VOracleClusterDetailFields.MachineId | SortOperator.Ascending);
                        sorter.Add(VOracleClusterDetailFields.ProductNm | SortOperator.Ascending);
                        sorter.Add(VOracleClusterDetailFields.InstanceNm | SortOperator.Ascending);
                        Sorter = sorter;
                    }
                    _GV.DataSource = ddh.GetOracleClusterNodesDistinct(Filter, Sorter, true, _rs);

...

        public DataTable GetOracleClusterNodesDistinct(IRelationPredicateBucket irpb, ISortExpression ise, bool bExactMatch, ResultsetFields rs)
        {
            DataTable _dt = new DataTable();
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                try
                {
                    adapter.FetchTypedList(rs, _dt, irpb, 0, ise, false);
                }
                catch (Exception ex)
                {

                }
            }
            return _dt;

        }

This code works fine if I do not include Filter.Relations.Add(VOracleClusterDetailEntity.Relations.ApplicationEntityUsingAppId); and the ApplicationShortNm field from the application table.

Maybe I am going about the the wrong way, but I am attempting to select only certain columns from a view joined to a single table and include a DISTINCT keyword. I have several slightly different queries I need to run against this view + table combo and I didn't want to have to create a TypedList or view for each one.

Suggestions?

On LLBLGen Designer, What type of relation you specified for VOracleClusterDetailEntity<->ApplicationEntityUsingAppId ? (Could be that your TypedList's relation is LEFT/RIGHT, and you are adding to DynamicList as INNER).

The relationship is:

VOracleClusterDetail - Application (m:1)

The issue that I am seeing is that the correct SQL is being generated, but the data table is not being populated. When I captured the SQL going to the database and executed it outside of my program, I am getting several rows returned (with the correct resultset, where clause, sort clause, etc).

So, if you copy the generated SQL and run it on SQLServer query tool, the results come back, right? And in your code, no results are retrieved.

Just to check, please update to the latest runtime library version.