I need to convert following query to expressions for typedlist. Can it be done? Or do I have to go to typedview or something else?

SELECT d.PublishedUserAlertDetailId, c.CommentDate FROM PublishedUserAlertDetail as d LEFT OUTER JOIN (SELECT PublishedUserAlertDetailId, max(CommentDate) as CommentDate FROM PublishedUserAlertDetailComment GROUP BY PublishedUserAlertDetailId) as c ON d.PublishedUserAlertDetailId = c.PublishedUserAlertDetailId

I believe that your query could be expressed as this.

SELECT PublishedUserAlertDetail.PublishedUserAlertDetailId, max(PublishedUserAlertDetailComment.CommentDate) as CommentDate FROM PublishedUserAlertDetail left outer join PublishedUserAlertDetailComment on PublishedUserAlertDetail.PublishedUserAlertDetailId = PublishedUserAlertDetailComment.PublishedUserAlertDetailId GROUP BY PublishedUserAlertDetail.PublishedUserAlertDetailId

Which is reproduced in LLBLGen as a dynamic list as.

 // C#

        /*
         * SELECT PublishedUserAlertDetail.PublishedUserAlertDetailId, 
         *  max(PublishedUserAlertDetailComment.CommentDate) as CommentDate
         * FROM PublishedUserAlertDetail left outer join PublishedUserAlertDetailComment
         * on PublishedUserAlertDetail.PublishedUserAlertDetailId = PublishedUserAlertDetailComment.PublishedUserAlertDetailId
         * GROUP BY PublishedUserAlertDetail.PublishedUserAlertDetailId
         */

        DataAccessAdapter adapter = new DataAccessAdapter();
        ResultsetFields fields = new ResultsetFields(2);
        fields.DefineField(PublishedUserAlertDetailFieldIndex.CarId, 0, "PublishedUserAlertDetailId");
        fields.DefineField(PublishedUserAlertDetailCommentFieldIndex.CommentDate, 1, "CommentDate");
        IRelationPredicateBucket filter = new RelationPredicateBucket();
        filter.Relations.Add(PublishedUserAlertDetailEntity.Relations.PublishedUserAlertDetailCommentEntityUsingPublishedUserAlertDetailId, JoinHint.Left);
        GroupByCollection groupBy = new GroupByCollection();
        groupBy.Add(PublishedUserAlertDetailFields.PublishedUserAlertDetailId);
        fields[1].AggregateFunctionToApply = AggregateFunction.Max;
        DataTable dynamicList = new DataTable();
        adapter.FetchTypedList(fields, dynamicList, filter, 0, null, false, groupBy);
        GridView1.DataSource = dynamicList;
        GridView1.DataBind();

This is done using adapter so if you are using self servicing and have any issues let us know.

I am not using dynamic lists. Would this still apply.

You have some options here:

1- Use the dynamic list approach. (The easiest to implement) 2- Use a database view to represent the entire query then map it to either a Typed View or an Entity. 3- Use a database view to represent the outer joind query and map it to an Entity, then you may use the designer to define a typed list and use the "Relations Used" box to define a Left Join.

I looked at the dynamic list example and I modified the way I fetch typed list and it generated following statement:

SELECT DISTINCT [PublishedUserAlertDetail].[PublishedUserAlertDetailId], [PublishedUserAlert].[OrganizationCode], [PublishedUserAlert].[OrganizationName], [PublishedUserAlert].[Message], [PublishedUserAlertDetail].[DetailMessage], [PublishedUserAlert].[DateProcessed], [PublishedUserAlertDetailComment].[Comment], [PublishedUserAlert].[CategoryName], [PublishedUserAlert].[TypeName], [PublishedUserAlertDetail].[CurrentStateFlag], MAX([PublishedUserAlertDetailComment].[CommentDate]) AS[CommentDate] FROM [PublishedUserAlert]
INNER JOIN [PublishedUserAlertDetail]
ON [PublishedUserAlert].[PublishedUserAlertId]=[PublishedUserAlertDetail].[PublishedUserAlertId] LEFT JOIN [PublishedUserAlertDetailComment]
ON [PublishedUserAlertDetail].[PublishedUserAlertDetailId]=[PublishedUserAlertDetailComment].[PublishedUserAlertDetailId] --WHERE ( ( ( [PublishedUserAlert].[UserId] = 14 AND [PublishedUserAlert].[OrganizationCode] -- IN (001, 041, 042, 043, 044, 045) -- AND [PublishedUserAlertDetail].[CurrentStateFlag] <> 'R'))) GROUP BY [PublishedUserAlertDetail].[PublishedUserAlertDetailId] [PublishedUserAlert].[OrganizationCode], [PublishedUserAlert].[OrganizationName], [PublishedUserAlert].[Message], [PublishedUserAlertDetail].[DetailMessage], [PublishedUserAlert].[DateProcessed], [PublishedUserAlertDetailComment].[Comment], [PublishedUserAlert].[CategoryName], [PublishedUserAlert].[TypeName], [PublishedUserAlertDetail].[CurrentStateFlag]

however this is not producing the result that I need as I get all detailcomments not just the max ones, as the groupby clause contains grouping by all fileds that are in select list not just the PublishedUserAlertDetailId. Need to figure out a way to do this. Basically I need a last comment that was made to the alert to be included in the list so I can show it to the user in the grid. Anyway thanks for you help.

That's because your last posted query contained many fields than your first posted one. So using any query I think you'd better go for the other database view options.

Anyway, Brian (bclubb) suggests the following query:

[PublishedUserAlertDetailComment].[Comment] should not be used in the group by. Since they are all different comments they will all show up.

The query will need to be something like this I think Code: select PublishedUserAlertDetail.PublishedUserAlertDetailId, PublishedUserAlertDetailComment.CommentDate, PublishedUserAlertDetailComment.Comment FROM PublishedUserAlertDetail left join (select PublishedUserAlertDetailId, max(CommentDate) as CommentDate from PublishedUserAlertDetailComment group by PublishedUserAlertId) LastCommentResults on LastCommentResults.PublishedUserAlertId = PublishedUserAlertDetail.PublishedUserAlertId left join PublishedUserAlertDetailComment on LastCommentResults.CommentDate = PublishedUserAlertDetailComment.CommentDate

But this may be incorrect if the dates are the same on two comments.

Well, since I have a control over the db schema, I added 'last comment' to the detail table, so I do not have to query the comments table at all. Thank you very much for your help. The level of support is amazing.