How to distinct a duplicate row in EF Core 6 with method syntax?

Question

I want to query my database where I am searching a contract id by grouping them with LcNoListId. It is possible to contains duplicate value in LcNoListId column against a contract id. At this point I need to distinct all the duplicate LcNoListId and need to return without duplicate record.

I tried the below function for returning rows without duplicate value in LcNoListId column. But the Distinct() function is not working.

[HttpGet("contract-no/{id}")]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public IActionResult Get(int id)
{
    try
    {
        IEnumerable<BTBPending> objBTBPendingList = _unitOfWork.BTBPending.GetAll(includeProperties: "ProformaInvoice,ContractList,SupplierList,CountryList,ItemList,BuyerList,StyleList,TradeTermList,ErpRemarksList,StatusList,LcNoList,UdAmendList");
            
        var query = objBTBPendingList
                .Where(x => x.ContractListId == id && x.UdAmendList == null)
                .GroupBy(c => c.LcNoListId)
                .Where(grp => grp.Distinct().Count() > 1);

        // var que = Company.Distinct();
        return Ok(query);
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, $"Something went wrong in the {nameof(Get)}");
        return StatusCode(500, "Internal Server Error, Please Try Again Leter!");
   }
}

This function returns all the rows which contain duplicate value in the LcNoListId column. But I don't need any duplicate values in LcNoListId column.

It is clearly shown that this Distinct() function is not working in this context. Please help me to find a solution.

Answer 1

Use the following query:

var query = objBTBPendingList
    .Where(x => x.ContractListId == id && x.UdAmendList == null)
    .GroupBy(c => c.LcNoListId)
    .Select(grp => grp.First());

But looks like you have stuck with ineffective repository pattern realization. With pure EF Core it is possible to make more performant query and do not retrieve duplicates and not wanted records from database.