c# - Display this SQL into MVC View -

c# - Display this SQL into MVC View -

i have mvc 5 entity framework 6. i've been trying figure out using linq razor view , i'm not getting results.

how t-sql

select rt.name, count(r.rtypeid) rtypid dbo.request r bring together dbo.rtype rt on r.rtypeid = rt.rtypeid grouping rt.name, r.rtypeid

that returns these results...

name rtypeid alter request 42 new request 386 re-run 28 other 1

here 2 models created rt

namespace dbmr.models { using system; using system.collections.generic; using system.componentmodel.dataannotations; using system.linq; public partial class rtype { public rtype() { this.requests = new hashset<request>(); this.requestinputs = new hashset<requestinput>(); } public int? rtypeid { get; set; } [display(name = "request type:")] public string name { get; set; } public nullable<bool> isselected { get; set; } public virtual icollection<request> requests { get; set; } public virtual icollection<requestinput> requestinputs { get; set; } } }

for r

namespace dbmr.models { using system; using system.collections.generic; using system.componentmodel.dataannotations; using system.linq; public partial class request { public int requestid { get; set; } public string jobid { get; set; } public string title { get; set; } public int statusid { get; set; } public int? rtypeid { get; set; } public int urgencyid { get; set; } public int categoryid { get; set; } public int dtypeid { get; set; } public int resolutionid { get; set; } public int analystid1 { get; set; } [datatype(datatype.date)] public int analystid2 { get; set; } public nullable<system.datetime> opendt { get; set; } [datatype(datatype.date)] public nullable<system.datetime> closedt { get; set; } public int analystid3 { get; set; } public virtual analyst analyst { get; set; } public virtual analyst analyst1 { get; set; } public virtual analyst analyst2 { get; set; } public virtual category category { get; set; } public virtual dtype dtype { get; set; } public virtual resolution resolution { get; set; } public virtual rtype rtype { get; set; } public virtual status status { get; set; } public virtual urgency urgency { get; set; } } }

just trying create page in mvc app displays summary table.

what best way accomplish this?

controller:

//create context using(var ctx = new mydbcontext()) { var items = ctx.requests .groupby(r => new { r.rtype.name, r.rtypeid }) .select(r => new myrequestdata { name = r.rtype.name, count = r.count() }); homecoming view(items); }

class hold data

public class myrequestdata { public string name { get; set; } public int count { get; set; } }

view:

<table> <tr> <th> heading 1 </th> <th> heading 2</th> </tr> @foreach(var item in model) { <tr> <td>@item.name</td> <td>@item.count</td></r> } </table>

c# entity-framework model-view-controller