Linq groups and fetch max id

Question

Is there any way to optimize the code below so that you search the forms that have the maximum version according to the IdCentroCusto?

In the code below I’m doing the grouping and catching the CM.Max(d => d.Versao) to fetch the correct form self.

var Formularios = qrFormularios
                       .GroupBy(F => new { F.IdCentroCusto, F.Versao })
                       .Select(CM => new
                       {
                           Formulario = qrFormularios.FirstOrDefault(n => n.Versao == CM.Max(d => d.Versao) && n.IdCentroCusto == CM.Key.IdCentroCusto),
                       })
                       .ToList();

You would need a query to return all the form fields to me.

[Serializable, Table("tb_Formularios")]
    public partial class rtb_Formularios 
    {
        [Key]
        public Int32 IdFormulario { get; set; }
        public Int32 IdModFormulario { get; set; }
        [MaxLength(100)]
        public String TituloFormulario { get; set; }
        public Int32 Vigencia { get; set; }
        public Int32 IdUsuario { get; set; }
        public Int32 IdCentroCusto { get; set; }
        public Int32 Versao { get; set; }
        public Int32? IdLogStatus { get; set; }
        public Int32 IdPassoWorkflow { get; set; }
        public DateTime? DtTransmisao { get; set; }
        public DateTime DtInclusao { get; set; }
        [Timestamp]
        public Byte[] SeqAlteracao { get; set; }

        [NotMapped]
        public Int32 PKValue
        {
            get { return IdFormulario; }
        }
    } 

Answer 1

Marconcilio, you can first make a subquery to return the latest version of each cost center... then you make a Join with this subquery.

var centrosCusto = 
    from formulario in qrFormularios
    group formulario.Versao by formulario.IdCentroCusto into grupo
    select new { 
        IdCentroCusto = grupo.Key, 
        Versao = grupo.Max(versao => versao)
    };

var formularios = (
    from formulario in qrFormularios
    join centroCusto in centrosCusto on new { formulario.IdCentroCusto, formulario.Versao } equals new { centroCusto.IdCentroCusto, centroCusto.Versao }
    select formulario;
).ToList();