Certain databases like SQLite don't support APPLY operators so this kind of query may not be translated. Because of this distinction, this kind of queries translates to CROSS APPLY in the absence of DefaultIfEmpty and OUTER APPLY when DefaultIfEmpty is applied. But if DefaultIfEmpty is applied on the collection selector then the outer element will be connected with a default value of the inner element. If the collection is empty for an outer element, then no results would be generated for that outer element. It translates to APPLY operations in many relational databases. That's why we need to evaluate the collection selector for each outer element. When the collection selector references the outer element, which isn't in a where clause (as the case above), it doesn't translate to a database join. Ĭollection selector references outer in a non-where case var query = from b in context.Set()įrom p in context.Set().Where(p => b.BlogId = p.BlogId)įrom p in context.Set().Where(p => b.BlogId = p.BlogId).DefaultIfEmpty() Because of this distinction, this kind of queries translates to INNER JOIN in the absence of DefaultIfEmpty and LEFT JOIN when DefaultIfEmpty is applied.

Normally this case arises when using collection navigation on the outer element as the collection selector.

When the collection selector has a where clause, which references the outer element, then EF Core translates it to a database join and uses the predicate as the join condition. Ĭollection selector references outer in a where clause It translates to CROSS JOIN in relational databases. When the collection selector isn't referencing anything from the outer source, the result is a cartesian product of both data sources. Collection selector doesn't reference outer Depending on how the collection selector is related to the outer data source, SelectMany can translate into various different queries on the server side. In a way, it's a join but without any condition so every outer element is connected with an element from the collection source. The LINQ SelectMany operator allows you to enumerate over a collection selector for each outer element and generate tuples of values from each data source. įurther, if the key selectors are anonymous types, EF Core generates a join condition to compare equality component-wise. On photo.PersonPhotoId equals person.PhotoId So EF Core generates a join condition by comparing the outer key selector to the inner key selector for equality. While the LINQ Join has outer and inner key selectors, the database requires a single join condition.

It naturally translates to INNER JOIN on relational databases. The LINQ Join operator allows you to connect two data sources based on the key selector for each source, generating a tuple of values when the key matches. You can view this article's sample on GitHub.

Sqlite inner join syntax