Grouping in Linq to Entities?

Question

I have 3 variables:

• wqReqIds: a list of Ids
• aliases: A list of aliases that occur in a table more than once (where the record ID is contained in wqReqIds)
• allWqAssociates: A list of AssociateInfo whose wqReqId can be found in the list wqReqIds

What I want to do: If AssociateInfo's property Alias can be found in the list of aliases, I want to put an asterisk * in front of the defined Alias.

The uncommented Alias is where I last left off, and as you can imagine, it threw an error. I sort of expected it, but am at a loss as to how to get what I need done in one go. Is this even possible?

Please see my code below, and thanks for the help!

  List<int> wqReqIds = requests.Select(x => x.WorkQueueRequestId).ToList();
  var aliases = db.WorkQueueRequestDetails.Where(w => wqReqIds.Contains(w.WorkQueueRequestId))
                     .GroupBy(x => x.AssociatesHistory.Alias)
                     .Where(g => g.Count() > 1).ToList();


  var allWqAssociates = db.WorkQueueRequestDetails.Where(w => wqReqIds.Contains(w.WorkQueueRequestId))
                     .Select(x => new AssociateInfo
                     {
                         WorkQueueRequestId = x.WorkQueueRequestId,
                         //Alias = x.AssociatesHistory.Alias,
                         Alias = aliases.Where(y => y.Key == x.AssociatesHistory.Alias).Any() 
                              ? "***" + x.AssociatesHistory.Alias 
                              : x.AssociatesHistory.Alias,
                         //Alias = aliases.Contains(x.AssociatesHistory.Alias) 
                              // ? "***" + x.AssociatesHistory.Alias 
                              // : x.AssociatesHistory.Alias,
                         Name = x.AssociatesHistory.FirstName + (x.AssociatesHistory.MiddleInitial == null ? " " : " " + x.AssociatesHistory.MiddleInitial + " ") + x.AssociatesHistory.LastName
                     })
                     .ToList();

Answer 1

The below LINQ has been tested and should work. Notice on the last variable we are calling ToList before the select. This is so we can use other functions such as ToString() and ternary operator without having EF try to convert it. There might be some variation in the data models since you didn't provide. I also implemented a ToString override on the full name which you can see at the bottom of this answer.

var wqReqIds = requests.Select(x => x.WorkQueueRequestId).ToList();

var aliases = db.WorkQueueRequestDetails.Where(x => wqReqIds.Contains(x.WorkQueueRequestId))
        .GroupBy(x => x.AssociatesHistory.Alias)
        .Where(x => x.Count() > 1)
        .Select(x => x.Key)
        .ToList();


var allWqAssociates = db.WorkQueueRequestDetails.Where(w => wqReqIds.Contains(w.WorkQueueRequestId))
        .ToList()
        .Select(x => new AssociateInfo
        {
            WorkQueueRequestId = x.WorkQueueRequestId,
            Alias = aliases.Any(y => y == x.AssociatesHistory.Alias)
                ? "***" + x.AssociatesHistory.Alias
                : x.AssociatesHistory.Alias,
            Name = x.AssociatesHistory.ToString()
        });

Model with ToString implementation

public class AssociatesHistory
{
    public string Alias { get; set; }
    public string FirstName { get; set; }
    public string MiddleInitial { get; set; }
    public string LastName { get; set; }

    public override string ToString()
    {
        return $"{FirstName} {MiddleInitial} {LastName}";
    }
}