SQL，结合两个表进行查询

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.

基本纲要。我正在尝试获得一个基于TestRunId的testsuiteexecution列表。因此，我需要基于TestSuiteId和TestRunId进行搜索，以获得TestSuiteExectuions的列表，该列表基于他们被执行的TestRun。注意，TestSuiteExecutionId可能没有关联的TestRunExecutionId，这仅仅意味着它被排除在搜索之外。

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)

我遇到的问题是主表缺少TestRunId，所以我需要找到/连接那个关联，或者通过多次搜索(出于效率的考虑，我不想这样做)来创建它

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,

testsuiteexecution表包含以下信息，

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

问题是为了完成搜索，我需要相关的TestRunId。有一个单独的表，它具有相关联的TestRunExecutionId和TestRunId，如下所示

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,

我一直在阅读SQL连接，在完美的情况下，我可以创建一个临时表，包含以下列，以执行我的查询，

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

这样，我就有了正在运行的TestSuite和TestRuns之间的连接，并且能够获得testsuiteexecution的集合。这两个表之间的连接桥是TestRunExecutionId，但是我不确定如何使用哪种类型的连接，或者如何用LINQ格式写出连接

2 个解决方案

#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:

这两种方法都适用于1:1和1:许多关系，大致相当于下面的SQL:

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

#2

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

对此进行盲目尝试，但是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:

如果您需要在运行时留下JOIN，您应该能够完成以下操作:

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
}

#1