Linq code improvement issues with filters

Question

I have this linq query:

private async Task<IQueryable<MyModel>> GetClaimList(MyDto input)
{
    var claimQry = await _claimRepository.WithDetailsAsync();
    var claimBatchQry = await _claimBatchRepository.GetQueryableAsync();
    var remittanceClaimQry = await _remittanceClaimRepository.WithDetailsAsync();

    var claims = (from claimBatch in claimBatchQry
                from transactionSet in claimBatch.TransactionSets.DefaultIfEmpty()
                from transactionSetClaim in transactionSet.Claims.DefaultIfEmpty()
                join claim in claimQry on transactionSetClaim.ClaimId equals claim.Id
                where ...
                orderby claimBatch.Id descending
                group new { claimBatch, transactionSet, claim } by claim.Id into grouped
                select new ClaimTransactionSetBatch
                {
                    Batch = grouped.Select(x => x.claimBatch).FirstOrDefault(),
                    TransactionSet = grouped.Select(x => x.transactionSet).FirstOrDefault(),
                    Claim = grouped.Select(x => x.claim).FirstOrDefault()
                }).ToList();


    return claims.AsQueryable();
}

As you can see, I used FirstOrDefault() to retrieve the first elements from the grouped data, ensuring efficient data retrieval without unnecessary enumeration.

But I need to return it AsQueryable() because in another method I have:

public async Task<PagedResultDto<ClaimSummaryDto>> GetListAsync(GetClaimListDto input)
{
    var claimTransactionSetBatch = await this.GetClaimList(input);

    var claims = claimTransactionSetBatch.Select(x => new ClaimSummaryDto()
    {
        AgencyIdentifier = x.Claim.AgencyIdentifier,
        ....
    }

    claims = claims.OrderBy(string.IsNullOrWhiteSpace(input.Sorting) ? "DateOfServiceStart" : input.Sorting).PageBy(input);

    return new PagedResultDto<ClaimSummaryDto>(total, claims.ToList());
}

So, instead return an IQueryable, if I return it as a list, this code:

claims.OrderBy(string.IsNullOrWhiteSpace(input.Sorting) ? "DateOfServiceStart" : input.Sorting).PageBy(input);

Throws an error, because it need to be an IQueryable to perform the sorting:

Severity Code Description Project File Line Suppression State Error CS1929 'IEnumerable' does not contain a definition for 'OrderBy' and the best extension method overload 'DynamicQueryableExtensions.OrderBy(IQueryable, string, params object?[])' requires a receiver of type 'System.Linq.IQueryable'

So, I'm worried about the performance issues of this query because we materialize the query when we use .ToList(), then move it to AsQueryable again and in the method where is used, we convert it to a list again, how can I improve this code? Also, if I do not apply the .ToList()from the GetClaimList method like this:

...

    {
        Batch = grouped.Select(x => x.claimBatch).FirstOrDefault(),
        TransactionSet = grouped.Select(x => x.transactionSet).FirstOrDefault(),
        Claim = grouped.Select(x => x.claim).FirstOrDefault()
    });


return claims;

It throws a big error when it tries to materialize the query at the end:

return new PagedResultDto<ClaimSummaryDto>(total, claims.ToList());

Error:

The LINQ expression 'DbSet() .Where(c => __ef_filter__p_0 || (Guid?)EF.Property(c, "TenantId") == __ef_filter__CurrentTenantId_1) .SelectMany( collectionSelector: c => DbSet() .Where(c0 => EF.Property<Guid?>(c, "Id") != null && object.Equals( objA: (object)EF.Property<Guid?>(c, "Id"), objB: (object)EF.Property<Guid?>(c0, "ClaimBatchId"))) .DefaultIfEmpty(), resultSelector: (c, c) => new TransparentIdentifier<ClaimBatch, ClaimTransactionSet>( Outer = c, Inner = c )) .SelectMany( collectionSelector: ti => DbSet() .Where(c1 => EF.Property<Guid?>(ti.Inner, "Id") != null && object.Equals( objA: (object)EF.Property<Guid?>(ti.Inner, "Id"), objB: (object)EF.Property<Guid?>(c1, "TransactionSetId"))) .DefaultIfEmpty(), resultSelector: (ti, c) => new TransparentIdentifier<TransparentIdentifier<ClaimBatch, ClaimTransactionSet>, ClaimTransactionSetClaim>( Outer = ti, Inner = c )) .Join( inner: DbSet() .Where(c2 => __ef_filter__p_0 || (Guid?)EF.Property(c2, "TenantId") == __ef_filter__CurrentTenantId_1), outerKeySelector: ti0 => (object)ti0.Inner.ClaimId, innerKeySelector: c2 => (object)c2.Id, resultSelector: (ti0, c2) => new TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<ClaimBatch, ClaimTransactionSet>, ClaimTransactionSetClaim>, Claim>( Outer = ti0, Inner = c2 )) .Where(ti1 => ti1.Outer.Outer.Outer.SourceClaimBatchId == null && True && True && False || __input_Status_1 .Contains(ti1.Inner.Status) && True && True && True && True) .OrderByDescending(ti1 => ti1.Outer.Outer.Outer.Id) .GroupBy(ti1 => ti1.Inner.Id) .Select(g => new ClaimTransactionSetBatch{ Batch = g .AsQueryable() .Select(e0 => e0.Outer.Outer.Outer) .FirstOrDefault(), TransactionSet = g .AsQueryable() .Select(e0 => e0.Outer.Outer.Inner) .FirstOrDefault(), Claim = g .AsQueryable() .Select(e0 => IncludeExpression( EntityExpression: IncludeExpression( EntityExpression: IncludeExpression( EntityExpression: IncludeExpression( EntityExpression: e0.Inner, NavigationExpression: MaterializeCollectionNavigation( Navigation: Claim.ServiceLines, subquery: DbSet() .Where(c3 => EF.Property<Guid?>(e0.Inner, "Id") != null && object.Equals( objA: (object)EF.Property<Guid?>(e0.Inner, "Id"), objB: (object)EF.Property<Guid?>(c3, "ClaimId")))), ServiceLines) , NavigationExpression: MaterializeCollectionNavigation( Navigation: Claim.CustomProperties, subquery: DbSet() .Where(c4 => EF.Property<Guid?>(e0.Inner, "Id") != null && object.Equals( objA: (object)EF.Property<Guid?>(e0.Inner, "Id"), objB: (object)EF.Property<Guid?>(c4, "ClaimId")))), CustomProperties) , NavigationExpression: MaterializeCollectionNavigation( Navigation: Claim.TransactionSets, subquery: DbSet() .Where(c5 => EF.Property<Guid?>(e0.Inner, "Id") != null && object.Equals( objA: (object)EF.Property<Guid?>(e0.Inner, "Id"), objB: (object)EF.Property<Guid?>(c5, "ClaimId")))), TransactionSets) , NavigationExpression: MaterializeCollectionNavigation( Navigation: Claim.EventHistory, subquery: DbSet() .Where(c6 => __ef_filter__p_0 || (Guid?)EF.Property(c6, "TenantId") == __ef_filter__CurrentTenantId_1) .Where(c6 => EF.Property<Guid?>(e0.Inner, "Id") != null && object.Equals( objA: (object)EF.Property<Guid?>(e0.Inner, "Id"), objB: (object)EF.Property<Guid?>(c6, "ClaimId")))), EventHistory) ) .FirstOrDefault() } ) .OrderBy(e1 => e1.Claim.ServiceLines .Select(x => x.DateOfServiceStart) .Where(date => date.HasValue) .Min())' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

And I think this is because the firstOrDefault() need to be materialized first (This is why I used the.ToList())

Classes:

ClaimTransactionSetBatch

internal class ClaimTransactionSetBatch
{
    public Claim Claim { get; set; }
    public ClaimTransactionSet TransactionSet { get; set; }
    public ClaimBatch Batch { get; set; }
}

Claim:

public class Claim : AuditedAggregateRoot<Guid>, IMultiTenant, IProcessibleType {
    private IList<ClaimEvent> _eventHistory;
    public IList<ClaimEvent> EventHistory { get => this._eventHistory.OrderByDescending(e => e.TimeStamp).ToList(); set => _eventHistory = value; }
    private string _claimTypeCode;
    private decimal? _netPaidAmount;
    private ClaimPaymentStatus _paymentStatus;
    private ClaimVisitMatchStatus _visitMatchStatus;
    private ClaimStatus _status;
    private IList<ClaimAction> _claimActions;
    public IList<ClaimAction> AvailableActions { get { ... } }
    public Guid? TenantId { get; set; }
    public string SubmitterIdentifier { get; set; }
    public string ReceiverIdentifier { get; set; }
    public ClaimPaymentStatus PaymentStatus { get => this._paymentStatus; set { ... } }
    public string ClaimIdentifier { get; set; }
    public string BatchIdentifier { get; set; }
    // Include the rest of the properties here
    public string ClientGender { get; set; }
    public string ClientDiagnosisCode { get; set; }
    public Decimal NetAmount { get; set; }
    public string ClaimTypeCode { get => this._claimTypeCode; set { ... } }
    public ClaimType ClaimType { get; private set; }
    public string FacilityTypeCode { get; set; }
    public ClaimProcessingStatus ProcessingStatus { get; set; }
    public string ProcessingResultMessage { get; set; }
    public ClaimStatus Status { get => this._status; set { ... } }
    public Guid? ProcessingRulesetId { get; set; }
    public ClaimVisitMatchStatus VisitMatchStatus { get => this._visitMatchStatus; set { ... } }
    public decimal? NetPaidAmount { get => this._netPaidAmount; set { ... } }
    public DateTime? LastPaymentReceivedDate { get; set; }
    public string CostCenterIdentifier { get; set; }
    public string CostCenterCode { get; set; }
    public IList<ClaimServiceLine> ServiceLines { get; set; }
    public IList<ClaimCustomProperty> CustomProperties { get; set; }
    public IEnumerable<ClaimTransactionSetClaim> TransactionSets { get; private set; }
    public ClaimRulesetEntityType ClaimRulesetEntityType => ClaimRulesetEntityType.Claim;
    
    public Claim() {
        this.ServiceLines = new List<ClaimServiceLine>();
        this.CustomProperties = new List<ClaimCustomProperty>();
        this.EventHistory = new List<ClaimEvent>();
        this._eventHistory = new List<ClaimEvent>();
        this._claimActions = new List<ClaimAction>();
        this._paymentStatus = ClaimPaymentStatus.PaymentPending;
        this._status = ClaimStatus.InProcess;
    }

    public Claim(Guid id) : base(id) {
        this.ServiceLines = new List<ClaimServiceLine>();
        this.CustomProperties = new List<ClaimCustomProperty>();
        this.EventHistory = new List<ClaimEvent>();
        this._eventHistory = new List<ClaimEvent>();
        this._claimActions = new List<ClaimAction>();
        this._paymentStatus = ClaimPaymentStatus.PaymentPending;
        this._status = ClaimStatus.InProcess;
    }

    public Claim(Guid id, Guid claimTransactionSetId) : this(id) {
        //ClaimTransactionSetId = claimTransactionSetId;
    }
}

ClaimTransactionSet

public class ClaimTransactionSet : Entity<Guid>
{
    private IList<ClaimTransactionSetClaim> claims = new List<ClaimTransactionSetClaim>();

    private DateTime? transmissionStatusChangedDate;
    private TransmissionStatus transmissionStatus;

    public Guid ClaimBatchId { get; set; }
    public Guid? SourceClaimTransactionSetId { get; set; }

    public IEnumerable<ClaimTransactionSetClaim> Claims => this.claims;

    public string GroupControlNumber { get; set; }
    public string GroupSenderCode { get; set; }
    public string GroupReceiverCode { get; set; }
    public string TransactionSetControlNumber { get; set; }

    public TransmissionStatus TransmissionStatus { get => this.transmissionStatus; set { if (this.transmissionStatus != value) { this.transmissionStatus = value; this.transmissionStatusChangedDate = DateTime.UtcNow; } } }

    public DateTime? TransmissionStatusChangedDate => this.transmissionStatusChangedDate;
}

ClaimBatch:

public class ClaimBatch : AuditedAggregateRoot<Guid>, IMultiTenant, IProcessibleType
{
    public Guid? TenantId { get; set; }
    public Guid? SourceClaimBatchId { get; set; }
    public string InterchangeControlNumber { get; set; }
    public string BatchIdentifier { get; set; }
    public DateTime? BatchDate { get; set; }
    public string SenderIdentifier { get; set; }
    public string ReceiverIdentifier { get; set; }
    public string AgencyIdentifier { get; set; }
    public DateTime ReceivedDate { get; set; }
    public string Filename { get; set; }
    public ClaimBatchProcessingMethod ProcessingMethod { get; set; }

    public ClaimProcessingStatus ProcessingStatus { get; set; }
    public string ProcessingResultMessage { get; set; }
    public Guid? ProcessingRulesetId { get; set; }

    public IList<ClaimTransactionSet> TransactionSets { get; set; }

    public IList<ClaimBatchEvent> EventHistory { get; set; }
}

Answer 1

This query should be transletable.

var claimBatchQry = ... // initialize as IQueryable created from DbSet

var allRecords = 
    from claimBatch in claimBatchQry
    from transactionSet in claimBatch.TransactionSets
    from transactionSetClaim in transactionSet.Claims
    where ...
    select new { claimBatch, transactionSet, transactionSetClaim };

 var resultQuery = 
    from d in allRecords.Select(d => new { ClaimId = d.transactionSetClaim.ClaimId })
    from r in allRecords.Where(r => r.transactionSetClaim.ClaimId == d.ClaimId)
        .OrderByDescending(r => r.claimBatch.Id)
        .Take(1)
    select new ClaimTransactionSetBatch
    {
        Batch = r.claimBatch,
        TransactionSet = r.transactionSet,
        Claim = r.transactionSet.Claim
    };

Answer 2

Ideally you would only have one context, and then you can do it as one big IQueryable.

But if that is not possible:

You should remove the AsQueryable and keep the final result as IEnumerable.

Then in the ordering, for Enumerable.OrderBy you need an actual delegate, not a string property name.

One option is a dictionary of sorting functions

public static Dictionary<string, Func<IEnumerable<ClaimSummaryDto>, IEnumerable<ClaimSummaryDto>>> _funcs = new()
{
    { "SomeColumn", source => source.OrderBy(c => c.SomeColumn) },
    { "SomeColumn2", source => source.OrderBy(c => c.SomeColumn2) },
};

public async Task<PagedResultDto<ClaimSummaryDto>> GetListAsync(GetClaimListDto input)
{
    var claimTransactionSetBatch = await this.GetClaimList(input);

    var claims = claimTransactionSetBatch.Select(x => new ClaimSummaryDto()
    {
        AgencyIdentifier = x.Claim.AgencyIdentifier,
        ....
    };

    _funcs.TryGetValue(input.Sorting, out var func);
    func ??= source => source.OrderBy(c => c.DateOfServiceStart);

    claims = func(claims).PageBy(input);

   return new PagedResultDto<ClaimSummaryDto>(total, claims.ToList());
}