0

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.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
Masum Rayhan
  • 1
  • 2
  • Please refer [this answer](https://stackoverflow.com/a/10720079/7687666) first, if it not works, please let me know. – Jason Pan Nov 07 '22 at 09:45
  • ASP.NET is a web app framework, not a database library or ORM. Your code has a critical bug too - it's loading the entire table in memory, then trying to filter the loaded objects using LINQ, without any benefit of indexes. That `GetAll` should never be used in queries. EF Core doesn't need repositories (a DbContext is already a multi-entity UoW and repository) – Panagiotis Kanavos Nov 07 '22 at 11:25
  • It's unclear what you want to do. `DISTINCT()` works fine here. It's applied to in-memory objects though, not rows. Inside an object group, the rows are most likely distinct. If you want to return only a single object per `LcNoListId` you can use [DistinctBy](https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.distinctby?view=net-7.0) instead of `GroupBy` – Panagiotis Kanavos Nov 07 '22 at 11:32

1 Answers1

0

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.

Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32