Click here to Skip to main content
14,667,947 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 12-Sep-12 17:28pm
v2

Rate this:
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();
   
v2
Comments
dartfrog 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 14-Sep-12 4:55am
   
hii, get this updated ans
Rate this:
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.
   
v2
Comments
dartfrog 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:
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();
   
v2
Comments
dartfrog 13-Sep-12 1:51am
   
thanks ur answer
why u put </tblactivity> ? i am really newbie in linq
prashant patil 4987 13-Sep-12 5:18am
   
Hey check my solution 3. i am updating my solution.
Rate this:
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();
   
v2
Comments
dartfrog 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)




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