Summarizing code using loop

Question

User-2060576634 posted

hi ..

I have this code which works fine but ever since I tried to summarize it using nested loops I encountered errors specially with the .ToList() method.. can somebody give it a try please? (I've even highlighted the parts that change in each loop)

@{   var db = Database.Open("mydatabase");
     var userid = WebSecurity.CurrentUserId;
     
     //1st loop
     
     var selectitem1scores = "SELECT Score From ItemResults WHERE ExamId=@0 AND ItemId=@1";
     var item1scores= db.Query(selectitem1scores,1,1);
     var listitem1scores = item1scores.ToList();
     var listitem1scoresavg = db.QueryValue("SELECT AVG(Score) From ItemResults WHERE ExamId=@0 AND ItemId=@1",1,1);
     var stdvalue1 = (decimal)Functions.StandardDeviation(listitem1scores);
     
     foreach (var row in db.Query("SELECT ItemResultId,Score From ItemResults WHERE ExamId=@0 AND ItemId=@1",1,1))
     {
       var z = (row.score - listitem1scoresavg)/stdvalue1;
       var b= z*1000 + 5000;
       var updatebalanced = "UPDATE ItemResults SET Balanced=@0 WHERE ItemResultId=@1 AND ItemId=@2";
       db.Execute(updatebalanced,b,row.ItemResultId,1);
              
     }
 
     //2nd loop
     var selectitem2scores = "SELECT Score From ItemResults WHERE ExamId=@0 AND ItemId=@1";
     var item2scores= db.Query(selectitem2scores,1,2);
     var listitem2scores = item2scores.ToList();
     var listitem2scoresavg = db.QueryValue("SELECT AVG(Score) From ItemResults WHERE ExamId=@0 AND ItemId=@1",1,2);
     var stdvalue2 = (decimal)Functions.StandardDeviation(listitem2scores);
     
     foreach (var row in db.Query("SELECT ItemResultId,Score From ItemResults WHERE ExamId=@0 AND ItemId=@1",1,2))
     {
       var z = (row.score - listitem2scoresavg)/stdvalue2;
       var b= z*1000 + 5000;
       var updatebalanced = "UPDATE ItemResults SET Balanced=@0 WHERE ItemResultId=@1 AND ItemId=@2";
       db.Execute(updatebalanced,b,row.ItemResultId,2);
}

Answer 1

User-830258159 posted

Please provide more details  like what was the error, exact line of code where u got the error, etc.

Answer 2

User-2060576634 posted

I thought this code might do the trick:

@{
     var db = Database.Open("mydatabase");
     var userid = WebSecurity.CurrentUserId;
     var selectitems="SELECT * FROM items WHERE type=1";
 
     foreach(var row in db.Query(selectitems))
        {
          var selectitemscores = "SELECT Score From ItemResults WHERE ExamId=@0 AND ItemId=@1";
          var itemscores= db.Query(selectitemscores,1,row.ItemId);
          var listitemscores = itemscores.ToList();
          var listitemscoresavg = db.QueryValue("SELECT AVG(Score) From ItemResults WHERE ExamId=@0 AND ItemId=@1",1,row.ItemId);
          var stdvalue = (decimal)Functions.StandardDeviation(listitemscores);
     
             foreach (var rowi in db.Query("SELECT ItemResultId,Score From ItemResults WHERE ExamId=@0 AND ItemId=@1",1,row.ItemId))
               {
                 var z = (rowi.score - listitemscoresavg)/stdvalue;
                 var b= z*1000 + 5000;
                 var updatebalanced = "UPDATE ItemResults SET Balanced=@0 WHERE ItemResultId=@1 AND ItemId=@2";
                 db.Execute(updatebalanced,b,rowi.ItemResultId,rowi.ItemId);
               }
        }
     
}

but I get an error on this line:

var listitemscores = itemscores.ToList();

'System.Collections.ObjectModel.ReadOnlyCollection<object>' does not contain a definition for 'ToList'

Answer 3

User1713851783 posted

Passing a dynamic type (row.ItemId) into the Database.Query method has the effect of changing the return type from IEnumerable to a ReadonlyCollection, that doesn't contain a definition for the ToList() method.

Try casting dynamic type:

var itemscores= db.Query(selectitemscores,1,(int)row.ItemId);