How to use LINQ with calculated properties in a single pass?

Question

I'd like to make a LINQ query, extracting dynamic properties (calculated fields) of my entities in a single pass, without get the error "The specified type member 'EntityKey' is not supported in LINQ to Entities". Here is the only working way I found, but I am sure there are better and more elegant methods:

       var q = (from i in
                 (from x in context.Tickets
                    select new { x.OperatoreID, x.DataObiettivo })
                group i by new { i.OperatoreID } into g
                select new vmOperatoreDateObiettivo
                {
                    OperatoreID = g.Key.OperatoreID, 
                    NOperatore = "", // field value to be updated...
                    DataObiettivo = g.Max(d => d.DataObiettivo),
                    MinutiAllaScadenza = 0, // field to be updated...
                    Alert = "" // field value to be updated...
                }).ToList();

        // Here I update my fields with a second pass....

        foreach (vmOperatoreDateObiettivo e in q)
        {

            string nome = context.Operatori
                           .Where(t => t.OperatoreID == e.OperatoreID)
                           .First().CognomeNomePuntato.ToString();
            e.NOperatore = nome;
            int minscad = context.Tickets
                          .Where(t => t.OperatoreID == e.OperatoreID).AsEnumerable().Min(a => a.MinutiAllaScadenza);
            e.MinutiAllaScadenza = minscad;
            string sev = context.Tickets
                          .Where(t => t.OperatoreID == e.OperatoreID).AsEnumerable().Min(a => a.Alert);
            e.Alert = sev;             
        }

Thanks in advance!

Answer 1

Try adding a let clause to your query and define calculated field, like so:

var q = (from i in
        (from x in context.Tickets
            select new { x.OperatoreID, x.DataObiettivo })
         group i by new { i.OperatoreID } into g
         let nOperatore = context.Operatori
                           .Where(t => t.OperatoreID == e.OperatoreID)
                           .First().CognomeNomePuntato.ToString() &&
            minutialla = context.Tickets
                            .Where(t => t.OperatoreID == e.OperatoreID)
                            .AsEnumerable().Min(a => a.MinutiAllaScadenza) &&
            alert = context.Tickets
                      .Where(t => t.OperatoreID == e.OperatoreID)
                      .AsEnumerable().Min(a => a.Alert)
         select new vmOperatoreDateObiettivo
         {
             OperatoreID = g.Key.OperatoreID, 
             NOperatore = nOperatore,
             DataObiettivo = g.Max(d => d.DataObiettivo),
             MinutiAllaScadenza = minutialla,
             Alert = alert
         }).ToList();