none
Ho to join on diapason of values RRS feed

  • Question

  • Please tell me how to write such kind of join in by linq:

    SELECT * FROM refGoods AS G

            JOIN #Distributors AS D ON G.id >= D.minId and G.id <= D.maxId 

    ?

     

    Sunday, September 19, 2010 10:01 PM

Answers

  • For the join criteria, change to a where-clause join:

    var q =
      from g in dc.refGoods
      from d in dc.distributors
      where g.id >= d.minId && g.Id <= d.maxId
      select new { d, g };

    ...the temp table however (#Distributors) is not directly supported. You can use temp tables if you manually change the dbml to include the temp table prefix, but you must also handle creating/dropping them outside of L2S (e.g. dc.ExecuteCommand). Supply the L2S DC with a sqlconnection to ensure that creating/using/dropping temp tables is done on the same connection / sql session.


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    Monday, September 20, 2010 3:07 AM
    Answerer

All replies

  • For the join criteria, change to a where-clause join:

    var q =
      from g in dc.refGoods
      from d in dc.distributors
      where g.id >= d.minId && g.Id <= d.maxId
      select new { d, g };

    ...the temp table however (#Distributors) is not directly supported. You can use temp tables if you manually change the dbml to include the temp table prefix, but you must also handle creating/dropping them outside of L2S (e.g. dc.ExecuteCommand). Supply the L2S DC with a sqlconnection to ensure that creating/using/dropping temp tables is done on the same connection / sql session.


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    Monday, September 20, 2010 3:07 AM
    Answerer
  • Thank u very much Kristofer)
    Monday, September 20, 2010 10:51 AM