SUPER_USER SUPER_USER - 4 months ago 24
SQL Question

convert SQL to LINQ query

i want to convert this query into LINQ or with lambda expression

ALTER procedure [dbo].[grid_data]
@fromdate datetime,
@todate datetime,
@region varchar(50)
as
Select D.ID as ID, D.OName,
(Select Count(*) from tblve WHERE MID = D.ID and Vme <> ''),
D.Mil,D.Speed
from tblRe M
inner join tblReg D
On M.RID = D.RID


I try this method but this show error on count

[WebMethod]
public static string search_data(DateTime fromdate, DateTime todate, string region)
{
try
{
T1 ts = new T1();
var query = (from M in ts.tblRe
join D in ts.tblReg on M.RID equals D.RID
where
M.Region == region
&& M.StartDate <= fromdate
&& M.EndDate >= todate
select new {
D.ID,
D.OName,
Count = ts.tblve.Where(x => x.MID == D.ID && x.Vme !=
'').Count()
D.Mil,
D.Speed
}).ToList();


THIS SHOWS ERROR
Empty character literal
on this line
'')

Answer

What about this solution:

  var query = from M in context.tblRe 
              join D in context.tblReg on M.RID equals D.RID
              where M.StartDate <= fromdate && M.EndDate >= todate && M.region == region 
              select new {                      
                  D.ID,
                  D.OName,
                  Count = context.tblve.Where(x => x.MID == D.ID && x.Vme != "").Count()
                  D.Mil,
                  D.Speed
              };
Comments