I have search date time fromdate - todate. I need to add dropdownlist "Catruc" but i dont know how to do at Linq.

enter image description here

Database select query "Catruc"

enter image description here

enter image description here

View:

enter image description here

Controller: how can i add "Catruc" into "where"???

 public DongphuocDbContext db = new DongphuocDbContext();

        // GET: Report
        public ActionResult Index(DateTime? start, DateTime? end)
        {
            var query = db.View_XeQuaTramReport;
            if (start == null || end == null)
                {
                return View();
            }
            DateTime strStart = start.Value.Date;
            DateTime strEnd = end.Value.Date;
            var result = db.View_XeQuaTramReport
                .Where(r => r.NgayCa >= strStart && r.NgayCa <= strEnd)
                .GroupBy(r => new { r.LoaiVe, r.LoaiXe, r.Phi })
                .Select(g => new XeQuaTramDTO
                {
                    LoaiVe = g.Key.LoaiVe,
                    LoaiXe = g.Key.LoaiXe,
                    Phi = g.Key.Phi,
                    TongPhi = g.Sum(r => r.Phi),
                    TongXe = g.Count()
                })
                .OrderBy(r => new { r.LoaiVe, r.LoaiXe, r.Phi, r.TongPhi, r.TongXe })
                .ToList();

            return View(result);
        }

    }

CodePudding user response：

According to your query results, this should work:

public ActionResult Index(DateTime? start, DateTime? end)
        {
            var query = db.View_XeQuaTramReport;
            if (start == null || end == null)
                {
                return View();
            }
            DateTime strStart = start.Value.Date;
            DateTime strEnd = end.Value.Date;
            var result = db.View_XeQuaTramReport
                .Where(r => r.NgayCa >= strStart && r.NgayCa <= strEnd && r.CaTruc == 1)
                .GroupBy(r => new { r.LoaiVe, r.LoaiXe, r.Phi })
                .Select(g => new XeQuaTramDTO
                {
                    LoaiVe = g.Key.LoaiVe,
                    LoaiXe = g.Key.LoaiXe,
                    Phi = g.Key.Phi,
                    TongPhi = g.Sum(r => r.Phi),
                    TongXe = g.Count()
                })
                .OrderBy(r => new { r.LoaiVe, r.LoaiXe, r.Phi, r.TongPhi, r.TongXe })
                .ToList();

            return View(result);
        }

If you also want to include the value in your select, you need to add it to the groupby:

public ActionResult Index(DateTime? start, DateTime? end)
            {
                var query = db.View_XeQuaTramReport;
                if (start == null || end == null)
                    {
                    return View();
                }
                DateTime strStart = start.Value.Date;
                DateTime strEnd = end.Value.Date;
                var result = db.View_XeQuaTramReport
                    .Where(r => r.NgayCa >= strStart && r.NgayCa <= strEnd && r.CaTruc == 1)
                    .GroupBy(r => new { r.CaTruc, r.LoaiVe, r.LoaiXe, r.Phi })
                    .Select(g => new XeQuaTramDTO
                    {
                        CaTruc = g.Key.Catruc,
                        LoaiVe = g.Key.LoaiVe,
                        LoaiXe = g.Key.LoaiXe,
                        Phi = g.Key.Phi,
                        TongPhi = g.Sum(r => r.Phi),
                        TongXe = g.Count()
                    })
                    .OrderBy(r => new { r.LoaiVe, r.LoaiXe, r.Phi, r.TongPhi, r.TongXe })
                    .ToList();
    
                return View(result);
            }

CodePudding user response：

View:

 <center>
<p>
    @using (Html.BeginForm("Index", "Report", FormMethod.Get))
    {
    <div ><form method="post"> <div >
                <label>Ca trực</label>
                <select  id="nhanvien">
                </select></div> </form>
        <input type="date" name="start" />
        <input type="date" name="end" />
        <input type="submit" name="submit" value="Search" />
    </div>
       
    }
</p>
<table >
    <tr>
        <th>LoaiVe </th>
        <th> Loaixe</th>
        <th>Phi </th>
        <th>Tong phi </th>
        <th>Tong xe </th>
    </tr>
    @if (Model != null)
    {
        if (Model.Count > 0)
        {
            foreach (var item in Model)
            {
                <tr>
                    <td> @Html.DisplayFor(modelItem => item.LoaiVe)</td>
                    <td> @Html.DisplayFor(modelItem => item.LoaiXe)</td>
                    <td> @Html.DisplayFor(modelItem => item.Phi)</td>
                    <td> @Html.DisplayFor(modelItem => item.TongPhi)</td>
                    <td> @Html.DisplayFor(modelItem => item.TongXe)</td>
                </tr>
            }
        }

    }
</table>