locked
Entity SQL Not returning any results RRS feed

  • Question

  • Hi;

    I am resonably new to the entiry framework and entity SQL.  Please consider the following two eSQL statements:

    "SELECT VALUE d FROM MDAFinancialPeriods AS d WHERE d.FinPeriodDtStart = DATETIME'2008-12-01 00:00:00'"
    

    as expected the above stament works correctly.  However please now cosider the next example which is using a reverse BETWEEN:

    "SELECT VALUE d FROM MDAFinancialPeriods AS d WHERE DATETIME'2008-12-01 00:00:00' BETWEEN d.FinPeriodDtStart AND d.FinPeriodDtEnd"
    

    interestingly, the above statement returns the correct financial period, providing that supplied datetime value matches either the beginning or ending date stored in the database.  However, if any other date is supplied that falls between the starting or ending dates stored in the database, then no entiity object is returned.

    When this failed, I then attempted the following statement as an alternative which works correctly.

    "SELECT VALUE d FROM MDAFinancialPeriods AS d WHERE d.FinPeriodDtStart <= DATETIME'2008-12-06 00:00:00' AND d.FinPeriodDtEnd >= DATETIME'2008-12-06 00:00:00'"
    

    I am wondering why the reverse BETWEEN is not working in EF4 and perhaps whether this may be a bug?

    Thanking you in advance

    Regards

    Dave



    Sunday, June 5, 2011 1:43 PM

Answers

  • Hi,

    My first move would be to check the underlying SQL (using ObjectQuery.ToTraceString). If it's a bug it's likely something wrong in the underlying generated SQL statement.

    Note also that you are NOT using always the same date (December 1 for BETWEEN but December 6 for your "emulation"). Not sure but it could obscure your issue analysis.

    Anyway seeing the generated SQL should help to quickly see if this is an EF bug or if further investigation is needed.


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Marked as answer by modao Thursday, June 9, 2011 7:08 AM
    Wednesday, June 8, 2011 6:20 PM
  • Hi Patrice;

    Thank you for your advice.  As i said i am new to EF and had not as yet discovered the ToTraceString method.

    It does in fact appear to be a bug:

    I took my original query i.e.:

    SELECT VALUE d FROM MDAFinancialPeriods AS d WHERE DATETIME'2008-12-01 00:00:00' BETWEEN d.FinPeriodDtStart AND d.FinPeriodDtEnd
    

    then created an ObjectQuery which returned no results.  I then inspected the underlying TSQL and ran it directly in SMSS and the expected record was returned. 

    I have posted this as a bug on connect at the following link:

        https://connect.microsoft.com/VisualStudio/feedback/details/674748

    Kind Regards

    Dave

    • Marked as answer by modao Thursday, June 9, 2011 7:08 AM
    Thursday, June 9, 2011 7:06 AM

All replies

  • Hi modao,

    Thank you for posting.

    AFAIK, if you want to achieve BETWEEN keyword in LINQ, you have to use greater than or less than symbol in your code like you mentioned.

    Of course, you can write your custom comparison method to achieve BETWEEN…AND function in your code. Like:
    public static bool Between<T>(this T s, T l, T h) where T : IComparable 
    
    
    
    { 
    
    
    
     return s.CompareTo(l) >= 0 && s.CompareTo(h) <= 0; 
    
    
    
    }
    
    
    
    

    Please check this threads which was like yours. http://social.msdn.microsoft.com/Forums/en-us/linqprojectgeneral/thread/98324c66-301c-47fe-aae2-160d9df07d04 


    Hope this helps. If you have any question, welcome to post back.

    Best Regards,


    Larcolais Gong[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.

     

    Tuesday, June 7, 2011 8:58 AM
  • Hi Larcolais;

    Thank you for your reply.

    I am not however using LINQ, yet rather Entity SQL. 

    I am now a little confused, as the official Microsoft Entity SQL Reference, states that the BETWEEN operator is a valid comparison operator in Entity SQL as can be found at the following link:

      http://msdn.microsoft.com/en-us/library/bb386889.aspx

    If the keyword is supported in eSQL, then I believe that my example above should in-fact work.

    Can anyone shed some further light on this issue and does anyone believe that perhaps I should be reporting it as a Microsoft Connect issue?

    Kind Regards

    Dave

    Tuesday, June 7, 2011 9:39 AM
  • it doesnt seem a bug. the following code snippet was referred from MSDN document.

    SELECT VALUE product FROM AdventureWorksEntities.Product 
      AS product where product.ListPrice BETWEEN 50 AND 90
    

    even if you wrote a sql syntax in SSMS, i dont think this can be executed successfully. Let me say:

    SELECT * FROM TABLE1 WHERE Price BETWEEN 90 AND 100

    the above sql syntax is correct. But the following code snippet is wrong.

    SELECT * FROM TABLE1 WHERE 90 BETWEEN startDate AND endDate

    E-Linq was also transferred into T-SQL. So i dont think the above wrong code snippet on SSMS side can be executed unblocked.

    Thanks,

    Wednesday, June 8, 2011 9:55 AM
  • Let me say:

    SELECT * FROM TABLE1 WHERE Price BETWEEN 90 AND 100

    the above sql syntax is correct. But the following code snippet is wrong.

    SELECT * FROM TABLE1 WHERE 90 BETWEEN startDate AND endDate

    Hi CupOverTheTable;

    Thank you for your reply.

    Your second example (i.e. a reverse BETWEEN statement) does execute successfully in SMSS against native TSQL, and is something that I have been using for years in our financials application. 

    The "real-world" example of this issue (currently in use), is where I wish to return the correct Financial Period from a Financial Periods table where the supplied date falls BETWEEN the start and end dates stored in the table

    Our app is cross platform compatible, and the reverse BETWEEN statement currently works as a native SQL command against SQL Server, Oracle, Sybase, DB2 and Firebird.  My guess is that this type of reverse BETWEEN statement will execute correctly against virtually all SQL compliant databases.

    Based on this, I therefore believe that (due to the fact that a reverse BETWEEN statement is supported by most major database flavors), the REVERSE BETWEEN should also be supported by Entity SQL.

    Any other thoughts on this issue (MS employee thoughts also welcome), would be highly appreciated.

    Kind Regards

    Dave


    Wednesday, June 8, 2011 5:25 PM
  • Hi,

    My first move would be to check the underlying SQL (using ObjectQuery.ToTraceString). If it's a bug it's likely something wrong in the underlying generated SQL statement.

    Note also that you are NOT using always the same date (December 1 for BETWEEN but December 6 for your "emulation"). Not sure but it could obscure your issue analysis.

    Anyway seeing the generated SQL should help to quickly see if this is an EF bug or if further investigation is needed.


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Marked as answer by modao Thursday, June 9, 2011 7:08 AM
    Wednesday, June 8, 2011 6:20 PM
  • Hi Patrice;

    Thank you for your advice.  As i said i am new to EF and had not as yet discovered the ToTraceString method.

    It does in fact appear to be a bug:

    I took my original query i.e.:

    SELECT VALUE d FROM MDAFinancialPeriods AS d WHERE DATETIME'2008-12-01 00:00:00' BETWEEN d.FinPeriodDtStart AND d.FinPeriodDtEnd
    

    then created an ObjectQuery which returned no results.  I then inspected the underlying TSQL and ran it directly in SMSS and the expected record was returned. 

    I have posted this as a bug on connect at the following link:

        https://connect.microsoft.com/VisualStudio/feedback/details/674748

    Kind Regards

    Dave

    • Marked as answer by modao Thursday, June 9, 2011 7:08 AM
    Thursday, June 9, 2011 7:06 AM