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.