locked
Use Linq to get records more than N days (DateDiffDay) RRS feed

  • Question

  • Here is the code

    'retrieve any late records
                Dim open = From late In coidb.COI_Headers
                           Where late.Active = True And late.COI_Status = 1 And SqlMethods.DateDiffDay(late.CreatedDate, Now()) > 6
                           Select New openCOI With {
                               .id = late.COI_ID,
                               .userName = late.User_name,
                               .email = late.User_email,
                               .begindate = late.CreatedDate,
                               .daysover = SqlMethods.DateDiffDay(late.CreatedDate, Now())
                               }
                lateList.Add(open)

    When I remote out the SQLMETHOD, I get a slew of return records, however I only want the ones who are 'stale-dated' out 7 or more days.  There is a CreatedDate in the database table.  I want to take that date, compare it to today, and return the integer datediff.  Is there some syntax error here I am not seeing?

    Thanks


    Brad Allison

    Tuesday, November 19, 2019 7:37 PM

Answers

  • Is the code executing? If the code is executing then, it can't be a syntax error.

    Linq is not a panacea solution. What database technology is being used and is an ORM like Entity Framework being used? 

    • Marked as answer by guyinkalamazoo3 Wednesday, November 20, 2019 12:50 PM
    Tuesday, November 19, 2019 8:34 PM
  • Hi,

    Could you share us a screen shot about your current result or an error message?

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by guyinkalamazoo3 Wednesday, November 20, 2019 12:50 PM
    Wednesday, November 20, 2019 6:05 AM

All replies

  • Is the code executing? If the code is executing then, it can't be a syntax error.

    Linq is not a panacea solution. What database technology is being used and is an ORM like Entity Framework being used? 

    • Marked as answer by guyinkalamazoo3 Wednesday, November 20, 2019 12:50 PM
    Tuesday, November 19, 2019 8:34 PM
  • Hi,

    Could you share us a screen shot about your current result or an error message?

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by guyinkalamazoo3 Wednesday, November 20, 2019 12:50 PM
    Wednesday, November 20, 2019 6:05 AM
  • I just tried using the standard DATEDIFF and get this


    Brad Allison

    Wednesday, November 20, 2019 11:44 AM
  • Using the SqlMethods.DateDiffDay as shown in my original message


    Brad Allison

    Wednesday, November 20, 2019 11:47 AM
  • I am using SQL Server back end and perhaps my logic is wrong.  Syntaxually (is that word?) the program itself does not err out.  Maybe instead of doing it here in the program, I can write a stored proc to get records who are open and greater than 6 days out.  Can I run a stored proc from linq?

    Brad Allison

    Wednesday, November 20, 2019 11:50 AM
  • I am using SQL Server back end and perhaps my logic is wrong.  Syntaxually (is that word?) the program itself does not err out.  Maybe instead of doing it here in the program, I can write a stored proc to get records who are open and greater than 6 days out.  Can I run a stored proc from linq?

    Brad Allison

    You still have not indicated what database technology Linq is using  to access the database. The error message is quite clear as to the problem of you creating a Linq query against the database where the engine of the technology using Linq cannot generate the T-SQL the Linq query is requesting to be submitted to the DB engine for execution, which I am going to assume is Linq-2-SQL or the Entity Framework.

    The SQLMethods() or DateDiff() is not viable in the Linq query and T-SQL cannot be generated because of it.

    You using a sproc will depend upon what technology you are using to access the database, which you have not indicated what you are using. 

    And no, Linq itself cannot use a sproc just like it cannot use the DateDiff(). However,  what technology is hosting Linq to access the MS SQL Server database tables could possibly use a sproc. 

     




    • Edited by DA924x Wednesday, November 20, 2019 12:56 PM
    Wednesday, November 20, 2019 12:40 PM
  • And an update:  It now works.  My test data was flawed and had the created dates set to a future day and not a past date.  Resetting those dates gets me the data I need.

    Brad Allison

    Wednesday, November 20, 2019 12:50 PM
  • And an update:  It now works.  My test data was flawed and had the created dates set to a future day and not a past date.  Resetting those dates gets me the data I need.

    Brad Allison

    Is it impossible to explain what technology is hosting Linq that you are using, becuase the Linq technology is not doing it  by itself, and it must be hosted, like Linq-2-Entities uses the Entity Framework.

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/linq-to-entities

    What are you using that is using Linq and is the Linq provider, becuase it seems as  if you don't know this?

    https://blogs.msdn.microsoft.com/charlie/2006/10/05/links-to-linq/

    I ask this for future posters that may view the thread. 

    Wednesday, November 20, 2019 1:29 PM