retrieve duplicate value from linq

Question

4.00/5 (1 vote)

See more:

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 17:27pm

dartfrog

Updated 12-Sep-12 17:28pm

v2

Add a Solution

4 solutions

Solution 4

Add orderby in your LINQ and Use First() method

C#

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();

Posted 12-Sep-12 18:08pm

Sangramsingh Pawar

Updated 12-Sep-12 18:09pm

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.

Solution 3

Try this:

C#

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();

Posted 12-Sep-12 18:00pm

prashant patil 4987

Updated 12-Sep-12 23:17pm

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.

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.

Posted 12-Sep-12 17:40pm

Ashraff Ali Wahab

Updated 12-Sep-12 17:41pm

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Ganesh Nikam · Accepted Answer · 2012-09-13T00:45:00

hiii,

if your activitydate is of datatype C# DateTime then

use following query

C#

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();

retrieve duplicate value from linq

4 solutions

Solution 5

Solution 4

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us