Converting SQL to LINQ
Hello, I need help with a LINQ/C# query. I have the following SQL query:
Select
TestKitAnalyteEQCLotNumber.TestKitID,
TestKitAnalyteEQCLotNumber.AnalyteID,
TestKitAnalyteEQCLotNumber.EQCLotNumberID,
LinkTestKitAnalyteEQCLotNumberAcceptableResponse.ResponseID,
TestKit.Name,
TestKit.TestKitID As TestKitID1,
AssayTestProcess.TestKitID As TestKitID2,
AssayTestProcess.AssayID
From
TestKitAnalyteEQCLotNumber Join
LinkTestKitAnalyteEQCLotNumberAcceptableResponse On
LinkTestKitAnalyteEQCLotNumberAcceptableResponse.TestKitAnalyteEQCLotNumberID =
TestKitAnalyteEQCLotNumber.TestKitAnalyteEQCLotNumberID Join
TestKit On TestKitAnalyteEQCLotNumber.TestKitID = TestKit.TestKitID Join
AssayTestProcess On AssayTestProcess.TestKitID = TestKit.TestKitID
Where
TestKitAnalyteEQCLotNumber.AnalyteID = 126 And
TestKitAnalyteEQCLotNumber.EQCLotNumberID = 657 And
AssayTestProcess.AssayID = 3899
The SQL query is returning the correct results. However, when I write the query in LINQ, I am getting an extra record. Here is my LINQ query:
from TestKitAnalyteEQCLotNumbers in EntityContext.TestKitAnalyteEQCLotNumbers
from t in TestKitAnalyteEQCLotNumbers.Responses
from AssayTestProcesses in EntityContext.AssayTestProcesses
where
TestKitAnalyteEQCLotNumbers.AnalyteID == 126 &&
TestKitAnalyteEQCLotNumbers.EQCLotNumberID == 657 &&
AssayTestProcesses.AssayID == 3899
select new {
TestKitAnalyteEQCLotNumbers.TestKitID,
TestKitAnalyteEQCLotNumbers.AnalyteID,
TestKitAnalyteEQCLotNumbers.EQCLotNumberID,
t.ResponseID,
AssayTestProcesses.TestKit.Name,
TestKitID1 = AssayTestProcesses.TestKit.TestKitID,
TestKitID2 = AssayTestProcesses.TestKitID,
AssayTestProcesses.AssayID
}
Seems like my LINQ query is not doing the inner join with AssayTestProcess On AssayTestProcess.TestKitID = TestKit.TestKitID because the extra record is being picked up from the AssayTestProcess table.
Thank you!
ASKER
I am still having a little trouble because LinkTestKitAnalyteEQCLotNumberAcceptableResponse is not part of the EntityContext. That table is used to link results from 2 other tables, TestKitAnalyteEQCLotNumber and the Response table. Since it does not have an index of its own it cannot be accessed directly the way SQL can.
join testResponse in EntityContext.LinkTestKitAnalyteEQCLotNumberAcceptableResponse
In above line, Replace "EntityContext.LinkTestKitAnalyteEQCLotNumberAcceptableResponse"
with "tKitAnalyteEQCLotNumber .Responses"
I guess this will solve your problem
ASKER
I am still having issues referencing the
- TestKitAnalyteEQCLotNumber should be replace with tKitAnalyteEQCLotNumber
- AssayTestProcess should be replaced with AssayTstProcess
from tKitAnalyteEQCLotNumber in EntityContext.TestKitAnalyteEQCLotNumbers
join testResponse in EntityContext.LinkTestKitAnalyteEQCLotNumberAcceptableResponse
on tKitAnalyteEQCLotNumber.TestKitAnalyteEQCLotNumberID
equals testResponse.TestKitAnalyteEQCLotNumberID
join tKit in EntityContext.TestKit
on tKitAnalyteEQCLotNumber.TestKitID
equals tKit.TestKitID
join AssayTstProcess in EntityContext.AssayTestProcesses
on AssayTstProcess.TestKitID
equals tKit.TestKitID
where
tKitAnalyteEQCLotNumber.AnalyteID == 126 &&
tKitAnalyteEQCLotNumber.EQCLotNumberID == 657 &&
AssayTestProcess.AssayID == 3899
select new {
tKit.TestKitID,
tKitAnalyteEQCLotNumber.AnalyteID,
tKitAnalyteEQCLotNumber.EQCLotNumberID,
testResponse.ResponseID,
AssayTestProcess.TestKit.Name,
TestKitID1 = AssayTestProcess.TestKit.TestKitID,
TestKitID2 = AssayTestProcess.TestKitID,
AssayTestProcess.AssayID
}
LINQ query works like
FROM <A one object from list> IN <object collection>
JOIN <B one object from list> IN <object collection>
ON B.OneProperty equals A.Oneproperty
Same as we do in SQL joins, hope you understand now :)
Please find below LINQ query with joins:
from tKitAnalyteEQCLotNumber in EntityContext.TestKitAnalyteEQCLotNumbers
join testResponse in EntityContext.LinkTestKitAnalyteEQCLotNumberAcceptableResponse
on TestKitAnalyteEQCLotNumber.TestKitAnalyteEQCLotNumberID
equals testResponse.TestKitAnalyteEQCLotNumberID
join tKit in EntityContext.TestKit
on TestKitAnalyteEQCLotNumber.TestKitID
equals tKit.TestKitID
join AssayTestProcess in EntityContext.AssayTestProcesses
on AssayTestProcess.TestKitID
equals tKit.TestKitID
where
tKitAnalyteEQCLotNumber.AnalyteID == 126 &&
tKitAnalyteEQCLotNumber.EQCLotNumberID == 657 &&
AssayTestProcess.AssayID == 3899
select new {
tKit.TestKitID,
tKitAnalyteEQCLotNumber.AnalyteID,
tKitAnalyteEQCLotNumber.EQCLotNumberID,
testResponse.ResponseID,
AssayTestProcess.TestKit.Name,
TestKitID1 = AssayTestProcess.TestKit.TestKitID,
TestKitID2 = AssayTestProcess.TestKitID,
AssayTestProcess.AssayID
}