7 Tips to write better LINQ Queries
1/ Use of AsNoTracking
For read only queries e.g. (GetAll,GetById
etc.) use AsNoTracking
, when we use it entities are not tracked for change so it brings data more speedily.
var students = await_context
.Students
.AsNoTracking()
.ToListAsync();
2/ Include necessary Entities and Columns
While retrieving data from multiple table make sure to include only necessary tables if you add tables that are not needed it will increase headache of query.
Use eager loading only when it is necessary.
Similarly don’t select the all columns from table , just retrieve necessary columns information using SELECT
3/ For large data use Skip and Take
When you are dealing with a lot of information use Skip and Take to retrieve data from table because if we try to bring all data in single try it can take time which will give a bad user experience, Skip
and Take
two integer and skips provided values and then takes next values.
public async Task<Tuple<int, List<CityDto>>> GetAllAsync(
int page,
int pageSize)
{
int fromPage = (page - 1) * pageSize;
int count = _context.MdCities.AsNoTracking().Count();
var list = await _context
.MdCities
.Include(x => x.District)
.AsNoTracking()
.ProjectTo<CityDto>(_mapper.ConfigurationProvider)
.Skip(fromPage)
.Take(pageSize)
.ToListAsync();
return Tuple.Create(count, list);
}
4/ Use async methods
For better user experience use async methods e.g. FirstOrDefaultAsync
, SingleOrDefaultAsync
and ToListAsync
var cityName = await _context
.MdCities
.FirstOrDefaultAsync(x =>
x.Name.Equals("Lahore", StringComparison.OrdinalIgnoreCase)
);
5/ Reduce Trips to Database
Use bulk operations available for SAVE/DELETE operations instead of iterating one by one it will reduce trips to database. After that only write one line await _context.SaveChangesAsync();
to reflect changes over database.
var removeList = await _context
.MdCities
.Where(x => x.Id > 10)
.ToListAsync();
_context.RemoveRange(removeList);
6/ Use TryGetNonEnumeratedCount
While working with large collection of data we use pagination and our pagination is displayed on the basis of total records available. So instead of .Count()
method use TryGetNonEnumeratedCount
it attempts to determine the number of elements in a sequence without forcing an enumeration.
int totalCities = 0;
bool isCountSuccessful = _context
.MdCities
.TryGetNonEnumeratedCount(out totalCities);
7/ Use of IQueryable
When you are dealing with a long list of filters and your filters are condition based make use of IQueryable
,it executes queries on the server side.
IQueryable<MdCity> cityQueryable = context.MdCities.AsQueryable();
if(someCondition)
{
cityQueryable = context.MdCities.Where(....);
}
if(someCondition)
{
}
cityQueryable = context.MdCities.Where(....);
if(someCondition)
{
}
cityQueryable = context.MdCities.Where(. );
if(someCondition)
{
}
cityQueryable = _context.MdCities.Where(....);
After all conditions we can use .ToList()
to fetch data , so it makes query faster because we are first creating complete query and then we are bringing the data.
Whenever you're ready, there are 3 ways I can help you:
- Subscribe to my youtube channel : For in-depth tutorials, coding tips, and industry insights.
- Promote yourself to 9,000+ subscribers : By sponsoring this newsletter
- Patreon community : Get access to all of my blogs and articles at one place