SQL, combining two tables for query

Question

Basic rundown. I'm trying to get a list of TestSuiteExecutions based on a TestRunId. So I need to do a search based on a TestSuiteId and TestRunId to get a list of the TestSuiteExectuions based on what TestRun they were executed off of. Note that a TestSuiteExecutionId may not have an associated TestRunExecutionId, which would simply mean it's excluded from the search.

The issue I'm running into is the main table lacks the TestRunId, so I need to find/connect that association or make it myself via multiple searches (which I don't want to do for efficiency sake)

Something similar to this;

from t in testSuiteExecutionsTable
where t.testSuiteId == testSuiteId &&  t.testRunId == testRunId
select t).ToList();

The TestSuiteExecutions Table has the following information,

TestSuiteExecutionId  TestSuiteId  TestRunExecutionId
--------------------  -----------  ------------------
        990                1               100
        991                2               NULL
        992                3               102
        993                1               103

The issue is in order to complete the search, I need the associated TestRunId. There is a separate table which has the associated TestRunExecutionId and TestRunId as follows

TestRunExecutionsTable

 TestRunExecutionId    TestRunId
--------------------  -----------
        100                1
        102                2
        103                1

I've been reading on SQL Joins, and in a perfect world I would be able to create a temporary table with the following columns to execute my query off of,

TestSuiteExecutionId  TestSuiteId  TestRunExecutionId TestRunId
--------------------  -----------  ------------------ ---------
        990                1               100            1
        991                2               NULL          NULL
        992                3               102            2
        993                1               103            1

That way I have the connection between what TestSuite is being run off which TestRuns, and be able to get my collection of TestSuiteExecutions. The connection bridge between the two tables is the TestRunExecutionId, but I'm not exactly sure how to go about which type of join to use, or how to write out the join in LINQ format

Answer 1

You can express your query with existential quantifier, like this:

var res = dbContext.TestSuiteExecutionsTable.Where(e =>
    e.TestSuiteId == testSuiteId && dbContext.TestRunExecutionsTable.Any(r =>
        r.TestRunId == testRunId && r.TestRunExecutionId == e.TestRunExecutionId
    )
).ToList();

This works both for 1:1 and 1:Many relationships, and roughly corresponds to SQL below:

SELECT *
FROM TestSuiteExecutionsTable e
WHERE e.TestSuiteId = @testSuiteId
  AND EXISTS (
      SELECT *
      FROM TestRunExecutionsTable r
      WHERE r.TestRunId = @testRunId AND r.TestRunExecutionId = e.TestRunExecutionId
  )

Answer 2

Taking a blind stab at this, but there is a join in LINQ.

Obviously you'll need to correct your Context properties, but this should work without much fiddling.

from suite in Context.TestSuiteExecution
join run in Context.TestRunExecution on suite.TestRunExecutionId equals run.TestRunExecutionId
select new 
{
    TestSuiteExecutionId = suite.TestSuiteExecutionId,
    TestSuiteId = suite.TestSuiteId,
    TestRunExecutionId = suite.TestRunExecutionId,
    TestRunId = run.TestRunId
}

If you need to LEFT JOIN on the run, you should be able to do this one:

from suite in Context.TestSuiteExecution
join r in Context.TestRunExecution on suite.TestRunExecutionId equals r.TestRunExecutionId
    into runs
from run in runs.DefaultIfEmpty()
select new 
{
    TestSuiteExecutionId = suite.TestSuiteExecutionId,
    TestSuiteId = suite.TestSuiteId,
    TestRunExecutionId = suite.TestRunExecutionId,
    TestRunId = run.TestRunId
}