none
Join multiple criteria with condition greater than or equals RRS feed

  • Question

  • Hi,

    I'm trying to implement the following join in LINQ and wondering what is the best way to do it.

    SELECT s.Ticker , f.LastTrade , d.TickDate, *
    FROM T1 s
    INNER JOIN T2 f ON s.SecID = f.SecID
    INNER JOIN T3 d ON f.SecID = d.SecID AND f.LastTrade >= d.TickDate

    Thanks in advance.

    V

    Tuesday, July 19, 2011 6:33 PM

Answers

  • Hi vije;

    The join methods provided in the LINQ framework are Join and GroupJoin. These methods perform equijoins, or joins that match two data sources based on equality of their keys so therefore you can not do something like this, f.LastTrade >= d.TickDate, in a join. One way to do it would be to filter out those record using a where clause as in the following query.

    DataContext ctx = new DataContext();
    
    var query = from s in ctx.T1
          join f in ctx.T2 on s.SecID equals f.SecID
          join d in ctx.T3 on f.SecID equals d.SecID 
          where f.LastTrade >= d.TickDate
          select new {
          	Ticker = s.Ticker,
          	LastTrade = f.LastTrade,
          	TickDate = d.TickDate
          };
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by Vipstercular Wednesday, July 20, 2011 6:55 AM
    Tuesday, July 19, 2011 7:47 PM

All replies

  • Hi vije;

    The join methods provided in the LINQ framework are Join and GroupJoin. These methods perform equijoins, or joins that match two data sources based on equality of their keys so therefore you can not do something like this, f.LastTrade >= d.TickDate, in a join. One way to do it would be to filter out those record using a where clause as in the following query.

    DataContext ctx = new DataContext();
    
    var query = from s in ctx.T1
          join f in ctx.T2 on s.SecID equals f.SecID
          join d in ctx.T3 on f.SecID equals d.SecID 
          where f.LastTrade >= d.TickDate
          select new {
          	Ticker = s.Ticker,
          	LastTrade = f.LastTrade,
          	TickDate = d.TickDate
          };
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by Vipstercular Wednesday, July 20, 2011 6:55 AM
    Tuesday, July 19, 2011 7:47 PM
  • Thanks Fernando - your query works.

    -V

    Wednesday, July 20, 2011 6:56 AM
  • Not a problem Vije, glad I was able to help.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, July 20, 2011 2:00 PM