Linq GroupBy and Concat

Question

I have Parent child one to many relationship between Alerts & Matches Table, group by among these tables work fine with aggregate function count, max, etc. But when I try to select matches records to display it in a comma separated string it gives the following error.

System.InvalidOperationException: The LINQ expression could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.```

This is my basic query

var groupedQuery = 
    from a in _clientContext.Alerts
    join m in _clientContext.AlertsMatches
        on a.AlertID equals m.alertID
    group new { a, m } by new
    {
        a.AlertID,
        a.AlertDate,
        a.AlertScore,
    } into gr
    select new
    {
        AlertId = gr.Key.AlertID,
        AlertDate = gr.Key.AlertDate,
        AlertScore = gr.Key.AlertScore,
        ScenarioNames = gr.Select(x => x.m.Scenario.ScenarioName),
    };

var query = 
    from item in groupedQuery
    select new AlertsDTO
    {
        AlertId = item.AlertId,
        AlertDate = item.AlertDate,
        Scenario = string.Join(", ", item.ScenarioNames)
    };

Answer 1

P.S. for the future , please provide more info, so issue can be re-created without any assumptions. without the class structure its difficult to assume where is an issue, in addition cut-of top of statement makes it more difficult. i created example base on my assumption on class structure based on what i see in code. main issue - ScenarioNames - is a list.

ScenarioNames = gr.Select(x => x.m.Scenario.ScenarioName).ToList()

example:

void Main()
{
    var AlertsMatches = new List<AlertsMatch>();
    AlertsMatches.Add(new AlertsMatch() { alertID = 1, Scenario = new Scenario() { ID = 1, ScenarioName = "Name1" } });
    AlertsMatches.Add(new AlertsMatch() { alertID = 1, Scenario = new Scenario() { ID = 1, ScenarioName = "Name2" } });
    AlertsMatches.Add(new AlertsMatch() { alertID = 1, Scenario = new Scenario() { ID = 1, ScenarioName = "Name3" } });

    var TblA = new List<TblA>();
    TblA.Add(new TblA() {AlertID=1, AlertDate = DateTime.Now, AlertScore = 1});

    var groupedQuery = from a in TblA
        join m in AlertsMatches
        on a.AlertID equals m.alertID
       group new { a, m } by new
       {
           a.AlertID,
           a.AlertDate,
           a.AlertScore,
       } into gr

       select new
       {
           AlertId = gr.Key.AlertID,
           AlertDate = gr.Key.AlertDate,
           AlertScore = gr.Key.AlertScore,
           ScenarioNames = gr.Select(x => x.m.Scenario.ScenarioName).ToList(),
       };

    var query = from item in groupedQuery
                select new AlertsDTO
                {
                    AlertId = item.AlertId,
                    AlertDate = item.AlertDate,
                    Scenario = string.Join(", ", item.ScenarioNames)
                };
    query.Dump();
}

public class AlertsDTO { 
    public int AlertId {get;set;}
    public DateTime AlertDate {get;set;}
    public string Scenario {get;set;}
}

public class Scenario { 
    public int ID {get;set;}
    public string ScenarioName {get;set;}
}

public class TblA{
    public int AlertID {get;set;}
    public DateTime AlertDate {get;set;}
    public int AlertScore {get;set;}
}

public class AlertsMatch
{
    public int alertID { get; set;}
    public Scenario Scenario {get;set;}
    
}

UPDATE 2: to last wuestion how to filter

void Main()
{
    var AlertsMatches = new List<AlertsMatch>();
    AlertsMatches.Add(new AlertsMatch() { alertID = 1, Scenario = new Scenario() { ID = 1, ScenarioName = "Name1" } });
    AlertsMatches.Add(new AlertsMatch() { alertID = 1, Scenario = new Scenario() { ID = 1, ScenarioName = "Name2" } });
    AlertsMatches.Add(new AlertsMatch() { alertID = 1, Scenario = new Scenario() { ID = 1, ScenarioName = "Name3" } });

    var TblA = new List<TblA>();
    TblA.Add(new TblA() { AlertID = 1, AlertDate = DateTime.Now, AlertScore = 1 });

    var groupedQuery = from a in TblA
                       join m in AlertsMatches
                       on a.AlertID equals m.alertID
                       group new { a, m } by new
                       {
                           a.AlertID,
                           a.AlertDate,
                           a.AlertScore,
                       } into gr

                       select new
                       {
                           AlertId = gr.Key.AlertID,
                           AlertDate = gr.Key.AlertDate,
                           AlertScore = gr.Key.AlertScore,
                           Scenarios = gr.Select(x => new { scenarioName = x.m.Scenario.ScenarioName, scenarioID = x.m.Scenario.ID}).ToList(),
                       };

    var query = from item in groupedQuery
                where (item.Scenarios.Any(i=> i.scenarioID ==1))
                select new AlertsDTO
                {
                    AlertId = item.AlertId,
                    AlertDate = item.AlertDate,
                    Scenario = string.Join(", ", item.Scenarios.Select(sn=> sn.scenarioName))
                };
    query.Dump();
}

public class AlertsDTO
{
    public int AlertId { get; set; }
    public DateTime AlertDate { get; set; }
    public string Scenario { get; set; }
}

public class Scenario
{
    public int ID { get; set; }
    public string ScenarioName { get; set; }
}

public class TblA
{
    public int AlertID { get; set; }
    public DateTime AlertDate { get; set; }
    public int AlertScore { get; set; }
}

public class AlertsMatch
{
    public int alertID { get; set; }
    public Scenario Scenario { get; set; }

}

Answer 2

The first thing that came to my mind was you can explicitly fill your scenario.

Parallel.Foreach(query, item => {
  item.Scenario = string.Join(", ", item.ScenarioNames);
});

Otherway:

I think thats error about the not executing scenarios from IQueryable/IEnumerable think before re-select it because data not exist yet its just query. After each select you can use tolist for them. Im not sure about this just fyi.