CODEWITHSUNDEEP
entity-frameworklinq-to-entitiesentity-framework-4.1
Alex Netkachov
Alex Netkachov

Reputation: 13532

Fetch navigation property with 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!

Upvotes: 1

Views: 1297

Answers (2)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364279

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.

Upvotes: 1

undefined
undefined

Reputation: 34248

I think this may be slow because you are using count. I would just try .Any() here instead as it will probably be a heap faster

Upvotes: 0

Related Questions