ααα ααααΈααααΆα
Π
αα
ααΈαααααΎααααααα αΆααα·ααΈααΆααααααα½αα
ααα½αααααααααααααααΆααααΆααααααΎαααααα·αααααΆαααΌααααααΆααααααΉα αααα½α LINQ.
ααΆααααΌαααΆαααααααΆααααΆ ααΈα(Language-Integrated Query) ααΊααΆααΆααΆααΆαααα αα·αααΆααααα½ααααααΆαααα½αααααααα·ααααααα
Π LINQ αα SQL ααΊααΆαα αα αααα·ααααΆαααααΆααα αΌαααααΎαα·αααααααααα»α DBMS α αααααΊααΆα§αααααααααΆαα₯αααα·αααααααΆααααααΎααΆαααΆαα½ααα·αααααα ααααααα½αααααΌαααΆααααααΎαααΆααααααΆααΆαααααΆα ααααααααΆααααααΉαααααΌαααΆαααααααααα ααΆ αααα½α SQL platform αα·αααααΎαα ααΆαααααΆαααΈαααααΌαααααΆααα·αααααααααααΆααααΆαααααα·ααααα·α αααα»αααααΈααααααΎαααα DBMS ααΎαααΆααααααΆ αααΆαααΈααααααΎ MS SQL.
αααααΆαααΆαααΆ, αααα½α LINQ αα·αβααααΌαβααΆαβαααααααβαα βααΆβααΆαβαααααβαααβααα’βαααα»αβααα αααα½α SQLααα DBA αααααΆααααα·αααααα’αΆα αααααααΆαα½αααΉαααΆααααααΎαα‘αΎαααΆααα’ααααααΆααααααΎαααααα·αααααΆα αααα½α SQL:
- ααΆααααααΆααααα’αααα»α (α αΌααα½α) αα·ααααααααααα (ααΈαααααα)
- nuances ααΆα αααΎααααα»αααΆαααααΎααααΆααααΆααααααΆαα αα·ααααααααααααα»α
- ααΆααααααααα½αααΆα αααΎααααα»αααΆααααα½ααααααααα IN αα ααΎ αα·αααααααΠΈ αα·αβαα αααα»α, <> ααΎ αα·ααααααα
- ααααΆαααααααΆααααααα·ααααααααααααααααΆααααααΆααΆαααααααα’αΆαααα CTE α’αααααΆααΆα
- ααΆαααααΎααααΆαααααααα (αααααΎα) ααΆαα½αααΉαααΆαααααΆααα·αααΆαααααΆαααΆααΆα ααΆαα½α (... )
- ααΆαααααΎααααΆαααα·αααααΆααααααΆαααααΎαα·αα·ααααααΆαααααααΆααα½αααΎααααΈαααα αΆααααΆαα’αΆααα·ααααααααααααααααΌαααΆαααα αααα’αα‘α»ααααααααΎαααΎα
ααΆαααΆααααααααααΆαα’αα»ααααααααΆαααααααααα αααα½α SQL αα αααα ααααα αααα½α LINQ ααΊ:
- ααΆαααααα½αααααα½αααααααααΆαααααΎαααΎααα·ααααααααΆααααΌααα αααα»αααααΎαα½αα
- αααα½ααααα»αααΌαααΌα ααααΆ ααααα ααΈαααα»αααΆααα αααααΆαα’αΆααα·ααααααααααα·αα αΆαααΆα αα αααΎαα
- αααα»ααααααααααααα α»αααΆαααΆα (α‘αΌααΈαα "αα·α" αα·α "α¬") - αα·α ΠΈ ORααααα½ααααα αΌααα αααα»ααααααααααααααα»αααααΆα ααΆαα±ααααΆααα·ααααααΆαααααα·ααΈαααααΎαααααα·αααααΆααααααΆααααααααααααααα·ααααααΆα ααααααααααααααααΆααααΆαα αΆαααΆα α ααΈαααα»αα αΆααααααΎαααααααααααΆααααΉααααααααααα ααααα (INDEX ααααα) ααΆααααα»ααααααααα
- ααΆαβααΆααβαααα»αβαααα βααβαααα½αβααβααααΎβα±ααβααΆαβαααβααΆαβαααα αΆβααααΆααβααΆααα ααα ααααΈαααααααΆααα SQL αα·αααΆααα·ααΆααααααααΆααααα½ααα ααΎαααααααα’αααα’αα·αααααα αα·α ααΆααΆ
αα·ααΈααΆααααααααααΎαααααα·αααααΆα
α₯α‘αΌααααααΌαααααΆααααΈαααααααΆαααα αα·ααΈααΆααααααααααΎαααααα·αααααΆαα
1) ααΆαααααΎαα·αα·αααααααααα
ααΆααΆααΆαααα’αααα»ααααα»αααΆααα·α αΆαααΆααΎααααααα ααΎααΆααΆαααααΎαααΎααα αααααααΆααΉαααΆαααααα½αααΆααααΌαααααΌαααΆααααααΎαα‘αΎααα»ααα·αααΆααΆαααααΆαααα½α α¬ααΈα (αααααα·ααΈ-ααα»ααα-ααααα·ααααα·ααΆα) αα·αααΆαα½ααααα»ααααααααααααααααΆα (IsClosed, Canceled, Enabled, Status)α ααΆααΆαααΆααααααΆααααΆαααααα»αααΆααααααΎαααααααααααααααααααααΆααααααΌαααααΆααααααα
αααααααααΆααααααα ααα»αααα αααααααΎαααΎαααΆαααΆαααααααααααααΆαααααΆααααΌααααα»ααααααΆααααα‘αααααα·αα αααααααα½αα
α§ααΆα ααα ααΎαααΆααααααα·ααΈα ααα½α 500000α ααααααΆαααΆαααααααΆααααααα·ααΈαααααα ααα½α 2000 ααα»αααααα αααααΆαααααα·αα·αααααααααΆαααααΎαααΎαααααΉαααααΌαααΉααα½αααααααααααΎαααΈ INDEX ααααα αα ααΎααΆααΆααααα½α α αΎαααΉαα’αα»ααααΆαα±ααα’αααααααΎαααΎααα·ααααααααΆααααΆαααΆαααα ααααΆαααααα·αα·ααααααααα·ααααααΆα αααααα
ααααα ααααααα·αα·ααααα’αΆα ααααΌαααΆααααααα’ααααααααΆαααΆααααααΆαααααα»ααααααΆααααΆααα·ααΆααααααΆααααα½α α¬αααααΌααααα·αα·ααΎααααααααα αααΆαααΈααααααΎ MS SQL:
αα·ααααααααΎαααΆααα’ααααΆαααααααΆαα’αααΈαα·αα·αααααααααΆαα ααΎαααααααααααααααααα α
αααααΆαααΆαααΆααααα αα·αα·αααα αα·αααααΆαααααααΆαα ααΆααΏααααΆαα·ααΈααΆααααααααα»αααΆαααααα»ααααααααΆααααΉααααα·ααΆαααααΆαααααααα·αααΆαααα’α αααα½α LINQ ΠΈ αααα½α SQL.
ααΌα αααααΆαα’αα»ααααααααααα ααααΈαα·ααααα αΆα ααΆααΏααααΆααΆαααΆααααααΆαααααααΆααα’αΆααΈααααααααα»αααΆαα’αα»ααααααααααα’αΆααΈαααααααΆααααααααΆαααααααΆααααΆααα ααΌα ααααα αΎα ααααΎαααααααααααΌαααΆαααααααα ααααααΆαααααααααααααΎααααΆαααααααΆααα
αααααα ααα»αααα½αααααα αααααΆαα’αααααααΎααααΆαααα·ααααααααααΌαααΆααα·ααααααα α»αααααααααα»α α αΎαααΆαααααα·ααααα’αΆα ααα½αααααΆαααααΆαααααΎαααααα ααα»α αααααΆααα’αααααααΎααααΆααα
αα·ααΈααΆααααααααα’αα»ααααΆαα±αααααααααΆααααααΌαααΆαα’αΆααΈααααα ααα»αααααα ααΈαααα»αααΆαααααααααααΎαααΆαααααααααααααααααΆααααααααΆαααααααααΆαααααααααααααΆαα αααααααα αΆα
ααΆαααα½αα±ααα αα αΆααααααααΆαα αααα»αααααΎαααΆαααααΆαααααααααα·αα·ααααα αΆαααΆα αααΎααααΈααααααααΆααααααααααα MS SQL ααΆααααααΎαααααα·αααααΆαα’αΆα αα·αααααΉαααααΌα αα½αααΆαααα αααααααααααααααΆααααααα
- ααααα·αααΎααΆααα·αα·αααααααααΆααααα»αααΆαααααααααααΆαα½α α αΎα
- ααααα·αααΎβααΆαβαααα»αβααΆααΆαβαα·αβα’αΆα
βααααΌαβααΆαβααααΎβαα·αα·ααααβαααβααΆαβααβααΆαβααΉααααα·αβααΆαβααααΎβαα·αα·αααα (αα·αααααΆβαααα’α·αβααααααβααα
αα ααΈααα ).
2) ααΆααααα αΌααα»ααααααααα ααΆαα»αααααααααααΈαα½α
αααααααααΆααα½αα ααα½αααΈααΆααΆααα½α ααααααααΎααΆααΌαααααΆααααααΆαααααα»ααααααααααα α’αΆα ααααΌαααΆααααα½ααααααΆαααααΆαααΆαααααΈαα½αα
αααααΆααΆααα·αααΆαα·ααααααααΆααααΆαααααΆαααΆα αααααΆααααααΆααΆαααααααααΈα α¬α ααα½ααααα
α§ααΆα ααα:
IsClosed = 0 αα·α Canceled = 0 αα·α Enabled = 0 ααααΌαααΆααααα½αααα ααααΆαααΆα = α’.
αααααΊααΆααααααααααα»αααααααααααΆαααΆαα ααα½ααααααααΌαααΆαααααΆα ααΎααααΈααΆααΆααΆααααΆαααΆαααΆαααααααααΌαααΆααααα αΌααααα»αααΆααΆαα αααααΆαα αα»αααααααααααΈαααααααΌαααΆαααααΎαα·αα·ααααα
αααβααΆβαααααβααααΆαβααΌαααααΆαβαααααΆααβαααα αΆβααΆαβα’αα»αααα αααααβααΎαβα αΌαβααααΎβαα·ααααααβαααβαα·αβα αΆαααΆα αβααααΆα
3) αααααΆαααααα·αααααΆα
ααΆα’αα»αααα αααα»α αααα½α LINQ ααΆααΆαααααααα’αΆαααα CTE αα·αα’αααααΆααΆααα·αα’αΆα ααααΎαααααααΆααααΆαααα
ααααααΆαααΆααααα ααΆααα·ααΈαα½αααααααααααΎααααΈαααααΎαααααα·αααααΆααααααΆααααααΈααα - αα·αααααΆααααααΆαααααΎαα·αα·ααααα
αααα»ααααααααα (ααΈα§ααΆα αααααΆαααΎ) IsClosed = 0 αα·α Canceled = 0 αα·α Enabled = 0 (α¬αααα»αααααααααααααααααααααΆαααααααα) ααααΆαααΆαααααΎαααααα’αα½ααααα»αααΆαααααΎααααΆαααα½αααΆαα αααα»ααα·αααααΆααααααΆαααααΎαα·αα·αααα ααααααααΆαα»ααα·ααααααααΌα αααΈαααα»ααααα½αα
ααα»ααααααΆαααΆαααΉααααα·ααα½αα ααα½ααα ααααααααΎααα·αααααΆαα
- ααΆαααααΎααααΆαααα subqueries, clauses αα·ααααααα αα½αααααααΌαααΆααααα½ααααααΆαααααΎααααΆαα α αΌααα½α
- α’ααααα·αα’αΆα ααααΎααααααααΆαααα αα ααΈα, αα ααΈαααΆααα’αα, ααααΈβααΎαααα, α’αα·αααΊαα·α
- α’ααααα·αα’αΆα ααααΎααΆαααααΆα αα·αααααΆααΆααΆαααΆαααα αααααΎα
- αα·αααΆαααααααΆαααααΎααΆαααΆαα½αααααααα
- ααΆαα·αα’αΆα αα αα½α αααααα»αααΆααααα αΆααα·αααααααααα»ααα·αααααΆααα½αααΈααΆααΆαααααααααααΆ
ααΆααΆααΆαααααΆαααααα»αααΆαα αα αΆαααΆα’ααααααααααααα·αααααΆααααααΆαααααΎααααΆαααα·αααααΆααααααΆαααααΎαα·αα·ααααα’αΆα αααααα ααΆαααααΆααααααΆαααααΎαα·αα·ααααααΆααα»αααααα
ααα»αααααα αααα α αα·αααααΆα αα·αα·ααααααΆαααααα’αΆα ααΉααα·αααααΌαααΆαααααΎ α αΎαααΎααααΈααααΎαα½αααΆα±ααα αααΆααααΆαα α’αααααααΌααααααααΆαα ααΆαα½α (NOEXPAND).
α αΆααααΆααααΈαα αααα»α αααα½α LINQ ααΆαα·αα’αΆα αα αα½α αααααα»αααΆααααααααααααΆααααα½ααααα»αααΆααΆα ααΌα ααααα’αααααααΌααααααΎαααααΆααα½ααααααααα - "αα»α" ααααααααααΆααααααα
CREATE VIEW ΠΠΠ―_ΠΏΡΠ΅Π΄ΡΡΠ°Π²Π»Π΅Π½ΠΈΡ AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);
4) ααΆαααααΎααααΆαααα»αααΆαααΆααΆα
ααΆααΉαααΆαααα αααα»α αααα½α LINQ αααα»ααααααααα½ααα α¬αααα»ααααααααΎαα·αααααΆααααααΆααα ααΆαααααααααααα»αααααΆααααααΎαααΆαααα½αα α»ααααααααΆαα½αααΉααα ααΆααααααααααααα·ααααα·αααααα»αααααΆα αα·ααααααααααα»αα
α’αααααααααααααααΆαααααααΆαααααΎααααΆαααα»αααΆαααΆααΆααααα»α αααα½α LINQ:
- αααααααΆα ααΌα
ααΆαα
αααα»αααααΈαααα·αααααΆα αααααααΌαααααΎ αα·ααααααΆααααΆααααα»αα½α ααα»ααααα’αααα’αΆα
ααααααΆαααααα»ααααααΆαααΆαααααααααα
αΌαα
ααΈαα»αααΆα (@param1, @param2 ... )
ααΆαααααα ααααΌαα·αααααααααα’αΆα ααααααααΆαα’αΆα αααααα ααΆαα - αα
αααα»αααααΈααααΆαααααΎααααΆαααα»αααΆαααΆααΆα αα·αααΆαααΆαααΉααααααΉαααααΆααααΌα
αα
αααα»αααααΈαααα·αααααΆααα·αα·αααααααααΆααα·αααααΆααΆαααΎααα
- ααΆαααααΆαααΆααΆαα
ααΆααααααΆα ααΈα α’αααβαα·αβα’αΆα βαααααΆααβαα·αα·ααααβααΆβαααβαα½αβααααΌαβααΆαβααααΎ αα·αβαααααβααααα·αβα―ααβαα·ααααααβαααβαα½αα
ααα»αααααα»αααΆαααΆααααααααΆαααΆαααααα
ααΆαα½αααΉααα»αααΆα α’αααα’αΆα αααααα ααΆαααΌααααααΆααααα½αααααα·ααααα·ααααααααααΎααΆα αααα αααΆαααααααΆααααααΎααΆαααΆαα½αααααααααα αα·αααααα·αααΆαα―αααα·ααααααααααΌαααΆααααααα - ααΆαααααΎααααΆαααα»αααΆαα’αα»ααααΆαα±αα αα
αααα»αααΆαααααααααααΆαα½αααΉααα·αααααΆααααααΆαααααΎαα·αα·αααα ααΎααααΈααα½αααΆαα
- αααααα·ααααΆααααΌαα·αααααααααα»αααααΆα (ααΌααααΈααααααΎααααα·ααα»α)
- ααΆααααα·ααααααααΈααΆααΆαααααααααααΆααΆα αααΎαα
- ααΆαααααΎααααΆαα αα ααΈα ΠΈ αα·ααααααα
- ααΆαααααΆαααΆααΆαα
- ααΆααααααααΌα αααααΎα ααΆαβααααααααβααααΆααβααΆααβαα
βαααβαααβααΎαβααααΌαβααΆαβααΎααααΈβαααααβα±ααβααΌαβααΆαβαααα½αβαα·αα·αααβααααβααααΆα OPTION(MAXDOP N)ααααΆαααααααααΆαααααα·ααααα·αααα½αα α§ααΆα ααα:
- α’αααα’αΆα αααααΆααααΆααααααΎαα‘αΎααα·ααααααααααααααααΆααααα½α αααααΎα (αααααα‘αΎααα·α)
- α’αααα’αΆα αααααΆααααΆααΎααααΌαααααααααααΆααααα½αα±ααααααΎααααΆααα αΌααα½ααααααΆααααααΆαααα αααα»ααααα½α αααααΎα (αααααααΆααααααΆαα·α)
ααααααΆααααα’α·αααααααα’αααΈ αααααΎα ααΆααα·αααααΆ
αα ααΈααα . - ααΆαααααΎααααΆααααααααα·ααααααααΌα
α
ααα’αα αα·αα
αΆαααΆα
ααααα»αα
αα·αα αΆαααΆα ααααααΆαα»ααααα»ααα·αααααααααα αααα»αααααΆαααααααΆαααα (ααΌα ααααΈαααααΆαααααΎαα·αα·αααα) αααα’ααααα ααααααΌααααααα·ααααααααΆααααΆαααΆααααααα
α§ααΆα αααααΆαααΆααΆααααααααα ααΈαααααα ααΆαααΈααααΌαααΆαααααΎ (a, α, α).ααΆαααααααΆ ααααΎααΆααα’ααααΆαααααααααααα a = 0 αα·α b = 0.
αααβαααΆαβααΆβααΆαβααααΎβαα»αβαααααΆααβααΆαβαααβ c α’αααα αααΎααααα
α’αα»ααααΆαα±αααααααααα a = 0 αα·α b = 0 ααΆαα·αααΆαα½αααΎααααα»αααΆαααααααααααααααααΆαααααααα ααΆαααααααααΆααΆααααΆαα ααα»αααααααααααααα ααΎ Ρ ααααα½αβααΆαβααααΎαααΎαβα α»αβαααβαα½αβααβαααααααααΆα
αα ααΈααααα»αααΆαααΆααΆαα’αΆα ααΆαααααΎαααα’ααΆαα
ααΌα ααααΆαααααααα αα»αααΆαααΆααΆαααΊα’αΆα ααααααααΆαααΆαα αααΎαααΆααα»α αα·αααααααααΆαααα»ααααααααΆααααα·ααααα·α
α§αα ααα
ααΌααααα‘ααααΎαααΆαα’αα»ααααα§ααΆα ααααααααααΎααΌαααααΆααα·αααααααααα½αααΆα§ααΆα αααα
ααΆαααααΎαα½αα ααααΎαααΎαααααα½ααααα αΌαααααΆααΌαααΆααΆαααΆα αααΎα αα·αααααΎαα·αααααΆααααα½α (OperativeQuestions) ααααααα»ααααααααΆααααααααααΌαααΆααααα½ααα·αα·αααααΆαα’ααΈααα (ααΆαααα αα·ααααααα) αα "αααα½αααααα·ααααα·ααΆα"α
ααααΎααα 1
(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM [dbo].[Questions] AS [Extent1]
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id],
[Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId],
[Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4]
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2]
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[OperativeQuestions] AS [Extent5]
WHERE (([Extent5].[Email] = @p__linq__0) OR (([Extent5].[Email] IS NULL)
AND (@p__linq__0 IS NULL))) AND ([Extent5].[Id] = [Extent1].[Id])
));
αα·αααααΆαβααΆαβαα ααΆααααααααβαααα»αααααΆαα ααΆβααΆαβααΆαβααααΆααβαααα½αβαα αα·αβααααΎβααΆαβαααααα DISTINCTαααααΆααΌαα ααΊααΆααααα·ααααα·ααΆααααααΉαααα’ααααΎααααΆαααααα»αααα·αααα
ααααΌαα½αααΈαααα½αααααα·ααααα·ααΆαααΊαααα αααα½ααααΊααααααααααΆα
αααα αΆα ααααααΆαα½ααααα½ααααααΊααΆαααααΆαααααααααααΆααΈαααα½αααΆααααα αααα½αααααΆααααα»αααααΌαααΆαααααα·ααααα·ααΎαα·αααααΆα [OperativeQuestions] ααααα½ααααααααΆαα [Email] = @p__linq__0 α’αα»ααααΆαα±ααααΎααααααααΆαααααΎαααΎααααααα (ααΆαααα αα·ααααααα) αα αΌααααααΆαααααααααααααΆα
α αΎαααΆα αΆααααΌα ααΆααΆ αααα½ααααα½αααααααΆαααααααααΆααααααα [Email] = @p__linq__0 α αΎααααααΆαααααααααααααΆααΈαααΈααααΆααααααα½αααααααΌαααΆαααααΆααααα Id ααΆαα½ααααα½α α αΎααααα½αααΉαααΏαα
ααΆααα·α ααΆαααΆααααααΆααααΆαααααΆαααααααααααΆααΆαααΆααα’ααα αα·αα·αααααΎαααΆαααααΎααααααααααα½αααααααααΆααααΆαα½α Id ααΈ OperativeQuestions αα·αααΆαααααααΆαα’ααΈαααα
ααΆαααΆααα·α ααααΎαααααααΎαααΆαααΆαα½ααααααααααΆ OperativeQuestions ααΆαααααΊαααΆααα’αα ααα»ααααααΆααααα·ααααααα αααΆααα’αΆααααααααα»ααααααααααααΌαααΆαααΆααααα’ααΈααααα
αααα½αααααα·ααααα·ααΆαααΎαα’αααααα
ααααΎααα 2
CREATE VIEW [dbo].[OperativeQuestions]
AS
SELECT DISTINCT Q.Id, USR.email AS Email
FROM [dbo].Questions AS Q INNER JOIN
[dbo].ProcessUserAccesses AS BPU ON BPU.ProcessId = CQ.Process_Id
OUTER APPLY
(SELECT 1 AS HasNoObjects
WHERE NOT EXISTS
(SELECT 1
FROM [dbo].ObjectUserAccesses AS BOU
WHERE BOU.ProcessUserAccessId = BPU.[Id] AND BOU.[To] IS NULL)
) AS BO INNER JOIN
[dbo].Users AS USR ON USR.Id = BPU.UserId
WHERE CQ.[Exp] = 0 AND CQ.AnswerId IS NULL AND BPU.[To] IS NULL
AND (BO.HasNoObjects = 1 OR
EXISTS (SELECT 1
FROM [dbo].ObjectUserAccesses AS BOU INNER JOIN
[dbo].ObjectQuestions AS QBO
ON QBO.[Object_Id] =BOU.ObjectId
WHERE BOU.ProcessUserAccessId = BPU.Id
AND BOU.[To] IS NULL AND QBO.Question_Id = CQ.Id));
ααΆαααΌααααααΈαα·αααααΆαααααΌααα αααα»α DbContext (EF Core 2)
public class QuestionsDbContext : DbContext
{
//...
public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
//...
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
}
}
αααα½α LINQ ααααΌα
var businessObjectsData = await context
.OperativeQuestions
.Where(x => x.Email == Email)
.Include(x => x.Question)
.Select(x => x.Question)
.SelectMany(x => x.ObjectQuestions,
(x, bo) => new
{
Id = x.Id,
ObjectId = bo.Object.Id,
ObjectTypeId = bo.Object.ObjectType.Id,
ObjectTypeName = bo.Object.ObjectType.Name,
ObjectExternalId = bo.Object.ExternalId
})
.ToListAsync();
αααα»αααααΈαα·αααααα ααΎααααα»ααα·α αΆαααΆαααααααααΆαα αααααααα αΆααααααααααΆαααΆαααααΆααααααΌααα ααΆαααααααα ααααα·ααααα αΆαααΆααΆαααΆα ααααα‘ααααΆαα½αααΉααααααααααααααααα½α ααΆααααα ("αααα½αααααα") αααααΉααααααΌαα±ααααΆαααααααΆααααααΆαααααααααΆααΆαα½αααΉααα·αααααα αα·ααααααΆααΆα±ααααΆααααααα .
αααααΈααΆαααααΆαααααααααΆαααααα’ααααα ααΆααααααΎααα½αααααααααααΎααααΈαααααΎαααααα·αααααΆααααα αΆαααα
αααβααααβα ααααβααΊβααΎααααΈβαααααΆβαα»αβααΆαα»βααα [Email] = @p__linq__0 ααΈβαα·αααααΆα OperativeQuestions α
ααααΆααα»αααΆαααΆααΆα [dbo].[OperativeQuestionsUserMail] αα αααα»αααΌαααααΆααα·ααααααα
ααΆααααααΆαααααΎα’ααΈαααααΆαααΆαααΆαααααααααα αΌα ααΎαααα½αααΆαααΆααΆαααααααααα·αα
ααααΎααα 3
CREATE FUNCTION [dbo].[OperativeQuestionsUserMail]
(
@Email nvarchar(4000)
)
RETURNS
@tbl TABLE
(
[Id] uniqueidentifier,
[Email] nvarchar(4000)
)
AS
BEGIN
INSERT INTO @tbl ([Id], [Email])
SELECT Id, @Email
FROM [OperativeQuestions] AS [x] WHERE [x].[Email] = @Email;
RETURN;
END
ααΆαααα‘ααααΆααΆααααααααΆαα½αααΉααα ααΆαααααααααα·αααααααααααΆααααααααΆαα»αα
ααΎααααΈα±αααααα½ααα ααΆαα OperativeQuestionsUserMail ααΆαααΆαααα’αααααΎα αα·αααΆααααααΆααααα½αααααα’αααααΎα αα ααΆααααααααααααΉαααΉαααΊααααΌαααΆαααΆαααΆα αα·ααα·α ααΆααΆααααα‘ααααΆααΆααααα‘αααααα·α...
αααα»αααααΈααα αααα½αααΈ 1 αααααααΌαααΆαααααΌαααΆαααααααααα ααΆαααα½αααΈ 4α
ααααΎααα 4
(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM (
SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] (@p__linq__0)
) AS [Extent0]
INNER JOIN [dbo].[Questions] AS [Extent1] ON([Extent0].Id=[Extent1].Id)
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id], [Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId], [Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4]
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2]
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]);
αα·αααααΆααααααΈ αα·ααα»αααΆααα αααα»α DbContext (EF Core 2)
public class QuestionsDbContext : DbContext
{
//...
public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
//...
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
}
}
public static class FromSqlQueries
{
public static IQueryable<OperativeQuestion> GetByUserEmail(this DbQuery<OperativeQuestion> source, string Email)
=> source.FromSql($"SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] ({Email})");
}
αααα½α LINQ α α»αααααα
var businessObjectsData = await context
.OperativeQuestions
.GetByUserEmail(Email)
.Include(x => x.Question)
.Select(x => x.Question)
.SelectMany(x => x.ObjectQuestions,
(x, bo) => new
{
Id = x.Id,
ObjectId = bo.Object.Id,
ObjectTypeId = bo.Object.ObjectType.Id,
ObjectTypeName = bo.Object.ObjectType.Name,
ObjectExternalId = bo.Object.ExternalId
})
.ToListAsync();
ααααΆαααααααααααΆααααα·ααααα·ααΆαααααΆααα α»αααΈ 200-800 ms αα 2-20 ms ααα αααααΊααΏαααΆααααααα
ααααα·αααΎααΎαααααΆααΆααααα ααααααα½αα±αα 350 ms ααΎαααα½αααΆα 8 ms α
ααΈα’αααααααααααααΆααααααα ααΎαααααα½αααΆαα
- ααΆαααα α»αααΆααΌαα ααααααα»αα’αΆα
- ααΆαααα α»ααααΆαααααΆααααααααααΆαααααΆααααααααΆαα
- ααΆααααααααααααααΆαααααααΆααααΆααααααα ααΆααααααααα’αΆα ααα½αααααΆαα
ααα ααααΈααααα·ααααΆα
ααΆααααααΎαααααα·αααααΆα αα·αααΆααααααααΌαααΆαα α ααΌαααααααΌαααααΆααα·αααααα MS SQL ααΆααααααΆα ααΈα ααΊααΆαααα αΆαααα’αΆα αααααααΆαααΆαα
ααΆαααα α·ααααα»αααΆαα αα·αααΆααααΈαααααΆααααααΆααΆαααΆααααααΆααααΆαααααα»αααΆαααΆααααα
αα ααΎαααααΎαααΆαα
- ααΆα αΆαααΆα αααΎααααΈαα·αα·αααααΎααα·αααααααααααααΎααααΎαααΆα (ααααα αααααααα·αααααααααααΆαααααΎαααΎα)
- α’αα»ααααααΆαααααΎαα·αα·ααααααααΉαααααΌααααα·αααααααααα
- αα·αα·αααααΆαααααΉαααααΌαααααααααααα αΌααα½ααααΆαααΆααΆα
ααΆαααααΎα±αααααααΎαα‘αΎααα·ααααααΆαααααα αΆαα±ααααΎα:
- ααΌαααααΆαααααααΎ αα·αααααααααααααααΎααααΆαα
- ααΆαααααΎα‘αΎααα·αααΌααααα»ααααα½αααααααααααΆ αα·αααΆααα·ααΆαα ααα»α ααααααααααααααααα
- αα αααα»α SSMS α¬ GUI αααααααααααααΆαα αααΆαααΈααααααΎ SQL ααααΎα±αααααα½αααΆαααααΎαα‘αΎα αααα½α SQL (αααα ααααΆααααα»ααα·ααααααααααα·αααααα αααααΎααααα½ααααααααααααααΎααΆααααα»αααα (α’αΆα ααΆαα αααΎα))
- αα ααααΆααααΆαα α»αααααα αααααααΆααΌαααααΆααααααααα αααα½α SQL, αα ααΆαααααααααααα»αααααΌαααΆαααΆααααα‘αΎααα·α αααα½α LINQ
αααααα αααα½α LINQ αα½αααααΆαααααααααΌα ααααΆααααα·ααα αααα»ααα ααΆαααααααααα ααΉαααΆαααα’αααα»ααααααΆαααααα αααα½α SQL ααΈα ααα»α 3 α
ααΆαααΉααα»α
α’ααα»αα
αααΎαα
αααααα·ααααα½αααΆαααΆα
ααααα: www.habr.com