1

Mow I can translate this:

SELECT * 
FROM vectors as v
INNER JOIN points as p 
ON v.beginId = p.id OR v.endId = p.id

Into linq2sql statement? Basically I want this:

var query = from v in dc.vectors
            join p in dc.points on p.id in (v.beginId, v.endId)
            ...
            select ...;

I know, I can do this dirty through Union construction, but is there a better way than duplicating most of the query?

eocron
  • 6,885
  • 1
  • 21
  • 50
  • Possible duplicate of [How to do joins in LINQ on multiple fields in single join](http://stackoverflow.com/questions/373541/how-to-do-joins-in-linq-on-multiple-fields-in-single-join) – Paweł Hemperek Oct 17 '16 at 07:30
  • @PawełHemperek - This is not a duplicate of that question since he needs an `or` operator and not an `end` – Gilad Green Oct 17 '16 at 07:33

1 Answers1

2

You can't have an on clause in linq-to-sql with an or. You need to do:

var result = from v in dc.vectors
             from p in dc.points
             where p.id == v.beginId || p.id == v.endId
             select new { v, p };

Equivalent to the sql of:

SELECT * 
FROM vectors as v,
     points as p 
WHERE v.beginId = p.id 
OR v.endId = p.id
Gilad Green
  • 36,708
  • 7
  • 61
  • 95
  • Yes, but I think it is inefficient. Where clause is basicaly multiply your tables to calculate intersection. Where as Join uses hash sets to find it. – eocron Oct 19 '16 at 14:39
  • @eocron06- Both joins can do it as a nested join and not a hash join - it depends on the database/indexes/statistics. You can take the sql that is being generates and check its explain plan in the database and see if it can be improved :) – Gilad Green Oct 19 '16 at 15:11