I can see in the documentation that you should be able to do what I need through the LLBL object model, I just cannot seem to wrap my brain around it yet. I would really appreciate a pointer in the right direction. I am going to have an administrative page for viewing usage stats, etc. Here's a sample (not complete, but you get the idea) stored proc for the type of view I want to create:

CREATE Procedure [dbo].[pr_GetUserStats]
@StartDate datetime,
@EndDate datetime

AS

SELECT COUNT(*) as TotalEventsCreated
FROM Events 
WHERE
    DATEDIFF(day, CreateDate, @StartDate) <= 0
    AND DATEDIFF(day, CreateDate, @EndDate) >= 0

SELECT
    count(ev.EventID) as NumEventsCreated,
    m.MemberName as CreatedBy
FROM 
    Events ev
    JOIN members m
        on m.MemberID = ev.CreatedBy
WHERE
    DATEDIFF(day, ev.CreateDate, @StartDate) <= 0
    AND DATEDIFF(day, ev.CreateDate, @EndDate) >= 0
GROUP BY
    m.MemberName
ORDER BY
    NumEventsCreated desc

GO

This returns a resultset of the total count of events and a resultset of members with their individual counts of events created. Ideally there would also be paging, etc., but I'm not quite that far yet... Can anyone give me some pseudo-code (or even better, real code) that shows how you would implement a similar query through the LLBL object model?

I'm really struggling with knowing WHAT I want to do but not really quite grasping the LLBL syntax for it once I get beyond the simple loading of entities and collections. Hopefully it gets a little easier with practice...

bmoeskau wrote:

I can see in the documentation that you should be able to do what I need through the LLBL object model, I just cannot seem to wrap my brain around it yet. I would really appreciate a pointer in the right direction. I am going to have an administrative page for viewing usage stats, etc. Here's a sample (not complete, but you get the idea) stored proc for the type of view I want to create:

CREATE Procedure [dbo].[pr_GetUserStats]
@StartDate datetime,
@EndDate datetime
AS
SELECT COUNT(*) as TotalEventsCreated
FROM Events 
WHERE
    DATEDIFF(day, CreateDate, @StartDate) <= 0
    AND DATEDIFF(day, CreateDate, @EndDate) >= 0

The main problem is DATEDIFF. You can however rewrite this to:

SELECT COUNT(*) as TotalEventsCreated
FROM Events 
WHERE
    CreateDate BETWEEN @StartDate AND @EndDate

You can then write that as a call to GetDbCount on the Adapter (if you're using adapter) or the TotalEventsCreatedCollection if you're using selfservicing, by passing in a Between predicate created using PredicateFactory.Between(). Be sure you pass in startdate as a date with time = 0.0.0 and enddate with a time set to 23.59.59.

SELECT
    count(ev.EventID) as NumEventsCreated,
    m.MemberName as CreatedBy
FROM 
    Events ev
    JOIN members m
        on m.MemberID = ev.CreatedBy
WHERE
    DATEDIFF(day, ev.CreateDate, @StartDate) <= 0
    AND DATEDIFF(day, ev.CreateDate, @EndDate) >= 0
GROUP BY
    m.MemberName
ORDER BY
    NumEventsCreated desc
GO

You can create this using a dynamic list. To get you started:

ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(EventsFieldIndex.EventID, 0, "NumEventsCreated", string.Empty, AggregateFunction.Count);
fields.DefineField(MembersFieldIndex.MemberName, 1, "CreatedBy");
// in selfservicing, you have to use a separate relationcollection and predicateexpression
// instead of a RelationPredicateBucket which is adapter specific
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(EventsEntity.Relations.MembersUsingMemberID);
// add here the between predicate as created with the GetDBCount 

// then create a groupby:
GroupByCollection groupBy = new GroupByCollection(0;
groupBy.Add(fields[1]);

// then create a sortexpression 

Then use the dynamic list fetch logic as described in Using the generated code -> selfservicing/adapter -> Using the typed view and typed list classes -> Creating Dynamic lists