Entity Framework query optimisation and code mutualisation-CodePudding

I have a web application with a single table and 2 screens:

A listing screen with Id, Name, Calc1 and Calc2 fields (this screen contains filters and pagination)
A detail screen with Id, Name, Calc1, Calc2 and Calc3 fields

Fields Id and Name come from the database.

Calc1, Calc2 and Calc3 fields are very complex expressions. These expressions are written in C# and they are translated into SQL by EF.

Here is my query structure:

var query = from t in mydbcontext.mytable 
            where ...
            select new MyViewModel {
               Id = t.Id,
               Name = t.Name,
               Calc1 = xxx   yyy - zzz/2 ...
               Calc2 = www   ee   eg/2 ...
               Calc3 = sdf   df - dg/2 ...
            }

This query is stored in a common Service.

Calc1,2,3 are calculations but also contains data lookup (in other tables).

query is an IQueryable object. This is very important because i have filters and pagination on my listing screen.

I am working with this query in my full application. I don't want to implement Calc1, Calc2 and Calc3 in multiple parts of my code.

It works fine but i have a problem: Calc3 is calculated everytime. But this information is not necessary in the listing screen.

I cannot remove it because i need this information in the second screen.

My question is how can i do ?

I have tried several approaches but this approaches needs to work with List<> instead of IQuerayble<> and i want to work only with IQuerayble. (for example i have tried to put Calc expressions in C# functions)

Thanks

CodePudding user response：

Write common function, which returns everything and helper functions which simplifies projection. EF should remove not used parts during SQL generaton.

public static class MyDbContextQueries
{
    public static IQueryable<MyViewModel> GetQueryForEverything(this MyDbConttext ctx, int somefilter)
    {
        var query = from t in ctx.mytable 
            where t.Some == somefilter
            select new MyViewModel {
               Id = t.Id,
               Name = t.Name,
               Calc1 = xxx   yyy - zzz/2 ...
               Calc2 = www   ee   eg/2 ...
               Calc3 = sdf   df - dg/2 ...
            };

        return query;
    }

    public static IQueryable<MyViewModel> GetConcreteFields(this IQueryable<MyViewModel> query)
    {
        var query = from q in query
            select new MyViewModel {
               Id = q.Id,
               Name = q.Name,
               Calc1 = q.Calc1
            };

        return query;
    }
}

Usage is simple:

var query = mydbcontext.GetQueryForEverything(someId)
   .GetConcreteFields()
   .ToList();

CodePudding user response：

When it comes to centralizing query generation with IQueryable I find it's best to separate the projection out as a separate concern. If you have one view that wants CalcC where another doesn't so it would be optimal not to bother computing it, then these are similar, not identical concerns.

The centralized part of the query becomes:

var query = from t in mydbcontext.mytable 
        where ...;

The ViewModels become:

public class MyViewModel 
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Calc1 { get; set; }
    public decimal Calc2 { get; set; }
}

public class MyOtherViewModel : MyViewModel
{
    public decimal Calc3 { get; set; }
}

Then projecting your ViewModel is moved to a level where it is individually managed.

 var myViewModels = await MySpecificQuery()
      .OrderBy(x => ...)
      .Select(x => new MyViewModel { ... })
      .Skip(page * pageSize)
      .Take(pageSize)
      .ToListAsync();

vs.

 var myOtherViewModels = await MySpecificQuery()
      .OrderBy(x => ...)
      .Select(x => new MyOtherViewModel { ... })
      .Skip(page * pageSize)
      .Take(pageSize)
      .ToListAsync();

To avoid the kludge of the projection I recommend using Automapper and it's ProjectTo method. The approach I generally use is in my ViewModels I create a simple static method to return the mapping expression. As an example:

public static MapperConfigurationExpression BuildConfig(MapperConfigurationExpression config = null)
{
    if (config == null)
        config = new MapperConfigurationExpression();

    config.CreateMap<RosterTemplateType, RosterTemplateTypeViewModel>()
        .ForMember(x => x.CreatedBy, opt => opt.MapFrom(src => src.CreatedBy.Name))
        .ForMember(x => x.CostCentre, opt => opt.MapFrom(src => src.DefaultCostCentre.Name))
        .ForMember(x => x.BaselineId, opt => opt.MapFrom(src => src.Baseline.BaselineId))
        .ForMember(x => x.HasBaselineData, opt => opt.MapFrom(src => src.Baseline.BaselineDays.Any(x => x.IsActive && x.RoleCount > 0)))
        .ForMember(x => x.IsInUse, opt => opt.Ignore())
        .ForMember(x => x.IsEditable, opt => opt.Ignore())
        .ForMember(x => x.IsEditing, opt => opt.Ignore());
    return config;
}

What this allows you to do is build a mapping expression for one or more view models that you will be projecting to (for things like parent/child relationships) then building a mapper configuration as needed to give to the ProjectTo method:

var config = new MapperConfiguration(RosterTemplateViewModel.BuildConfig());
var viewModels = await RosterRepository.GetRosterTemplateTypesByOrgUnit(viewModel.OrgUnitId)
                .OrderBy(x => x.Name)
                .ProjectTo<RosterTemplateViewModel>(config)
                .ToListAsync();

This keeps the bulk of the projection configuration tucked away where it is relevant and keeps the actual projection down to one line in the resulting query.