none
help with my linq to sql query RRS feed

  • Question

  •       double lag = -120;
          string latency = DateTime.UtcNow.AddSeconds(lag).ToString("yyyy-MM-dd HH:mm:ss.000");
          string connectionstring = "******************* ";
                
          try
          {
            rangenetDataContext db = new rangenetDataContext(connectionstring);
    
            var q = from c in db.SLMLives
                where c.Timestamp = latency
                select new
                {
                  timestamp = c.Timestamp.ToString(),
                  deviceid = c.DeviceID.ToString(),
                  value = c.Value.ToString()
                };
            foreach (var x in q)
            {
              textBox4.Text += x.deviceid + x.value + x.timestamp;
            }
            dataGridView1.DataSource = q;
    
                
            
          }
          catch
          {
            textBox4.Text = "Connection Issue";
          }
        }
    

    here is the code im using to select records where the timestamp in utc was 2 minutes ago, BUT.

    I can't get any results from the query because the datetime format is a little wonky in the DB.

    Can someone point me in the right direction about how to fix this issue. right now, the code where c.Timestamp = latency won't work because latency is string type

    Friday, July 8, 2011 12:52 PM

All replies

  • Look into finding out what kind of datetime it is from the database vs the local value and do a Datetime.Compare on it.
    William Wegerson (www.OmegaCoder.Com)
    Friday, July 8, 2011 1:38 PM
    Moderator
  • the date and time format is datetime in the db.

    i'm having trouble using linq to find the time in utc just a little before the system time.

    the format is yyyy:MM:dd HH:mm:ss

    i can't figure out how to get the string back into datetime for linq to query with.

    i just keep getting errors about can't convert bool to datetime or datetime to string.


    all i need really from linq is to find the max(timestamp)
    Friday, July 8, 2011 4:35 PM
  • Hi PSLinux,

     

    Welcome to the fourm.

    When the Linq convert to SQL, it will be deal with the TimeStamp.

    Such as:

    The time '2011,11,07 14:37:35 742' will convert as string "2011,11,07 14:37:35" in the application. TimeSpan.ToString();

    But when it convert to sql by Linq:

    My sample:

    from p in products

    where p.createtime.ToString()== SelectTime

    select p;

    Convert to sql:

    select * from product

    where (convert(nvarchar(max),createtime))=@p0;

    So we can't get the results.

     

    I suggest you can use the timecompare.

    as:

    var time=DateTime.Now;

    var time2=time.AddSeconds(2);

    var result=from p in products

                    where p.createTime>time && p.createTime<time2

                    select p;

     

    I hope that would be help you.

     

     

    Thanks to all the participators.


    If it's helpful for you, Please vote or mark. Thank you!

    David Peng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, July 11, 2011 7:32 AM
    Moderator