Hi, i have an enrollment table that holds classid, semesterid, and studentid. i would like to get the ids of students taking a class in selected semesters. so the parameters will be semesterids as an int list and the classid.

how can i do that? i cand think/find a way to use an int list in predicate expression.

here is a quick example of what i am trying to accomplish. consider the following:

classid semesterid studentid 1 1 1 1 1 2 1 1 3 1 2 1 1 2 3

if classid =1 and semesterids list has only 1; there will be 3 students (1,2, and 3). if classid = 1 and semesterids list has 1 and 2; there will be 2 students (1 and 3 which are taking classid 1 in both semester 1 and semester 2)

thanks in advance -shane

You can use FieldCompareRangePredicate:

List<int> semesterIds = new List<int>();
values.Add(31);
values.Add(39);

int classId = 1;

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(EnrollementFields.SemesterId == semesterIds);
bucket.PredicateExpression.Add(EnrollementFields.ClassId == classId);

// ...

daelmo wrote:

You can use FieldCompareRangePredicate:

List<int> semesterIds = new List<int>();
values.Add(31);
values.Add(39);

int classId = 1;

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(EnrollementFields.SemesterId == semesterIds);
bucket.PredicateExpression.Add(EnrollementFields.ClassId == classId);

// ...

Thank you David but there is a little problem. fieldcomparerange predicate doesnt AND, i believe it ORs. if i run it over the sample data i used in my post it brings the students that are enrolled for classid=1 in semester1 or semester 2, not semester1 and semester 2.

The first thing you should do: You have to come up with the SQL query you need to execute.

Please post the SQL query here, so we can help you write it in LLBLGen code.

Walaa wrote:

The first thing you should do: You have to come up with the SQL query you need to execute.

Please post the SQL query here, so we can help you write it in LLBLGen code.

I am not really sure about the sql i need. not even sure if there is a valid sql for that. do you think i should change my logic there and go the long way such as; for every student in classid=1 in semester 1, check if he exists in classid=1 in semester 2.

sounds really simple when you think about it but i guess it is not possible with a single basic query.

i have one more question guys. this has nothing to do with the initial one.

what would be the best way to accomplish this: i have an int list holding studentids and another int list holding semestrids and the classid

i want to create enrollment entities using them. enrollment table has studentid, semesterid, and classid as its fields. right now i have two foreach loops inside each other. one goes through the semesterids, second goes throught the studentsids and saves every enrollment entity individually. if i have 2 semesterids and 30 studentsids, there goes 60 .Save() on enrollment entity.

is there a way to make it more effective maybe? thank you for all the responses, you guys rock!

Inside the for loops, instead of saving, add the student entity to an EntityCollection, and after the for loops save the EntityColection.

Note: Under the hood a Save will be called for each entity, but in the same connection and in one transaction.

Walaa wrote:

Inside the for loops, instead of saving, add the student entity to an EntityCollection, and after the for loops save the EntityColection.

Note: Under the hood a Save will be called for each entity, but in the same connection and in one transaction.

Walaa, thanks for the tip. i have tried what you suggested but having a problem that i cant figure out. Everything seems soooo ok but somehow i am getting timeout error which i am sure is related to transaction i use. here is what happens:

i have a bunch of students enrolled in a class (enrollment table keeps this with studentid, classid, semesterid fields). user adds or removes some students from a class and submits the form. what i do is, remove all students from the enrolled class for the selected semester and add the new students in. and i do all this in one transaction since i remove all students from the class i dont want to lose them if something goes wrong while adding them back into the class. but i am getting timeout error at line 19 of the attached txt file which simply tries to add the first student into my enrollment collection. enrollments.Add(new EnrollmentEntity(classId, semesterId, studentId));

i dont get it. this is a new entity and somehow system locks it down. any idea what is happening here and how can i fix it?

thank you -shane

Strange.

Passing the PK values in the CTor of the EnrollmentEntity, makes the framework attempt to fetch the entity. So please set the PK values outside the CTor.

Also please populate the enrollments collection outside of the transaction block (before starting the transaction).

So your code should look like:

        public static bool EnrollStudentsIntoClass(List<int> studentsToAdd, int classId, List<int> semesterIds, out string error)
        {
            // First delete all students in selected class and semesters
            // Then add the new students. Do all in one transaction

            // Go through each semester and student to create their enrollment record


            EnrollmentCollection enrollments = new EnrollmentCollection();
            foreach (int semesterId in semesterIds)
            {
                foreach (int studentId in studentsToAdd)
                {
                    //EnrollStudentIntoClass(studentId, classId, semesterId);
                    var enrollment = new EnrollmentEntity();
                    enrollment.ClassId = classId;
                    enrollment.SemesterId = semesterId;
                    enrollment.StudentId= studentId;

                    enrollments.Add(enrollment));
                }
            }

            Transaction trans = new Transaction(IsolationLevel.ReadCommitted, "UpdateEnrollment");
            error = string.Empty;
            try
            {
                // Delete all students in selected class and semester using the transaction
                DeleteAllStudentsInClass(classId, semesterIds, trans, out error);

                // Enroll new students into selected class for selected semesters
                trans.Add(enrollments);
                enrollments.SaveMulti();
                trans.Commit();
                return true;
            }
            catch (Exception e)
            {
                error = e.Message;
                trans.Rollback();
                return false;
            }
        }

Walaa wrote:

Strange.

Passing the PK values in the CTor of the EnrollmentEntity, makes the framework attempt to fetch the entity. So please set the PK values outside the CTor.

Also please populate the enrollments collection outside of the transaction block (before starting the transaction).

So your code should look like:

        public static bool EnrollStudentsIntoClass(List<int> studentsToAdd, int classId, List<int> semesterIds, out string error)
        {
            // First delete all students in selected class and semesters
            // Then add the new students. Do all in one transaction

            // Go through each semester and student to create their enrollment record


            EnrollmentCollection enrollments = new EnrollmentCollection();
            foreach (int semesterId in semesterIds)
            {
                foreach (int studentId in studentsToAdd)
                {
                    //EnrollStudentIntoClass(studentId, classId, semesterId);
                    var enrollment = new EnrollmentEntity();
                    enrollment.ClassId = classId;
                    enrollment.SemesterId = semesterId;
                    enrollment.StudentId= studentId;

                    enrollments.Add(enrollment));
                }
            }

            Transaction trans = new Transaction(IsolationLevel.ReadCommitted, "UpdateEnrollment");
            error = string.Empty;
            try
            {
                // Delete all students in selected class and semester using the transaction
                DeleteAllStudentsInClass(classId, semesterIds, trans, out error);

                // Enroll new students into selected class for selected semesters
                trans.Add(enrollments);
                enrollments.SaveMulti();
                trans.Commit();
                return true;
            }
            catch (Exception e)
            {
                error = e.Message;
                trans.Rollback();
                return false;
            }
        }

it worked. thank you. even if the framework tries fetching the entity, isnt it a new entity and has nothing to do with the deleted one? ok there is a transaction block on the deleted entity but i am creating a new one. does the framework think it is the exact same entity since they have the same primary key values?

thank you again

No, the timeout comes from the database, as the Delete seems to block the reads.

Walaa wrote:

No, the timeout comes from the database, as the Delete seems to block the reads.

i know that but isnt the framework telling database what to do? i think i am trying to understand where exactly blocking is happening. this issue confuses me about transactions and the way framework handles them.

-shane