How to apply group by instead of distinct when need only unique rows?

Question

I am working on ASP.NET Razor page LINQ to SQL function. I face issue I can't display distinct rows from result returned for viewmodel AssetLPOvm

Desired result

If I have 2 or 3 rows as sample exactly every thing is same and repeated so I need to return only one rows unique or distinct because other rows is repeated .

So exactly I need to return distinct result using group by instead of distinct.

I tried using distinct as below but it not return distinct rows.

So what I do to solve issue?

    public List<AssetLPOvm> GetAllLpoDetails(string SelectedCompany)
    {    
        var AssetBranches = _unitOFWorkAsset.AssetBranches.GetList(x => x.CompanyNo == SelectedCompany && Convert.ToInt32(x.UserID) > 0 );
            
        var NewAssetTagging = _unitOFWorkAsset.NewAssetTagging.GetList(x => x.LPONo > 0);
var result = (from assetBranch in AssetBranches
               join user in Users on assetBranch.UserID equals user.UserID
               join assetTagging in NewAssetTagging on Convert.ToDecimal(assetBranch.LPONO) equals assetTagging.LPONo
     
               select new AssetLPOvm
               {
                   AssetItemNo= assetBranch.AssetItemNo,
                   SupplierName=assetTagging.SupplierName,
                   SelectedMCUName= assetBranch.SelectedMCUName,
                   AssetOwner=assetBranch.AssetOwner,
                   ItemCode=assetBranch.ItemCode,
                          
                   Qty = assetBranch.Qty,
                   LPONO = assetBranch.LPONO
                                }).Distinct().ToList();
 return result;
}

    public class AssetLPOvm
    {    
        public string LPONO { get; set; }

        public string AssetItemNo { get; set; }

        public string SupplierName { get; set; }

        public string SelectedMCUName { get; set; }

        public string AssetOwner { get; set; }

        public double Qty { get; set; }

        public string ItemCode { get; set; }


    }

    public class AssetBranches
    {
      [Key]
      [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
      public decimal ID {get;set;}
      public string AssetItemNo { get; set; }
      public string SelectedMCUName { get; set; }
      public string AssetOwner { get; set; }
      public double Qty { get; set; }
      public string LPONO { get; set; }
      public string ItemCode { get; set; }

       }
    public class NewAssetTagging
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public decimal ID { get; set; }
        public decimal? LPONo { get; set; }
        public decimal? SupplierNumber { get; set; }

    }
public class User
{
    public string vLoginName {get;set;}
    public string UserName {get;set;}
}

How to make group by to result of function instead using distinct function?

I try with distinct but not return distinct rows.

Answer 1

Group by all columns , you can have a try:

.GroupBy(p => new {
    p.AssetItemNo,
    p.SupplierName,
    p.SelectedMCUName,
    p.AssetOwner,
    p.ItemCode,
    p.Qty,
    p.LPONO}).ToList();

Answer 2

I think you can use GroupBy as below.

.GroupBy(x=>x.ItemCode).Select(x=>x.First()).ToList()

Answer 3

I suspect that the reason .Distinct() did not work as expected is that your AssetLPOvm class does not implement IEquatable<T>. The equality comparison used by .Distinct() then only performs a reference comparison, not a value comparison. Two different AssetLPOvm objects will always compare unequal, even if they have the same values. The result is that no de-duplication is performed.

This can be resolved by:

1. implementing IEquatable<T>.
2. Select the values into an anonymous object (which will implement a value base comparison), apply .Distinct(), and then use .Select() to convert to AssetLPOvm objects.
3. Use .DistinctBy(), which allows you to explicitly define the values to be compared.

Try replacing the .Distinct() with:

.DistinctBy(a => new { a.AssetItemNo, a.SupplierName, a.SelectedMCUName, a.AssetOwner, a.ItemCode, a.Qty, a.LPONO})

I believe clearly shows the original intent, while avoiding the extra overhead of .GroupBy().