none
LINQ Join tables with Between not equals RRS feed

  • Question

  • Hi,

    I would like to know how to join a table based on a between value rather than a exact matching value. e.g:

    SELECT table1.field1
    FROM table1
    INNER JOIN table2  ON table1.DateField1 >= table2.DateField1 and table1.DateField1 <= table2.DateField2


    How would I do that in LINQ?

    Thanks for your help.
    Friday, January 29, 2010 1:49 PM

Answers

  • Hi Pieter,

    Such situations can be handled in LINQ by giving an always-true join expression and transferring the inner join condition to the where clause:

    from t1 in Table1
    join t2 in Table2 on 1 equals 1
    where t1.DateField1 >= t2.DateField2 && t1.DateField <= t2.DateField
    select t1;

    Hope that helps.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    • Marked as answer by Pieter Theron Thursday, February 4, 2010 6:07 AM
    Friday, January 29, 2010 5:23 PM

All replies

  • Hi Pieter,

    Such situations can be handled in LINQ by giving an always-true join expression and transferring the inner join condition to the where clause:

    from t1 in Table1
    join t2 in Table2 on 1 equals 1
    where t1.DateField1 >= t2.DateField2 && t1.DateField <= t2.DateField
    select t1;

    Hope that helps.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    • Marked as answer by Pieter Theron Thursday, February 4, 2010 6:07 AM
    Friday, January 29, 2010 5:23 PM
  • Hi Pieter,

     

    I agree with Syed and his post is very helpful.   Besides, I think the current thread is a question instead of a general discussion, so I will change the thread type to benefit more community members.   If you have any questions regarding this case, please be free to let us know. 

     

    Have a great day, you both!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, February 1, 2010 1:41 AM
    Moderator
  • Hi Pieter,

     

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, February 3, 2010 12:58 AM
    Moderator
  • Hi,

    Thank you very much for the reply! It works like a charm. 

    Lingzhi, sorry I thought I posted a question not a discussion.

    Thanks a million for the help guys!


    Pieter
    Thursday, February 4, 2010 6:07 AM