Hi

The current system is implemented in the following way:

private void ucResults_DataRequired(object sender, DataRequiredEventArgs e)
        {
            ucResults.Visible = true;
            e.SelectFilter = ucSearch.SearchPredicate;
            e.Relations = ucSearch.SearchRelations;
        }

ie, every search result datagrid uses a select filter to decide the datarequired.

Is there a way to change the above using PredicateExpression or whatever to achieve the following sql query:

select name, max(year) from qualification group by name;

I know how to get max and group by to work together but it returns a datatable (as in the following code) but the current implementation cannot take datatable.

ResultsetFields fields = new ResultsetFields(2);
                fields.DefineField(QualificationFieldIndex.QualificationId, 0, "QualificationId", "Qualification", AggregateFunction.Max);
                fields.DefineField(QualificationFieldIndex.Name, 1, "Name", "Qualification");
                IGroupByCollection groupByClause = new GroupByCollection();
                groupByClause.Add(fields[1]);
                TypedListDAO list = new TypedListDAO();
                DataTable dt = new DataTable();
                list.GetMultiAsDataTable(fields, dt, 0, null, null, null, true, groupByClause, null, 0, 0);

Thanks for your help.

Idrees

know how to get max and group by to work together but it returns a datatable (as in the following code) but the current implementation cannot take datatable

To what is the dataGrid binded?

Thanks for your reply walaa.

To what is the dataGrid binded?

I am very much new to .NET. The DataBind() method is implemented in the parent class as follows:

    public class EntityDataGridBase : UserControlEx
    {
        #region Controls

        protected System.Web.UI.WebControls.DataGrid grid;
        protected Simbient.NatureCare.Anastasia.Controls.ServerPager pager;
        protected System.Web.UI.WebControls.Label noResults;

        #endregion

        #region Variables

        protected readonly Unit _defaultWidth = new Unit();

        protected IPredicate _selectFilter = null;
        protected ISortExpression _sortClauses = null;
        protected IRelationCollection _relations = null;
        protected IPrefetchPath _prefetch = null;
        protected bool _displayGridIfEmpty = false;

        #endregion

        #region Properties

        public Unit Width
        {
            get { return ViewState["Width"] == null ? _defaultWidth : (Unit) ViewState["Width"]; }
            set { ViewState["Width"] = value; }
        }

        public System.Web.UI.WebControls.DataGrid GridControl
        {
            get { return grid; }
        }

        public Simbient.NatureCare.Anastasia.Controls.ServerPager PagerControl
        {
            get { return pager; }
        }

        public int PageSize
        {
            get { return PagerControl.PageSize; }
            set { PagerControl.PageSize = value; }
        }

        public System.Web.UI.WebControls.Label NoResultsControl
        {
            get { return noResults; }
        }

        public IPredicate SelectFilter
        {
            get { return _selectFilter; }
        }

        public ISortExpression SortClauses
        {
            get { return _sortClauses; }
        }

        public IRelationCollection Relations
        {
            get { return _relations; }
        }

        public virtual IPrefetchPath PrefetchPath
        {
            get { return _prefetch; }
        }

        public virtual EntityCollectionBaseEx DataSource
        {
            get { return null; }
        }

        /// <summary>
        /// Display grid if empty? (If false displays 'noresults' control).
        /// </summary>
        public virtual bool DisplayGridIfEmpty
        {
            get { return _displayGridIfEmpty; }
            set { _displayGridIfEmpty = value; }
        }

        #endregion

        #region Events

        public delegate void DataRequiredEventHandler(object sender, DataRequiredEventArgs e);

        public event DataRequiredEventHandler DataRequired;

        protected virtual void OnDataRequired(DataRequiredEventArgs args)
        {
            if (DataRequired != null)
            {
                DataRequired(this, args);
            }
        }

        public delegate void GridValidatedEventHandler(object sender, GridValidatedEventArgs e);

        public event GridValidatedEventHandler GridValidated;

        protected virtual void OnGridValidated(GridValidatedEventArgs args)
        {
            if (GridValidated != null)
            {
                GridValidated(this, args);
            }
        }

        #endregion

        #region Public Functions

        public virtual DataGridColumn GetColumn(int index)
        {
            return grid.Columns[index];
        }

        #endregion

        #region Protected Functions

        protected virtual IRelationCollection GetDefaultRelations()
        {
            return null;
        }

        protected virtual ISortExpression GetDefaultSortClauses()
        {
            return null;
        }

        protected virtual IPrefetchPath GetDefaultPrefetchPaths()
        {
            return null;
        }

        public override void DataBind()
        {
            DataRequiredEventArgs args = new DataRequiredEventArgs();
            OnDataRequired(args);
            // Re-get the prefetch paths
            _prefetch = GetDefaultPrefetchPaths();
            this._selectFilter = args.SelectFilter == null ? this._selectFilter : args.SelectFilter;
            this._sortClauses = args.SortClauses == null ? this._sortClauses : args.SortClauses;
            this._relations = args.Relations == null ? this._relations : args.Relations;
            this._prefetch = args.PrefetchPath == null ? this._prefetch : args.PrefetchPath;

            if(this.DataSource != null)
            {
                PagerControl.DataSource = this.DataSource;
                PagerControl.SortClauses = this.SortClauses;
                PagerControl.SelectFilter = this.SelectFilter;
                PagerControl.Relations = this.Relations;
                PagerControl.PrefetchPath = this.PrefetchPath;
                
                if (_prefetch != null)
                    pager.UseClientPaging = true;

                PagerControl.DataBind();

                if(!this.pager.UseClientPaging)
                {
                    PagerControl.Visible = PagerControl.PageCount > 1;
                    GridControl.Visible = this.DataSource.Count > 0 || _displayGridIfEmpty;
                    noResults.Visible = !(this.DataSource.Count > 0 || _displayGridIfEmpty);
                }
            }
        }

        #endregion

        #region Event Handlers

        private void EntityDataGridBase_Load(object sender, EventArgs e)
        {
            GridControl.Width = this.Width;
            PagerControl.Width = this.Width;

            if (GridControl.HeaderStyle.CssClass.Length == 0) { GridControl.HeaderStyle.CssClass = "dgHead"; }
            if (GridControl.ItemStyle.CssClass.Length == 0) { GridControl.ItemStyle.CssClass = "dgRow"; }
            if (GridControl.AlternatingItemStyle.CssClass.Length == 0) { GridControl.AlternatingItemStyle.CssClass = "dgAltRow"; }

            PagerControl.Visible = PagerControl.PageCount > 1;
        }

        private void EntityDataGridBase_PreRender(object sender, EventArgs e)
        {
            if(this.pager.UseClientPaging && this.PagerControl.DataSource != null)
            {
                PagerControl.Visible = PagerControl.PageCount > 1;
                GridControl.Visible = this.PagerControl.DataSource.Count > 0 || _displayGridIfEmpty;
                noResults.Visible = !(this.PagerControl.DataSource.Count > 0 || _displayGridIfEmpty);
            }
        }

        private void pager_PageIndexChanged(object sender, ServerPagerPageChangedEventArgs e)
        {
            PagerControl.CurrentPageIndex = e.NewPageIndex;
            this.DataBind();
        }

        private void grid_EditCommand(object source, DataGridCommandEventArgs e)
        {
            grid.EditItemIndex = e.Item.ItemIndex;
            this.DataBind();
        }

        private void grid_CancelCommand(object source, DataGridCommandEventArgs e)
        {
            grid.EditItemIndex = -1;
            this.DataBind();
        }

        #endregion

        #region Web Form Designer generated code
        override protected void OnInit(EventArgs e)
        {
            InitializeComponent();
            base.OnInit(e);

            _sortClauses = GetDefaultSortClauses();
            _relations = GetDefaultRelations();
        }

        private void InitializeComponent()
        {
            this.Load += new EventHandler(EntityDataGridBase_Load);
            this.PreRender += new EventHandler(EntityDataGridBase_PreRender);
            this.pager.PageIndexChanged += new Simbient.NatureCare.Anastasia.Controls.ServerPager.PageIndexChangedEventHandler(pager_PageIndexChanged);
            this.grid.EditCommand += new DataGridCommandEventHandler(grid_EditCommand);
            this.grid.CancelCommand += new DataGridCommandEventHandler(grid_CancelCommand);
        }
        #endregion
    }

    #region Custom Event Args

    public class DataRequiredEventArgs : EventArgs
    {
        private IPredicate _selectFilter;
        private ISortExpression _sortClauses;
        private IRelationCollection _relations;
        private IPrefetchPath _prefetch;

        public DataRequiredEventArgs() : base()
        {
        }

        public IPredicate SelectFilter
        {
            get { return _selectFilter; }
            set { _selectFilter = value; }
        }

        public ISortExpression SortClauses
        {
            get { return _sortClauses; }
            set { _sortClauses = value; }
        }

        public IRelationCollection Relations
        {
            get { return _relations; }
            set { _relations = value; }
        }

        public IPrefetchPath PrefetchPath
        {
            get { return _prefetch; }
            set { _prefetch = value; }
        }
    }

    public class GridValidatedEventArgs : EventArgs
    {
        private bool _isValid = false;

        public GridValidatedEventArgs(bool isValid) : base()
        {
            this._isValid = isValid;
        }

        public bool IsValid
        {
            get { return this._isValid; }
            set { this._isValid = value; }
        }
    }

    #endregion
}

Sorry to post the whole class here, unfortunately I can't understand this part and don't know what the grid is bound to. Please let me know if you need any additional information related to this class.

Thanks in advance.

Idrees

Somewhere in the code, I was expecting to see something like:

grid.DataSource = dt;
grid.DataBind();

Walaa wrote:

Somewhere in the code, I was expecting to see something like:

grid.DataSource = dt;
grid.DataBind();

It's there, it looks different:

            if(this.DataSource != null)
            {
                PagerControl.DataSource = this.DataSource;  // <<<<<<<<<<<
                PagerControl.SortClauses = this.SortClauses;
                PagerControl.SelectFilter = this.SelectFilter;
                PagerControl.Relations = this.Relations;
                PagerControl.PrefetchPath = this.PrefetchPath;
                
                if (_prefetch != null)
                    pager.UseClientPaging = true;

                PagerControl.DataBind();    // <<<<<<<<<<<<<

MAX() is an aggregate function. Entities are sets of data elements, which aren't aggregated, so you can't use MAX() or groupby on entity data and expect to get entity data back, because what does 'MAX()' mean in the context of an entity? So the select you proposed is a dyn. list. You then thus have to fetch a dyn. list to get that data.

Thanks for your reply guys.

Somewhere in the code, I was expecting to see something like:

grid.DataSource = dt; grid.DataBind();

I was expecting the same Walaa and even tried that. And Otis was absolutely right PagerControl.DataSource is the one controlling all of our search pages. But the problem I am facing is, I don't know how to convert our resulting datatable to a type this DataSource expects.

The DataSource is declared as follows:

        public EntityCollectionBaseEx DataSource
        {
            get { return _dataSource; }
            set { _dataSource = value; }
        }

And the EntityCollectionBaseEx is:

///////////////////////////////////////////////////////////////
// This is generated code. If you modify this code, be aware
// of the fact that when you re-generate the code, your changes
// are lost. If you want to keep your changes, make this file read-only
// when you have finished your changes, however it is recommended that
// you inherit from this class to extend the functionality of this generated
// class or you modify / extend the templates used to generate this code.
//////////////////////////////////////////////////////////////
// Code is generated using LLBLGen Pro version: 1.0.2005.1
// Code is generated on: 
// Code is generated using templates: NatureCare C# template set for SqlServer (1.0.2004.2)
// Templates vendor: Solutions Design.
// Templates version: 1.0.2005.9.18
//////////////////////////////////////////////////////////////
using System;
using System.Data;
using System.Collections;
using System.ComponentModel;
using System.Xml;
using System.Runtime.Serialization;

using Simbient.NatureCare.Objects.EntityClasses;
using Simbient.NatureCare.Objects.FactoryClasses;
using Simbient.NatureCare.Objects.DaoClasses;
using Simbient.NatureCare.Objects.HelperClasses;
using Simbient.NatureCare.Objects.ValidatorClasses;

using SD.LLBLGen.Pro.ORMSupportClasses;

namespace Simbient.NatureCare.Objects.CollectionClasses
{
    
    // __LLBLGENPRO_USER_CODE_REGION_START AdditionalNamespaces
    // __LLBLGENPRO_USER_CODE_REGION_END

    /// <summary>
    /// Collection class for storing and retrieving collections of Entity objects. 
    /// </summary>
    [Serializable]
    public abstract class EntityCollectionBaseEx : EntityCollectionBase
    {
        /// <summary>
        /// CTor
        /// </summary>
        /// <param name="propertyDescriptorFactoryToUse">PropertyDescriptor factory to use in GetItemProperties method. Complex databinding related.</param>
        /// <param name="entityFactoryToUse">The EntityFactory to use when creating entity objects during a GetMulti() call.</param>
        public EntityCollectionBaseEx(IPropertyDescriptorFactory propertyDescriptorFactoryToUse, Type collectionType, IEntityFactory entityFactoryToUse)
            :base(propertyDescriptorFactoryToUse, collectionType, entityFactoryToUse)
        {
        }


        /// <summary>
        /// CTor
        /// </summary>
        /// <param name="propertyDescriptorFactoryToUse">PropertyDescriptor factory to use in GetItemProperties method. Complex databinding related.</param>
        /// <param name="entityFactoryToUse">The EntityFactory to use when creating entity objects during a GetMulti() call.</param>
        ///  <param name="validatorToUse">The validator object to use when creating entity objects during a GetMulti() call.</param>
        public EntityCollectionBaseEx(IPropertyDescriptorFactory propertyDescriptorFactoryToUse, Type collectionType, IEntityFactory entityFactoryToUse, IValidator validatorToUse)
            :base(propertyDescriptorFactoryToUse, collectionType, entityFactoryToUse, validatorToUse)
        {
        }


        /// <summary>
        /// Private CTor for deserialization
        /// </summary>
        /// <param name="info"></param>
        /// <param name="context"></param>
        protected EntityCollectionBaseEx(SerializationInfo info, StreamingContext context) : base(info, context)
        {
        }
        
        
        /// <summary>
        /// Gets a new instance of this EntityCollection.
        /// </summary>
        /// <returns>Collection derived from EntityCollectionBaseEx.</returns>
        public abstract EntityCollectionBaseEx GetNewInstance();


        /// <summary>
        /// Gets the amount of Entity objects in the database.
        /// </summary>
        /// <returns>the amount of objects found</returns>
        public abstract int GetDbCount();


        /// <summary>
        /// Gets the amount of Entity objects in the database, when taking into account the filter specified.
        /// </summary>
        /// <param name="filter">the filter to apply</param>
        /// <returns>the amount of objects found</returns>
        public abstract int GetDbCount(IPredicate filter);


        /// <summary>
        /// Gets the amount of Entity objects in the database, when taking into account the filter specified and the 
        /// relations specified.
        /// </summary>
        /// <param name="filter">the filter to apply</param>
        /// <param name="relations">The relations to walk</param>
        /// <returns>the amount of objects found</returns>
        public abstract int GetDbCount(IPredicate filter, IRelationCollection relations);
        
        /// <summary>
        /// Retrieves in this Collection object all Entity objects which match with the specified filter, formulated in
        /// the predicate or predicate expression definition, using the passed in relations to construct the total query.
        /// </summary>
        /// <param name="selectFilter">A predicate or predicate expression which should be used as filter for the entities to retrieve.</param>
        /// <param name="maxNumberOfItemsToReturn"> The maximum number of items to return with this retrieval query.</param>
        /// <param name="sortClauses">The order by specifications for the sorting of the resultset. When not specified, no sorting is applied.</param>
        /// <param name="relations">The set of relations to walk to construct the total query.</param>
        /// <param name="prefetchPathToUse">the PrefetchPath which defines the graph of objects to fetch.</param>
        /// <returns>true if succeeded, false otherwise</returns>
        public abstract bool GetMulti(IPredicate selectFilter, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relations, IPrefetchPath prefetchPathToUse);
        
        /// <summary>
        /// Deletes from the persistent storage all  entities which match with the specified filter, formulated in
        /// the predicate or predicate expression definition.
        /// </summary>
        /// <param name="deleteFilter">A predicate or predicate expression which should be used as filter for the entities to delete. Can be null, which
        /// will result in a query removing all  entities from the persistent storage</param>
        /// <returns>Amount of entities affected, if the used persistent storage has rowcounting enabled.</returns>
        public abstract int DeleteMulti(IPredicate deleteFilter);


        /// <summary>
        /// Deletes from the persistent storage all  entities which match with the specified filter, formulated in
        /// the predicate or predicate expression definition.
        /// </summary>
        /// <param name="deleteFilter">A predicate or predicate expression which should be used as filter for the entities to delete.</param>
        /// <param name="relations">The set of relations to walk to construct the total query.</param>
        /// <returns>Amount of entities affected, if the used persistent storage has rowcounting enabled.</returns>
        public abstract int DeleteMulti(IPredicate deleteFilter, IRelationCollection relations);
        
        
        /// <summary>
        /// Updates in the persistent storage all entities which have data in common with the specified Entity. 
        /// If one is omitted that entity is not used as a filter. Which fields are updated in those matching entities depends on which fields are
        /// <i>changed</i> in entityWithNewValues. The new values of these fields are read from entityWithNewValues. 
        /// </summary>
        /// <param name="entityWithNewValues">Entity instance which holds the new values for the matching entities to update. Only
        /// changed fields are taken into account</param>
        /// <param name="updateFilter">A predicate or predicate expression which should be used as filter for the entities to update. Can be null, which
        /// will result in an update action which will affect all  entities.</param>
        /// <returns>Amount of entities affected, if the used persistent storage has rowcounting enabled.</returns>
        public abstract int UpdateMulti(IEntity entityWithNewValues, IPredicate updateFilter);


        /// <summary>
        /// Updates in the persistent storage all entities which have data in common with the specified Entity. 
        /// If one is omitted that entity is not used as a filter. Which fields are updated in those matching entities depends on which fields are
        /// <i>changed</i> in entityWithNewValues. The new values of these fields are read from entityWithNewValues. 
        /// </summary>
        /// <param name="entityWithNewValues">Entity instance which holds the new values for the matching entities to update. Only
        /// changed fields are taken into account</param>
        /// <param name="updateFilter">A predicate or predicate expression which should be used as filter for the entities to update.</param>
        /// <param name="relations">The set of relations to walk to construct the total query.</param>
        /// <returns>Amount of entities affected, if the used persistent storage has rowcounting enabled.</returns>
        public abstract int UpdateMulti(IEntity entityWithNewValues, IPredicate updateFilter, IRelationCollection relations);
        
        #region Events
        
        public delegate void UpdateMultiStartedEventHandler(object sender, EntityCollectionMultiEventArgs e);

        public event UpdateMultiStartedEventHandler UpdateMultiStarted;

        protected virtual void OnUpdateMultiStarted(EntityCollectionMultiEventArgs args)
        {
            
            // __LLBLGENPRO_USER_CODE_REGION_START CustomOnUpdateMultiStartedCode
            if (!AuditChangeEntity.AuditOff)
            {
                EntityCollectionBaseEx beingChanged = this.GetNewInstance();
                beingChanged.GetMulti(args.Filter, 0, null, args.Relations, 0, 0);
                AuditChangeEntity.GetAndSaveChangesForCollection(args.Changes, beingChanged, false);
            }
            // __LLBLGENPRO_USER_CODE_REGION_END
        
            if (UpdateMultiStarted != null)
            {
                UpdateMultiStarted(this, args);
            }
        }
        
        public delegate void UpdateMultiCompleteEventHandler(object sender, EntityCollectionMultiEventArgs e);

        public event UpdateMultiCompleteEventHandler UpdateMultiComplete;

        protected virtual void OnUpdateMultiComplete(EntityCollectionMultiEventArgs args)
        {
            
            // __LLBLGENPRO_USER_CODE_REGION_START CustomOnUpdateMultiCompleteCode
            // __LLBLGENPRO_USER_CODE_REGION_END
        
            if (UpdateMultiComplete != null)
            {
                UpdateMultiComplete(this, args);
            }
        }
        
        public delegate void DeleteMultiStartedEventHandler(object sender, EntityCollectionMultiEventArgs e);

        public event DeleteMultiStartedEventHandler DeleteMultiStarted;

        protected virtual void OnDeleteMultiStarted(EntityCollectionMultiEventArgs args)
        {
            
            // __LLBLGENPRO_USER_CODE_REGION_START CustomOnDeleteMultiStartedCode
            if (!AuditChangeEntity.AuditOff)
            {
                EntityCollectionBaseEx beingDeleted = this.GetNewInstance();
                beingDeleted.GetMulti(args.Filter, 0, null, args.Relations, 0, 0);
                AuditChangeEntity.GetAndSaveChangesForCollection(args.Changes, beingDeleted, true);
            }
            // __LLBLGENPRO_USER_CODE_REGION_END
        
            if (DeleteMultiStarted != null)
            {
                DeleteMultiStarted(this, args);
            }
        }
        
        public delegate void DeleteMultiCompleteEventHandler(object sender, EntityCollectionMultiEventArgs e);

        public event DeleteMultiCompleteEventHandler DeleteMultiComplete;

        protected virtual void OnDeleteMultiComplete(EntityCollectionMultiEventArgs args)
        {
            
            // __LLBLGENPRO_USER_CODE_REGION_START CustomOnDeleteMultiCompleteCode
            // __LLBLGENPRO_USER_CODE_REGION_END
        
            if (DeleteMultiComplete != null)
            {
                DeleteMultiComplete(this, args);
            }
        }
        
        #endregion

        #region Custom EntityCollection code
        
        // __LLBLGENPRO_USER_CODE_REGION_START CustomEntityCollectionCode
        // __LLBLGENPRO_USER_CODE_REGION_END
        #endregion
        
        #region Included Code
        #endregion
    }
    
    #region Custom EventArgs
    
    public class EntityCollectionMultiEventArgs : EventArgs
    {
        private IEntity _changes;
        private IPredicate _filter;
        private IRelationCollection _relations;

        public EntityCollectionMultiEventArgs(IEntity changes, IPredicate filter, IRelationCollection relations)
        {
            this._changes = changes;
            this._filter = filter;
            this._relations = relations;
        }
        
        public IEntity Changes
        {
            get { return this._changes; }
        }

        public IPredicate Filter
        {
            get { return this._filter; }
        }

        public IRelationCollection Relations
        {
            get { return this._relations; }
        }
    }
    
    #endregion
}

Any ideas?

Thanks.

Does PagerControl accept a datatable as a datasource? I would almost think it has too. The dynamic list as Frans mentioned fills a datatable that you can use in your databinding. Take a look at the example in the manual it includes fields from different entities and does an aggregation. Let us know if using the dynamic list won't work.

Thank you all for your replies.

I figured out another possible approach to solve this. Instead of trying to get what I want initially when data is retrieved, I am trying to filter out from what is returned, using the following code:

            QualificationCollection qualEnrolment = (QualificationCollection) this.PagerControl.DataSource;
            ResultsetFields fields = new ResultsetFields(2);
            fields.DefineField(QualificationFieldIndex.Year, 0, "Year", "Qualification", AggregateFunction.Max);
            fields.DefineField(QualificationFieldIndex.Name, 1, "Name", "Qualification");
            IGroupByCollection groupBy = new GroupByCollection();
            groupBy.Add(fields[1]);
            qualEnrolment.GetScalar(QualificationFieldIndex.Year, null, AggregateFunction.Max, null, groupBy);

I am sure suggesting a way out with this would be easy for you guys. I am having a collection returned from db and want to filter out to get the result that I wanted as in sql

select max(year), name from qualification group by name

I am not getting what I wanted from the code above, I know something is wrong or I have not understood properly. Please can you shed some light on this.

Thanking you in advance.

Idrees

I think this code returns only the maximum year within the collection and groups it by Name (atleast that's what I have understood as what GetScalar() means). Instead I want maximum year within all names to be displayed.

Hope I am not too confusing.....

Then you should be using a DynamicList, (This is where you use the ResultsetFields)

Please check the example in the LLBLGen Pro manual: Using the generated code -> SelfServicing/Adapter -> Using dynamic lists

Thanks Walaa.

Yes, I even tried that as in the following code:

            ResultsetFields fields = new ResultsetFields(3);
            fields.DefineField(QualificationFieldIndex.QualificationId, 0, "QualificationId", "Qualification");
            fields.DefineField(QualificationFieldIndex.Name, 1, "Name", "Qualification");
            fields.DefineField(QualificationFieldIndex.Year, 2, "Year", "Qualification", AggregateFunction.Max);
            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields[1]);
            groupByClause.Add(fields[0]);
            TypedListDAO list = new TypedListDAO();
            DataTable dt = new DataTable();
            list.GetMultiAsDataTable(fields, dt, 0, null, null, null, true, groupByClause, null, 0, 0);

But this is returning all the records from the database, when I check dt.Rows.Count.

 ResultsetFields fields = new ResultsetFields(3);
            fields.DefineField(QualificationFieldIndex.QualificationId, 0, "QualificationId", "Qualification");
            fields.DefineField(QualificationFieldIndex.Name, 1, "Name", "Qualification");
            fields.DefineField(QualificationFieldIndex.Year, 2, "Year", "Qualification", AggregateFunction.Max);
            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields[1]);
            groupByClause.Add(fields[0]);
            TypedListDAO list = new TypedListDAO();
            DataTable dt = new DataTable();
            list.GetMultiAsDataTable(fields, dt, 0, null, null, null, true, groupByClause, null, 0, 0);

The above code should generate the following SQL:

SELECT ID, Name, MAX(Year) FROM Qualification GROUP BY ID, Name

And if ID is the PK, then this will return all the records in the table, since you are grouping by a PK, which is unique. Also if the NAME is unique, it will also return all the records even if you remove the ID from the Select parameters.

Knowing this, what should your SQL query be?

Got you.

I am removing ID from fields now and trying... fingers crossed....

thanks.

Brilliant Walaa, yes, it indeed worked... I'm excited.

I think we are almost there....

I could achieve the query I needed by removing ID from fields (the query I needed was):

select name, max(year) from qualification group by name

But since I am doing a work-around, I need the relevant ID for each record returned (69 records in total returned out of 183). Can you suggest me something...

Thanks.

Fine tuned my query now:

select distinct qualificationid, name, year from qualification q1 where year =
(select max(year) from qualification where name=q1.name
)
and qualificationid = (select min(qualificationid) from qualification where name=q1.name)

select distinct qualificationid, name, year from qualification q1 where year = (select max(year) from qualification where name=q1.name ) and qualificationid = (select min(qualificationid) from qualification where name=q1.name)

I don't have good feelings about this query. As the Min(ID) of a name might be in a different row than the one which contains the Max(year). So most probably your query will retunr no results because of the ANDed conflicting predicate.

Example of possible data:

ID Name Year 1 John 1995 2 John 2000 3 John 2001

Though this query is returning exactly the result that I am expecting, it may be a bad idea as you suggest. I can see your point which is very much valid. I'll have problems when I am writing data back to db based on what is returned here.

So I am trying to fine tune my query. But most important is that I need to implement it as LLBLGen code and not sql query.

My requirement is to: get the record which has the maximum year for every single name alongwith their relevant ids (which is pk). Any suggestions are much appreciated.

Thanks all for your time so far, especially Walaa.

Idrees

Hooray, I have achieved what I wanted in LLBLGen code, which reads as follows:

            ResultsetFields fields = new ResultsetFields(3);
            fields.DefineField(QualificationFieldIndex.QualificationId, 0, "QualificationId", "Qualification", AggregateFunction.Min);
            fields.DefineField(QualificationFieldIndex.Name, 1, "Name", "Qualification");
            fields.DefineField(QualificationFieldIndex.Year, 2, "Year", "Qualification", AggregateFunction.Max);
            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields[1]);
            TypedListDAO list = new TypedListDAO();
            DataTable dt = new DataTable();
            list.GetMultiAsDataTable(fields, dt, 0, null, null, null, true, groupByClause, null, 0, 0);

The only difference with previous posting is that I have included AggregateFunction.Min for ID. So the sql query now is:

select name, max(year), min(qualificationid) from qualification group by name

Thanks all for your support. Thanks Walaa.

Idrees

Sorry, thatz AggregateFunction.Max for ID as years pass by the IDs grow.

Glad you have worked it out.

as years pass by the IDs grow.

Thanks to the above rule, the query can be that simple. Otherwise you would have to make a bigger Query. Something like:

SELECT QualificationID, Name, Year 
FROM Qualification
INNER JOIN
{
SELECT Name, MAX(Year)  as MaxYear
FROM Qualification
GROUP BY Name
} t1
ON Name = t1.Name AND Year = t1.MaxYear