CODEWITHSUNDEEP
c#task
Jason
Jason

Reputation: 11

DbContext is not returning any data that is in the database

Why is allRecords returning with zero records when the database has records?

public record struct ImposterDbContextAuthorization(int AccountUserId, bool HasPermissionCanAccessAnyAccount, bool HasPermissionSuperUser = false) : IDbContextAuthorization;

public async Task<string> Send(int clinicId)
{
    // added await here
    Task.Run(async () =>
    {
        await SendIt(clinicId, DbContext.GetAccountId());
    });
    return "";
}
public async Task<string> SendIt(int clinicId, int accountId)
{
  var scope = _serviceScopeFactory.CreateScope();
  var dbOptions = scope.ServiceProvider.GetRequiredService<DbContextOptions<MyDbContext>>();
  var imposter = new ImposterDbContextAuthorization(accountId, true);
  var dbContext = new MyDbContext(dbOptions, imposter);

  var allRecords = await dbContext.Set<Visit>()
            .AsNoTracking()
            .Where(visit => visit.ClinicId == clinicId)
            .GroupBy(visit => visit.PatientId)
            .Select(group => group.Key).ToListAsync();
  // !!! allRecords.Count is 0, even though "SELECT * FROM visit WHERE clinic_id = clinicId" returns 1 record in database
  return "";
}

I tried adding await to the Task.Run, made the records return

SQL from the allRecords:

SQL: -- @__ef_filter__IncludeDeleted_0='False'
12:10:30:610    -- @__ef_filter___hasPermissionCanAccessAnyAccount_4='True'
12:10:30:610    -- @__ef_filter__p_3='True'
12:10:30:610    -- @__ef_filter___accountId_1='759' (Nullable = true)
12:10:30:610    -- @__ef_filter___accountUserId_2='860'
12:10:30:610    -- @__clinicId_0='1696'
12:10:30:610    SELECT v.patient_id
12:10:30:610    FROM visit AS v
12:10:30:610    WHERE v.discriminator = 'Visit' AND (@__ef_filter__IncludeDeleted_0 OR NOT (v.is_deleted)) AND (@__ef_filter___hasPermissionCanAccessAnyAccount_4 OR CASE
12:10:30:610        WHEN @__ef_filter__p_3 THEN v.account_id = @__ef_filter___accountId_1
12:10:30:610        ELSE EXISTS (
12:10:30:610            SELECT 1
12:10:30:610            FROM account_user AS a
12:10:30:610            WHERE a.id = @__ef_filter___accountUserId_2 AND a.account_id = v.account_id)
12:10:30:610    END) AND v.clinic_id = @__clinicId_0
12:10:30:610    GROUP BY v.patient_id {SourceContext="UrgentIQ.Services.Controller.Payment.PaymentService", ActionId="c34bc56c-bc7a-48ec-b921-fdbb02f31129", ActionName="UrgentIQ.WebApi.Controllers.Payment.PaymentController.AuthviaTxt (UrgentIQ.WebApi)", RequestId="0HNABH0DT8O89", RequestPath="/api/Payment/AuthviaTxt/1696", ThreadName=".NET TP Worker"}
12:10:30:610    2

Above was found with allRecords.ToQueryString(). I think maybe the error is because of the account_id is mismatched

Upvotes: 0

Views: 55

Answers (2)

Jason
Jason

Reputation: 11

The SQL has a filter for discriminator that was not equal to Visit

Upvotes: 0

bdcoder
bdcoder

Reputation: 3882

Your "Send" method is always returning an empty string, try the following:

public async Task<string> Send(int clinicId)
{
    return await SendIt(clinicId, DbContext.GetAccountId());
}

When calling "Send":

String result = await Send( clinicId );

Also, where do you return a string in the "SendIt" method? Add return statement after // TO DO comment:

public async Task<string> SendIt(int clinicId, int accountId)
{
  var scope = _serviceScopeFactory.CreateScope();
  var dbOptions = scope.ServiceProvider.GetRequiredService<DbContextOptions<MyDbContext>>();
  var imposter = new ImposterDbContextAuthorization(accountId, true);
  var dbContext = new MyDbContext(dbOptions, imposter);

  var allRecords = await dbContext.Set<Visit>()
            .AsNoTracking()
            .Where(visit => visit.ClinicId == clinicId)
            .GroupBy(visit => visit.PatientId)
            .Select(group => group.Key).ToListAsync();
  // !!! allRecords.Count is 0, even though "SELECT * FROM visit WHERE clinic_id = clinicId" returns 1 record in database

  // TO DO - return string to caller ...

}

Upvotes: 0

Related Questions