Assigning object inside foreach using Entity Framework is extremely slow

Question

I'm having a problem with assigning an object inside foreach loop using Entity Framework, I don't know why it's taking very long time (Almost 48 seconds for 1800 items in the loop!!).

Sample Code:

using (var db = new MyEntities())
{
     foreach (long r in Recipients) //Recipients has 1800 items.
     {
         var temp = new DirectMessage();
         temp = db.DirectMessages().FirstOrDefault();
         temp.SenderProfileImageUrl = "https://www.google.com.sa/images/branding/googlelogo/2x/googlelogo_color_120x44dp.png";    
     }
}

This simple loop is taking around 45 seconds!!

At testing and debugging, I noticed that this command temp = db.DirectMessages().FirstOrDefault(); is making the delay!

Also, originally it had .Where and .OrderBy using r.ID but I changed it to the simplest way to make sure the delay is not from the filtering.

Update, Original Code:

foreach (long r in Recipients)
                {
                    MsgObj = new AllMsgsClass();

                    MsgObj.LastMsg = db.DirectMessages.Where(a => (a.SenderID == r || a.RecipientID == r)).OrderByDescending(a => a.CreatedDate).AsNoTracking().FirstOrDefault();

                    try
                    {
                        if (MsgObj.LastMsg.MsgSort == "Sent")
                            MsgObj.LastMsg.RecipientProfileImageUrl = "https://avatars.io/twitter/" + MsgObj.LastMsg.RecipientScreenName + "/small";

                        else
                            MsgObj.LastMsg.SenderProfileImageUrl = "https://avatars.io/twitter/" + MsgObj.LastMsg.SenderScreenName + "/small";
                    }
                    catch (Exception dd)
                    {
                        string x = dd.Message;
                    }

                    MsgObj.SortOrder = Convert.ToDateTime(MsgObj.LastMsg.CreatedDate);
                    AllMSGsList.Add(MsgObj);
                }

Any help would be so appreciated!

Do you really need to re-query the same item from DB 1800 times? Why don't you cache this item into local variable? — Dennis
– Dennis, Commented Jun 28, 2018 at 8:51
Unfortunately, I do have for two reasons, to get the latest profile picture by user ID (in the question I just put google icon), and to do some sorting before binding. — AKO
– AKO, Commented Jun 28, 2018 at 9:00
You need to retrieve query execution plan, and analyze it. For SQL Server use Management Studio. — Dennis
– Dennis, Commented Jun 28, 2018 at 10:08

Sahil Sharma · Accepted Answer · 2018-06-28 09:17:32Z

1

If db.DirectMessages().FirstOrDefault() is calling database in every-iteration, it will take time. Why not use this instead:

using (var db = new MyEntities())
{
     var directMessage = db.DirectMessages().FirstOrDefault();
     foreach (long r in Recipients) //Recipients has 1800 items.
     {
         var temp = new DirectMessage();
         temp = directMessage;
         temp.SenderProfileImageUrl = "https://www.google.com.sa/images/branding/googlelogo/2x/googlelogo_color_120x44dp.png";    
     }
}

Updated Answer:

var directMessages = db.DirectMessages.ToList();

foreach (long r in Recipients)
                {
                    MsgObj = new AllMsgsClass();

                    MsgObj.LastMsg = directMessages.Where(a => (a.SenderID == r || a.RecipientID == r)).OrderByDescending(a => a.CreatedDate).AsNoTracking().FirstOrDefault();

                    try
                    {
                        if (MsgObj.LastMsg.MsgSort == "Sent")
                            MsgObj.LastMsg.RecipientProfileImageUrl = "https://avatars.io/twitter/" + MsgObj.LastMsg.RecipientScreenName + "/small";

                        else
                            MsgObj.LastMsg.SenderProfileImageUrl = "https://avatars.io/twitter/" + MsgObj.LastMsg.SenderScreenName + "/small";
                    }
                    catch (Exception dd)
                    {
                        string x = dd.Message;
                    }

                    MsgObj.SortOrder = Convert.ToDateTime(MsgObj.LastMsg.CreatedDate);
                    AllMSGsList.Add(MsgObj);
                }

edited Jun 28, 2018 at 9:17

answered Jun 28, 2018 at 8:53

Sahil Sharma

1,8992 gold badges18 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

AKO Over a year ago

Because I'm not actually calling FirstOrDefault, I just put it like this to test that the where clause is not the issue. The original command is: temp = db.DirectMessages.Where(a => (a.SenderID == r || a.RecipientID == r)).OrderByDescending(a => a.CreatedDate).AsNoTracking().FirstOrDefault();

Sahil Sharma Over a year ago

Do you need to call the database with every iteration?

AKO Over a year ago

I updated the question adding the real code, so maybe you can get an idea of why I need to call the db every iteration.

Sahil Sharma Over a year ago

If you can take db.DirectMessages.ToList(); to a variable and iterate from it, you can save database calls from 1800 to 1. Maybe you wouldn't need to call database again until a new change. I have updated the answer.

AKO Over a year ago

BINGO! it worked in less than a second!!!! It actually make sense! instead of reaching the DB each iteration it's just accessing the local List.. Thank you!

Abhishek Sharma · Accepted Answer · 2018-06-28 08:53:11Z

0

Once I faced the similar issue and I followed the below steps, I hope it will help you. This will also help to generate edmx faster. Setting the compatibility level of the database to 110 has worked for me. To check the compatibility level, run this script:

select compatibility_level from sys.databases where name = 'YOUR_DB_NAME'

To set the compatibility level, use this script:

alter database YOUR_DB_NAME set compatibility_level = 110

answered Jun 28, 2018 at 8:53

Abhishek Sharma

3601 silver badge10 bronze badges

Collectives™ on Stack Overflow

Assigning object inside foreach using Entity Framework is extremely slow

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related