In my database have 1 million member data. and need to load those data to grid. I have following code.
public List<MemberDto> GetMembers(string By = "", string searchTerm = "", string sortBy = "", string sortDiection = "")
{
List<EFModel.ClientData.Member> members = context.Members.ToList();
if (!string.IsNullOrEmpty(By))
{
if (By.ToLower() == "a")
{
members = members.Where(m => m.MemberNumber.IndexOf(searchTerm, StringComparison.OrdinalIgnoreCase) >= 0).ToList();
}
else if (By.ToLower() == "n")
{
members = members.Where(m => m.LastName.IndexOf(searchTerm, StringComparison.OrdinalIgnoreCase) >= 0).ToList();
}
}
List<MemberDto> memberDtos = new List<MemberDto>();
mapper.Map(members, memberDtos);
return memberDtos;
}
before I bind the above data to the grid data source. I'm doing the pagination. by set only 25 records for each grid view. but problem is it will take some considerable time fetch data, therefor I need do that pagination part and search part inside the above method.
So , I tried this query.
int pageIndex = 1;
int page size = 25;
var members = (from m in context.Members
where (string.IsNullOrEmpty(searchTerm) || m.MemberNumber.Contains(searchTerm))
&& (string.IsNullOrEmpty(searchTerm) || m.LastName.Contains(searchTerm))
select m).ToList().Skip(pageIndex).Take(size);
But this taking some considerable time to execute, what did I do wrong. How do I fix this issue. I just want to get data by searchTerm
and pageSize
and pageIndex
. In above By
can be a
or n
. a
for MemberNumber and n
for LastName
This:
string.IsNullOrEmpty(searchTerm) || m.MemberNumber.Contains(searchTerm)
Will require a full scan to find matching rows.
And This:
.ToList().Skip(pageIndex).Take(size);
Will load all the matching rows before paging, and doesn't have a guaranteed ordering, so you may get duplicates or miss rows across pages.
So at a minimum try (assuming MemberNumber is unique):
.OrderBy(m => m.MemberNumber).Skip(pageIndex).Take(size).ToList();