Using 'OR' inside a LinQ join query (adapting SQL into LinQ)

Question

I have a SQL query that basically joins the MyWords table to the MyTranslations table on two keys. Here are my tables for better understanding.

MyWords table:

Id
WordString
LangType

MyTranslations table:

Id
WordColumnAId
WordColumnBId

Please note that WordColumnAIdand WordColumnBId columns are FKs that represents the MyWords.Id. My SQL query:

SELECT MyTranslations.Id 
    ,WordColumnAId
    ,WordColumnBId
    ,MyWords.WordString
    ,MyWords.LangType
  FROM MyTranslations
  join MyWords on (MyTranslations.WordColumnAId = MyWords.Id or MyTranslations.WordColumnBId = MyWords.Id)
  where MyWords.LangType !=@currentLangType

I know it doesn't make sense to use join on with multiple keys at first glance but I'm trying to a cross-query that would join two tables whether the key is on WordColumnAId or WordColumnBId.

Problem

I'm trying to adapt the above T-SQL query into a LinQ query. The problem is that I can't find my way around LinQ to use two keys in a single join query.

Here's what I've got so far:

from translation in queryableTranslation
    join word in _myWordRepository on translation.WordColumnAId equals word.Id // This is where I want to add `or translation.WordColumnBId equals word.Id` but get errors.
    where word.LangType != currentLangType
    select new QueryResultDto {
        MyTranslationId = translation.Id,
        WordString = word.WordString,
        LanguageType = word.LangType,
        WordColumnAId = translation.WordColumnAId,
        WordColumnbId=translation.WordColumnbId,
    };

I'm very new to the LinQ and trying to learn. So my question: is there a way to achieve this in LinQ or am I trying the impossible? I'm also open to better approaches.

Answer 1

EF and other LINQ providers should translate query to INNER JOIN when using this syntax:

var query =
    from translation in queryableTranslation
    from word in _myWordRepository.Where(word => translation.WordColumnAId == word.Id 
      || translation.WordColumnBId = word.Id)
    where word.LangType != currentLangType
    select new QueryResultDto 
    {
        MyTranslationId = translation.Id,
        WordString = word.WordString,
        LanguageType = word.LangType,
        WordColumnAId = translation.WordColumnAId,
        WordColumnbId=translation.WordColumnbId,
    };