I have the following challenge to covert a T-SQL query to a Linq query in a C# project. As best as I can and for simplicity's sake, it is represented in the following script:
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
Notices that I have a many to many relationship between two tables, TableA and TableB through two different detail tables TableDetailsA and TableDetailsB. In this case TableDetailsA does not have data inserted into it.
So basically, using linq, in my C# project, I am able to replicate the union statement like so (ignore error in the linq if there are any...):
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();
However, I don't know how to replicate the second SQL statement that uses the ISNULL(...) to join TableB to TableA, which I would like to be able to do as this seems more efficient and elegant and would require less variables to be declared in my code (although I know this can be done in one var but I like to keep it clear).
[EDIT]
Using the second query given in the answer by Cetin Basoz, I've managed to build the following queries that generate the result I'm looking for. However, I still would prefer to find a way to for Linq to SQL to generate the ISNULL(..., ...), as seen in my last select statement above.
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
};
So, we are still waiting for our champion to unlock this deathly secret!
