C# LINQ DateTime 按月查看利润
本文关键字：LINQ DateTime | 更新日期: 2023-09-27 17:55:32
我想从下拉列表中按所选月份显示，该下拉列表将在 GRIDVIEW 中显示附有其各自订单、最终单价、原始成本的食品标题，以及根据累积最终单价和利润计算总销售额
但是，我对数据库中的日期时间有问题，因为我不确定如何转换日期时间以仅获取月份值。
这是我的代码。
。.aspx
<asp:DropDownList ID="DropDownListMonth" runat="server" Width="200px">
<asp:ListItem Value="1">January</asp:ListItem>
<asp:ListItem Value="2">February</asp:ListItem>
<asp:ListItem Value="3">March</asp:ListItem>
<asp:ListItem Value="4">April</asp:ListItem>
<asp:ListItem Value="5">May</asp:ListItem>
<asp:ListItem Value="6">June</asp:ListItem>
<asp:ListItem Value="7">July</asp:ListItem>
<asp:ListItem Value="8">August</asp:ListItem>
<asp:ListItem Value="9">September</asp:ListItem>
<asp:ListItem Value="10">October</asp:ListItem>
<asp:ListItem Value="11">November</asp:ListItem>
<asp:ListItem Value="12">December</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
<asp:Button ID="BtnSearch" runat="server" Text="Search" OnClick="BtnSearch_Click" /></td>
</tr>
</table>
<hr />
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:Label ID="lblShowMonth" runat="server" Text='<%# Eval("month") %>'></asp:Label>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false"
EmptyDataText="No records found">
<Columns>
<asp:TemplateField HeaderStyle-Font-Size="Large" HeaderText="Order Id">
<ItemTemplate>
<asp:Label ID="lblOrderId" runat="server" Text='<%# Eval("orderid") %>'></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" Height="50px" Width="175px"></ItemStyle>
</asp:TemplateField>
<asp:TemplateField HeaderStyle-Font-Size="Large" HeaderText="Food Title">
<ItemTemplate>
<asp:Label ID="lblFoodTitle" runat="server" Text='<%# Eval("foodtitle") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderStyle-Font-Size="Large" HeaderText="Final Unit Price">
<ItemTemplate>
<asp:Label ID="lblFinalUnitPrice" runat="server" Text='<%# Eval("finalunitprice") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderStyle-Font-Size="Large" HeaderText="Raw Cost">
<ItemTemplate>
<asp:Label ID="lblRawCost" runat="server" Text='<%# Eval("rawcost") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<table>
<tr>
<td>
<asp:Label ID="lblSales" runat="server" Text="Total Sales:"></asp:Label>
</td>
<td>
<asp:Label ID="lblTotalSales" runat="server" Text=""></asp:Label>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblProfit" runat="server" Text="Total Profit:"></asp:Label>
</td>
<td>
<asp:Label ID="lblTotalProfit" runat="server" Text=""></asp:Label>
</td>
</tr>
</table>
</ContentTemplate>
</asp:UpdatePanel>
aspx.cs
protected void BtnSearch_Click(object sender, EventArgs e)
{
int month = Convert.ToInt32(DropDownListMonth.SelectedItem.ToString());
double rawCost = 0.0;
double grossProfit = 0.0;
double profit = 0.0;
if (!this.IsPostBack)
{
var db = new FoodOrderingDBContext();
GridView1.DataSource = (from OrderTable in db.OrderTables
join OrderDetail in db.OrderDetails
on OrderTable.OrderID equals OrderDetail.OrderID
join FoodDetail in db.FoodDetails
on OrderDetail.FoodID equals FoodDetail.FoodID
where OrderTable.DeliveredDateTime.Value.Month == month
select new
{
month = OrderTable.OrderedDateTime,
foodtitle = OrderDetail.FoodDetail.FoodTitle,
finalunitprice = OrderDetail.FinalUnitPrice,
quantity = OrderDetail.Quantity,
rawcost = FoodDetail.RawCost,
}).ToList();
var myquery = (from OrderTable in db.OrderTables
join OrderDetail in db.OrderDetails
on OrderTable.OrderID equals OrderDetail.OrderID
join FoodDetail in db.FoodDetails
on OrderDetail.FoodID equals FoodDetail.FoodID
where OrderTable.DeliveredDateTime.Value.Month == month
select new
{
month = OrderTable.OrderedDateTime,
foodtitle = OrderDetail.FoodDetail.FoodTitle,
finalunitprice = OrderDetail.FinalUnitPrice,
quantity = OrderDetail.Quantity,
rawcost = FoodDetail.RawCost,
});
foreach (var item in myquery)
{
rawCost += item.rawcost;
grossProfit += item.finalunitprice;
}
profit = grossProfit - rawCost;
lblTotalSales.Text = grossProfit.ToString();
lblTotalProfit.Text = profit.ToString();
}
}
请协助我:(
数据库中的类型是 DateTime? 。因此，如果您确定该值不为 null，则可以将其转换为 DateTime 并使用其Month值：
var myquery = (from OrderTable in db.OrderTables
join OrderDetail in db.OrderDetails
on OrderTable.OrderID equals OrderDetail.OrderID
join FoodDetail in db.FoodDetails
on OrderDetail.FoodID equals FoodDetail.FoodID
where ((DateTime)(OrderTable.DeliveredDateTime)).Month == month
select new
{
month = OrderTable.OrderedDateTime,
foodtitle = OrderDetail.FoodDetail.FoodTitle,
finalunitprice = OrderDetail.FinalUnitPrice,
quantity = OrderDetail.Quantity,
rawcost = FoodDetail.RawCost,
});