I and new to LLBLGen, really like what I have seen, but now I am lost on how to take it to the next step. Done the basic stuff now I require to get more complicated. My original SQL looks like this.

SELECT jg.JobGroup, jg.JobGroupName, jg.EEOCode, jc.JobCode, jc.JobTitle, g.Grade,
    e.Total, e.[Min], e.Fem, e.Asi, e.Blk, e.His, e.Ind, e.Isl, e.Oth, e.Wht
FROM JobGroup jg
    INNER JOIN JobCode jc ON jg.PlanID = jc.PlanID AND jg.JobGroup = jc.JobGroup
    INNER JOIN Grade g ON g.PlanID = jc.PlanID AND g.Grade = jc.Grade
    INNER JOIN Employment e ON e.PlanID = jc.PlanID AND e.JobCode = jc.JobCode
WHERE jg.PlanID = @PlanID
ORDER BY jg.DispSeq, jg.JobGroup

Basically i need to do this stored Proc in code, I did all the gen stuff already, works great. And I need to return it as a datatable, for our report generator to use.

The Stored Proc works great, now I need to be able to do this in LLBLGen. Right now were testing many products and tryign to find the right fit for our company. I really like this product. The documantation could be better. But thats not what I'm here to find out. So far i have come up wiht this.

            Using objAdapter As New DatabaseSpecific.DataAccessAdapter
                Using objJobGroup As New EntityCollection(Of JobGroupEntity)(New JobGroupEntityFactory())
                    Dim objBucket As New RelationPredicateBucket
                    Dim objSorter As New SortExpression()
                    Dim dtprojection = New DataTable()
                    Dim objProjectList As New Generic.List(Of IEntityPropertyProjector)

                    Try

                        objBucket.Relations.Add(EntityClasses.JobCodeEntity.Relations.JobGroupEntityUsingPlanIdJobGroup, "", "", JoinHint.Inner)
                        objBucket.Relations.Add(EntityClasses.GradeEntity.Relations.JobCodeEntityUsingPlanIdGrade, "", "", JoinHint.Inner)
                        objBucket.Relations.Add(EntityClasses.EmploymentEntity.Relations.JobCodeEntityUsingPlanIdJobCode, "", "", JoinHint.Inner)
                        objBucket.PredicateExpression.Add(New PredicateExpression(HelperClasses.JobGroupFields.PlanId = mintPlanID))
                        objSorter.Add(HelperClasses.JobGroupFields.DispSeq Or SortOperator.Ascending)
                        objSorter.Add(HelperClasses.JobGroupFields.JobGroup Or SortOperator.Ascending)
                        objAdapter.FetchEntityCollection(objJobGroup, objBucket, Nothing, objSorter)


                        'create the entiy view for passing data top the datatable
                        Using objView As New EntityView2(Of JobGroupEntity)(objJobGroup)

                            'project the ddata to a datatable
                            objProjectList.Add(New EntityPropertyProjector(JobGroupFields.JobGroup, "JobGroup"))
                            objProjectList.Add(New EntityPropertyProjector(JobGroupFields.JobGroupName, "JobGroupName"))
                            objProjectList.Add(New EntityPropertyProjector(JobGroupFields.Eeocode, "Eeocode"))
                            objProjectList.Add(New EntityPropertyProjector(JobCodeFields.JobCode, "JobCode"))
                            objProjectList.Add(New EntityPropertyProjector(JobCodeFields.JobTitle, "JobTitle"))
                            objProjectList.Add(New EntityPropertyProjector(GradeFields.Grade, "Grade"))
                            objProjectList.Add(New EntityPropertyProjector(EmploymentFields.Total, "Total"))
                            objProjectList.Add(New EntityPropertyProjector(EmploymentFields.Min, "Min"))
                            objProjectList.Add(New EntityPropertyProjector(EmploymentFields.Fem, "Fem"))
                            objProjectList.Add(New EntityPropertyProjector(EmploymentFields.Asi, "Asi"))
                            objProjectList.Add(New EntityPropertyProjector(EmploymentFields.Blk, "Blk"))
                            objProjectList.Add(New EntityPropertyProjector(EmploymentFields.His, "His"))
                            objProjectList.Add(New EntityPropertyProjector(EmploymentFields.Ind, "Ind"))
                            objProjectList.Add(New EntityPropertyProjector(EmploymentFields.Isl, "Isl"))
                            objProjectList.Add(New EntityPropertyProjector(EmploymentFields.Oth, "Oth"))
                            objProjectList.Add(New EntityPropertyProjector(EmploymentFields.Wht, "Wht"))

                            ' create the actual projection.
                            objView.CreateProjection(objProjectList, dtprojection)

                            Return dtprojection
                        End Using

                    Finally
                        If Not objBucket Is Nothing Then
                            objBucket = Nothing
                        End If
                        If Not objSorter Is Nothing Then
                            objSorter = Nothing
                        End If
                        If Not dtprojection Is Nothing Then
                            dtprojection = Nothing
                        End If
                        If Not objProjectList Is Nothing Then
                            objProjectList = Nothing
                        End If
                    End Try
                End Using
            End Using

I've done some break points before the projection and the view is not populated correctly. Using LLBLGen Pro 2.5.

I would really appreciate any help. Please don't post its in the mauanl, cause I've been looking thru it for 2 days and have not solved the problem.

Thanks

Should i be looking at Typed List? If so is there a good example some where?

there doesn't seem to be anything fancy about your query, this could be created as a typedlist instead of a dynamic list. the code generator would then generate the typedlist for you. (a typed list is a datatable). this is redundant.

objBucket.Relations.Add(EntityClasses.JobCodeEntity.Relations.JobGroupEntityUsingPlanIdJobGroup, "", "", JoinHint.Inner)

could simply be

objBucket.Relations.Add(EntityClasses.JobCodeEntity.Relations.JobGroupEntityUsingPlanIdJobGroup)

not sure why your using projection. this is used when you want to map the database to custom business object. you want a datatable to a typedlist would be better. here is the shell you would need

IEntityFields2 fields = new ResultSet(NumberOfFields)
fields[0] = EmploymentFields.FieldName1;
fields[1] = EmploymentFields.FieldName2;
...
fields[N] = EmploymentFields.FieldName3;

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(MyEntity.Relations.SomeEntityUsingSomeField);
bucket.PredicateExpression.Add(MyEntityFields.SomeField == aValue);

ISortExpression sorter = new SortExpression();
sorter.Add(MyEntityFields.SomeField | SortOperator.Ascending);

DataTable results = new DataTable();
using(IDataAccessAdapter adapter = new DataAccessAdapter)
{
     adapter.FetchTypedList(fields, results, bucket, 0, sorter);
}
return results;

if you create the typedlist in the designer the code would look something like this

MyTypedList results = new MyTypedList();

IRelationPredicateBucket bucket = result.GetRelationshipBucket(); //don't rembmer the exact method.

ISortExpression sorter = new SortExpression();
sorter.Add(MyEntityFields.SomeField | SortOperator.Ascending);

using(IDataAccessAdapter adapter = new DataAccessAdapter)
{
     adapter.FetchTypedList(fields, results, bucket, 0, sorter);
}
return results;

This seemed to work, doing some basic checking, size of the datatable is correct.

[colorvalue="0011FF"]
                Using objTypedListJobGroup As New AAP.TypedListClasses.JobGroupReportTypedList
                    Dim objb As IRelationPredicateBucket
                    Dim objSorter As New SortExpression()
                    Dim objPredicate As IPredicateExpression

                    Try
                        objPredicate = New PredicateExpression(HelperClasses.JobGroupFields.PlanId = mintPlanID)

                        objSorter.Add(HelperClasses.JobGroupFields.DispSeq Or SortOperator.Ascending)
                        objSorter.Add(HelperClasses.JobGroupFields.JobGroup Or SortOperator.Ascending)
                        Using objAdapter As New DatabaseSpecific.DataAccessAdapter
                            objAdapter.FetchTypedList(objTypedListJobGroup, objPredicate, 0, objSorter, True)
                        End Using

                        Return objTypedListJobGroup
                    Finally
                        If Not objSorter Is Nothing Then
                            objSorter = Nothing
                        End If
                    End Try
                End Using
[/color]

Unsure why it works and how it knows to do inner joins? In your example, i tried using the IRelationPredicateBucket but hat thru an error, could not convert bucket to predicate expression. And not sure what yoour first parameter of "Fields" is.

It appears to be working, and much shorter code. Was not happy doing all that projection stuff, seemed very excessive. The typed set was definitely the way to go.

Is there anyway to see what the Dynamic SQL this fetch is generating?

Just so anyone else wnats to know. Defintiely create somehting like I have in the Generator. Its got some great features, and some how knew exactly what i was going for. The above code worked great, exactly lie the stored proc.

Basically in the Generator when you created a typed list you'll get all the possible choices for joins in the "Entity Selection" tab. Only change I made was to "Hint" at inner joins, which seemed to be the default anyway.

Great job LLBLGen, although it took awhile to find, it worked great.

Thanks