none
Error in OrderBy, is there a better way to do this? RRS feed

  • Question

  • I'm rather new to LINQ so here goes.

    My code with names changed to protect the guilty:


                    var row= (from p in m_InstanceDataContext.TableA
                                                 where p.DocID == m_Document.DocID
                                                 orderby (
                                                           a => Math.Abs((p.LabSystemDate - m_Document.FileDate).Ticks)
                                                         )
                                                 select p).Single();

    The compiler gives me this error: The type of one of the expressions in the orderby clause is incorrect.  Type inference failed in the call to 'OrderBy'.


    Any thoughts?

    Does anyone know of a better way to approach this.   I'm trying to find the related record using non-unquie pieces of data.  The DocID is reused ever few years so I need to use a combination of DocID and the Date to find the related record.  I thought  using the difference in the two dates and the absolute value would get me what I need.

    In fact I've figured out the code in T-SQL:

        select top 1 * from TableA
        where DocID = @docid
        order by ABS(DateDiff(day,LabSystemDate,@fileDate)) asc

    I guess the bottom line is I'm trying to translate this code to LINQ or find a better way overall.

    Let me know what you think....

    Thanks!
    Paul


    Friday, May 23, 2008 11:22 PM

Answers

  • Of course, you can use it as following :

     

    Code Snippet
    var row= (from p in m_InstanceDataContext.TableA
                                                 where p.DocID == m_Document.DocID
                                                 orderby 
                                                          

    System.Data.Linq.SqlClient.SqlMethods.DateDiffDay

    (p.LabSystemDate, m_Document.FileDate)
                                                         
                                                 select p).First();

     

     

     

    Monday, May 26, 2008 1:02 PM

All replies

  • Hi PaulW,


    This is the Linq equivalent to the TSQL that you have mentioned:

     

    Code Snippet
    var row= (from p in m_InstanceDataContext.TableA
                                                 where p.DocID == m_Document.DocID
                                                 orderby 
                                                           Math.Abs((p.LabSystemDate - m_Document.FileDate).Ticks)
                                                         
                                                 select p).First();

     

     

    Notice that you don't write the lambda to orderby clause and to get Top 1 you need to use First() method instead of Single() since Single() will throw an error if duplicates are found.

     

    As a matter of a better way, it seemed to me that calculation you are doing( math & abs) seems potentially irrelevant; I dont know what @filedate stands for but you can either compare if it is equal to a date you have (in addition to docId) or Eliminate Math.Abs and use "OrderBy SomeDate" instead of doing Math.Abs .

     

    Hope this helps.

    Saturday, May 24, 2008 1:18 AM
  • In addition to what Sidar mentions, you may consider using the SqlMethods.DateDiffDay() function to obtain the difference between the two dates.

    Thanks,
    Diego

    Sunday, May 25, 2008 11:33 PM
  • I can use SqlMethods.DateDiffDay()  in the order by then in my LINQ statement?

     

    Thanks,

    Paul

    Monday, May 26, 2008 4:03 AM
  • Of course, you can use it as following :

     

    Code Snippet
    var row= (from p in m_InstanceDataContext.TableA
                                                 where p.DocID == m_Document.DocID
                                                 orderby 
                                                          

    System.Data.Linq.SqlClient.SqlMethods.DateDiffDay

    (p.LabSystemDate, m_Document.FileDate)
                                                         
                                                 select p).First();

     

     

     

    Monday, May 26, 2008 1:02 PM