none
LINQ and SQL query RRS feed

  • Question

  • I try this SQL query and this query display data

    SELECT distinct
    tblvv.ID,
    oname,
    regno
    from tblvv
    join tblrv on rv.ID=tblvv.ID
    join tblre on re.RID=rv.RID
    WHERE Region = 'UK' AND
    StartDate >= '2015-06-25 00:00:00.000' AND
    EndDate <= '2015-06-30 23:59:59.000'
    group by
    tblvv.ID,
    oname,
    regno
     protected void search_data_Click(object sender, EventArgs e)
            {
               
                try
                {
                    string fdate = fromdate.Value.Trim().Split('T')[0];
                    string tdate = todate.Value.Trim().Split('T')[0];
                    T1 ts = new T1();
                    var list1 = ts.tblvv.ToList(); 
                    var dq = (from vv in list1
                              join rv in ts.tblrv on vv.ID equals rv.ID
                              join re in ts.tblre on rv.RID equals re.RID
                              where
                              re.RID == Convert.ToInt32(regiondrop.SelectedValue)
                              && (re.StartDate == Convert.ToDateTime(fdate) && re.EndDate == Convert.ToDateTime(tdate))
                            
                              orderby rv.OName
                              select new
                              {
                                  ID = vv.ID,
                                  oname=rv.Oname,
                                  RegNo = rv.RegNo
    
                              }).ToList();
                    GridView1.DataSource = dq;
                    GridView1.DataBind();
    
                }
                catch (Exception)
                {
                    GridView1.Visible = false;
                    Label4.Text = ("No Data");
    
                }
    
            }

    and above is the linq query

    so when i execute SQL query this display data but when i run this LINQ query then i  select region ,dates and click on button then data is not display in gridview ?

    any solution?

    Saturday, June 25, 2016 5:37 AM

Answers

  • Hi rebmaamberrebmaamber,

    >>so when i execute SQL query this display data but when i run this LINQ query then i  select region ,dates and click on button then data is not display in gridview ?

    According to your description, it seems that the condition of LINQ and SQL statement are not the same, please modify your LINQ query like this:

    protected void search_data_Click(object sender, EventArgs e)
            {
               
                try
                {
                    string fdate = fromdate.Value.Trim().Split('T')[0];
                    string tdate = todate.Value.Trim().Split('T')[0];
                    T1 ts = new T1();
                    var list1 = ts.tblvv.ToList(); 
                    var dq = (from vv in list1
                              join rv in ts.tblrv on vv.ID equals rv.ID
                              join re in ts.tblre on rv.RID equals re.RID
                              where
                              vv.Region == "UK"  && (re.StartDate >= Convert.ToDateTime(fdate) && re.EndDate <= Convert.ToDateTime(tdate))                  
                              orderby rv.OName
                              select new
                              {
                                  ID = vv.ID,
                                  oname=rv.Oname,
                                  RegNo = rv.RegNo
    
                              }).ToList();
                    GridView1.DataSource = dq;
                    GridView1.DataBind();
    
                }
                catch (Exception)
                {
                    GridView1.Visible = false;
                    Label4.Text = ("No Data");
    
                }
    
            }

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, June 27, 2016 8:21 AM
    Moderator

All replies

  • I would say use Linqpad first (free) and play with it there before you comeback to the C# code.

    https://www.linqpad.net/

    Saturday, June 25, 2016 6:27 AM
  • Maybe you should use “>=” and “<=” instead of “==”.

    Then consider Distinct before ToList, and check if ‘group ... by’ is needed in your LINQ expression.


    • Edited by Viorel_MVP Saturday, June 25, 2016 7:41 AM
    Saturday, June 25, 2016 7:39 AM
  • i try but they said you must charge first
    Saturday, June 25, 2016 7:57 AM
  • i try but they said you must charge first
    https://www.linqpad.net/Download.aspx
    Saturday, June 25, 2016 8:57 AM
  • Hi rebmaamberrebmaamber,

    >>so when i execute SQL query this display data but when i run this LINQ query then i  select region ,dates and click on button then data is not display in gridview ?

    According to your description, it seems that the condition of LINQ and SQL statement are not the same, please modify your LINQ query like this:

    protected void search_data_Click(object sender, EventArgs e)
            {
               
                try
                {
                    string fdate = fromdate.Value.Trim().Split('T')[0];
                    string tdate = todate.Value.Trim().Split('T')[0];
                    T1 ts = new T1();
                    var list1 = ts.tblvv.ToList(); 
                    var dq = (from vv in list1
                              join rv in ts.tblrv on vv.ID equals rv.ID
                              join re in ts.tblre on rv.RID equals re.RID
                              where
                              vv.Region == "UK"  && (re.StartDate >= Convert.ToDateTime(fdate) && re.EndDate <= Convert.ToDateTime(tdate))                  
                              orderby rv.OName
                              select new
                              {
                                  ID = vv.ID,
                                  oname=rv.Oname,
                                  RegNo = rv.RegNo
    
                              }).ToList();
                    GridView1.DataSource = dq;
                    GridView1.DataBind();
    
                }
                catch (Exception)
                {
                    GridView1.Visible = false;
                    Label4.Text = ("No Data");
    
                }
    
            }

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, June 27, 2016 8:21 AM
    Moderator