ASP.Net: Order records by CASE statement in Linq

elvisidr...

SOLVED CLOSED ACTIVE ANSWERED
User: elvisidrizi1
Posted: on Jul 23, 2022 06:23 AM
Forum: ASP.Net Web Forms
Answer: 1
Views: 7620

Hello Everyone,

I am trying to create a chart in VB.net through SQL script and I have an issue with ordering the chart by weekday, while in SQL the information comes ordered correctly, in Visual Studio does not come and I think it has to do with this part of the code:

Dim years As List(Of String) = (From p In dt.AsEnumerable()
                                Order By p.Field(Of String)("Day")
                                Select p.Field(Of String)("Day")).Distinct().ToList()
 
If ChartDay.Series.Count() = 1 Then
    ChartDay.Series.Remove(ChartDay.Series(0))
End If
ChartDay.Series.Clear()
 
For Each Departments As String In Department
    'Dim x As List(Of Decimal) = New List(Of Decimal)()
    'Dim y As List(Of Decimal) = New List(Of Decimal)()
 
    Dim x As List(Of String) = New List(Of String)()
    Dim y As List(Of Decimal) = New List(Of Decimal)()
    For Each year As String In years
        x.Add(year)
        y.Add((From p In dt.AsEnumerable()
               Where p.Field(Of String)("User") = Departments And p.Field(Of String)("Day") = year
               Order By p.Field(Of String)("Day")
               Select p.Field(Of Decimal)("Total")).FirstOrDefault())

and this is the SQL script that I have written:

SELECT
         AVG(Price) As Total,
         --'Sum (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) as NOA,'
         FORMAT(Date, 'ddd', 'en-US')  AS 'Day',
         username as 'User'
FROM
        dbo.Expenses
WHERE
    Department = 'Grocery'
    GROUP BY
    --Department,,
    --username,
   FORMAT(Date, 'ddd', 'en-US') ,
   username
   ORDER BY
CASE WHEN FORMAT(Date, 'ddd', 'en-US') = 'Mon' THEN 0
     WHEN FORMAT(Date, 'ddd', 'en-US') = 'Tue' THEN 1
     WHEN FORMAT(Date, 'ddd', 'en-US') = 'Wed' THEN 2
     WHEN FORMAT(Date, 'ddd', 'en-US') = 'Thu' THEN 3
     WHEN FORMAT(Date, 'ddd', 'en-US') = 'Fri' THEN 4
     WHEN FORMAT(Date, 'ddd', 'en-US') = 'Sat' THEN 5
     WHEN FORMAT(Date, 'ddd', 'en-US') = 'Sun' THEN 6
END desc

And this is the result:

33.112272   Sun Elvis
37.551000   Sat Elvis
10.000000   Fri test-1
31.560285   Fri Elvis
39.854615   Thu Elvis
43.727317   Wed Elvis
44.222549   Tue Elvis
38.036363   Mon Elvis

Any help is much appriciated.

Shivam.M

0 2

Reply
ANSWER
User: Shivam.M
Replied: on Jul 23, 2022 08:02 AM
Report

Hi elvisidrizi1,

You need to use OrderBy with ThenBy for specifying the ordering like did in the SQL Case statement.

Please refer below sample.

Note: For this sample i have used temporary DataTable. For more details refer How to create Temporary Table in ASP.Net using C# and VB.Net.

HTML

<asp:GridView ID="gvDetails" runat="server">
</asp:GridView>

NameSpace

using System.Data;

VB.Net

Imports System.Data

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[3] {
                            new DataColumn("Price"),
                            new DataColumn("Day",typeof(string)),
                            new DataColumn("UserName")
        });
        dt.Rows.Add("31.560285", "Fri", "Elvis");
        dt.Rows.Add("39.854615", "Thu", "Elvis");
        dt.Rows.Add("33.112272", "Sun", "Elvis");
        dt.Rows.Add("44.222549", "Tue", "Elvis");
        dt.Rows.Add("37.551000", "Sat", "Elvis");
        dt.Rows.Add("10.000000", "Fri", "test-1");
        dt.Rows.Add("43.727317", "Wed", "Elvis");
        dt.Rows.Add("38.036363", "Mon", "Elvis");
        List<string> result = (from p in dt.AsEnumerable()
                                select p.Field<string>("Day")).Distinct()
                                .OrderBy(x => x == "Mon" ? 0 : 7)
                                .ThenBy(x => x == "Tue" ? 1 : 7)
                                .ThenBy(x => x == "Wed" ? 2 : 7)
                                .ThenBy(x => x == "Thu" ? 3 : 7)
                                .ThenBy(x => x == "Fri" ? 4 : 7)
                                .ThenBy(x => x == "Sat" ? 5 : 7)
                                .ThenBy(x => x == "Sun" ? 6 : 7)
                                .ToList();
        gvDetails.DataSource = result;
        gvDetails.DataBind();

    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim dt As DataTable = New DataTable()
        dt.Columns.AddRange(New DataColumn(2) {
                            New DataColumn("Price"),
                            New DataColumn("Day", GetType(String)),
                            New DataColumn("UserName")})
        dt.Rows.Add("31.560285", "Fri", "Elvis")
        dt.Rows.Add("39.854615", "Thu", "Elvis")
        dt.Rows.Add("33.112272", "Sun", "Elvis")
        dt.Rows.Add("44.222549", "Tue", "Elvis")
        dt.Rows.Add("37.551000", "Sat", "Elvis")
        dt.Rows.Add("10.000000", "Fri", "test-1")
        dt.Rows.Add("43.727317", "Wed", "Elvis")
        dt.Rows.Add("38.036363", "Mon", "Elvis")
        Dim result As List(Of String) = (From p In dt.AsEnumerable()
                                            Select p.Field(Of String)("Day")).Distinct() _
                                        .OrderBy(Function(x) If(x = "Mon", 0, 7)) _
                                        .ThenBy(Function(x) If(x = "Tue", 1, 7)) _
                                        .ThenBy(Function(x) If(x = "Wed", 2, 7)) _
                                        .ThenBy(Function(x) If(x = "Thu", 3, 7)) _
                                        .ThenBy(Function(x) If(x = "Fri", 4, 7)) _
                                        .ThenBy(Function(x) If(x = "Sat", 5, 7)) _
                                        .ThenBy(Function(x) If(x = "Sun", 6, 7)).ToList()
        gvDetails.DataSource = result
        gvDetails.DataBind()
    End If
End Sub

Screenshot

ASP.Net: Order records by CASE statement in Linq

Company

Explore

Follow us

Contact Us