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; }
}

Why are you using separate repositories for a single query? Ar ethey separate databases? — Charlieface
– Charlieface, Commented Apr 10, 2024 at 16:19
I'm using ABP framework; that is how the API structure works @Charlieface — Jesus
– Jesus, Commented Apr 10, 2024 at 16:28
Your query is bad and ineffective, what it hsould do? Do you have any navigation properties? Show them. And what is the methods WithDetailsAsync? What they return and why they are async? — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Apr 10, 2024 at 17:17
I can repeat that your query is bad, that's why you have exception. I can help you to write effective query if you supply me with requested information. — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Apr 10, 2024 at 18:10

Charlieface · Accepted Answer · 2024-04-10 22:03:51Z

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());
}

Svyatoslav Danyliv · Accepted Answer · 2024-04-11 09:16:12Z

0

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
    };

edited Apr 11, 2024 at 9:16

answered Apr 10, 2024 at 19:05

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

1 Comment

Jesus Over a year ago

But that is going to take 1 row from transactionSet and one row from transactionSetClaim, and I need to compare with all of the rows... my relations in sql are like:

select * from claimBatch as cb inner join  transactionSet as ts on cb.id =. ts.claimBatchId inner join claimTransactionSetClaim as cts on ts.Id = cts.transactionSetId

So the idea is to get all records in that linq grouping so as not to get repeated rows. So if transactionSetClaim have the same claimId in two different transactionSetId, I just want to get the latest transactionSetId. that's why the take 1 does not works

Collectives™ on Stack Overflow

Linq code improvement issues with filters

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related