Запросы

|(% style="width:285px" %) |(% style="width:1197px" %)Complex Query Operators

[[https:~~/~~/learn.microsoft.com/en-us/ef/core/querying/complex-query-operators>>https://learn.microsoft.com/en-us/ef/core/querying/complex-query-operators]]

3

|(% style="width:285px" %)Соединение таблиц с условием, с целью проверить, что во второй таблице нет записи из первой.|(% style="width:1197px" %)(((

4

|(% style="width:525px" %){{code language="C#"}}from t1 in <table1>

5

join t2 in <table2>

6

on new { Id = t1.Id, ConditionField = true }

7

equals new { Id = t2.ForeignId, ConditionField = t2.ConditionField }

8

into j

9

from r in j.DefaultIfEmpty()

10

where r == null

11

select t1.Id{{/code}}|(% style="width:239px" %){{code language="sql"}}SELECT t1.id

12

FROM

13

table1 AS t1

14

LEFT JOIN table2 AS t2

15

ON (t1.id = t2.foreign_id)

16

AND t2.condition_field

17

WHERE

18

(t2.id IS NULL){{/code}}|(% style="width:388px" %)Нет подзапросов и коррелированных запросов. Получен наиболее эффективный sql запрос.

19

|(% style="width:525px" %){{code language="c#"}}from t1 in <table1>

20

from t2 in <table2>

21

.Where(e => t1.Id == e.ForeignId && e.ConditionField == true)

22

.DefaultIfEmpty()

23

where t2 == null

24

select t1.Id{{/code}}|(% style="width:239px" %){{code language="sql"}}SELECT t1.id

FROM

table1 AS t1

LEFT JOIN (

SELECT

t2.id,

t2.foreign_id

FROM

table2 AS t2

WHERE

t2.condition_field

) AS t ON t1.id = t.foreign_id

36

WHERE

37

(t.id IS NULL){{/code}}|(% style="width:388px" %)Пока нет проверки t2.ConditionField, запрос корректный, но при добавлении проверки мы получаем подзапрос.

38

|(% style="width:525px" %){{code language="c#"}}<table1>

39

.Where(

40

e => !<table2>

41

.Where(e => e.ConditionField)

42

.Select(e => e.ForeignId)

43

.Contains(e.Id)

44

){{/code}}|(% style="width:239px" %){{code language="sql"}}SELECT t1.id

FROM table1 AS t1

WHERE

NOT (

EXISTS (

SELECT

1

FROM

table2 AS t2

WHERE

t2.condition_field

AND (t2.foreign_id = t1.id)

56

)

57

){{/code}}|(% style="width:388px" %)Получаем коррелированный запрос.

)))

|(% style="width:285px" %)Несколько соединений с условием|(% style="width:1197px" %)(((

62

|{{code language="c#"}}from t1 in <table1>

63

join t21 in <table2>

64

on new { Id = t1.Id, ConditionField = true }

65

equals new { Id = t21.ForeignId, ConditionField = t21.ConditionField }

66

into j1

67

from r1 in j1.DefaultIfEmpty()

68

join t22 in <table2>

69

on new { Id = t1.Id, ConditionField = true }

70

equals new { Id = t22.ForeignId, ConditionField = t22.ConditionField }

71

into j2

72

from r2 in j2.DefaultIfEmpty()

73

where r1 != null && r2 == null

74

select t1.Id{{/code}}|{{code language="sql"}}SELECT

t1.id

FROM

table1 AS t1

LEFT JOIN table2 AS t21 ON (t1.id = t21.foreign_id)

79

AND t21.condition_field

80

LEFT JOIN table2 AS t22 ON (t1.id = t22.foreign_id)

81

AND NOT (t22.condition_field)

82

WHERE

83

(t21.id IS NOT NULL)

84

AND (t22.id IS NULL){{/code}}

85

)))

86

|(% style="width:285px" %)Соединение DbSet и локальной коллекции.|(% style="width:1197px" %)[[Выборка данных на основе локальной коллекции>>doc:Разработка.NET.Работа с БД.EntityFramework | EntityFrameworkCore.Сценарии и проблемы.Выборка данных на основе локальной коллекции.WebHome]]

87

|(% style="width:285px" %)Соединение таблиц с условием, отличным от равенства|(% style="width:1197px" %)(((

88

|Greater Than Condition in Linq Join

89

[[https:~~/~~/stackoverflow.com/questions/3725032/greater-than-condition-in-linq-join>>https://stackoverflow.com/questions/3725032/greater-than-condition-in-linq-join]]|

90

|{{code language="c#"}}from e in entity.M_Employee

91

from p in entity.M_Position.Where(p => e.PostionId >= p.PositionId)

92

select p;{{/code}}|Генерирует условие в join.

93

|{{code language="c#"}}var query = from e in entity.M_Employee

94

from p in entity.M_Position

95

where e.PostionId >= p.PositionId