title: Mapping table view function for use in app service and ultimately as datatable ajax request in aspnetboilerplate
body: <p>Im trying my hardest to use a table view function as a datasource for a service in my aspnetboilerplate implementation.</p>
  <p>Below is the code i have in the service, i have added the function to the context and this seems to return the correct data but i get context errors when querying it with the context. I assume this is because the IQueryable is causing an issue after the controller has called the paged result.</p>
  <p>Im not sure if you guys need any more info, im sure im making a silly error and help would be hugely appreciated.</p>
  <p>Cheers in advance!</p>
  <pre><code>public class AggregatedWorkerReportAppService : TestAppServiceBase, IAggregatedWorkerReportAppService
  {
      private readonly UserManager _userManager;
      private TestContext context =&gt; _dbContextProvider.GetDbContext();
      private readonly IDbContextProvider&lt;TestContext&gt; _dbContextProvider;
  
      /// &lt;summary&gt;
      /// 
      /// &lt;/summary&gt;
      /// &lt;param name=&quot;userManager&quot;&gt;&lt;/param&gt;
      /// &lt;param name=&quot;dbContextProvider&quot;&gt;&lt;/param&gt;
      public AggregatedWorkerReportAppService(
          UserManager userManager,
          IDbContextProvider&lt;TestContext&gt; dbContextProvider
          )
      {
          _userManager = userManager;
          _dbContextProvider = dbContextProvider;
      }
  
      /// &lt;summary&gt;
      /// 
      /// &lt;/summary&gt;
      /// &lt;param name=&quot;input&quot;&gt;&lt;/param&gt;
      /// &lt;returns&gt;&lt;/returns&gt;
      public IQueryable&lt;AggregatedWorkerReportByDate&gt; CreateFilteredQuery(AggregatedPagedWorkerReportResultRequestDto input)
      {
          var loggedInUser = _userManager.GetUserById((int)AbpSession.UserId);
  
          var result = context.WorkerAggregatedReportByDate(DateTime.Now.AddYears(-1), DateTime.Now)
                  .Include(x =&gt; x.Worker)
                  //.Where(x =&gt; x.User.Id == loggedInUser.Id || x.User.PayGroups.Intersect(loggedInUser.PayGroups).Any())
                  .WhereIf(!input.Keyword.IsNullOrWhiteSpace(), x =&gt; x.WorkerName.Contains(input.Keyword));
  
          return result;
      }
  
      public async Task&lt;PagedResultDto&lt;AggregatedWorkerReportListDto&gt;&gt; GetAllAggregatedWorkerReportsAsync()
      {
          var loggedInUser = _userManager.GetUserById((int)AbpSession.UserId);
  
          var AggregatedWorkerReports = await context.WorkerAggregatedReportByDate(DateTime.Now.AddYears(-1), DateTime.Now)
          .Include(x =&gt; x.Worker)
          .Include(x =&gt; x.User)
          .Select(x =&gt; new AggregatedWorkerReportByDate
          {
              Worker = x.Worker,
              WorkerId = x.WorkerId,
              WorkerBarcodeId = x.WorkerBarcodeId,
              Hours = Math.Round(x.Hours, 2),
              Quantity = Math.Round(x.Quantity, 2),
              Weight = Math.Round(x.Weight, 2),
              TotalCost = Math.Round(x.TotalCost, 2),
              User = x.User
          })
          .ToListAsync();
  
          return new PagedResultDto&lt;AggregatedWorkerReportListDto&gt;(AggregatedWorkerReports.Count(),
              AggregatedWorkerReports.Select(x =&gt; new AggregatedWorkerReportListDto(x)).OrderBy(y =&gt; y.WorkerName).ToList()
          );
      }
  }
  </code></pre>
  <pre><code>public IQueryable&lt;AggregatedWorkerReportByDate&gt; WorkerAggregatedReportByDate(DateTime dateFrom, DateTime dateTo)
  
  modelBuilder
      .Entity&lt;AggregatedWorkerReportByDate&gt;()
      .HasNoKey()
      .ToTable(&quot;AggregatedWorkerReportByDate&quot;, t =&gt; t.ExcludeFromMigrations());
  
  modelBuilder
      .HasDbFunction(typeof(TestContext)
      .GetMethod(nameof(WorkerAggregatedReportByDate), new[] { typeof(DateTime), typeof(DateTime) }));
  
  </code></pre>