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: 7187

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.

Download FREE API for Word, Excel and PDF in ASP.Net

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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

ASP.Net: Order records by CASE statement in Linq

Company

Explore

Follow us

Contact Us