CODEWITHSUNDEEP
asp.net-mvclinq
Aqil Azad
Aqil Azad

Reputation: 45

Excluding specific records using LINQ query

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.

Upvotes: 2

Views: 1539

Answers (4)

Gert Arnold
Gert Arnold

Reputation: 109080

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

Upvotes: 1

Umar Farooq Khawaja
Umar Farooq Khawaja

Reputation: 3987

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

Upvotes: 0

user7116
user7116

Reputation: 64068

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

Upvotes: 1

Dan Esparza
Dan Esparza

Reputation: 28367

LINQ 'Except' method

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

Upvotes: 0

Related Questions