none
Transaction credits/debits zero by date (Sql to Linq) RRS feed

  • Question

  • This T-sql will return 0 if debits and credits sum to zero for all dates and returns 1 if debits and credits sum to non-zero on any date:

    DECLARE @datechecks TABLE
    ( checkcount int
    )
    INSERT
    INTO @datechecks
    EXEC
    sp_executesql
    N'SELECT  CASE SUM(DEBIT) - SUM(CREDIT)
     WHEN  0
     THEN 0
     ELSE 1
     END AS FLAGS
     FROM  TEST
    GROUP BY TRANSDATE'
    SELECT
    CASE SUM(checkcount)
     WHEN 0
     THEN 0
     ELSE 1
     END
    FROM @datechecks

    How can I implement this validation check in Linq? Thanks!

    Tuesday, November 30, 2010 1:47 PM

Answers

  • Hello,

    Linq to Sql was not able to handle table variable directly. However, in your scenario, you could try to translate the sql statements as below:

    var query = from test in dbcontext.TEST
     	  group test by test.TransDate into g
    	  select new {Flag = (g.Sum(p => p.DEBIT) - g.Sum(p => p.CREDIT)) == (int)0 ? 0 : 1 }
    
    

    Another workaround is to put the script in a stored procedure and then imports it in the data model to call it directly in data context. Please have a try and let me know if it does not work.

     


    Best Regards,
    Roahn Luo
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com
    • Marked as answer by liurong luo Monday, December 6, 2010 10:07 AM
    Friday, December 3, 2010 4:29 AM

All replies

  • Hello,

    Linq to Sql was not able to handle table variable directly. However, in your scenario, you could try to translate the sql statements as below:

    var query = from test in dbcontext.TEST
     	  group test by test.TransDate into g
    	  select new {Flag = (g.Sum(p => p.DEBIT) - g.Sum(p => p.CREDIT)) == (int)0 ? 0 : 1 }
    
    

    Another workaround is to put the script in a stored procedure and then imports it in the data model to call it directly in data context. Please have a try and let me know if it does not work.

     


    Best Regards,
    Roahn Luo
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com
    • Marked as answer by liurong luo Monday, December 6, 2010 10:07 AM
    Friday, December 3, 2010 4:29 AM
  • Hello,

    I'm writing to check the status of this thread. Please let me know if you need more help. Thanks a lot!


    Welcome to Microsoft All-In-One Code Framework to download or request code samples from Microsoft Community Team!
    Monday, December 6, 2010 10:07 AM
  • Hi Roahn,

    Thanks for your Replies. I need to learn more about Linq to Sql and your suggestion sparks my interest in what can be done. I ended up using the visual collections on the screen to produce my results. It is often difficult to change old habits.

    Thanks,

    Mike

    Saturday, February 26, 2011 3:09 PM