How to optimize LINQ query to fetch data from multiple table

Question

I've to fetch data from 4 tables by joining them using LINQ and also have to apply sorting on it, But it takes about 8-9 seconds to execute from the MVC application, so how do I optimize it to be executed in just 2-3 seconds with sorting. There are about 1000 records to be fetched.

Sujeet Suman · Answer

Navratna I have gone through your code. All are fine but I want to improve one part. Please once go through below code: Your Code:- _personal = _personal.Where(rec => rec.recruiter == userId).OrderByDescending(c => c.Start_Date).ToList().Union(_personal.Where(rec => rec.recruiter != userId). OrderBy (row => row.recruiterName).ThenByDescending(c => c.Start_Date).ToList()); My Code:- _personal = _personal.Where(rec => rec.recruiter == userId).OrderByDescending(c => c.Start_Date).Union(_personal.Where(rec => rec.recruiter != userId).OrderByDescending(c => c.Start_Date)).ToList(); Why you are using 2 orderby . Whereas only last orderby will work. If this is helpful for you then please accept my Answer .

Navratna Pawale · Answer

yep! It works... Reduced the time upto 2-3 seconds... Thanks..

Cassie Mod · Answer

If you remove one of the 2 orderby's it will probably go a lot faster

Navratna Pawale · Answer

Actually the query isn't the problem.. The sorting has, we must require the sorting, it's taking a long time. If you have other way to do it, then please share.... Thank for reply,

Sujeet Suman · Answer

Navratan please try below code once: var resultset = from table01 in db.table01 join table02 in db.table02 on table01.ID equals table02.ID join table03 in db.table03 on table02.ID equals table03.ID join table04 in db.table04 on table03.ID equals table04.ID select new { table01, table02, table03, table04}; If this is helpful for you then please accept my Answer .

Sujeet Suman · Answer

Navratna can you share your LINQ query here please.....