I need some guidance on how best to build a typedList.

I have four tables I am joining together:

Student (stu_id, first_name, last_name) Course (crs_nbr, crs_title) Class (cls_id, crs_nbr) Registration (stu_id, cls_id, reg_date) Stu_Marks (stu_id, cls_id, semester, mark)

The registration table is the intersection of student/class so that I know what classes a student is registered in. The Stu_Marks table tells me the mark (or grade) a student got in a particular class.

My typed list has a few columns from each table, and the current relations chosen for me when I added the tables to the typed list look like:

Student.stu_id to Registration.stu_id Registration.cls_id to Class.cls_id Class.crs_nbr to Course.crs_nbr Student.stu_id to Stu_Marks.stu_id

...and while this is fine, it pulls back ALL the stu_marks for every class this student has ever taken. I have created a filter on registration to only bring back the classes he is currently registered in, but that cls_id filter never propagates to the stu_marks.cls_id.

How do I add the join from Registration.cls_id to Stu_marks.cls_id?

NickD wrote:

I need some guidance on how best to build a typedList.

I have four tables I am joining together:

Student (stu_id, first_name, last_name) Course (crs_nbr, crs_title) Class (cls_id, crs_nbr) Registration (stu_id, cls_id, reg_date) Stu_Marks (stu_id, cls_id, semester, mark)

The registration table is the intersection of student/class so that I know what classes a student is registered in. The Stu_Marks table tells me the mark (or grade) a student got in a particular class.

My typed list has a few columns from each table, and the current relations chosen for me when I added the tables to the typed list look like:

Student.stu_id to Registration.stu_id Registration.cls_id to Class.cls_id Class.crs_nbr to Course.crs_nbr Student.stu_id to Stu_Marks.stu_id

...and while this is fine, it pulls back ALL the stu_marks for every class this student has ever taken. I have created a filter on registration to only bring back the classes he is currently registered in, but that cls_id filter never propagates to the stu_marks.cls_id.

How do I add the join from Registration.cls_id to Stu_marks.cls_id?

A join merges entities (tables) together to one big set of attributes and data and you then filter out the rows you want using where clauses. The typed list thus simply joins all the entities together using an INNER JOIN (and then the order is not important). This gives a lot of rows in your case as you have 5 entities in the typed list.

The join done is: Student INNER JOIN Registration on Student.stu_id = Registration.stu_id INNER JOIN Class on Registration.cls_id = Class.cls_id INNER JOIN Course on Course.crs_nbr = Class.crs_nbr INNER JOIN Stu_Marks ON Student.stu_id = Stu_Marks.stu_id

This gives more rows than you need, as you need only the rows where cls_id is equal in all columns where it is mentioned.

So if you add a predicate expression which (using FieldCompareExpressionPredicates to compare fields) makes sure that Stu_Marks.cls_id=Registration.cls_id, you filter out the rows you don't want.

I think with that filter you'll get the rows you want.

Thanks for your quick response. Your response time is most impressive. I'm still attempting to wrap my head around your impressive product though, so bear with me.

Otis wrote:

So if you add a predicate expression which (using FieldCompareExpressionPredicates to compare fields) makes sure that Stu_Marks.cls_id=Registration.cls_id, you filter out the rows you don't want.

I think with that filter you'll get the rows you want.

I tried this

IPredicateExpression clsIDCompare = new PredicateExpression();
clsIDCompare.Add(PredicateFactory.CompareExpression( Stu_marksFieldIndex.Cls_id,ComparisonOperator.Equal,ClassFieldIndex.Cls_id));

...and got a compile error because ClassFieldIndex.cls_id can not be converted to IExpression. What am I missing? My understanding was you were telling me how to add in more join text. Were you really telling me I need to filter against a real value?

NickD wrote:

Thanks for your quick response. Your response time is most impressive. I'm still attempting to wrap my head around your impressive product though, so bear with me.

I've written a code snippet below which should make your filter work

Otis wrote:

So if you add a predicate expression which (using FieldCompareExpressionPredicates to compare fields) makes sure that Stu_Marks.cls_id=Registration.cls_id, you filter out the rows you don't want.

I think with that filter you'll get the rows you want.

I tried this

IPredicateExpression clsIDCompare = new PredicateExpression();
clsIDCompare.Add(PredicateFactory.CompareExpression( Stu_marksFieldIndex.Cls_id,ComparisonOperator.Equal,ClassFieldIndex.Cls_id));

...and got a compile error because ClassFieldIndex.cls_id can not be converted to IExpression. What am I missing? My understanding was you were telling me how to add in more join text. Were you really telling me I need to filter against a real value?

No I'm not adding more joins, I'm adding filters

so what I want to create is: SELECT ... FROM Student INNER JOIN Registration on Student.stu_id = Registration.stu_id INNER JOIN Class on Registration.cls_id = Class.cls_id INNER JOIN Course on Course.crs_nbr = Class.crs_nbr INNER JOIN Stu_Marks ON Student.stu_id = Stu_Marks.stu_id WHERE Stu_Marks.cls_id=Registration.cls_id

This could also be done as: SELECT ... FROM Student INNER JOIN Registration on Student.stu_id = Registration.stu_id INNER JOIN Class on Registration.cls_id = Class.cls_id INNER JOIN Course on Course.crs_nbr = Class.crs_nbr INNER JOIN Stu_Marks ON Student.stu_id = Stu_Marks.stu_id AND Stu_Marks.cls_id = Registration.cls_id

The latter should give you the same results, but is a bit harder to create with a typed list in selfservicing so we're going the first route, for the code it doesn't matter.

IPredicateExpression clsIDCompare = new PredicateExpression();
clsIDCompare.Add(
    PredicateFactory.CompareExpression(
        Stu_marksFieldIndex.Cls_id, ComparisonOperator.Equal,
        EntityFieldFactory.Create(ClassFieldIndex.Cls_id)));

Should do the trick.

Otis wrote:

IPredicateExpression clsIDCompare = new PredicateExpression();
clsIDCompare.Add(
    PredicateFactory.CompareExpression(
        Stu_marksFieldIndex.Cls_id, ComparisonOperator.Equal,
        EntityFieldFactory.Create(ClassFieldIndex.Cls_id)));

Should do the trick.

It did not work, but I'm sure it's me . I double checked and I used your exact code. The compiler error is : "The best overloaded method match for 'HLPUSD.SMART.DAL.SmartDev.FactoryClasses.PredicateFactory. CompareExpression(HLPUSD.SMART.DAL.SmartDev.Stu_marksFieldIndex, SD.LLBLGen.Pro.ORMSupportClasses.ComparisonOperator, SD.LLBLGen.Pro.ORMSupportClasses.IExpression)' has some invalid arguments"

What am I missing?

NickD wrote:

Otis wrote:

IPredicateExpression clsIDCompare = new PredicateExpression();
clsIDCompare.Add(
    PredicateFactory.CompareExpression(
        Stu_marksFieldIndex.Cls_id, ComparisonOperator.Equal,
        EntityFieldFactory.Create(ClassFieldIndex.Cls_id)));

Should do the trick.

It did not work, but I'm sure it's me . I double checked and I used your exact code. The compiler error is : "The best overloaded method match for 'HLPUSD.SMART.DAL.SmartDev.FactoryClasses.PredicateFactory. CompareExpression(HLPUSD.SMART.DAL.SmartDev.Stu_marksFieldIndex, SD.LLBLGen.Pro.ORMSupportClasses.ComparisonOperator, SD.LLBLGen.Pro.ORMSupportClasses.IExpression)' has some invalid arguments"

What am I missing?

You're not missing anything, it's me not doing a decent job writing decent code

The code SHOULD be:

IPredicateExpression clsIDCompare = new PredicateExpression();
clsIDCompare.Add(
    PredicateFactory.CompareExpression(
        Stu_marksFieldIndex.Cls_id, ComparisonOperator.Equal,
        new Expression(EntityFieldFactory.Create(ClassFieldIndex.Cls_id))));

Sorry for that

Otis wrote:

The code SHOULD be:

IPredicateExpression clsIDCompare = new PredicateExpression();
clsIDCompare.Add(
    PredicateFactory.CompareExpression(
        Stu_marksFieldIndex.Cls_id, ComparisonOperator.Equal,
        new Expression(EntityFieldFactory.Create(ClassFieldIndex.Cls_id))));

It worked partially this time

The join works in sql and when I view the results in Query Analyzer, I get results back, but when it gets back to my TypedList there is nothing there. I did my best to investigate. Is it because LLBLGen is comparing a field to an expression? I'm just totally guestting here.

AAAAAARGG

Oh this is so embarrassing. I need: WHERE Stu_Marks.cls_id=Registration.cls_id But I created a predicate which did: WHERE Stu_Marks.cls_id=Class.cls_id

Code:

IPredicateExpression clsIDCompare = new PredicateExpression();
clsIDCompare.Add(
PredicateFactory.CompareExpression(
Stu_marksFieldIndex.Cls_id, ComparisonOperator.Equal,
new Expression(EntityFieldFactory.Create(RegistrationFieldIndex.Cls_id))));

Which compares Stu_marks.Cls_id with Registration.Cls_id, what it's needed.

Otis wrote:

AAAAAARGG

Oh this is so embarrassing. I need: WHERE Stu_Marks.cls_id=Registration.cls_id But I created a predicate which did: WHERE Stu_Marks.cls_id=Class.cls_id

Code:

IPredicateExpression clsIDCompare = new PredicateExpression();
clsIDCompare.Add(
PredicateFactory.CompareExpression(
Stu_marksFieldIndex.Cls_id, ComparisonOperator.Equal,
new Expression(EntityFieldFactory.Create(RegistrationFieldIndex.Cls_id))));

Which compares Stu_marks.Cls_id with Registration.Cls_id, what it's needed.

Well, I tried that one myself (before posting my prior cry for help) and that hadn't worked either. Here's the deal, when I use your above suggested code, I get no errors, BUT I also get nothing back in my TypedList. However, when I am listening with SQL Profiler and I copy the sqlText that is sent from the Fill method and paste it into Query Analyzer, I get results back.

Here is all my code for that method

IPredicateExpression regDateFilter = new PredicateExpression();

regDateFilter.Add(PredicateFactory.CompareValue(RegistrationFieldIndex.Date,ComparisonOperator.LessEqual, DateTime.Now));

IPredicateExpression lvDateFilter = new PredicateExpression();
lvDateFilter.Add(PredicateFactory.CompareNull(RegistrationFieldIndex.Leave_date));
lvDateFilter.AddWithOr(PredicateFactory.CompareValue(RegistrationFieldIndex.Leave_date, ComparisonOperator.GreaterEqual,DateTime.Now));

IPredicateExpression stuIDFilter = new PredicateExpression();
stuIDFilter.Add(PredicateFactory.CompareValue(Stu_marksFieldIndex.Stu_id,ComparisonOperator.Equal,stuID));

IPredicateExpression clsIDCompare = new PredicateExpression();
clsIDCompare.Add(PredicateFactory.CompareExpression(Stu_marksFieldIndex.Cls_id,ComparisonOperator.Equal,new Expression(EntityFieldFactory.Create(RegistrationFieldIndex.Cls_id))));
                                
IPredicateExpression theFilter = new PredicateExpression();
theFilter.Add(regDateFilter);
theFilter.AddWithAnd(lvDateFilter);
theFilter.AddWithAnd(stuIDFilter);
theFilter.AddWithAnd(clsIDCompare);

ISortExpression markSort = new SortExpression(SortClauseFactory.Create(Stu_marksFieldIndex.Cls_id,SortOperator.Ascending));

StuMarkListTypedList tl = new StuMarkListTypedList(false);

tl.Fill(0,markSort,false,theFilter);
this.dgStuMarks.DataSource = tl;
this.dgStuMarks.DataBind();

The "regDateFilter" and "lvDateFilter" are trying to give me a "where reg_date <= today and (leave_date is null or leave_date >= today" which only gives me current registrations.

Oddly enough, when I comment out the clsIDCompare filter I get back LOTS of results (which is predictable since I'm missing a join at that point) but that also shows that something else is not "broken" because I can display results when they're returned.

Hopefully this will help shed light on where I'm not doing something right.

BTW - Thanks for all your work with me.

NickD wrote:

Here's the deal, when I use your above suggested code, I get no errors, BUT I also get nothing back in my TypedList. However, when I am listening with SQL Profiler and I copy the sqlText that is sent from the Fill method and paste it into Query Analyzer, I get results back.

so the query you need is the code I suggested, but the parameter values you pass in are too restrictive? You copied the SQL query sent to the sqlserver, which didn't give any results in the typed list, into query analyzer and then it DID result in results? What values did you specify for the parameters in the query?

Your lvDateFilter is not correct I think:

IPredicateExpression lvDateFilter = new PredicateExpression();
lvDateFilter.Add(PredicateFactory.CompareNull(RegistrationFieldIndex.Leave_date));
lvDateFilter.AddWithOr(PredicateFactory.CompareValue(RegistrationFieldIndex.Leave_date, ComparisonOperator.GreaterEqual, DateTime.Now));

will never meet a row unless leavedate is already filled in for a student or always NULL. This is because you specify that leavedate is something in the future OR null. (which can be true for your db, if leavedate is filled in when a student is entered into the system, I'm not sure about that).

I've simplified your query a bit:

IPredicateExpression filter = new PredicateExpression();
filter.Add(PredicateFactory.CompareValue(RegistrationFieldIndex.Date, ComparisonOperator.LessEqual,DateTime.Now));

IPredicateExpression lvDateFilter = new PredicateExpression();
lvDateFilter.Add(PredicateFactory.CompareNull(RegistrationFieldIndex.Leave_date));
lvDateFilter.AddWithOr(PredicateFactory.CompareValue(RegistrationFieldIndex.Leave_date, ComparisonOperator.GreaterEqual,DateTime.Now));

filter.AddWithAnd(lvDateFilter);
filter.AddWithAnd(PredicateFactory.CompareValue(Stu_marksFieldIndex.Stu_id,ComparisonOperator.Equal,stuID));
filter.AddWithAnd(
    PredicateFactory.CompareExpression(
        Stu_marksFieldIndex.Cls_id,
        ComparisonOperator.Equal,
        new Expression(EntityFieldFactory.Create(RegistrationFieldIndex.Cls_id))));

ISortExpression markSort = new SortExpression(SortClauseFactory.Create(Stu_marksFieldIndex.Cls_id,SortOperator.Ascending));
StuMarkListTypedList tl = new StuMarkListTypedList(false);
tl.Fill(0,markSort,false,filter);

Oddly enough, when I comment out the clsIDCompare filter I get back LOTS of results (which is predictable since I'm missing a join at that point) but that also shows that something else is not "broken" because I can display results when they're returned.

You're not missing a join, all joins are there You miss a filter on the results. If you look closely at the results you'll get when you remove the last predicate (the one with the expression), is that Stu_Marks.cls_id is not equal to Registration.Cls_id in a lot of rows (due to the join) which gives you a lot of rows you don't want.

Now, what I'd like to know is: which SQL query did work, i.e. you said you copied over a sql profiler query to query analyzer and that one worked, which was produced with the llblgen pro code. (If I understood you correctly ) Could you paste that query here, which worked?

My turn to fess up. I figured out why I was getting results in Query Analyzer and not getting them in the web page. I was pointed to two different databases. So, the db I was using in QA had the data, while the one that the web page was pointing to did not. Oops!

Now that I've fessed up, I must say that I am enjoying learning your product and it seems very robust. Thanks for the code improvement above! (Guess what, it works now)

I always love a happy end