LINQ select duplicate object values-CodePudding

I have a table:

Name | account info| AccountNumber
-----| ------------| ------
abc  |  IT         | 3000
bdc  |  Desk       | 2000
sed  |  Kitchen    | 3000
afh  |  work       | 4000
hsfs |  home       | 2000

I want to achieve something like this:

Name | account info| DisguiseInfo
-----| ------------| ------
abc  |  IT         | Acc1
bdc  |  Desk       | Acc2
sed  |  Kitchen    | Acc1
afh  |  work       | Acc3
hsfs |  home       | Acc2

I tried doing this:

 int count = 1;
 var disguise = listResults.GroupBy(x => x.ID).Select(y => 
 y.First()).Distinct();
 foreach (var i in disguise)
{
    i.DisguiseName = "Acc "   count;
    count  ;
}

Which gives a results like this (very close to what I want):

 Name | account info| DisguiseInfo
-----| ------------| ------
abc  |  IT         | Acc1
bdc  |  Desk       | Acc2
sed  |  Kitchen    | 
afh  |  work       | Acc3
hsfs |  home       |

The problem with that is that, it doesn't give the ability to add the same string value 'Acc1' to the same duplicate value in the list, (the rest of the table comes blank only the fist values gets replaced), So how do I replace the entire value with matching IDs?

//EDIT the data is being populated using sqlcommand in a class called SQLQuery, in this class there's a method called Account which execute like this:

SqlDataReader reader = command.ExecuteReader();
 List<ViewModel> returnList = new List<ViewModel>();
if (reader.HasRows)
{
 while (reader.Read())
 {
   ViewModel vm = new ViewModel();
   vm.Name = reader.GetString(2);
   vm.AccountInfo= reader.GetString(3);
   vm.AccountNumber = reader.GetInt32(4);

  returnList.Add(vm)
 }
}

so this method return the first table above no issues. In my controller action, is where I want to perhaps copy the SQLQuery return list into another list to filter so I'm doing (in the action method):

 public async Task<IActionResult> DisguiseAction(string accNum)
 {
  List<ViewModel> executeSQL = new List<ViewModel>();
  SQLQuery getQuery = new SQLQuery();
  executeSQL = getQuery.Account(accNum); //at this point the sql 
  //gets executed with the correct value. Now I need to disguise the 
  //value. which I did
  
  int count = 1;
 var disguise = listResults.GroupBy(x => x.ID).Select(y => 
 y.First()).Distinct();
 foreach (var i in disguise)
{
    i.DisguiseName = "Acc "   count;
    count  ;
}
 }

CodePudding user response：

Order this table by AccountInfo, then go through the sorted table and always compare two consecutive elements according to AccountInfo and if i - 1 < i, then raise the value Acc by 1.

CodePudding user response：

Your problem is the .Distinct() call, that only takes the first element out of each group. Due to the fact, that you need to memoize all already seen values, it is easier to use a dictionary to hold the already mapped values. One possibility could be:

var accounts = new List<Account>
{
    new Account { Name = "abc", Department = "IT", AccountInfo = 3000 },
    new Account { Name = "bdc", Department = "Desk", AccountInfo = 2000 },
    new Account { Name = "sed", Department = "Kitchen", AccountInfo = 3000 },
    new Account { Name = "afh", Department = "work", AccountInfo = 4000 },
    new Account { Name = "hsfs", Department = "home", AccountInfo = 2000 },
};

var mappings = new Dictionary<int, string>();

var summary = accounts
    .Select(acc => new AccountSummary
    {
        Name = acc.Name,
        Department = acc.Department,
        DisguiseInfo = GetOrAddMapping(acc.AccountInfo, mappings)
    })
    .ToList();

foreach (var item in summary)
{
    Console.WriteLine(JsonSerializer.Serialize(item));
}

And the helper method would in this case be:

private static string GetOrAddMapping(int accountInfo, Dictionary<int, string> mappings)
{
    if (!mappings.TryGetValue(accountInfo, out var value))
    {
        value = $"Acc{mappings.Count   1}";
        mappings.Add(accountInfo, value);
    }

    return value;
}

CodePudding user response：

using System;
using System.Collections.Generic;
public class Ent
{
    public Ent(string a, string b, string c)
    {
        name = a;
        location = b;
        id = c;
    }
    public string name;
    public string location;
    public string id;
    public override string ToString()
    {
        return $"{name} | {location} | {id}";
    }
}

public class Program
{

    public static void Main()
    {
        var input = new List<Ent>
        {
        new Ent("abc", "IT",      "3000"),
        new Ent("bcd", "Desk",    "2000"),
        new Ent("sed", "Kitchen", "3000"),
        new Ent("afh", "work",    "4000"),
        new Ent("hsf", "home",    "2000"),
        };
        var output = input
                .GroupBy(x => x.id)    // x is of type Ent
                .SelectMany(y =>       // y is of type IGrouping<string, IEnumerable<Ent>>
                    y.Select(z =>      // z is of type Ent 
                        new Ent(z.name, z.location, "Acc"   y.Key.Substring(0, 1))));
        foreach(var line in output)
            Console.WriteLine(line);
    }
}

Gives an output that looks like:

abc | IT | Acc3
sed | Kitchen | Acc3
bcd | Desk | Acc2
hsf | home | Acc2
afh | work | Acc4

This code works using GroupBy on the id, then unroll the groups using SelectMany, but now we have the Key for each group. So when unrolling, re-create each line, but replace the id with a transformed value of the Key.

CodePudding user response：

After grouping by AccountInfo, you could take advantage of the .SelectMany() overload that provides an indexer for the source element (i.e. an indexer for the AccountInfo values).

In the following example, I am assuming that you have two separate classes for the original (identifiable) accounts and the disguised accounts, e.g.:

public class BasicAccount
{
    public string Name { get; set; }
    public string AccountType { get; set; }
}

public class Account : BasicAccount
{
    public int AccountInfo { get; set; }
}

public class DisguisedAccount : BasicAccount
{
    public string DisguisedInfo { get; set; }
}

If your original accounts are collected in a variable List<Account> accounts as such:

List<Account> accounts = new()
{
    new() { Name = "abc", AccountType = "IT", AccountInfo = 3000 },
    new() { Name = "bdc", AccountType = "Desk", AccountInfo = 2000 },
    new() { Name = "sed", AccountType = "Kitchen", AccountInfo = 3000 },
    new() { Name = "afh", AccountType = "work", AccountInfo = 4000 },
    new() { Name = "hsfs",AccountType = "home", AccountInfo = 2000 }
};

, your disguised accounts could be produced as follows:

IEnumerable<DisguisedAccount> disguisedAccounts = accounts
    .GroupBy(a => a.AccountInfo)
    .SelectMany(( accountsByInfo, counter ) => accountsByInfo
        .Select(account => new DisguisedAccount
            {
                Name = account.Name,
                AccountType = account.AccountType,
                DisguisedInfo = $"Acc{counter}"
            }));

Note: Using this approach, you lose the ordering given by the original accounts collection. The resulting collection is:

Name	AccountType	DisguisedInfo
abc	IT	Acc1
sed	Kitchen	Acc1
bdc	Desk	Acc2
hsfs	home	Acc2
afh	work	Acc3

Example fiddle here.