Excluding specific records using LINQ query

Question

StudentID   Session   Semester   Subject

1           2012      1          1
1           2012      1          2
1           2012      1          3
1           2012      1          4
1           2012      1          5
1           2012      2          6
1           2012      2          7
1           2012      2          8
1           2012      2          9
1           2012      2          10
1           2013      2          1
1           2013      2          13
1           2013      2          14
1           2013      2          15
1           2013      2          16
1           2013      3          17
1           2013      3          18
1           2013      3          19
1           2013      3          20
1           2013      3          21

I don't know what can i name the query which I want to generate. I searched a lot but couldn't find any result.
What I want to do is to write a query which will help me to select the records of semester 1,2 and 3 but want to exclude the records which are the same semester of different session. To specifically say (I want to retrieve semester one of 2012 and 2 and 3 of 2013).

This action is repeated with unspecified session and semester.

Could you write data that you want to retrieve, like above table? I mean could you write result data table? — AliRıza Adıyahşi
– AliRıza Adıyahşi, Commented May 9, 2013 at 13:23
@AliRızaAdıyahşi I want to select the semester 1 of 2012 plus semester 2 and 3 of 2013 — Aqil Azad
– Aqil Azad, Commented May 9, 2013 at 13:29
@AqilAzad: why do you want to do this? Given the table it does not make any sense as to why you would need to do this. Is the user telling you which years/semesters they want? Or is this just a simple case of "I'd like to have the results of this query?" — user7116
– user7116, Commented May 9, 2013 at 13:35
If I have understood the query correctly, I think Aqil wants to select the minimum session for each semester. Admittedly, such a query would make sense for courses where there were multiple occurrences for the course and we wanted to select the earliest one. — Umar F Khawaja
– Umar F Khawaja, Commented May 9, 2013 at 13:43
@sixlettervariables I actullay want to generate the transcript for the students of an institute. they might have failed in a specific semester of a specific session. They will definitely join the next session and will get the result for the same semester in new session. What i want is that I don't want to miss the previous results. I want to only select the new semester plus previous semesters which are not repeated in other sessions. I think that make sense for my client. — Aqil Azad
– Aqil Azad, Commented May 9, 2013 at 13:57

user7116 · Accepted Answer · 2013-05-09 13:39:34Z

1

I can't figure out what that query is meant to represent, but you certainly can use LINQ to achieve that result.

Where will help you par down the results and SelectMany will help you join multiple sequences.

var studentData = ...; // your table of data

var studentId = ...; // student Id

var requestedRecords = new [] 
    {
        new { Session = 2012, Semester = 1 },
        new { Session = 2013, Semester = 2 },
        new { Session = 2013, Semester = 3 },
    };

var query = requestedRecords.SelectMany(
                rr => studentData.Where(
                          ss => ss.StudentID == studentId
                             && ss.Session == rr.Session 
                             && ss.Semester == rr.Semester)
            );

answered May 9, 2013 at 13:39

user7116

64.4k17 gold badges150 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gert Arnold · Accepted Answer · 2013-05-09 13:47:10Z

Here it is in Linqpad:

var tuples = new[] {
Tuple.Create(1, 2012, 1, 1),
Tuple.Create(1, 2012, 1, 2),
Tuple.Create(1, 2012, 1, 3),
Tuple.Create(1, 2012, 1, 4),
Tuple.Create(1, 2012, 1, 5),
Tuple.Create(1, 2012, 2, 6),
Tuple.Create(1, 2012, 2, 7),
Tuple.Create(1, 2012, 2, 8),
Tuple.Create(1, 2012, 2, 9),
Tuple.Create(1, 2012, 2, 10),
Tuple.Create(1, 2013, 2, 1),
Tuple.Create(1, 2013, 2, 13),
Tuple.Create(1, 2013, 2, 14),
Tuple.Create(1, 2013, 2, 15),
Tuple.Create(1, 2013, 2, 16),
Tuple.Create(1, 2013, 3, 17),
Tuple.Create(1, 2013, 3, 18),
Tuple.Create(1, 2013, 3, 19),
Tuple.Create(1, 2013, 3, 20),
Tuple.Create(1, 2013, 3, 21)};
tuples
    .GroupBy(t => t.Item3)
    .Select (g => g.Where(x => x.Item2 == g.Max (x1 => x1.Item2)))
    .Dump();

So you get the semesters in the highest year you find for a semester.

Output:

Item1 Item2 Item3 Item4 
1 2012 1 1 
1 2012 1 2 
1 2012 1 3 
1 2012 1 4 
1 2012 1 5 

Item1 Item2 Item3 Item4 
1 2013 2 1 
1 2013 2 13 
1 2013 2 14 
1 2013 2 15 
1 2013 2 16 

Item1 Item2 Item3 Item4 
1 2013 3 17 
1 2013 3 18 
1 2013 3 19 
1 2013 3 20 
1 2013 3 21

Dan Esparza · Accepted Answer · 2013-05-09 13:29:36Z

0

LINQ 'Except' method

I think you might be interested in the LINQ 'Except' method.

answered May 9, 2013 at 13:29

Dan Esparza

28.5k29 gold badges104 silver badges127 bronze badges

2 Comments

Aqil Azad Over a year ago

Thanks, but i couldn't found a query to select the excludes for except. I mean I couldn't query the semester 2 of 2012

Umar F Khawaja Over a year ago

Except would work if what needs to be excluded was known in advance. I don't think that would work here.

Umar F Khawaja · Accepted Answer · 2013-05-09 13:40:34Z

0

I think you need to do a self-join here. You will want to first do a group-by and get the min(session) for each session/semester combination, then you want to join this result with the full table again joining on session and semester.

Here's a very good resource for LINQ samples. I do not have my LINQpad to hand, otherwise I would have knocked something up for you.

I normally steer clear from recommending products, but LINQPad is a think of beauty when it comes to prototyping LINQ queries or even C# snippets.

The SQL will look something like this:

SELECT
    *
FROM
    Course AS C
    INNER JOIN
    (
        SELECT
            MIN(IC.Session),
            IC.Semester
        FROM
            Course AS IC
        GROUP BY
            IC.Session,
            IC.Semester
    ) AS Q ON C.Session = Q.Session AND C.Semester = Q.Semester

edited May 9, 2013 at 13:40

answered May 9, 2013 at 13:23

Umar F Khawaja

3,9971 gold badge34 silver badges53 bronze badges

3 Comments

Aqil Azad Over a year ago

I think I did what you say. You may please write your query even if SQL

Aqil Azad Over a year ago

unfortunately the result is different

Umar F Khawaja Over a year ago

Try MAX rather than MIN. I think that should sort you out.

Collectives™ on Stack Overflow

Excluding specific records using LINQ query

4 Answers 4

Comments

Comments

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related