Click here to Skip to main content
15,917,971 members
Please Sign up or sign in to vote.
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

1 solution

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

    }
}
 
Share this answer
 
Comments
Member 11581003 29-May-15 12:46pm    
Thank you very much for the replay. I tried the solution - created public ActionResult OffendersWithMultipleRecordsTest(), put the query, put the return View(results) statement, created a strongly typed view but when I run the OffendersWithMultipleRecordsTest.CSHTML, it is giving the following error -

The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbQuery`1[<>f__AnonymousType7`4[System.Int32,System.String,System.String,System.Nullable`1[System.DateTime]]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1 :-(.
Sascha Lefèvre 29-May-15 13:08pm    
Try with adding a .ToList() at the very end of the query :)
Member 11581003 29-May-15 16:45pm    
Thanks again. I tried both with and without tolist() but no luck. BTW, do you suggest any good online resources for learning ASP.NET MVC and Linq? I usually use Lynda, codeproject and kudvenkat's videos.

With tolist(), I am getting the following error


The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbQuery`1[<>f__AnonymousType7`4[System.Int32,System.String,System.String,System.Nullable`1[System.DateTime]]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[

I found one article at http://www.codeproject.com/Articles/761687/Grouping-Gridview-in-Asp-Net-MVC was able to make it work.

Thanks again
Sascha Lefèvre 29-May-15 20:06pm    
You're welcome!

Have you already seen the (relatively new) "Learning Zones" on the homepage www.codeproject.com here? There are selected articles compiled for topics like ASP.NET/MVC.

Other than that I have no particular recommendations, sorry. I prefer learning the basics from books and then just google for answers to special questions :)

But you could just post this as a separate question here and you probably get some good recommendations for online resources.

Good luck! :)
Member 11581003 29-May-15 20:37pm    
Thanks. I will explore "Learning Zones". I love codeproject.com - it is very resourceful.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900