I have a database:
- My query SQL I need to show in my web:
strDateStart = Format(Me.ctlNgayBD.Value, "yyyy/MM/dd")
strDateEnd = Format(Me.ctlNgayKT.Value, "yyyy/MM/dd")
Select Loaive, LoaiXe,
Count(*) As Tongxe,
Sum(Phi) As Tongphi,
phi
From View_XeQuaTramreport As a
Where catruc = " & catruc & " And Convert(varchar(10),NgayCa,111) = '" & strDate & "'
Group by Loaive,Loaixe,phi
Order by Loaive,Loaixe,phi
Select Loaive, LoaiXe,
Count(*) As Tongxe,
Sum(Phi) As Tongphi,
phi
From View_XeQuaTramreport As a
Where Convert(varchar(10),NgayCa,111) >= '" & strDateStart & "'
and Convert(varchar(10),NgayCa,111) <= '" & strDateEnd & "'
Group by Loaive,Loaixe,phi
Order by Loaive,Loaixe,phi"
Image example SQL select:
My controller: Is there a way to use LINQ a instead of string SQL query in controller?
public DongphuocDbContext db = new DongphuocDbContext();
// GET: Report
public ActionResult Index(DateTime? start, DateTime? end )
{
var _context = new DongphuocDbContext();
string mainconn = ConfigurationManager.ConnectionStrings["DongphuocDbContext"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(mainconn);
string sqlquery = "select Loaive,LoaiXe,Count(*) As Tongxe , Sum(Phi) As Tongphi ,Phi" " from [dbo].[View_XeQuaTramReport] As a " "where NgayCa between '" start "' and '" end "'" " Group by Loaive,Loaixe,phi" " Order by Loaive,Loaixe,phi";
SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
sqlconn.Open();
SqlDataAdapter sda = new SqlDataAdapter(sqlcomm);
DataSet ds = new DataSet();
sda.Fill(ds);
List<View_XeQuaTramReport> lc = new List<View_XeQuaTramReport>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
lc.Add(new View_XeQuaTramReport
{
LoaiVe = Convert.ToByte(dr["LoaiVe"]),
LoaiXe = Convert.ToByte(dr["LoaiXe"]),
Phi = Convert.ToDecimal(dr["Phi"])
});
}
sqlconn.Close();
ModelState.Clear();
return View(lc);
}
My view: I want to show 2 tables in roll one view. And add Count"Tongxe" & Sum"TongPhi" but I can't add.
<center>
<p>
@using (Html.BeginForm("Index", "Report", FormMethod.Get))
{
<input type="date" name="start" />
<input type="date" name="end" />
<input type="submit" name="submit" value="Search" />
}
</p>
<table >
<tr>
<th> @Html.DisplayName("LoaiVe") </th>
<th> @Html.DisplayName("LoaiXe") </th>
<th> @Html.DisplayName("Phi") </th>
</tr>
@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>
</tr>
}
</table>
</center>
This is the model configuration of DongphuocDbContext:
modelBuilder.Entity<View_XeQuaTramReport>()
.Property(e => e.Phi)
.HasPrecision(19, 4);
modelBuilder.Entity<View_XeQuaTramReport>()
.Property(e => e.MSNV)
.IsUnicode(false);
modelBuilder.Entity<View_XeQuaTramReport>()
.Property(e => e.maNhanVien)
.IsUnicode(false);
modelBuilder.Entity<View_XeQuaTramReport>()
.Property(e => e.ID)
.IsUnicode(false);
I need to help change string SQL query -> LINQ
I need to add
Count"Tongxe"andsum"TongPhi"
CodePudding user response：
Look in your DongphuocDbContext to see if a View_XeQuaTramReport collection is already defined there. If so, the following may do what you need:
DateTime start = ...
DateTime end = ...
var Result = _context.View_XeQuaTramReport
.Where(item => item.NgayCa >= start && item.NgayCa <= end) // Date arithmetic, not text
.GroupBy(item => new {item.Loaive, item.Loaixe, item.Phi}) // Multi-value key
.Select(grp => new {
Loaive = grp.Key.Loaive,
Loaixe = grp.Key.Loaixe,
Tongxe = grp.Count(),
Tongphi = grp.Sum(item => item.Phi),
Phi = grp.Key.Phi
})
.ToList();
A side note regarding your original query: You should never do date comparisons by converting the dates to text (as in Convert(varchar(10),NgayCa,111)). Instead, learn how to parameterize your queries, passing start and end in as proper DATE/DATETIME/DATETIME2 types and perform direct date-to-date comparisons. This is critical for database efficiency, because if you had an index on NgayCa (you likely should), this index would become useless if you do string comparisons instead of date comparisons.
CodePudding user response：
This looks like SQL Server, and the date values in the images look like datetime values. There's no reason to convert dates to strings, even from the date picker controls. In fact, the current SQL query runs far slower than it should and may even return the wrong results due to the string conversions.
The SQL query should look like this, using the date-typed parameters @from and @to
Select Loaive, LoaiXe,
Count(*) As Tongxe,
Sum(Phi) As Tongphi,
phi
From View_XeQuaTramreport As a
Where NgayCa between @from and @to
Group by Loaive,Loaixe,phi
Order by Loaive,Loaixe,phi
EF Core allows creating an equivalent query with LINQ :
public ActionResult Index(DateTime start, DateTime end )
{
using (var context = new DongphuocDbContext())
{
var results=context.View_XeQuaTramReport
.AsNoTracking()
.Where(r=>r.NgayCa >= start.Date &&
r.NgayCa < end.Date.AddDays(1))
.GroupBy(r=>new{r.Loaive,r.Loaixe,r.phi})
.Select(g=>new View_XeQuaTramReport
{
LoaiVe = g.Key.LoaiVe,
LoaiXe = g.Key.LoaiXe,
Phi = g.Key.Phi,
Tongxe = g.Key.Count(),
Tongphi = g.Sum(r=>r.Phi)
})
.OrderBy(r=>new {r.Loaive,r.Loaixe,r.Phi})
.ToList();
return View(results);
}
}
LINQ has no Between clause. Date comparisons are tricky too, if the database field contains a time component. The Where clause in this query will return all rows with a date value greater or equal than the start date (start.Date) and strictly less than the next day after end (end.Date.AddDays(1)). This way, there's no reason to trim r.NgayCa from its date component, if it has one :
.Where(r=>r.NgayCa >= start.Date && r.NgayCa < end.Date.AddDays(1))
AsNoTracking() is used to tell EF to not track the entities it loads. Normally EF will track all the entities it loads so it can detect any changes and generate modification SQL statements when SaveChanges is called. This takes more memory and slows down the query.
Finally, the DbContext is defined in a using block to ensure it's disposed when it's no longer needed. Otherwise the DbContext and the entities it tracks will remain in memory until the garbage collector runs.
Conditional queries
The question's action uses nullable dates. That's only meaningful if we want the same action to search before, after or between dates. In that case both the SQL and LINQ queries would have to be rewritten. None of these would work with a NULL date: Convert(varchar(10),NgayCa,111) >=NULL, Ngayca>= NULL or Ngayca between NULL and ....
In LINQ, the query can be build dynamically :
public ActionResult Index(DateTime? start, DateTime end )
{
var query=context.View_XeQuaTramReport
.AsNoTracking()
.AsQueryable();
if(startDate.HasValue)
{
query=query.Where(r=>r.NgayCa >= start.Value.Date);
}
if(endDate.HasValue)
{
query=query.Where(r=>r.NgayCa < end.Value.Date.AddDays(1));
}
var results=query.GroupBy(r=>new{r.Loaive,r.Loaixe,r.phi})
.Select(g=>new View_XeQuaTramReport {
LoaiVe = g.Key.LoaiVe,
LoaiXe = g.Key.LoaiXe,
Phi = g.Key.Phi,
Tongxe = g.Key.Count(),
Tongphi = g.Sum(r=>r.Phi)
})
.OrderBy(r=>new {r.Loaive,r.Loaixe,r.Phi})
.ToList();
CodePudding user response：
@Panagiotis Kanavos Something error here
