Linq - Query on 2 Lists Optimization

Question

I am having 2 Lists & I am querying on this 2 List. Both the List are populated with huge data. So the query is taking long time.

When I usually face this performance issue, I simply convert SQL queries & run them directly & get the result in a datatable. But this time I cannot do this as these 2 are not tables but Lists of Models.

How to Optimize this Query or what else should i do?

Code :-

List<TempInOut> listTempInOut = new List<TO_TempInOut>();
List<ShiftSchedule> tempShiftSch  = new List<TO_TempInOut>();

var data = (from B in tempShiftSch
                                    from C in listTempInOut
                                    where
                                        B.CompanyId == companyId &&
                                        C.CompanyId == companyId &&
                                        B.EmployeeId == C.EmployeeId &&
                                        C.InDate >= StrOutStart &&
                                        C.InDate <= StrOutEnd &&
                                        B.ShiftId == item.ShiftCode &&
                                        B.ShiftDate == tempInputDate
                                    select new
                                    {
                                        C.EmployeeId,
                                        C.InDate,
                                        C.Time_Date1
                                    }).ToList();

score 0 · Answer 1 · answered Dec 02 '16 at 11:03

0

Implement an IEqualityComparer for your types, use HashSet for each collection, and use the HashSet.Intersect method to get your output.

answered Dec 02 '16 at 11:03

Jimbobyo

1

score 0 · Answer 2 · answered Dec 02 '16 at 11:35

You can simplify your query to two stepsand compare time. I am thinking of something like that.

var listTempInOutResult = listTempInOut.Where(C => C.CompanyId == companyId 
                                               && C.InDate >= StrOutStart 
                                               && C.InDate <= StrOutEnd);
var employessIds = listTempInOutresult.Select(x => x.EmployeeId).ToList();

var data = tempShiftSch.Where(B => employessIds.Contains(B.EmployeeId) 
                               && B.CompanyId == companyId 
                               && B.ShiftDate == tempInputDate 
                               && B.ShiftId == item.ShiftCode)
                        .Select(C=> new
                        {
                          C.EmployeeId,
                          C.InDate,
                          C.Time_Date1
                        }).ToList();

@Anup Did you try it .. interesting to know the result !! – Ahmed Dec 02 '16 at 12:01 — Ahmed, Dec 02 '16 at 12:01

score 0 · Answer 3 · edited May 23 '17 at 12:24

0

if you are working with iqueryable it would be better to use joins. see this StackOverflow question

edited May 23 '17 at 12:24

Community

1
1

answered Dec 02 '16 at 15:22

giorgi dvalishvili

158
1
5

Linq - Query on 2 Lists Optimization

3 Answers3