Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# VB ASP.NET .NET LINQ MVC3
Hi all,
 
I have some issue with linq statement. here is my table, named Tblactivity
 
activityid bigint
studentid bigint
activitydate datetime
description nvarchar(Max)
status bit
 
i populate Tblactivity with data. such as
 
activityid | studentid | activitydate | description | status
1 | 1 | 2012-08-15 00:30:00 | descA | A
2 | 3 | 2012-08-16 12:30:00 | descAA | A
3 | 2 | 2012-08-16 13:15:12 | descB | B
4 | 3 | 2012-09-02 10:20:11 | descS | A
5 | 3 | 2012-09-02 11:00:00 | descS | A
6 | 4 | 2012-09-02 14:00:00 | descAA | S
 
so i develop linq to get detail information of student id = 3. here is my linq code
 
public List<Tblactivity> getStudents(long studentid, string status)
   {
	studentdatacontext db=new studentdatacontext;
            try
            {
                List<Tblactivity> BG = (from o in db.Tblactivities
                              orderby o.activitydate descending where o.studentid == studentid &&  
                              o.activitydate.Value.Month <= DateTime.Now.Month &&
                              o.activitydate.Value.Month >= DateTime.Now.Date.AddMonths(-1)&&
                              o.status == status select o).ToList();
 
                return BG;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
 
 
so i got all of the records according to student id = 3.but i want only one record when the activitydate is same (the latest record).
i read some article they suggest me to use MAX(). but i don't know how to develop to suit with the above function. pls suggest me.
 
Thanks,
df
Posted 12-Sep-12 18:27pm
Edited 12-Sep-12 18:28pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

hiii,
 
if your activitydate is of datatype C# DateTime then
 
use following query
 
List<Tblactivity> BG= (from o in db.Tblactivities
                                     where o.studentid == studentid &&
                                       o.activitydate.Value.Month <= DateTime.Now.Month &&
                                       o.activitydate.Value.Month >= DateTime.Now.Date.AddMonths(-1) &&
                                       o.status == status
                                       && o.activitydate == (from n in db.Tblactivities where n.studentid == o.studentid && Convert.ToString(n.activitydate).Substring(0, 10) == Convert.ToString(o.activitydate).Substring(0, 10) orderby n.activitydate descending select n).Max(l => l.activitydate)
                                   select o).Distinct().ToList();
  Permalink  
v2
Comments
dartfrog at 14-Sep-12 4:34am
   
Hi Bro, thanks
 
i want List<Tblactivity> BG rather than var BG how should i do it?
is there any suggestion to do it?
Ganesh Nikam at 14-Sep-12 4:55am
   
hii, get this updated ans
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You know it is only one record you need to get, why don't you retrieve using First() instead of ToList() since you are sorting it descending by activitydate, the first record is going to be your latest record.
  Permalink  
v2
Comments
dartfrog at 13-Sep-12 0:36am
   
Thanks for your suggestion
but i want : 2012-08-16 12:30:00 and 2012-09-02 11:00:00 instead of
2012-08-16 12:30:00, 2012-09-02 10:20:11 and 2012-09-02 11:00:00.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Try this:
List<tblactivity> BG = (from o in db.Tblactivities
                       orderby o.activitydate descending where o.studentid == studentid &&  
                       o.activitydate.Value.Month <= DateTime.Now.Month &&
                       o.activitydate.Value.Month >= DateTime.Now.Date.AddMonths(-1)&&
                       o.status == status select o).ToList().Distinct();
  Permalink  
v2
Comments
dartfrog at 13-Sep-12 1:51am
   
thanks ur answer
why u put </tblactivity> ? i am really newbie in linq
prashant patil 4987 at 13-Sep-12 5:18am
   
Hey check my solution 3. i am updating my solution.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Add orderby in your LINQ and Use First() method
Tblactivity BG = (from o in db.Tblactivities
                orderby o.activitydate descending where o.studentid == studentid &&
                o.activitydate.Value.Month <= DateTime.Now.Month &&
                o.activitydate.Value.Month >= DateTime.Now.Date.AddMonths(-1)&&
                o.status == status orderby o.activitydate descending select o).First();
  Permalink  
v2
Comments
dartfrog at 13-Sep-12 0:38am
   
Thanks for your suggestion but i want : 2012-08-16 12:30:00 and 2012-09-02 11:00:00 instead of 2012-08-16 12:30:00, 2012-09-02 10:20:11 and 2012-09-02 11:00:00.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 350
1 Jochen Arndt 190
2 Richard MacCutchan 135
3 Sergey Alexandrovich Kryukov 130
4 DamithSL 95
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,310


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 14 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100