Linq Execute Slow?

public async Task Schedule_Save(Array EmpID, int RoasterID, DateTime Startdate, DateTime EndDate)
{
try
{
ArrayList ExistCode = new ArrayList();
#region Master
PakOman_NedoEntities _Db = new PakOman_NedoEntities();
int itemsCount = EmpID.Length;
int i = 0;
foreach (var Emp in EmpID)
{
string[] EmpName = Emp.ToString().Split('|');
i++;
//Thread.Sleep(10);
long empID = long.Parse(EmpName[0].ToString());
var IsExist = await (from m in _Db.EmpScheduleMasters
join d in _Db.EmpScheduleDetails
on m.ID equals d.MID
where m.EmpId == empID && d.AttDate >= Startdate && d.AttDate <= EndDate
select m).ToListAsync();
if (IsExist.Count > 0)
{
ExistCode.Add(IsExist);
}
else
{
EmpScheduleMaster objempM = new EmpScheduleMaster();
objempM.GeneralSchedulePKId = RoasterID;
objempM.StartDate = Startdate.ToString();
objempM.EndDate = EndDate.ToString();
objempM.CreatedBy = HttpContext.Current.Session["UserName"].ToString();
objempM.CreatedDate = DateTime.Now;
objempM.EmpId = int.Parse(EmpName[0].ToString());
_Db.EmpScheduleMasters.Add(objempM);
await _Db.SaveChangesAsync();
long MID = objempM.ID;
for (var dt = Startdate; dt <= EndDate; dt = dt.AddDays(1))
{
EmpScheduleDetail objempD = new EmpScheduleDetail();
var av = await (from q in _Db.ShiftScheduleGeneralMasters
join a in _Db.ShiftScheduleGeneralDetails on q.PKId equals a.MID
where a.AttDate == dt.Date && q.PKId == RoasterID
select new { a.ShiftID }).FirstOrDefaultAsync();
if (av.ShiftID > 0)
{
objempD.ShiftID = av.ShiftID;
}
objempD.AttDate = dt.Date;
objempD.AttDay = dt.DayOfWeek.ToString();
objempD.MID = MID;
_Db.EmpScheduleDetails.Add(objempD);
await _Db.SaveChangesAsync();
}
}
//CALLING A FUNCTION THAT CALCULATES PERCENTAGE AND SENDS THE DATA TO THE CLIENT
//Thread T1 = new Thread(delegate ()
//{
// Functions.SendProgress(EmpName[1].ToString() + ":- schedule update in progress...", i, itemsCount, DataHelper.SessionUserName);
//});
//T1.Start();
Functions.SendProgress(EmpName[1].ToString() + ":- schedule update in progress...", i, itemsCount, DataHelper.SessionUserName);
}
#endregion
return ExistCode;
}
catch (Exception ex)
{
ErrorLogging.Add(ex.Message, DateTime.Now, HttpContext.Current.Session["UserName"].ToString(), "ModelScheduleUpdate", "Schedule_Save");
return null;
}
}

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Laxmidhar Sahoo6y ago
Hi

In foreach loop each time you call savechanges() method .

You can apply bulkInsert method or after addrange use savechanges()
List emps = new List();
foreach()
{
EmpScheduleMaster objempM = new EmpScheduleMaster();
.
.
emps.add(objempM)
}
_DB..BulkInsert(emps);
For more follow the link
https://entityframework-extensions.net/bulk-insert
https://weblog.west-wind.com/posts/2013/Dec/22/Entity-Framework-and-slow-bulk-INSERTs
0
Joginder Banger6y ago
In a first look you insert data in both table with a single call.

For example
Category c = new Category();
c.name = "Accessories";
Product p = new Product();
p.name = "USB Mouse";
c.Products.Add(p);

//and finally
db.Categories.Add(c);
db.SubmitChanges();
+1
Mohsin Arif6y ago
1. public async Task Schedule_Save(Array EmpID, int RoasterID, DateTime Startdate, DateTime EndDate)
2. {
3. try
4. {
5. ArrayList ExistCode = new ArrayList();
6. #region Master
7. PakOman_NedoEntities _Db = new PakOman_NedoEntities();
8. int itemsCount = EmpID.Length;
9. int i = 0;
10. foreach (var Emp in EmpID)
11. {
12. string[] EmpName = Emp.ToString().Split('|');
13. i++;
14. //Thread.Sleep(10);
15. long empID = long.Parse(EmpName[0].ToString());
16. var IsExist = await (from m in _Db.EmpScheduleMasters
17. join d in _Db.EmpScheduleDetails
18. on m.ID equals d.MID
19. where m.EmpId == empID && d.AttDate >= Startdate && d.AttDate <= EndDate
20. select m).ToListAsync();
21. if (IsExist.Count > 0)
22. {
23. ExistCode.Add(IsExist);
24. }
25. else
26. {
27. EmpScheduleMaster objempM = new EmpScheduleMaster();
28. objempM.GeneralSchedulePKId = RoasterID;
29. objempM.StartDate = Startdate.ToString();
30. objempM.EndDate = EndDate.ToString();
31. objempM.CreatedBy = HttpContext.Current.Session["UserName"].ToString();
32. objempM.CreatedDate = DateTime.Now;
33. objempM.EmpId = int.Parse(EmpName[0].ToString());
34. _Db.EmpScheduleMasters.Add(objempM);
35. await _Db.SaveChangesAsync();
36. long MID = objempM.ID;
38. for (var dt = Startdate; dt <= EndDate; dt = dt.AddDays(1))
39. {
40. EmpScheduleDetail objempD = new EmpScheduleDetail();
41. var av = await (from q in _Db.ShiftScheduleGeneralMasters
42. join a in _Db.ShiftScheduleGeneralDetails on q.PKId equals a.MID
43. where a.AttDate == dt.Date && q.PKId == RoasterID
44. select new { a.ShiftID }).FirstOrDefaultAsync();
45. if (av.ShiftID > 0)
46. {
47. objempD.ShiftID = av.ShiftID;
48. }
49. objempD.AttDate = dt.Date;
50. objempD.AttDay = dt.DayOfWeek.ToString();
51. objempD.MID = MID;
52. _Db.EmpScheduleDetails.Add(objempD);
53. await _Db.SaveChangesAsync();
54. }
55. }
56. //CALLING A FUNCTION THAT CALCULATES PERCENTAGE AND SENDS THE DATA TO THE CLIENT
57. //Thread T1 = new Thread(delegate ()
58. //{
59. // Functions.SendProgress(EmpName[1].ToString() + ":- schedule update in progress...", i, itemsCount, DataHelper.SessionUserName);
60. //});
61. //T1.Start();
62. Functions.SendProgress(EmpName[1].ToString() + ":- schedule update in progress...", i, itemsCount, DataHelper.SessionUserName);
64. }
65. #endregion
67. return ExistCode;
69. }
70. catch (Exception ex)
71. {
72. ErrorLogging.Add(ex.Message, DateTime.Now, HttpContext.Current.Session["UserName"].ToString(), "ModelScheduleUpdate", "Schedule_Save");
73. return null;
74. }
76. }