CODEWITHSUNDEEP
c#linq
Jordan
Jordan

Reputation: 530

LINQ join statement issues

I have a problem with a LINQ statement.

I have 3 tables: Examinations, ExaminationProtocols and SampleTests.

Until now I have used this statement as I needed only the information only of the first two tables.

var baseQuery = from e in context.Examinations
                join ep in context.ExaminationProtocols
                on e.ID equals ep.ExaminationID into er
                from r in er.DefaultIfEmpty()
                select new { E = e, EP = r };

But now I need to get the ExaminationProtocols that have at least 1 SampleTest with a field acccurate = true.

The foreign key between the SampleTest and ExaminationProtocols is 

EP.ID equal ST.examinationProtocolID

I tried to join the third table inside the statement but doesn't seem to get the result I desire.

If anyone can give me a tip I'll be thankful.

Upvotes: 2

Views: 82

Answers (2)

roman
roman

Reputation: 117380

It could be written like this

var baseQuery = from e in Examinations
              join ep in ExaminationProtocols.Where(x => SampleTests.Where(y => y.accurate).Select(z => z.examinationProtocolID).Contains(x.ID))
                  on e.ID equals ep.ExaminationID into er
              from r in er.DefaultIfEmpty()
              select new { E = e, EP = r };

Upvotes: 0

Andrei
Andrei

Reputation: 56688

Does this give you the required result?

var baseQuery = from e in context.Examinations
                join ep in context.ExaminationProtocols
                    on e.ID equals ep.ExaminationID into er
                from r in er.DefaultIfEmpty()
                join st in context.SampleTests
                    on r.ID equals st.examinationProtocolID
                where st.acccurate
                select new { E = e, EP = r };

Upvotes: 2

Related Questions