0

I have the following SQL Query:

    SELECT D.[UNIC]
      ,D.[UNIC_ETICHETA]
      ,D.[CATEGORIE]
      ,D.[NUME]
      ,A.[VALOARE]
      ,A.[UM]   
  FROM FINAL D
  CROSS APPLY 
   ( 
   SELECT TOP 1 [ VALOARE],[ UM]     
   FROM FINAL E
   WHERE E.UNIC = D.UNIC AND E.[UNIC_ETICHETA] = D.[UNIC_ETICHETA] AND E.[CATEGORIE] = D.[CATEGORIE]
   ) A 
  GROUP BY D.UNIC, D.[UNIC_ETICHETA], D.[CATEGORIE], D.[NUME], A.[VALOARE], A.[UM]

How would I transform it to a lambda or linq query?

The class model bellow.

 [Table("FINAL", Schema = "dbo")]
  public partial class Final
  {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int UNIC
    {
      get;
      set;
    }
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int UNIC_ETICHETA
    {
      get;
      set;
    }
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string? CATEGORIE
    {
      get;
      set;
    }
    public string? NUME
    {
      get;
      set;
    }
    public decimal? VALOARE
    {
      get;
      set;
    }
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string? UM
    {
      get;
      set;
    }
  }

I've got to something like this so far, but I dont know how to get the first VALOARE and UM from that CROSS APPLY.

   Final finalGroupped = (Final)final.GroupBy(g => new { g.UNIC, g.UNIC_ETICHETA, g.CATEGORIE, g.NUME, g.VALOARE, g.UM })
                    .Select(b => new Final
                    {
                        UNIC = b.Key.UNIC,
                        UNIC_ETICHETA = b.Key.UNIC_ETICHETA,
                        CATEGORIE = b.Key.CATEGORIE,
                        NUME = b.Key.NUME,
                        VALOARE = b.Key.VALOARE,
                        UM = b.Key.UM
                    });

Desired output will be like the one in the image bellow:

enter image description here

Catalin Cernat
  • 137
  • 3
  • 15
  • Do you mean that you have all the data locally in a list, or do you mean that you want write some linq that will make eg EF do this? – Caius Jard Dec 09 '21 at 07:19
  • LINQ to make EF do this, yes, you are correct. – Catalin Cernat Dec 09 '21 at 07:19
  • 2
    Also, a TOP N without an ORDER BY doesn't make any sense, so you're really just saying "get any record from a group x" ? – Caius Jard Dec 09 '21 at 07:21
  • Does this answer your question? [How do I write this cross apply query in LINQ-to-SQL?](https://stackoverflow.com/questions/9787482/how-do-i-write-this-cross-apply-query-in-linq-to-sql) – Cleptus Dec 09 '21 at 07:21
  • Updated my question with some more details. – Catalin Cernat Dec 09 '21 at 07:37
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. – NetMage Dec 11 '21 at 00:37

0 Answers0