Для объединения таблиц по определенному критерию используется метод Join . Например, в нашем случае таблица телефонов и таблица компаний имеет общий критерий — id компании, по которому можно провести объединение таблиц:
Метод Join принимает четыре параметра:
вторую таблицу, которая соединяется с текущей
свойство объекта — столбец из первой таблицы, по которому идет соединение
свойство объекта — столбец из второй таблицы, по которому идет соединение
новый объект, который получается в результате соединения
В итоге данный запрос будет транслироваться в следующее выражение SQL:
Аналогичного результата мы могли бы достигнуть, если бы использовали оператор join:
Соединение трех таблиц
Допустим, у нас есть три таблицы, которые связаны между собой и которые описываются следующими моделями:
In this tutorial let us look into how to use Join Query in Entity Framework to load the data from two or more tables.
It is always advisable to use navigational properties to query the related data. You can refer to the article Querying Related data using Navigational Properties from our last tutorial. You should use the Join Query operators only if the tables do not have any navigational properties defined on them or you want to fine-tune the generated queries for performance benefits.
We continue to use the AdventureWorks Database for our model. If you missed the previous tutorials you can go through those from the following the LINQ to Entities tutorial
Table of Content
Using Join
The following query joins Person and EmailAddresses table using the join Query operator. The Join operator uses the Equals Keyword to compare the specified properties
The following query Joins the Person table with EmailAddress table on BusinessEntityID Key. The query looks very similar to the normal database SQL Join Queries.
Query Syntax
Method Syntax
The method query syntax uses the Join method. Join method is an extension method, which takes the following syntax.
IEnumerable outer
The first sequence to join. In our case it is Person table. Note that join is the extension method.
IEnumerable inner
The sequence to join to the first sequence. Here we use EmailAddresses, Which we are going to join to the Person Table.
Func outerKeySelector
A function to extract the join key from each element of the first sequence. Here we use lambda expression p => p.BusinessEntityID. The Key BusinessEntityID of the Person table is used to Join the two tables
Func innerKeySelector
A function to extract the join key from each element of the second sequence. This is similar to the outerKeySelector, but the specify the key to be used from the second table. In our case is BusinessEntityID field from EmailAddresses table.
A function to create a result element from two matching elements. Here the two matching elements are TOuter which is our Person table ( p) & TInner which is Emailaddress table (e). We build a anonymous type new
What would be the query for:
in entity framework?
This is what I wrote:
but it’s wrong. Can some one guide me to the path?
3 Answers 3
Where db is your DbContext . Generated query will look like (sample for EF6):
In case anyone’s interested in the Method syntax, if you have a navigation property, it’s way easy:
If you don’t, unless there’s some Join() override I’m unaware of, I think it looks pretty gnarly (and I’m a Method syntax purist):
You could use a navigation property if its available. It produces an inner join in the SQL.
Not the answer you’re looking for? Browse other questions tagged c# entity-framework or ask your own question.
Linked
Related
Hot Network Questions
To subscribe to this RSS feed, copy and paste this URL into your RSS reader.
site design / logo © 2019 Stack Exchange Inc; user contributions licensed under cc by-sa 4.0 with attribution required. rev 2019.11.15.35459
