CODEWITHSUNDEEP
c#sql-serverlinqasp.net-mvc-4
Anup
Anup

Reputation: 9738

Linq Optimizing query with records not matching from another table

I am having following query which pulls data from a table with records that should not be present in another table.

The query is working perfectly but it is taking too much time & performance is affected tremendously.

What changes can i make to this query to get better performance or should i be doing this in another way?

var data = (from A in ctx.tblMachine
where
     A.CompanyId == companyId &&
     A.InOutDate >= tempDt &&
     A.InOutDate <= toDate &&
!(from B in ctx.tblEntry
where
     B.CompanyId == companyId &&
     A.EmployeeId == B.EmployeeId &&
     A.InOutDate == B.EntryDate &&
     B.EntryMethod == "M"
select new
{
     B.EmployeeId
}).Contains(new { EmployeeId = A.EmployeeId })
orderby
A.EmployeeId, A.InOutDate select new
{
     A.EmployeeId,
     A.InOutDate,
     A.InOutFlag,
     A.InOutTime
}).ToList();

Upvotes: 0

Views: 112

Answers (1)

Priyank_Vadi
Priyank_Vadi

Reputation: 1138

You can try with join also instead of inner query... You can use like this make change as per your need..

var data = (from A in ctx.tblMachine
        join B in ctx.tblEntry on A.EmployeeId == B.EmployeeId &&
                                    A.InOutDate == B.EntryDate &&
                                    B.CompanyId == companyId &&
                                    B.EntryMethod == "M" 
        where
                A.CompanyId == companyId &&
                A.InOutDate >= tempDt &&
                A.InOutDate <= toDate &&
                !(B.EmployeeId).Contains(new { EmployeeId = A.EmployeeId })
        orderby
        A.EmployeeId, A.InOutDate
        select new
        {
            A.EmployeeId,
            A.InOutDate,
            A.InOutFlag,
            A.InOutTime
        }).ToList();

Upvotes: 1

Related Questions