0

i have correctly running code for mvc4 using c# as follows:

        try
        {

            foreach (var spec in db.Questions)
            {
                int[] ans = db.Answers.Where(m => m.in_Id == spec.in_Id).Select(m => m.in_AnswerID).ToArray();

                Random RandString = new Random();
                foreach (var prod in db.Sheet)
                {
                    SheetAns prodAns = new SheetAns()
                    {
                         in_SheetID = prod.in_SheetID

                    };
                    SheetAns.in_AnswerID = ans[RandString.Next(0, ans.Length)];
                    db.SheetAns.Add(prodAns);
                }
            }
            db.SaveChanges();
            return "Success" ;
        }
        catch(Exception ex)
        {
            return "Fail => " + ex.Message ;
        }

this code is having large execution time.

I want to perform same operation using sql query or stored procedure for database.

enter image description here

How can we convert this code to pure sql server 2008 query(or stored procedure) ?

Thank you, in advance.

Pranav Labhe
  • 1,943
  • 1
  • 19
  • 24
  • Please edit your question and show the structure of the data *in the database*. It is also helpful to show the desired results, so someone doesn't have to figure out what your code is doing. – Gordon Linoff Apr 18 '15 at 11:37

1 Answers1

0

How about first changing your query itself, so that it you no longer have the "select N + 1" problem. 

// Your select then becomes:
var newSheetAns = 
    from spec in Questions
    join ans in Answers on spec.in_Id equals ans.in_Id into g
    from prod in Sheets
    select new SheetAns()
    {
        in_SheetID = prod.in_SheetID,
        in_AnswerID = g
            .OrderBy(x => Guid.NewGuid()) // can't "ORDER BY NEWID()" in linq.
            .Select(x => x.in_AnswerID)
            .FirstOrDefault()
    };

// Now add them. This is still going to be slow, because entity framework
// does not support bulk inserts.
db.SheetAns.AddRange(newSheetAns);
db.SaveChanges();

If this is not fast enough, you can translate the above LINQ statement into a raw SQL INSERT statement (using ORDER BY NEWID() to randomize the answer id selection).

This can then be directly executed from Entity Framework like this:

db.Database.ExecuteSqlCommand( <your SQL statement here> );
Community
  • 1
  • 1
Alex
  • 13,024
  • 33
  • 62
  • in_AnswerID is supposed to be random from db.Answers.where(m=>m.in_Id == QueId) – Pranav Labhe Apr 18 '15 at 12:37
  • It is selecting them randomly using `OrderBy(x => Guid.NewGuid())` from `ans`. The `ans` result set contains those records `where ans.in_Id == spec.in_Id`, exactly as in the code you have posted in your question: `where(m => m.in_Id == spec.in_Id)`. So I don't understand your comment. – Alex Apr 18 '15 at 12:56
  • Where is instance of "prod" ... ? – Pranav Labhe Apr 20 '15 at 04:52
  • @PranavLabhe Ok, I see that I forgot a piece. Corrected – Alex Apr 20 '15 at 12:27