I Try this SQL query and try to convert this in LINQ but this SQL query show me data with no repetition which is correct where as when I convert this in linq show data with repetition
Select tblRV.ID as ID, tblRV.Owner, tblRV.Regno,
(Select Count(*) as total from tblvv WHERE MID = tblRV.ID and Name <> '')
as total,tblRV.MA, tblRV.MS from tblReg inner join tblRV On tblReg.RID = tblRV.RID
WHERE tblReg.StartDate >= '2016-06-01 00:00:00' AND tblReg.EndDate <= '2016-06-08 23:59:59' and tblReg.Region = 'UK' order by tblRV.Owner
LINQ QUERy
string result = "";
T1 ts = new T1();
var dq = (from ru in ts.tblReg
join rv in ts.tblRV on ru.RID equals rv.RID
where
ru.Region == region
&& ru.StartDate >= fromdate
&& ru.EndDate <= todate
orderby
rv.Owner
select new
{
ID = rv.ID,
owner = rv.Owner,
RegNo = rv.RegNo,
total= ts.tblVV.Where(x => x.MID == rv.ID && x.VName !="").Count(),
MA = rv.MA,
MS = rv.MS,
}).ToList();
DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Owner", typeof(string));
dt.Columns.Add("RegNo", typeof(string));
dt.Columns.Add("total", typeof(string));
dt.Columns.Add("MA", typeof(string));
dt.Columns.Add("MS", typeof(string));
// dt.Rows.Add(ID, ownername, RegNo, total, MileageAccumlation, MaxSpeed);
foreach (var c in dq)
{
dt.Rows.Add(c.ID, c.owner, c.RegNo, c.total, c.MA, c.MS);
}
result = DataSetToJSON(dt);
return result;
rows are different sql return 24 and linq return 100+ rows
If the result shows data with repetition, it might be related to where condition. Try adding StartDate, EndDate, Region as return data in your linq query and check variables value(eg. region, fromdate, todate).
I assume that data with repetition is related to fromdate, todate. As I said above, "Try adding StartDate, EndDate, Region as return data in your linq query and check variables value(eg. region, fromdate, todate).". After that, check the result date.
string result = "";
T1 ts = new T1();
var dq = (from ru in ts.tblReg
join rv in ts.tblRV on ru.RID equals rv.RID
where
ru.Region == region
&& ru.StartDate >= fromdate
&& ru.EndDate <= todate
orderby
rv.Owner
select new
{
ID = rv.ID,
owner = rv.Owner,
RegNo = rv.RegNo,
total= ts.tblVV.Where(x => x.MID == rv.ID && x.VName !="").Count(),
MA = rv.MA,
MS = rv.MS,
StartDate = ru.StartDate,
EndDate = ru.EndDate,
Region = ru.Region
}).ToList();
DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Owner", typeof(string));
dt.Columns.Add("RegNo", typeof(string));
dt.Columns.Add("total", typeof(string));
dt.Columns.Add("MA", typeof(string));
dt.Columns.Add("MS", typeof(string));
dt.Columns.Add("StartDate", typeof(string));
dt.Columns.Add("EndDate", typeof(string));
dt.Columns.Add("Region", typeof(string));
// dt.Rows.Add(ID, ownername, RegNo, total, MileageAccumlation, MaxSpeed);
foreach (var c in dq)
{
dt.Rows.Add(c.ID, c.owner, c.RegNo, c.total, c.MA, c.MS, c.StartDate, c.EndDate, c.Region);
}
result = DataSetToJSON(dt);
return result;
Member
239 Points
1074 Posts
SQL to LINQ query
Jul 23, 2016 09:30 AM|Capricon User|LINK
I Try this SQL query and try to convert this in LINQ but this SQL query show me data with no repetition which is correct where as when I convert this in linq show data with repetition
LINQ QUERy
string result = ""; T1 ts = new T1(); var dq = (from ru in ts.tblReg join rv in ts.tblRV on ru.RID equals rv.RID where ru.Region == region && ru.StartDate >= fromdate && ru.EndDate <= todate orderby rv.Owner select new { ID = rv.ID, owner = rv.Owner, RegNo = rv.RegNo, total= ts.tblVV.Where(x => x.MID == rv.ID && x.VName !="").Count(), MA = rv.MA, MS = rv.MS, }).ToList(); DataTable dt = new DataTable(); dt.Columns.Add("ID", typeof(int)); dt.Columns.Add("Owner", typeof(string)); dt.Columns.Add("RegNo", typeof(string)); dt.Columns.Add("total", typeof(string)); dt.Columns.Add("MA", typeof(string)); dt.Columns.Add("MS", typeof(string)); // dt.Rows.Add(ID, ownername, RegNo, total, MileageAccumlation, MaxSpeed); foreach (var c in dq) { dt.Rows.Add(c.ID, c.owner, c.RegNo, c.total, c.MA, c.MS); } result = DataSetToJSON(dt); return result;rows are different sql return 24 and linq return 100+ rows
why
All-Star
17642 Points
3510 Posts
Re: SQL to LINQ query
Jul 25, 2016 08:15 AM|Chris Zhao|LINK
Hi Bakhtawar,
If the result shows data with repetition, it might be related to where condition. Try adding StartDate, EndDate, Region as return data in your linq query and check variables value(eg. region, fromdate, todate).
Best Regards,
Chris
Member
239 Points
1074 Posts
Re: SQL to LINQ query
Jul 25, 2016 09:33 AM|Capricon User|LINK
how
All-Star
17642 Points
3510 Posts
Re: SQL to LINQ query
Aug 03, 2016 06:29 AM|Chris Zhao|LINK
Hi Bakhtawar,
I assume that data with repetition is related to fromdate, todate. As I said above, "Try adding StartDate, EndDate, Region as return data in your linq query and check variables value(eg. region, fromdate, todate).". After that, check the result date.
string result = ""; T1 ts = new T1(); var dq = (from ru in ts.tblReg join rv in ts.tblRV on ru.RID equals rv.RID where ru.Region == region && ru.StartDate >= fromdate && ru.EndDate <= todate orderby rv.Owner select new { ID = rv.ID, owner = rv.Owner, RegNo = rv.RegNo, total= ts.tblVV.Where(x => x.MID == rv.ID && x.VName !="").Count(), MA = rv.MA, MS = rv.MS, StartDate = ru.StartDate, EndDate = ru.EndDate, Region = ru.Region }).ToList(); DataTable dt = new DataTable(); dt.Columns.Add("ID", typeof(int)); dt.Columns.Add("Owner", typeof(string)); dt.Columns.Add("RegNo", typeof(string)); dt.Columns.Add("total", typeof(string)); dt.Columns.Add("MA", typeof(string)); dt.Columns.Add("MS", typeof(string)); dt.Columns.Add("StartDate", typeof(string)); dt.Columns.Add("EndDate", typeof(string)); dt.Columns.Add("Region", typeof(string)); // dt.Rows.Add(ID, ownername, RegNo, total, MileageAccumlation, MaxSpeed); foreach (var c in dq) { dt.Rows.Add(c.ID, c.owner, c.RegNo, c.total, c.MA, c.MS, c.StartDate, c.EndDate, c.Region); } result = DataSetToJSON(dt); return result;Best Regards,
Chris