Hi all,

I'm trying to get some data as an IDataReader using a filter that consists of fields from 2 different entities, here's my code:

       TypedListDAO dao = new TypedListDAO(); 
        ResultsetFields fields = new ResultsetFields(2);
        fields[0] = ContentLiveFields.Id;
        fields[1] = ContentLiveFields.Title;

        IPredicateExpression filter =
            new PredicateExpression(ContentLiveFields.ContentTypeId == (int) contentTypeId);
        filter.AddWithAnd(ContentLiveFields.SuspendedFlag == false);
        filter.AddWithAnd(ContentDraftFields.PublicationStatus < 20);

        ISortExpression sorter;
        if (contentTypeId == ContentTypeID.News)
            sorter = new SortExpression(ContentLiveFields.EmbargoDate | SortOperator.Descending);
        else
            sorter = new SortExpression(ContentLiveFields.CreationDate | SortOperator.Descending);

        IRelationCollection relations = new RelationCollection();
        relations.Add(new EntityRelation(ContentDraftFields.Id, ContentLiveFields.DraftId, RelationType.OneToOne));

        IDataReader reader = dao.GetAsDataReader(null, fields, filter, null, CommandBehavior.CloseConnection, itemsToRetrieve, sorter, false);
        return reader;

Problem is, when I use the RelationsCollection, maxNumberOfItemsToReturn stops working. If I take the relationCollection out of the call to GetAsDataReader (and remove the external field from the filter), it works fine, bringing back to correct number of items. Am I missing something?

LLBLGen version: 2.0.0.0 (Dec 6th 2006) Database: SqlServer 2005

Hi. My tests reflects this:

ResultsetFields fields = new ResultsetFields(3);
// simply set the fields in the indexes, which will use the field name for the column name
fields[0] = CustomersFields.CustomerId;
fields[1] = CustomersFields.CompanyName;
fields[2] = OrdersFields.OrderId;
PredicateExpression filter = new PredicateExpression(CustomersFields.Country == "Germany");
RelationCollection relations = new RelationCollection();
relations.Add(CustomersEntity.Relations.OrdersEntityUsingCustomerId);

TypedListDAO dao = new TypedListDAO();
IDataReader reader = dao.GetAsDataReader(null, fields, filter, relations, CommandBehavior.CloseConnection, 10, false);

while (reader.Read())
{
    Console.WriteLine("Row: {0} | {1} | {2} |",
        reader.GetValue(0), reader.GetValue(1), reader.GetValue(2));
}
reader.Close();

results: 10 records returned, using relations and allowDuplicate=false (important).

Could you try again and post the exactly code that doesn't work (using relations), also post the Verbose level tracing SQL generated.

The exact code i'm using is as follows

            TypedListDAO dao = new TypedListDAO(); 
            ResultsetFields fields = new ResultsetFields(4);
            fields[0] = ContentLiveFields.Id;
            fields[1] = ContentLiveFields.Title;
            fields[2] = ContentLiveFields.Abstract;
            fields[3] = ContentLiveFields.EmbargoDate;


            IPredicateExpression filter =
                new PredicateExpression(ContentLiveFields.ContentTypeId == (int) contentTypeId);
            filter.AddWithAnd(ContentLiveFields.SuspendedFlag == false);
            filter.AddWithAnd(ContentDraftFields.PublicationStatus < 20);

            ISortExpression sorter;
            if (contentTypeId == ContentTypeID.News)
                sorter = new SortExpression(ContentLiveFields.EmbargoDate | SortOperator.Descending);
            else
                sorter = new SortExpression(ContentLiveFields.CreationDate | SortOperator.Descending);

            IRelationCollection relations = new RelationCollection();
            relations.Add(new EntityRelation(ContentDraftFields.Id, ContentLiveFields.DraftId, RelationType.OneToOne));

            IDataReader reader = dao.GetAsDataReader(null, fields, filter, relations, CommandBehavior.CloseConnection, 5, sorter, false);
            return reader;

Note that i'm explicitly setting maxNumberOfItemsToReturn to 5 and allowDuplicate to false.

SQL Trace is:

exec sp_executesql N'SELECT [dbname].[dbo].[content_live].[id] AS [Id], [dbname].[dbo].[content_live].[title] AS [Title], 
[dbname].[dbo].[content_live].[abstract] AS [Abstract], [dbname].[dbo].[content_live].[embargo_date] AS [EmbargoDate] 
FROM ( [dbname].[dbo].[content_draft]  
INNER JOIN [dbname].[dbo].[content_live]  ON  [dbname].[dbo].[content_draft].[id]=[dbname].[dbo].[content_live].[draft_id]) 
WHERE ( ( [dbname].[dbo].[content_live].[content_type_id] = @ContentTypeId1 
    AND [dbname].[dbo].[content_live].[suspended_flag] = @SuspendedFlag2 
    AND [dbname].[dbo].[content_draft].[publication_status] < @PublicationStatus3)) 
    ORDER BY [dbname].[dbo].[content_live].[creation_date] DESC',
    N'@ContentTypeId1 int,@SuspendedFlag2 bit,@PublicationStatus3 int',@ContentTypeId1=5,@SuspendedFlag2=0,@PublicationStatus3=20

The plot thickens though, I've noticed that this is only a problem when the contentTypeId variable (used in the first predicate of the filter) is set to a certain value. When there is a problem, contentTypeId is set to 5. If i set it to 2, I get the following correct sql trace output and the correct number of items:

exec sp_executesql N'SELECT DISTINCT TOP 5 [dbname].[dbo].[content_live].[id] AS [Id], [dbname].[dbo].[content_live].[title] AS [Title], 
[dbname].[dbo].[content_live].[abstract] AS [Abstract], [dbname].[dbo].[content_live].[embargo_date] AS [EmbargoDate] 
FROM ( [dbname].[dbo].[content_draft]  
INNER JOIN [dbname].[dbo].[content_live]  ON  [dbname].[dbo].[content_draft].[id]=[dbname].[dbo].[content_live].[draft_id]) 
WHERE ( ( [dbname].[dbo].[content_live].[content_type_id] = @ContentTypeId1 
    AND [dbname].[dbo].[content_live].[suspended_flag] = @SuspendedFlag2 
    AND [dbname].[dbo].[content_draft].[publication_status] < @PublicationStatus3)) 
    ORDER BY [dbname].[dbo].[content_live].[embargo_date] DESC',
    N'@ContentTypeId1 int,@SuspendedFlag2 bit,@PublicationStatus3 int',@ContentTypeId1=2,@SuspendedFlag2=0,@PublicationStatus3=20

Hello,

what are the differences between your two entities? If you directly execute your request in sql, for the contenttypeid=5 do you notice something different?

Explanataion is here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=4021