Hi, i have such an entity structure: gradeLevel entity: description, grade chair staff: staffId, some more fields user: userid, fullname, some more field

staff is a subtype of user. grade chair is the foreign key that relates gradelevel with staff. not every grade level has a grade chair so it is a nullable column.

using this structure i am trying to create a datatable that lists all gradelevels with the following fields: gradelevel description, gradechair fullname.

i have 10 items in grade level table and only one of them has a grade chair assigned. whatever i do all i get is this one item since it has a gradechair. i did set obeyweakrelations to true but somehow having staff as the subtype of user wont use it and only returns the item that isnt null.

how can i get all gradelevels with grade chairs fullname? if no grade chair is assigned to a gradelevel, fullname will return null and wont prevent me from getting the gradelevel.

thanks in advance -shane

So, apparently you are creating a DynamicList, right? Please show us the code you have so far and the generated sql it produces. Remember that you also can add a relation indicating explicitly the kind of relation you want (left, right, none).

daelmo wrote:

So, apparently you are creating a DynamicList, right? Please show us the code you have so far and the generated sql it produces. Remember that you also can add a relation indicating explicitly the kind of relation you want (left, right, none).

here it is: (there is one more field here but the idea is the same)

var fields = new ResultsetFields(4); fields.DefineField(GradeLevelFields.GradeLevelId, 0); fields.DefineField(GradeLevelFields.ShortDescription, 1); fields.DefineField(GradelevelAcademicyearFields.CalculationMethod, 2); fields.DefineField(StaffFields.FullName, 3, "GradeChair");

        IRelationCollection relations = new RelationCollection
            {
                GradeLevelEntity.Relations.GradelevelAcademicyearEntityUsingGradeLevelId,
                GradelevelAcademicyearEntity.Relations.StaffEntityUsingGradeLevelChairId
            };
        relations.ObeyWeakRelations = true;

        var filter = new PredicateExpression { GradelevelAcademicyearFields.AcademicYearId == DBNull.Value };
        filter.AddWithOr(GradelevelAcademicyearFields.AcademicYearId == academicYearId);

        var sorter = new SortExpression(GradeLevelFields.IndexNumber | SortOperator.Ascending);

        var toReturn = new DataTable();
        var dao = new TypedListDAO();
        dao.GetMultiAsDataTable(fields, toReturn, 0, sorter, filter, relations, false, null, null, 0, 0);
        return toReturn;

here is the sql: (it right joins staff and user tables where it should left join them)

exec sp_executesql N'SELECT [cssisDB].[Core].[GradeLevel].[GradeLevelID] AS [GradeLevelId], [cssisDB].[Core].[GradeLevel].[ShortDescription], [cssisDB].[Core].[GradelevelAcademicyear].[CalculationMethod], [LPA_L1].[FullName] AS [GradeChair] FROM ((( [cssisDB].[Core].[GradeLevel] LEFT JOIN [cssisDB].[Core].[GradelevelAcademicyear] ON [cssisDB].[Core].[GradeLevel].[GradeLevelID]=[cssisDB].[Core].[GradelevelAcademicyear].[GradeLevelID]) LEFT JOIN [cssisDB].[Core].[Staff] [LPA_L2] ON [LPA_L2].[StaffID]=[cssisDB].[Core].[GradelevelAcademicyear].[GradeLevelChairID]) RIGHT JOIN [cssisDB].[Core].[User] [LPA_L1] ON [LPA_L1].[UserID]=[LPA_L2].[StaffID]) WHERE ( ( [cssisDB].[Core].[GradelevelAcademicyear].[AcademicYearID] IS NULL OR [cssisDB].[Core].[GradelevelAcademicyear].[AcademicYearID] = @AcademicYearId1) AND ( ( [LPA_L2].[StaffID] IS NOT NULL))) ORDER BY [cssisDB].[Core].[GradeLevel].[IndexNumber] ASC',N'@AcademicYearId1 int',@AcademicYearId1=1

LLBLGen Framework is clever to understand how the relations are built and it tires to do LEFT or RIGHT join depends on the situation. To understand this you should check carefully your table relations (how are setup already?).

Did you try to explicit indicate the Left join in the .Add method of the relations?

daelmo wrote:

LLBLGen Framework is clever to understand how the relations are built and it tires to do LEFT or RIGHT join depends on the situation. To understand this you should check carefully your table relations (how are setup already?).

Did you try to explicit indicate the Left join in the .Add method of the relations?

Yes i did show the left join. here is what i have now: var fields = new ResultsetFields(5); fields.DefineField(GradeLevelFields.GradeLevelId, 0); fields.DefineField(GradeLevelFields.ShortDescription, 1); fields.DefineField(GradelevelAcademicyearFields.CalculationMethod, 2); fields.DefineField(AcademicYearFields.AcademicYearName, 3, "Year"); fields.DefineField(StaffFields.FullName, 4, "GradeChair");

        IRelationCollection relations = new RelationCollection
            {
                { GradeLevelEntity.Relations.GradelevelAcademicyearEntityUsingGradeLevelId, JoinHint.Left },
                { GradelevelAcademicyearEntity.Relations.StaffEntityUsingGradeLevelChairId, JoinHint.Left },
                GradelevelAcademicyearEntity.Relations.AcademicYearEntityUsingAcademicYearId
            };

There are 16 items i am expecting but this only returns me one. i get what i want in sql managers view designer with no problem. Attached is a snapshot of what i do and what i get using the view designer. what am i missing in llblgen code?

the problem is llblgen generated sql adds this at the end of the query:

( [LPA_L2].[StaffID] IS NOT NULL)

where lap_l2 is the Staff table.

i have no such filter but i believe because of the subtype/supertype relationship between staff and user, it is added at the end.

any way to prevent it from happening?

Please examine the generated SQL and post it here.

Also provide the runtime library version (build number) used.

Walaa wrote:

Please examine the generated SQL and post it here.

Also provide the runtime library version (build number) used.

i did post the generated sql in my second message.

not sure about the build # but it is oct 4 2010 dated.

Sorry I wasn't clear.

• I ment the SQL code crossponding to your last code piece, the one using the JoinHint.Left.
• As for the RTL build number, please check the following thread for more details on that: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7725

Walaa wrote:

Sorry I wasn't clear.

• I ment the SQL code crossponding to your last code piece, the one using the JoinHint.Left.
• As for the RTL build number, please check the following thread for more details on that: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7725

there is no build number nowhere around the designer or the about screen. i believe it is :09302010

here is what i have right now: var fields = new ResultsetFields(5); fields.DefineField(GradeLevelFields.GradeLevelId, 0); fields.DefineField(GradeLevelFields.ShortDescription, 1); fields.DefineField(GradelevelAcademicyearFields.CalculationMethod, 2); fields.DefineField(AcademicYearFields.AcademicYearName, 3, "Year"); fields.DefineField(StaffFields.FullName, 4, "GradeChair");

        IRelationCollection relations = new RelationCollection
            {
                { GradeLevelEntity.Relations.GradelevelAcademicyearEntityUsingGradeLevelId, JoinHint.Left },
                { GradelevelAcademicyearEntity.Relations.StaffEntityUsingGradeLevelChairId, JoinHint.Left },
                { GradelevelAcademicyearEntity.Relations.AcademicYearEntityUsingAcademicYearId, JoinHint.Left }
            };

        var filter = new PredicateExpression { GradelevelAcademicyearFields.AcademicYearId == DBNull.Value };
        filter.AddWithOr(GradelevelAcademicyearFields.AcademicYearId == academicYearId);

        var sorter = new SortExpression(GradeLevelFields.IndexNumber | SortOperator.Ascending);

        var toReturn = new DataTable();
        var dao = new TypedListDAO();
        dao.GetMultiAsDataTable(fields, toReturn, 0, sorter, filter, relations, false, null, null, 0, 0);
        return toReturn;

and here is the sql it generated:

exec sp_executesql N'SELECT [cssisDB].[Core].[GradeLevel].[GradeLevelID] AS [GradeLevelId], [cssisDB].[Core].[GradeLevel].[ShortDescription], [cssisDB].[Core].[GradelevelAcademicyear].[CalculationMethod], [cssisDB].[Core].[AcademicYear].[AcademicYearName] AS [Year], [LPA_L1].[FullName] AS [GradeChair] FROM (((( [cssisDB].[Core].[GradeLevel] LEFT JOIN [cssisDB].[Core].[GradelevelAcademicyear] ON [cssisDB].[Core].[GradeLevel].[GradeLevelID]=[cssisDB].[Core].[GradelevelAcademicyear].[GradeLevelID]) LEFT JOIN [cssisDB].[Core].[Staff] [LPA_L2] ON [LPA_L2].[StaffID]=[cssisDB].[Core].[GradelevelAcademicyear].[GradeLevelChairID]) LEFT JOIN [cssisDB].[Core].[User] [LPA_L1] ON [LPA_L1].[UserID]=[LPA_L2].[StaffID]) LEFT JOIN [cssisDB].[Core].[AcademicYear] ON [cssisDB].[Core].[AcademicYear].[AcademicYearID]=[cssisDB].[Core].[GradelevelAcademicyear].[AcademicYearID]) WHERE ( ( [cssisDB].[Core].[GradelevelAcademicyear].[AcademicYearID] IS NULL OR [cssisDB].[Core].[GradelevelAcademicyear].[AcademicYearID] = @AcademicYearId1) AND ( ( [LPA_L2].[StaffID] IS NOT NULL))) ORDER BY [cssisDB].[Core].[GradeLevel].[IndexNumber] ASC',N'@AcademicYearId1 int',@AcademicYearId1=1

e106199 wrote:

Walaa wrote:

there is no build number nowhere around the designer or the about screen. i believe it is :09302010

Here is how to obtain the RTL : http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7725

e106199 wrote:

and here is the sql it generated:

exec sp_executesql N'SELECT [cssisDB].[Core].[GradeLevel].[GradeLevelID] AS [GradeLevelId], [cssisDB].[Core].[GradeLevel].[ShortDescription], [cssisDB].[Core].[GradelevelAcademicyear].[CalculationMethod], [cssisDB].[Core].[AcademicYear].[AcademicYearName] AS [Year], [LPA_L1].[FullName] AS [GradeChair] FROM (((( [cssisDB].[Core].[GradeLevel] LEFT JOIN [cssisDB].[Core].[GradelevelAcademicyear] ON [cssisDB].[Core].[GradeLevel].[GradeLevelID]=[cssisDB].[Core].[GradelevelAcademicyear].[GradeLevelID]) LEFT JOIN [cssisDB].[Core].[Staff] [LPA_L2] ON [LPA_L2].[StaffID]=[cssisDB].[Core].[GradelevelAcademicyear].[GradeLevelChairID]) LEFT JOIN [cssisDB].[Core].[User] [LPA_L1] ON [LPA_L1].[UserID]=[LPA_L2].[StaffID]) LEFT JOIN [cssisDB].[Core].[AcademicYear] ON [cssisDB].[Core].[AcademicYear].[AcademicYearID]=[cssisDB].[Core].[GradelevelAcademicyear].[AcademicYearID]) WHERE ( ( [cssisDB].[Core].[GradelevelAcademicyear].[AcademicYearID] IS NULL OR [cssisDB].[Core].[GradelevelAcademicyear].[AcademicYearID] = @AcademicYearId1) AND ( ( [LPA_L2].[StaffID] IS NOT NULL))) ORDER BY [cssisDB].[Core].[GradeLevel].[IndexNumber] ASC',N'@AcademicYearId1 int',@AcademicYearId1=1

So it generates LEFT JOINS. What if you run that code on your SQL Management Studio? What is the wrong part of that query?

in properties window it says 2.6.0.0 (screenshot attached)

I ve installed the latest a while ago and it hasnt changed yet so i am pretty sure i have the latest build for 2.6

and for the query generated: pay attention to this: [LPA_L2].[StaffID] IS NOT NULL

i have no such predicate but because of the sub/super relationship between the staff and user tables this condition has been added at the end which doesnt let me get the grade levels that has no gradelevel chair set.

in sql manager it returns one gradelevel only and i have 16 in db.

thanks

The screen shot you supplied showed the version number for the compact framework dll, rather than the main one Could you try downloading the latest version just to be sure...?

In the mean time I'll get the dev team to take a look.

Matt

MTrinder wrote:

The screen shot you supplied showed the version number for the compact framework dll, rather than the main one Could you try downloading the latest version just to be sure...?

In the mean time I'll get the dev team to take a look.

Matt

I did install the full version just now that i ve downloaded from the customer area. and still the same. i see no build number anywhere around the designer or the about screen. it says 2.6 Final (oct 9 2009)

you are right i loooked at the cf dll properties, the build number is 2.6.10.09.30

It looks like the runtime appends a type filter as a field in a subtype is present, but this is unnecessary as the relationship is on the subtype and the subtype is joined with a left join, so a 'NULL' is not a sign a supertype was joined, but due to the LEFT join.

The type filters are currently appended in any case, so when a subtype's fields (in this case Staff.FullName) is in the fields list, a type filter is added. Normally, this is a good thing, because subtypes are joined and you specified a field in the supertype.

The workaround is to specify User.Fullname instead of Staff.Fullname. Specifying User.Fullname won't append the type filter. You should do the following: - specify: fields.DefineField(UserFields.FullName, 4, "GradeChair", "SF"); - on the relation GradelevelAcademicyearEntity.Relations.StaffEntityUsingGradeLevelChairId, you have to specify an alias for StaffEntity. Do: GradelevelAcademicyearEntity.Relations.StaffEntityUsingGradeLevelChairId.SetAliases(string.Empty, "SF")

this should give you the query you want. Please let us know if this fixes your problem or not.

Otis wrote:

It looks like the runtime appends a type filter as a field in a subtype is present, but this is unnecessary as the relationship is on the subtype and the subtype is joined with a left join, so a 'NULL' is not a sign a supertype was joined, but due to the LEFT join.

The type filters are currently appended in any case, so when a subtype's fields (in this case Staff.FullName) is in the fields list, a type filter is added. Normally, this is a good thing, because subtypes are joined and you specified a field in the supertype.

The workaround is to specify User.Fullname instead of Staff.Fullname. Specifying User.Fullname won't append the type filter. You should do the following: - specify: fields.DefineField(UserFields.FullName, 4, "GradeChair", "SF"); - on the relation GradelevelAcademicyearEntity.Relations.StaffEntityUsingGradeLevelChairId, you have to specify an alias for StaffEntity. Do: GradelevelAcademicyearEntity.Relations.StaffEntityUsingGradeLevelChairId.SetAliases(string.Empty, "SF")

this should give you the query you want. Please let us know if this fixes your problem or not.

thanks, i ve tried it (i dont know if i did something wrong thou) here is what i have now:

var fields = new ResultsetFields(5); fields.DefineField(GradeLevelFields.GradeLevelId, 0); fields.DefineField(GradeLevelFields.ShortDescription, 1); fields.DefineField(GradelevelAcademicyearFields.CalculationMethod, 2); fields.DefineField(AcademicYearFields.AcademicYearName, 3, "Year"); fields.DefineField(UserFields.FullName, 4, "GradeChair", "SF");

        IRelationCollection relations = new RelationCollection
            {
                { GradeLevelEntity.Relations.GradelevelAcademicyearEntityUsingGradeLevelId, JoinHint.Left },
                { GradelevelAcademicyearEntity.Relations.StaffEntityUsingGradeLevelChairId, JoinHint.Left },
                { GradelevelAcademicyearEntity.Relations.AcademicYearEntityUsingAcademicYearId, JoinHint.Left }
            };
        GradelevelAcademicyearEntity.Relations.StaffEntityUsingGradeLevelChairId.SetAliases(string.Empty, "SF");

and i am getting: The multi-part identifier "SF.FullName" could not be bound.

You've to add the relationships to the collection using Add() and use the overload which sets the alias on the End entity to "SF" for the GradelevelAcademicyearEntity.Relations.StaffEntityUsingGradeLevelChairId relationship

Otis wrote:

You've to add the relationships to the collection using Add() and use the overload which sets the alias on the End entity to "SF" for the GradelevelAcademicyearEntity.Relations.StaffEntityUsingGradeLevelChairId relationship

it worked. thank you.