CODEWITHSUNDEEP
c#asp.net-mvcentity-frameworklinqlinq-to-entities
Sudhir Panda
Sudhir Panda

Reputation: 784

Get last record of each group in entityframework?

I want to retrieve last inserted record of each MobileNo in entity-framework.

Here is my table data.

ID      RegNo       MobileNo    CreatedDate
26727   190077348   9696562673  13-02-2017 06:31
26729   123782783   9696562673  13-02-2017 06:35
45779   530087328   5878525875  07-02-2017 07:23
99902   120058572   7379130560  08-02-2017 12:39
64477   180073650   7417516480  10-02-2017 13:47
81839   240087264   7754990580  11-02-2017 10:47

and want output like

ID      RegNo       MobileNo    CreatedDate 
26729   123782783   9696562673  13-02-2017 06:35
45779   530087328   5878525875  07-02-2017 07:23 
99902   120058572   7379130560  08-02-2017 12:39
64477   180073650   7417516480  10-02-2017 13:47
81839   240087264   7754990580  11-02-2017 10:47

Upvotes: 12

Views: 14714

Answers (2)

fubo
fubo

Reputation: 45947

Assuming the name of your table is Items:

var result = dbContext.Items.GroupBy(x => x.MobileNo)
                      .Select(x => x.OrderByDescending(y => y.CreatedDate).First());

Running sample: https://dotnetfiddle.net/3ud2pB

Upvotes: 30

Hiral Kukadiya
Hiral Kukadiya

Reputation: 35

try this.

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY MobileNo ORDER BY CreatedDate DESC) AS rn
   FROM yourtablename
)
SELECT *
FROM cte
WHERE rn = 1

Upvotes: -1

Related Questions