How to optimize this query using EF

Hi good day I am new with Entity Framework. I just wanna to know if there is a way I could improve my implementation. Here are the codes.

 public async Task<List<Record>> GetRecordsByBatchId(string batchId, string source)
    {
        List<string> idList = new List<string>();


        //[1] Get all parent ID from table 1 with a filter of source and batchId
        var parentIds= await _context.Set<FirstTable>()
            .Where(a => a.IsActive
                && a.BatchId.Equals(batchId)
                && a.Source.Equals(source)).Select(b => b.ParentId).ToListAsync();

        if (parentIds.Count() == 0)
        {
            return new List<Record>();
        }


        //[2] Query idNumber of each parentId from [1] to SecondTable
        List<long> idNumber = await _context.Set<SecondTable>()
            .Where(a => parentIds.Contains(a.Id))
            .Select(b => b.IdNumber).ToListAsync();


        //[3] Query Record/s that contains idNumber from previous query [2]. it is possible that 1 or 
        //more records has same idNumber
        List<Risk> recordByIdNumber = await _context.Set<SecondTable>()
            .Where(a => idNumber.Contains(a.IdNumber)).ToListAsync();


       //[4] In this part I just want to group the records in [3] by Id number and sort each group 
       //by its endorsementNumber in descending order and return the record with highest endorsement 
       //number for each group 
        return (from record in recordByIdNumber 
                group record by record.IdNumber into g
                orderby g.Key
                select g.OrderByDescending(risk =>risk.EndorsementNumber).FirstOrDefault()).ToList();
    }
}

The model for the FirstTable

    public class FirstTable
{
    public Guid? ParentId{ get; set; }
    public string BatchId { get; set; }
    public string Source { get; set; }
    public bool IsActive { get; set; }
}

The model for the SecondTable

 public class SecondTable
{
    public Guid Id{ get; set; }
    public int EndorsementNumber { get; set; }
    public long IdNumber { get; set; }
}

Note: I just include the necessary properties in the model.

This approach is working as expected. I just wanna know if there is a possibility that these queries could be optimized that there is only 1 query for the SecondTable table.

Any help would be greatly appreciated, thanks in advance.



Read more here: https://stackoverflow.com/questions/66998401/how-to-optimize-this-query-using-ef

Content Attribution

This content was originally published by Edward at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: