如何转换SQL，多对多关系，到Linq语句

我在c#项目中将T-SQL查询转换为Linq查询时遇到了以下挑战。为了简单起见，我尽我所能，用下面的脚本表示:

DECLARE @TableA TABLE (ID INT NOT NULL IDENTITY(1, 1)
                        , Data VARCHAR(10) NOT NULL)
DECLARE @TableDetailsA TABLE (ID INT NOT NULL IDENTITY(1, 1)
                                , TableAID INT NOT NULL
                                , TableBID INT NOT NULL)
DECLARE @TableDetailsB TABLE (ID INT NOT NULL IDENTITY(1, 1)
                                , TableAID INT NOT NULL
                                , TableBID INT NOT NULL)
DECLARE @TableB TABLE (ID INT NOT NULL IDENTITY(1, 1)
                        , Data VARCHAR(10) NOT NULL
                        , TableCID INT NOT NULL)
DECLARE @TableC TABLE (ID INT NOT NULL IDENTITY(1, 1)
                        , Data VARCHAR(10) NOT NULL)
INSERT INTO @TableA(Data)
VALUES ('Data set A')
INSERT INTO @TableC(Data)
VALUES ('Data set C')
INSERT INTO @TableB(Data, TableCID)
VALUES ('Data set B', 1)
--INSERT INTO @TableDetailsA(TableAID, TableBID)
--VALUES (1, 1)
INSERT INTO @TableDetailsB(TableAID, TableBID)
VALUES (1, 1)

SELECT A.Data AS [Data A]
        , B.Data AS [Data B]
        , C.Data AS [Data C]
FROM @TableA A
JOIN @TableDetailsA DA
    ON A.ID = DA.TableAID
JOIN @TableB B
    ON DA.TableBID = B.ID
JOIN @TableC C
    ON B.TableCID = C.ID
WHERE B.ID = 1
UNION
SELECT A.Data AS [Data A]
        , B.Data AS [Data B]
        , C.Data AS [Data C]
FROM @TableA A
JOIN @TableDetailsB BA
    ON A.ID = BA.TableAID
JOIN @TableB B
    ON BA.TableBID = B.ID
JOIN @TableC C
    ON B.TableCID = C.ID
WHERE B.ID = 1

SELECT A.Data AS [Data A]
        , B.Data AS [Data B]
        , C.Data AS [Data C]
FROM @TableA A
LEFT JOIN @TableDetailsA DA
    ON A.ID = DA.TableAID
LEFT JOIN @TableDetailsB DB
    ON A.ID = DB.TableAID
JOIN @TableB B
    ON B.ID = ISNULL(DA.TableBID, DB.TableBID)
JOIN @TableC C
    ON B.TableCID = C.ID
WHERE B.ID = 1

注意到我通过两个不同的详细表TableDetailsA和TableDetailsB在两个表TableA和TableB之间有多对多的关系。在本例中，TableDetailsA没有插入数据。

所以基本上，使用linq，在我的c#项目中，我能够像这样复制union语句(忽略linq中的错误，如果有任何…):

var firstQuery =    from ta in repo.TableA
                    join tda in repo.TableDetailsA
                        on ta.ID equals tda.TableAID
                    join tb in repo.TableB
                        on tb.ID equals tda.TableBID
                    join tc in repo.TableC
                        on tb.TableCID = tc.ID
                    select new
                            {
                                ta.Data
                                , tb.Data
                                , tc.Data
                            };
var secondQuery =   from ta in repo.TableA
                    join tdb in repo.TableDetailsB
                        on ta.ID equals tdb.TableAID
                    join tb in repo.TableB
                        on tb.ID equals tdb.TableBID
                    join tc in repo.TableC
                        on tb.TableCID = tc.ID
                    select new
                            {
                                ta.Data
                                , tb.Data
                                , tc.Data
                            };
var unionQuery = firstQuery.Union(secondQuery);
var data = unionQuery.ToList();

然而，我不知道如何复制使用ISNULL(...)将TableB加入TableA的第二个SQL语句，我希望能够做到这一点，因为这似乎更有效和优雅，并且需要更少的变量在我的代码中声明(虽然我知道这可以在一个变量中完成，但我喜欢保持清晰)。

[编辑]

使用Cetin Basoz在回答中给出的第二个查询，我已经设法构建了以下查询，生成了我正在寻找的结果。然而，我仍然希望找到一种方法，让Linq to SQL生成ISNULL(..., ...)，就像我在上面的最后一个选择语句中看到的那样。

var firstQuery = from ta in repo.TableA
                    from tda in ta.TableDetailsA.DefaultIfEmpty()
                    from tdb in ta.TableDetailsB.DefaultIfEmpty()
                    where ta.ID == 1
                    select new
                            {
                                TableAID = ta.ID
                                , TableBID = tda.TableB != null
                                                ? ns.TableB.ID
                                                : nsc.TableB.ID
                            };
var secondQuery = from fq in firstQuery
                    join ta in repo.TableA
                        on fq.TableAID equals ta.ID
                    join tb in repo.TableB
                        on fq.TableBID equals tb.ID
                    join tc in repo.TableC
                        on tb.TableCID  equals tc.ID
                    select new
                            {
                                TableAData = ta.Data
                                , TableBData = tb.Data
                                , TableCData = tc.Data
                            };

所以，我们仍然在等待我们的冠军解开这个致命的秘密!