使用内连接获取导航属性

发布于 2024-12-28 11:12:11 字数 1168 浏览 4 评论 0原文

我已经在 SqlCe 中为以下数据库表创建了实体数据模型：

CREATE TABLE [test_vulnerabilities] (
    [id] INTEGER PRIMARY KEY,
    [description] NTEXT NOT NULL DEFAULT ''
);

CREATE TABLE [test_software_vulnerabilities]
(
    [id] INTEGER PRIMARY KEY IDENTITY,
    [vulnerability_id] INTEGER NOT NULL
                       REFERENCES [test_vulnerabilities]([id]),
    [details] NTEXT NOT NULL DEFAULT ''
);

实体（通过添加基于现有数据库的实体模型创建）：

entity Vulnerability in set Vulnerabilities
    Id int
    Description string
    Software ICollection<SoftwareVulnerability> - navigation property

entity SoftwareVulnerability in set SoftwareVulnerabilities
    Id int
    Details string
    VulnerabilityId int
    Vulnerability Vulnerability - navigation property

并执行以下查询：

        var query = (from v in entities.Vulnerabilities.Include("Software")
                     where v.Id == id && v.Software.Count > 0
                     select v);

它非常非常非常慢，因为生成的 SQL 将漏洞与 software_vulnerability 连接起来与左外连接。

有没有什么方法可以简单地说我只想要具有非空 software_vulnerability 的漏洞并且 INNER JOIN 可以？

谢谢！

原文

I've created entity data model for the following database tables in SqlCe:

CREATE TABLE [test_vulnerabilities] (
    [id] INTEGER PRIMARY KEY,
    [description] NTEXT NOT NULL DEFAULT ''
);

CREATE TABLE [test_software_vulnerabilities]
(
    [id] INTEGER PRIMARY KEY IDENTITY,
    [vulnerability_id] INTEGER NOT NULL
                       REFERENCES [test_vulnerabilities]([id]),
    [details] NTEXT NOT NULL DEFAULT ''
);

Entities (created by adding entity model based on existing database):

entity Vulnerability in set Vulnerabilities
    Id int
    Description string
    Software ICollection<SoftwareVulnerability> - navigation property

entity SoftwareVulnerability in set SoftwareVulnerabilities
    Id int
    Details string
    VulnerabilityId int
    Vulnerability Vulnerability - navigation property

and executing the following query:

        var query = (from v in entities.Vulnerabilities.Include("Software")
                     where v.Id == id && v.Software.Count > 0
                     select v);

it is very-very-very slow because the generated SQL joins vulnerabilities with software_vulnerability with left outer join.

Is there any way to simply say that I want only vulnerabilities with non-empty software_vulnerability and the INNER JOIN is ok?

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

楠木可依 2025-01-04 11:12:11

不可以。您无法控制已使用的连接。您可以尝试还原查询：

var query = (from s in entities.SofwareVulnerabilities.Include("Vulnerability")
             where s.VulnerabilityId == id
             select s);

您将获得单个预期漏洞的所有软件漏洞，并且该漏洞将被包含在内。如果您的关系来自软件漏洞，并且已正确配置为强制关系，则应该使用内部联接。

No. You don't have control over used joins. You can try to revert the query:

var query = (from s in entities.SofwareVulnerabilities.Include("Vulnerability")
             where s.VulnerabilityId == id
             select s);

You will get all software vulnerabilities for your single expected vulnerability and the vulnerability will be included. If your relation is from software vulnerability is correctly configured as mandatory it should hopefully use inner join.

回复收藏 0 原文