Hi,

I hope someone can help me. I need to select the first row (all the data not only an id) per group using LLBLGen Pro 2.5

Thx

Sorry, your question is not detailed enough for us to know what you are trying acheive. Please could you provide some more information, table structures, code snippets, the SQL you would like to replicate etc to allow us to assist you.

Thanks

Matt

Im sorry,

here is some extra information, hope this is enough.

SQL:

Select id, headers, [subject], body, MailDate, Marked
from MonitorMailMessage
where mailboxid = 4 and id in(
select max(id)
from MonitorMailMessage
group by [subject])


Select id, headers, [subject], body, MailDate, Marked
from MonitorMailMessage
where mailboxid = 4

Table structure:

CREATE TABLE [dbo].[MonitorMailMessage](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [MailboxID] [int] NOT NULL,
    [Headers] [nvarchar](max) NOT NULL,
    [Subject] [nvarchar](255) NOT NULL,
    [Body] [nvarchar](max) NULL,
    [ZippedBody] [varbinary](max) NULL,
    [AttachmentContent] [varbinary](max) NULL,
    [AttachmentContentType] [nvarchar](50) NULL,
    [MailDate] [smalldatetime] NOT NULL,
    [Marked] [bit] NOT NULL

)

I would like to add the inner select with group by function only when I add some sort of IGroupByCollection of someting in the codes So that I have one function that returns me all first row records of the grouped by or all records.

Thx

Still I'm not sure what is the question.

Are you looking for a way to implement this:

Select id, headers, [subject], body, MailDate, Marked from MonitorMailMessage where mailboxid = 4 and id in( select max(id) from MonitorMailMessage group by [subject])

Just use a FieldCompareSetPredicate to implement the bold predicate. While using an entityField having the AggregateFunctionToApply property set to AggregateFunction.Max

Thx this is exectly what I am looking for.

I was forgotten that I also need a count of number of records that are grouped. so the SQL will be:

Select mmm.id, headers, [subject], body, MailDate, Marked, a.countAll
from MonitorMailMessage mmm Join (select max(id) as id, count(id) as countAll
from MonitorMailMessage
group by [subject]) as a 
on mmm.id = a.id
where mailboxid = 4 

How can I generate this code? I cant use FieldCompareSetPredicate I suppose

Please check Derived tables and dynamic relations

But that's a v.2.6 feature. (upgrading is just a piece of cake).

Ok I will upgrade, do you have some documentation or examples on how to get it working?

Please check the link in my previous post.

Sorry didnt saw it.

Untill now I have this:

Dim dtFields As New ResultsetFields(2)
                dtFields.DefineField(MonitorMailMessageFields.ID, 0)
                dtFields(0).AggregateFunctionToApply = AggregateFunction.Max
                dtFields.DefineField(MonitorMailMessageFields.ID, 1, "Total")
                dtFields(1).AggregateFunctionToApply = AggregateFunction.Count
                Dim dtGroupBy As New GroupByCollection(MonitorMailMessageFields.Subject)
                Dim dtDefinition As New DerivedTableDefinition(dtFields, "mmm1", New PredicateExpression(MonitorMailMessageFields.MailboxID = mailboxId), dtGroupBy)

                Dim relation As New DynamicRelation(dtDefinition, JoinHint.Inner, _
                                                    EntityType.MonitorMailMessageEntity, "mmm2", (New EntityField2(MonitorMailMessageFieldIndex.ID.ToString(), "mmm1", GetType(Integer)) = _
                           MonitorMailMessageFields.ID.SetObjectAlias("mmm2")))


                Dim filter As New RelationPredicateBucket()
                filter.Relations.Add(relation)
                filter.SelectListAlias = "mmm2"

                Dim messages As New EntityCollection(Of MonitorMailMessageEntity)()
                Using adapter2 As New DataAccessAdapter()
                    adapter2.FetchEntityCollection(messages, filter, 0, Nothing, (startRow \ pageSize) + 1, pageSize)
                End Using

SQL generated:

SELECT DISTINCT TOP 20 [LPA_m2].[ID], 
[LPA_m2].[MailboxID], 
[LPA_m2].[Headers], 
[LPA_m2].[Subject], 
[LPA_m2].[Body], 
[LPA_m2].[ZippedBody], 
[LPA_m2].[AttachmentContent], 
[LPA_m2].[AttachmentContentType], 
[LPA_m2].[MailDate], [LPA_m2].[Marked] 

FROM ( 

    (SELECT MAX([Prisma_Dev_MailMonitor].[dbo].[MonitorMailMessage].[ID]) AS [ID], 
    COUNT([Prisma_Dev_MailMonitor].[dbo].[MonitorMailMessage].[ID]) AS [Total] 

    FROM [Prisma_Dev_MailMonitor].[dbo].[MonitorMailMessage]  

    WHERE ( [Prisma_Dev_MailMonitor].[dbo].[MonitorMailMessage].[MailboxID] = @MailboxID1) 

    GROUP BY [Prisma_Dev_MailMonitor].[dbo].[MonitorMailMessage].[Subject]) [LPA_m1]  

INNER JOIN [Prisma_Dev_MailMonitor].[dbo].[MonitorMailMessage] [LPA_m2]  ON  [LPA_m1].[ID] = [LPA_m2].[ID])

I have 2 questions left:

• How do I get as aditional the Count "Total" in the result back?
• How can I add sorting? when I do like:

Dim sort As New SortExpression()
                Dim direction As SortOperator
                Select sortDirection
                    Case Web.UI.WebControls.SortDirection.Ascending : direction = SortOperator.Ascending
                    Case Web.UI.WebControls.SortDirection.Descending : direction = SortOperator.Descending
                End Select
                Select Case sortOrder
                    Case "Subject"
                        sort.Add(MonitorMailMessageFields.Subject Or direction)
                        sort.Add(MonitorMailMessageFields.MailDate Or SortOperator.Descending)
                        'Case "UserName"
                        '   sort.Add(MonitorMailboxFields.UserName Or direction)
                        '   sort.Add(MonitorMailMessageFields.MailDate Or SortOperator.Descending)
                    Case Else
                        sort.Add(MonitorMailMessageFields.MailDate Or SortOperator.Descending)
                End Select

It generates

ORDER BY [Prisma_Dev_MailMonitor].[dbo].[MonitorMailMessage].[MailDate] DESC

But it should be

ORDER BY [LPA_m2].[MailDate] DESC

Thx

Where are you adding that sorter variable? Please post the final code snippet you got.

Public Shared Function SelectWide(ByVal mailboxId As Integer, ByVal searchText As String, _
                                          ByVal Grouped As Boolean, ByVal minDate As DateTime, ByVal maxDate As DateTime, _
                                          ByVal pageSize As Integer, ByVal startRow As Integer, _
                                          ByVal sortOrder As String, ByVal sortDirection As System.Web.UI.WebControls.SortDirection) As EntityCollection(Of MonitorMailMessageEntity)
            Try
                Dim sort As New SortExpression()
                Dim direction As SortOperator
                Select Case sortDirection
                    Case Web.UI.WebControls.SortDirection.Ascending : direction = SortOperator.Ascending
                    Case Web.UI.WebControls.SortDirection.Descending : direction = SortOperator.Descending
                End Select
                Select Case sortOrder
                    Case "Subject"
                        sort.Add(MonitorMailMessageFields.Subject.SetObjectAlias("mmm2") Or direction)
                        sort.Add(MonitorMailMessageFields.MailDate.SetObjectAlias("mmm2") Or SortOperator.Descending)
                    Case Else
                        sort.Add(MonitorMailMessageFields.MailDate.SetObjectAlias("mmm2") Or SortOperator.Descending)
                End Select

                Dim filter As New RelationPredicateBucket()
                filter.SelectListAlias = "mmm2"

                If (minDate > DateTime.MinValue) AndAlso (maxDate = DateTime.MinValue) Then
                    filter.PredicateExpression.Add(MonitorMailMessageFields.MailDate.SetObjectAlias("mmm2") >= minDate)
                End If
                If (minDate = DateTime.MinValue) AndAlso (maxDate > DateTime.MinValue) Then
                    filter.PredicateExpression.Add(MonitorMailMessageFields.MailDate.SetObjectAlias("mmm2") < maxDate.AddDays(1))
                End If
                If (minDate > DateTime.MinValue) AndAlso (maxDate > DateTime.MinValue) Then
                    Dim pred As New PredicateExpression()
                    pred.Add(MonitorMailMessageFields.MailDate.SetObjectAlias("mmm2") >= minDate)
                    pred.Add(MonitorMailMessageFields.MailDate.SetObjectAlias("mmm2") < maxDate.AddDays(1))
                    filter.PredicateExpression.Add(pred)
                End If

                If Not String.IsNullOrEmpty(searchText) AndAlso (searchText <> "Search...") Then ' AndAlso (searchText <> TextManager.Instance.GetTextCached("SearchText", StringType.WatermarkText))
                    filter.PredicateExpression.Add(MonitorMailMessageFields.Subject.SetObjectAlias("mmm2") Mod String.Format("%{0}%", searchText))
                End If

                If Grouped Then
                    Dim dtFields As New ResultsetFields(2)
                    dtFields.DefineField(MonitorMailMessageFields.ID, 0)
                    dtFields(0).AggregateFunctionToApply = AggregateFunction.Max
                    dtFields.DefineField(MonitorMailMessageFields.ID, 1, "Total")
                    dtFields(1).AggregateFunctionToApply = AggregateFunction.Count
                    Dim dtGroupBy As New GroupByCollection(MonitorMailMessageFields.Subject)
                    Dim dtDefinition As New DerivedTableDefinition(dtFields, "mmm1", New PredicateExpression(MonitorMailMessageFields.MailboxID = mailboxId), dtGroupBy)

                    Dim relation As New DynamicRelation(dtDefinition, JoinHint.Inner, _
                                                        EntityType.MonitorMailMessageEntity, "mmm2", (New EntityField2(MonitorMailMessageFieldIndex.ID.ToString(), "mmm1", GetType(Integer)) = _
                               MonitorMailMessageFields.ID.SetObjectAlias("mmm2")))

                    filter.Relations.Add(relation)
                Else
                    filter.PredicateExpression.Add(MonitorMailMessageFields.MailboxID.SetObjectAlias("mmm2") = mailboxId)
                End If

                Dim messages As New EntityCollection(Of MonitorMailMessageEntity)()
                Using adapter2 As New DataAccessAdapter()
                    adapter2.FetchEntityCollection(messages, filter, 0, sort, (startRow \ pageSize) + 1, pageSize)
                End Using

                Return messages

            Catch ex As Exception
                LogWriter.Instance.Write("Business", LogWriter.MessageType.Error, ex)
                Throw
            End Try
        End Function

It generates Code: ORDER BY [Prisma_Dev_MailMonitor].[dbo].[MonitorMailMessage].[MailDate] DESC

But it should be Code: ORDER BY [LPA_m2].[MailDate] DESC

What's the value of the EmitAliasForExpressionAggregateField property of the corresponding sortClause? Is it set to true?

Im am not useing an "sortClause" but a SortExpression as you can see in the code above.

Dim sort As New SortExpression()
                Dim direction As SortOperator
                Select Case sortDirection
                    Case Web.UI.WebControls.SortDirection.Ascending : direction = SortOperator.Ascending
                    Case Web.UI.WebControls.SortDirection.Descending : direction = SortOperator.Descending
                End Select
                Select Case sortOrder
                    Case "Subject"
                        sort.Add(MonitorMailMessageFields.Subject.SetObjectAlias("mmm2") Or direction)
                        sort.Add(MonitorMailMessageFields.MailDate.SetObjectAlias("mmm2") Or SortOperator.Descending)
                    Case Else
                        sort.Add(MonitorMailMessageFields.MailDate.SetObjectAlias("mmm2") Or SortOperator.Descending)
                End Select

With SetObjectAlias the sort works, but now i still need to get the Count "Total" back in the result from the Derived table.

As you can't group an entitycollection (thus, no aggregate values), you should use DynamicList instead of EntityCollection.

I just added the dynamicList but now when I try to fetch the list it throws the error: "Object reference not set to an instance of an object."

Stracktrace:

   at SD.LLBLGen.Pro.ORMSupportClasses.FieldUtilities.FixFirstFieldForSourceDetermination(IList sourceFields)
   at SD.LLBLGen.Pro.ORMSupportClasses.FieldUtilities.FixFirstFieldForSourceDetermination(IList fieldsToFix, IRelationCollection relations)
   at SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore.AddInheritanceRelatedElementsToQueryElementsForDynamicList(InheritanceHierarchyType hierarchyType, IList fields, IPredicateExpression filter, IRelationCollection relations, IInheritanceInfoProvider infoProvider, String forEntityName)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateQueryFromElements(IEntityFields2 fieldCollectionToFetch, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize, IFieldPersistenceInfo[]& persistenceInfo, IRetrievalQuery& selectQuery)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)

All the parameters that I use are set and not nothing. I hope that you can help me with this. Thx

Dim sort As New SortExpression()
                Dim direction As SortOperator
                Select Case sortDirection
                    Case Web.UI.WebControls.SortDirection.Ascending : direction = SortOperator.Ascending
                    Case Web.UI.WebControls.SortDirection.Descending : direction = SortOperator.Descending
                End Select
                Select Case sortOrder
                    Case "Subject"
                        sort.Add(MonitorMailMessageFields.Subject.SetObjectAlias("mmm2") Or direction)
                        sort.Add(MonitorMailMessageFields.MailDate.SetObjectAlias("mmm2") Or SortOperator.Descending)
                    Case Else
                        sort.Add(MonitorMailMessageFields.MailDate.SetObjectAlias("mmm2") Or SortOperator.Descending)
                End Select

                Dim filter As New RelationPredicateBucket()
                filter.SelectListAlias = "mmm2"

                If (minDate > DateTime.MinValue) AndAlso (maxDate = DateTime.MinValue) Then
                    filter.PredicateExpression.Add(MonitorMailMessageFields.MailDate.SetObjectAlias("mmm2") >= minDate)
                End If
                If (minDate = DateTime.MinValue) AndAlso (maxDate > DateTime.MinValue) Then
                    filter.PredicateExpression.Add(MonitorMailMessageFields.MailDate.SetObjectAlias("mmm2") < maxDate.AddDays(1))
                End If
                If (minDate > DateTime.MinValue) AndAlso (maxDate > DateTime.MinValue) Then
                    Dim pred As New PredicateExpression()
                    pred.Add(MonitorMailMessageFields.MailDate.SetObjectAlias("mmm2") >= minDate)
                    pred.Add(MonitorMailMessageFields.MailDate.SetObjectAlias("mmm2") < maxDate.AddDays(1))
                    filter.PredicateExpression.Add(pred)
                End If

                If Marked Then
                    filter.PredicateExpression.Add(MonitorMailMessageFields.Marked.SetObjectAlias("mmm2") = True)
                End If

                If Not String.IsNullOrEmpty(searchText) AndAlso (searchText <> "Search...") Then ' AndAlso (searchText <> TextManager.Instance.GetTextCached("SearchText", StringType.WatermarkText))
                    filter.PredicateExpression.Add(MonitorMailMessageFields.Subject.SetObjectAlias("mmm2") Mod String.Format("%{0}%", searchText))
                End If

                Dim fields As New ResultsetFields(5)

                If Grouped Then
                    Dim dtFields As New ResultsetFields(2)
                    dtFields.DefineField(MonitorMailMessageFields.ID, 0)
                    dtFields(0).AggregateFunctionToApply = AggregateFunction.Max
                    dtFields.DefineField(MonitorMailMessageFields.ID, 1, "Total")
                    dtFields(1).AggregateFunctionToApply = AggregateFunction.Count
                    Dim dtGroupBy As New GroupByCollection(MonitorMailMessageFields.Subject)
                    Dim dtDefinition As New DerivedTableDefinition(dtFields, "mmm1", New PredicateExpression(MonitorMailMessageFields.MailboxID = mailboxId), dtGroupBy)

                    Dim relation As New DynamicRelation(dtDefinition, JoinHint.Inner, _
                                                        EntityType.MonitorMailMessageEntity, "mmm2", (New EntityField2(MonitorMailMessageFieldIndex.ID.ToString(), "mmm1", GetType(Integer)) = _
                               MonitorMailMessageFields.ID.SetObjectAlias("mmm2")))

                    filter.Relations.Add(relation)


                    fields.DefineField(MonitorMailMessageFields.ID, 0, "ID")
                    fields.DefineField(MonitorMailMessageFields.Subject, 1, "Subject")
                    fields.DefineField(MonitorMailMessageFields.MailDate, 2, "MailDate")
                    fields.DefineField(MonitorMailMessageFields.Marked, 3, "Marked")
                    fields.DefineField(dtFields(1), 4, "Total")

                Else
                    filter.PredicateExpression.Add(MonitorMailMessageFields.MailboxID.SetObjectAlias("mmm2") = mailboxId)
                End If
                Dim dynamicList As New DataTable()

                Using adapter2 As New DataAccessAdapter()
                    adapter2.FetchTypedList(fields, dynamicList, filter, 0, sort, False, Nothing, (startRow \ pageSize) + 1, pageSize)
                End Using

That's too much code to inspect, can you please simplify it. try to post a small repro code that produces the same error. (This exercise might also help you identify the issue).

Also the following warning was in the docs:

Fetches of entities in an inheritance hierarchy by using a Derived Table as the source to fetch the data from is discouraged if the Derived Table is specified manually. This is because the framework needs to formulate the query in a certain way to be able to determine the entity type of the rows returned. If a derived table is used, this isn't necessarily the case as the Derived Table might have a completely different resultset, which means that the resulting resultset isn't necessarily the resultset usable to fetch entities in an inheritance hierarchy.

Does this apply to your case?