locked
ASP.net MVC - Grouping data results by GameID in one <tr> row RRS feed

  • Question

  • Greetings!

    using VS2015, asp.net MVC. I have a stored procedure that displays a list of NFL matchups, like so:


    Notice each matchup will have the same GameID. I'd like one row on my web page layout to contain both teams in the matchup instead of each team having its own row. 

    This code was auto generated when I added an ADO.net Entity Data Model:

    namespace GamblersDen
    {
        using System;
        using System.Data.Entity;
        using System.Data.Entity.Infrastructure;
        using System.Data.Entity.Core.Objects;
        using System.Linq;
        
        public partial class TrinoviEntities : DbContext
        {
            public TrinoviEntities()
                : base("name=TrinoviEntities")
            {
            }
    
            public virtual ObjectResult<usp_GetMatchups_Result> usp_GetMatchups()
            {
                return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<usp_GetMatchups_Result>("usp_GetMatchups");
            }
        }
    }

    In my .context.cs, I have:

        public partial class usp_GetMatchups_Result
        {
            public int MatchupKey { get; set; }
            public int fkSportKey { get; set; }
    
            [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy}")]
            public System.DateTime Date { get; set; }
            public int GameID { get; set; }
            public string TeamAbbrev { get; set; }
            public string HomeAway { get; set; }
            public Nullable<decimal> Spread { get; set; }
            public Nullable<decimal> SpreadPct { get; set; }
            public Nullable<decimal> Total { get; set; }
            public Nullable<decimal> TotalPct { get; set; }
            public string WeatherForecast { get; set; }
            public Nullable<int> IsCurrentWeek { get; set; }
            public Nullable<int> Final { get; set; }
            public string Pick { get; set; }
    }

    And in my controller I have: 

    namespace GamblersDen.Controllers { public class NFLMatchupController : Controller { TrinoviEntities db = new TrinoviEntities(); // GET: NFLMatchup public ActionResult Index() { return View(db.usp_GetMatchups()); } }

    My view, added from controller using right click > Add View and also selecting the List as a template: 

    @model IEnumerable<GamblersDen.usp_GetMatchups_Result>
    
    @{
        ViewBag.Title = "This Week's NFL Matchups";
    }
    
    
    <div class="box-body">
        <table id="example2" class="table table-bordered table-hover">
            <thead>
                <tr>
                    <th>
                        @Html.DisplayNameFor(model => model.Date)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.TeamAbbrev)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.Spread)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.Total)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.WeatherForecast)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.Final)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.Pick)
                    </th>
                </tr>
                <tbody>
                @foreach (var item in Model)
                {
                    <tr>
                        <td>
                            @Html.DisplayFor(modelItem => item.Date)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.TeamAbbrev)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.Spread)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.Total)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.WeatherForecast)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.Final)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.Pick)
                        </td>
                    </tr>
                }
                </tbody>
            </thead>
        </table>
        </div>

    Which will successfully create the list, which I'm happy about:


    The GameID isn't shown because it's not useful to the user, however that's the column i'd like each matchup to group on. I was hoping to do it without a group header and have each team's data line separated in the cell. More like this:


    I realize there's dup data in some cells, which I can address in the stored procedure so I'm not worried about that. But I'm very new to c# programming and MVC, so I apologize in advance. If I've missed any code you'd need to see let me know.

    Thanks all


    • Edited by KenVilla Wednesday, September 20, 2017 5:50 AM
    Friday, September 15, 2017 8:11 PM

Answers

  • hello kenVilla ,

    the proper way of doing this is to change the query in the stored procedure. You should do a group by and do stringconcat on the teams

    other option would be to use linq, use a group by on usp_GetMatchups_Result

    https://docs.microsoft.com/en-us/dotnet/csharp/linq/group-query-results

    https://msdn.microsoft.com/en-us/library/bb534304(v=vs.110).aspx

    I prefer to use  group by's in my query's and not in the code. 

    kind regards. 

    Laurens


    Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.







    • Marked as answer by KenVilla Friday, September 15, 2017 9:05 PM
    • Edited by laurens vdb Friday, September 15, 2017 9:17 PM
    Friday, September 15, 2017 8:26 PM

All replies

  • hello kenVilla ,

    the proper way of doing this is to change the query in the stored procedure. You should do a group by and do stringconcat on the teams

    other option would be to use linq, use a group by on usp_GetMatchups_Result

    https://docs.microsoft.com/en-us/dotnet/csharp/linq/group-query-results

    https://msdn.microsoft.com/en-us/library/bb534304(v=vs.110).aspx

    I prefer to use  group by's in my query's and not in the code. 

    kind regards. 

    Laurens


    Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.







    • Marked as answer by KenVilla Friday, September 15, 2017 9:05 PM
    • Edited by laurens vdb Friday, September 15, 2017 9:17 PM
    Friday, September 15, 2017 8:26 PM
  • Ah yes. I suppose I could do this in SQL, thanks. I might be back though because I think I will need some styling around the data elements and I wasn't sure if doing so in sql would sort of limit me when it comes to styling. 

    Much appreciated for your help!

    Friday, September 15, 2017 9:08 PM
  • hello kenVilla ,

    the proper way of doing this is to change the query in the stored procedure. You should do a group by and do stringconcat on the teams

    other option would be to use linq, use a group by on usp_GetMatchups_Result

    https://docs.microsoft.com/en-us/dotnet/csharp/linq/group-query-results

    https://msdn.microsoft.com/en-us/library/bb534304(v=vs.110).aspx

    I prefer to use  group by's in my query's and not in the code. 

    kind regards. 

    Laurens


    Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.







    OK, so I modified my stored procedure make each matchup contained in a row. I can also confirm in the stored proc's output that this is happening. However, when it displays on my page, some of the columns will do the line feed, but other columns will not. To explain, my code below has been simplified since there are a lot of columns:

    Stored procedure runs this:

    	SELECT
    		  t1.GameID
    		  ,t1.Date
    		  ,STUFF(
    				 (  SELECT CHAR(10) + CONVERT(nvarchar(20),t2.TeamAbbrev)
    					FROM Matchups t2
    					WHERE t2.GameID = t1.GameID
    					  --AND t2.Name = t1.Name
    					FOR XML PATH ('')
    				  )
    				  ,1
    				  ,1
    				  ,''
    				  ) AS TeamAbbrev
    			,STUFF(
    				 (  SELECT CHAR(10) + CONVERT(nvarchar(20),t2.PassPct)
    					FROM Matchups t2
    					WHERE t2.GameID = t1.GameID
    					  --AND t2.Name = t1.Name
    					FOR XML PATH ('')
    				  )
    				  ,1
    				  ,1
    				  ,''
    				  ) AS PassPct
    			,STUFF(
    				 (  SELECT CHAR(10) + CONVERT(nvarchar(20),t2.PassYds)
    					FROM Matchups t2
    					WHERE t2.GameID = t1.GameID
    					  --AND t2.Name = t1.Name
    					FOR XML PATH ('')
    				  )
    				  ,1
    				  ,1
    				  ,''
    				  ) AS PassYds
    FROM Matchups t1
    GROUP BY 
    		 t1.Date
    		,t1.GameID

    However I had to change my .cs file so that they're all strings:

    namespace GamblersDen
    {
        using System;
        
        public partial class usp_GetMatchups_Result
        {
            public int GameID { get; set; }
            public System.DateTime Date { get; set; }
            public string TeamAbbrev { get; set; }
            public string PassPct { get; set; }
            public string PassYds { get; set; }
        }
    }

    So when it displays in the page, a row will look something like this:


    So you see for example the "TeamAbbrev" will not reflect the line feed, but on "PassPct, PassYds, etc, it will. Not sure what all else you would like to see in the code, but I'm happy to provide. I'm also in the process of deploying what I have now to the web. 

    So how can I get the line feeds to be consistent for each column? 

    Thanks again!


    • Edited by KenVilla Wednesday, September 20, 2017 3:44 AM
    Tuesday, September 19, 2017 11:51 PM
  • But I'm very new to c# programming and MVC, so I apologize in advance. If I've missed any code you'd need to see let me know.

    Well first off, you are breaking SoC and MVC principles. 

    https://en.wikipedia.org/wiki/Separation_of_concerns

    http://www.c-sharpcorner.com/UploadFile/56fb14/understanding-separation-of-concern-and-Asp-Net-mvc/

    You are eating or have seen some MS ASP.NET MVC teaching cheesecake where MS and its lovely wizards are doing things for you instead of you knowing how to do it.

    Anyway, you should be posting to the MVC forum at ASP.NET.

    http://forums.asp.net/

    Wednesday, September 20, 2017 2:55 AM
  • But I'm very new to c# programming and MVC, so I apologize in advance. If I've missed any code you'd need to see let me know.

    Well first off, you are breaking SoC and MVC principles. 

    https://en.wikipedia.org/wiki/Separation_of_concerns

    http://www.c-sharpcorner.com/UploadFile/56fb14/understanding-separation-of-concern-and-Asp-Net-mvc/

    You are eating or have seen some MS ASP.NET MVC teaching cheesecake where MS and its lovely wizards are doing things for you instead of you knowing how to do it.

    Anyway, you should be posting to the MVC forum at ASP.NET.

    http://forums.asp.net/

    I am indeed eating some wizard cheesecake. Thanks, I will take a look at the links.
    Wednesday, September 20, 2017 3:55 AM