Hey folks,

I am having a problem using LLBLGen. I used the code mentioned in this article http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=6370 and edited the way I think it has to be. The code is as followes:

string requestStatusCode = "Registered"; // RequestStatus.Code
int requestID = 0;// RequestStatusHistory.RequestID

IPredicateExpression subQuery = new PredicateExpression();
subQuery.Add(PredicateFactory.CompareExpression(RequestStatusFieldIndex.RequestStatusId, ComparisonOperator.Equal, new Expression(EntityFieldFactory.Create(RequestStatusHistoryFieldIndex.RequestStatusHistoryId)), "A2"));
subQuery.Add(PredicateFactory.CompareValue(RequestStatusFieldIndex.Code, ComparisonOperator.Equal, requestStatusCode));

IRelationCollection relations = new RelationCollection();
relations.Add(RequestStatusHistoryEntity.Relations.RequestStatusEntityUsingRequestStatusId, "A2");

IEntityField field1 = EntityFieldFactory.Create(RequestStatusHistoryFieldIndex.StartDate);
IEntityField field2 = EntityFieldFactory.Create(RequestStatusHistoryFieldIndex.StartDate);
field2.ObjectAlias = "A2";
field2.AggregateFunctionToApply = AggregateFunction.Max;

GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(field1);
groupBy.Add(field2);
field2.AggregateFunctionToApply = AggregateFunction.Max;

IPredicate predicate1 = new FieldCompareSetPredicate(field1, null, field2, null, SetOperator.Equal, subQuery);

PredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareSetPredicate(field1, null, field2, null, SetOperator.In, subQuery, relations, "A2", 100, null, false, groupBy));
filter.AddWithAnd(RequestStatusHistoryFields.RequestId == requestID);


RequestStatusHistoryCollection rshC = new RequestStatusHistoryCollection();
rshC.GetMulti(filter, 0, null, relations);

I am using these tables: - Request (1:n to RequestStatusHistory) -- RequestID - RequestStatus (1:n to RequestStatusHistory) -- Code - RequestStatusHistory -- RequestStatusID -- RequestID -- StartDate

I am getting a NullReferenceException at the GetMulti, and this message is the unuseful I can get. I really haven't got any idea.

I hope you have enough information.

In advance, thanks for your help!

Patrick

I'm unclear what your database structure is. I had thought there were only 2 tables until I got to your code, which appears to have 3. Can you clarify this for us? If you could post the CREATE TABLE script containing all your tables, that would help. Thanks.

Chester wrote:

I'm unclear what your database structure is. I had thought there were only 2 tables until I got to your code, which appears to have 3. Can you clarify this for us? If you could post the CREATE TABLE script containing all your tables, that would help. Thanks.

Ok, here it is!

[code] /****** Object: Table [dbo].[Request] Script Date: 09/03/2006 22:24:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Request]( [RequestID] [int] IDENTITY(1,1) NOT NULL, [UserID] [int] NOT NULL, [PublicationID] [int] NOT NULL, [RequestDate] [datetime] NOT NULL, [Quantity] [int] NOT NULL, CONSTRAINT [PK_Request] PRIMARY KEY CLUSTERED ( [RequestID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]

GO /****** Object: Table [dbo].[RequestStatus] Script Date: 09/03/2006 22:24:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[RequestStatus]( [RequestStatusID] [int] IDENTITY(1,1) NOT NULL, [Code] nvarchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_RequestStatus] PRIMARY KEY CLUSTERED ( [RequestStatusID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY], CONSTRAINT [IX_RequestStatus] UNIQUE NONCLUSTERED (

 ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[RequestStatusHistory] Script Date: 09/03/2006 22:24:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RequestStatusHistory](
    [RequestStatusHistoryID] [int] IDENTITY(1,1) NOT NULL,
    [RequestID] [int] NOT NULL,
    [RequestStatusID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_RequestStatusHistory] PRIMARY KEY CLUSTERED 
(
    [RequestStatusHistoryID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[RequestStatusHistory]  WITH CHECK ADD  CONSTRAINT [FK_RequestStatusHistory_Request] FOREIGN KEY([RequestID])
REFERENCES [dbo].[Request] ([RequestID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[RequestStatusHistory] CHECK CONSTRAINT [FK_RequestStatusHistory_Request]
GO
ALTER TABLE [dbo].[RequestStatusHistory]  WITH CHECK ADD  CONSTRAINT [FK_RequestStatusHistory_RequestStatus] FOREIGN KEY([RequestStatusID])
REFERENCES [dbo].[RequestStatus] ([RequestStatusID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[RequestStatusHistory] CHECK CONSTRAINT [FK_RequestStatusHistory_RequestStatus]

            ResultsetFields fields = new ResultsetFields(2);
            fields.DefineField(RequestStatusHistoryFieldIndex.RequestId, 0, "RequestID", "A2");
            fields.DefineField(RequestStatusHistoryFieldIndex.StartDate, 1, "StartDate", "A2");
            fields[1].AggregateFunctionToApply = AggregateFunction.Max;

            GroupByCollection groupBy = new GroupByCollection();
            groupBy.Add(fields[0]);

            PredicateExpression subSelectFilter = new PredicateExpression();
            subSelectFilter.Add(PredicateFactory.CompareExpression(RequestStatusHistoryFieldIndex.RequestId, ComparisonOperator.Equal, new Expression(RequestStatusHistoryFields.RequestId), "A2"));

            PredicateExpression selectFilter = new PredicateExpression(new FieldCompareSetPredicate(RequestStatusHistoryFields.StartDate, fields[1], SetOperator.In, subSelectFilter, null, "", 0, null, false, groupBy));

            RequestStatusHistoryCollection rshC = new RequestStatusHistoryCollection();
            rshC.GetMulti(selectFilter);

This is what I have got now. Not filtered, but that is ok for now. At least it works!

Thanks for helping me. By using SQL Profiler and the user reference manual I learned a lot about the way this works.

Thanks for helping anyway!

(The code is for anyone else who wants to know how it works.)

P.S. The SQL output is like this:

SELECT *
FROM   RequestStatusHistory
WHERE  StartDate IN
(
  SELECT MAX(A2.StartDate)
  FROM   RequestStatusHistory AS A2
  WHERE  A2.RequestID = RequestStatusHistory.RequestID
  GROUP
  BY     A2.RequestID
)