如何使用linq显示多个记录 [英] How to display multiple records using linq

问题描述

我有一个存储推荐详细信息的表 - 它包括推荐类型，推荐日期，OffenderID，OfficerID等字段。显然，一些违规者被多次推荐（多次），同样，OfficerID也在表中重复同一名警员推荐了很多罪犯。



我想做的是，我希望有一个查询显示（通过一个视图）所有犯罪者多次或多次被提及。



我能够通过查询编写一个组来获取罪犯ID和计数（基本上是Key和Count）但是我无法显示那些记录在推荐表中重复。

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.

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

现在变量结果将是有这样的东西

重点计数

--- -----

A10 3

L05 2



（A10和L05 - OffenderIDs）



我想将结果显示为foll视图中的ows（样本数据）

ReferralID罪犯姓名官员姓名推荐日期

---------- --------- ---- ------------ ------------------ ----

001亚当汤姆1 / 1/1414

015亚当埃里克2/5/2014

200亚当汤姆2014年6月6日

009露西戴夫2/2 / 2011

400 Lucy Kate 2015年2月2日



我也试过

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

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

但它给视图中的每个循环错误。





我对ASP.NET MVC，Linq，C＃太新了 - 任何帮助都是真的很感激。非常感谢。

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.

推荐答案

为了给你一个完整的查询，我必须知道你的罪犯和官员表/实体的样子。所以我会在这里做一个很好的猜测并说明我包含我编写的所有代码来测试它。你显然可以丢弃除底部查询之外的所有内容。我假设您将能够对其进行修改以使其正常工作，否则请发表评论:)



为清楚起见，我选择了富有表现力的长名称查询所以它可能看起来有点乱，因为换行 - 只需将它复制到Visual Studio中的某个地方:)

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

    }
}

这篇关于如何使用linq显示多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！