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