How to display multiple records using linq

Question

0.00/5 (No votes)

See more:

, +

I have a table that stores Referral details - it includes fields like Referral Type, Referral Date, OffenderID, OfficerID etc.) And obviously some Offenders are referred more than once (multiple times) and likewise, OfficerID also repeats in the table as many offenders are referred by the same Officer.

What I want to do is, I want to have a query that displays (thru a view) all offenders who have been referred multiple times or more than once.

I was able to write a group by query to get the Offender ID and the count (basically Key and the Count) but I am not able to display the records that is being repeated in the referral table.

C#

var results = db.Referrals
.GroupBy(em => em.OffenderID).Where(em => em.Count() > 1)
.Select(
   g =>
      new
      {
          g.Key,
          Count = g.Count()
      }
);

Right now the variable result will have something like this
Key Count
--- -----
A10 3
L05 2

(A10 and L05 - OffenderIDs)

I want to display the result as follows (sample data) in a view
ReferralID Offender Name Officer Name Referral Date
---------- ------------- ------------ ------------------ ----
001 Adam Tom 1/1/2014
015 Adam Eric 2/5/2014
200 Adam Tom 6/6/2014
009 Lucy Dave 2/2/2011
400 Lucy Kate 2/2/2015

I also tried

SQL

var duplicates = db.Referrals.
    .GroupBy(em => em.OffenderID)
    .Where( em => em.Skip(1).Any() )
    .SelectMany(  em => em );

But it gave error with the for each loop in the view.

I am too new to ASP.NET MVC, Linq, C# - any help is truly appreciated. Thank you very much.

Posted 28-May-15 11:44am

Member 11581003

Add a Solution

1 solution

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)

Sascha Lefèvre · Answer 1 · 2015-05-28T13:07:00

To give you a fully working query I would have to know how your Offender- and Officer-Tables/Entities look like. So I'll make a good guess here instead and to illustrate I include all the code I wrote to test it. You obviously can discard everything except the query at the bottom. I assume you'll be able to modify it to make it work, otherwise please leave a comment :)

For clarity I chose expressive long names for the identifiers in the query so it may look a bit messy here due to line breaks - just copy it into Visual Studio somewhere :)

C#

public class Referral
{
    public int ReferralID;
    public int OfficerID;
    public int OffenderID;
    public DateTime ReferralDate;
}

public class Offender
{
    public int OffenderID;
    public string Name;
}

public class Officer
{
    public int OfficerID;
    public string Name;
}

public class OffenderQuery
{
    public static void test()
    {
        var Offenders = new List<Offender>()
        {
            new Offender(){OffenderID = 1, Name = "Tom" },
            new Offender(){OffenderID = 2, Name = "Eric"},
            new Offender(){OffenderID = 3, Name = "Lucy"},
            new Offender(){OffenderID = 4, Name = "Kate"},
        };

        var Officers = new List<Officer>()
        {
            new Officer(){OfficerID = 1, Name = "Adam" },
            new Officer(){OfficerID = 2, Name = "Marc"},
        };

        var Referrals = new List<Referral>()
        {
            new Referral(){OffenderID = 1, OfficerID = 1, ReferralID = 1, ReferralDate = DateTime.Now},
            new Referral(){OffenderID = 2, OfficerID = 2, ReferralID = 2, ReferralDate = DateTime.Now},
            new Referral(){OffenderID = 2, OfficerID = 1, ReferralID = 3, ReferralDate = DateTime.Now},
            new Referral(){OffenderID = 3, OfficerID = 2, ReferralID = 4, ReferralDate = DateTime.Now},
            new Referral(){OffenderID = 3, OfficerID = 1, ReferralID = 5, ReferralDate = DateTime.Now},
            new Referral(){OffenderID = 4, OfficerID = 2, ReferralID = 6, ReferralDate = DateTime.Now}
        };

        var db = new { Offenders, Referrals, Officers };

        var results = db.Referrals
                        .GroupBy(referral => referral.OffenderID)
                        .Where(group => group.Count() > 1)
                        .Join(db.Referrals, group => group.Key, referral => referral.OffenderID, (group, referral) => new { count = group.Count(), referral })
                        .Join(db.Offenders, cr => cr.referral.OffenderID, offender => offender.OffenderID, (cr, offender) => new { cr.count, cr.referral, offender })
                        .Join(db.Officers, cro => cro.referral.OfficerID, officer => officer.OfficerID, (cro, officer) => new { cro.count, cro.referral, cro.offender, officer })
                        .Select(croo => new { croo.referral.ReferralID, OffenderName = croo.offender.Name, OfficerName = croo.officer.Name, croo.referral.ReferralDate });

    }
}