🥇 Ụzọ maka ịkwalite ajụjụ LINQ na C#.NET

Okwu Mmalite

В isiokwu a a tụlere ụfọdụ ụzọ njikarịcha Ajụjụ LINQ.
N'ebe a, anyị na-egosikwa ụfọdụ ụzọ maka njikarịcha koodu metụtara Ajụjụ LINQ.

A maara nke ahụ LINQ(Asụsụ-Integrated Ajụjụ) bụ asụsụ dị mfe ma dịkwa mma maka ịjụ isi iyi data.

А LINQ ka SQL bụ teknụzụ maka ịnweta data na DBMS. Nke a bụ ngwá ọrụ siri ike maka iji data rụọ ọrụ, ebe a na-ewu ajụjụ site na asụsụ nkwupụta, nke a ga-agbanwezi ka ọ bụrụ Ajụjụ SQL ikpo okwu ma zigara na nchekwa data nkesa maka igbu. N'ọnọdụ anyị, site na DBMS anyị pụtara Ihe nkesa MS SQL.

Otú ọ dị, Ajụjụ LINQ Agbanweghị ka ọ bụrụ ndị edere nke ọma Ajụjụ SQL, nke DBA nwere ahụmahụ nwere ike dee ya na nuances niile nke njikarịcha Ajụjụ SQL:

njikọ kacha mma (BỤ) na nzacha nsonaazụ ya (EBE)
ọtụtụ nuances na iji njikọ na ọnọdụ otu
ọtụtụ ọdịiche dị na nnọchi ọnọdụ IN on KWES .R.и Ọ BỤGHỊ NA, <> na KWES .R.
Nchikota rịzọlt nke etiti site na tebụl nwa oge, CTE, mgbanwe tebụl
eji ahịrịokwu (Mgbanwe) na ntuziaka na ihe ngosi tebụl NA (...)
iji echiche indexed dị ka otu n'ime ụzọ isi kpochapụ agụ data na-abaghị uru n'oge nhọrọ

The isi arụmọrụ bottlenecks nke pụta Ajụjụ SQL mgbe a na-achịkọta Ajụjụ LINQ bụ:

nkwado nke usoro nhọrọ data niile n'otu arịrịọ
na-emegharị koodu ahụ, nke na-eduga n'ọtụtụ ọgụgụ data na-enweghị isi
otu ọnọdụ ọtụtụ akụkụ (ezi uche "na" na "ma ọ bụ") - nA и OR, ijikọta n'ime mgbagwoju ọnọdụ, na-eduga na eziokwu na optimizer, na-enwe adabara na-abụghị ụyọkọ indexes maka ndị dị mkpa ubi, n'ikpeazụ na-amalite iṅomi megide clustered index (Nyocha INDEX) site na otu ọnọdụ
akwu miri emi nke subqueries na-eme ka ịkọwapụta nnukwu nsogbu Okwu SQL na nyocha nke atụmatụ ajụjụ n'akụkụ nke ndị mmepe na DBA

Ụzọ kachasị mma

Ugbu a, ka anyị gaa n'ihu na ụzọ kachasị mma.

1) Ntinye ntinye aka

Ọ kachasị mma ịtụle ihe nzacha na tebụl nhọrọ ndị bụ isi, ebe ọ bụ na a na-ewukarị ajụjụ a na gburugburu otu tebụl ma ọ bụ abụọ (ngwa-ọrụ ndị mmadụ) yana usoro ọnọdụ (Echichi, Kagbuo, Kwanyere, Ọnọdụ). Ọ dị mkpa ịmepụta indices kwesịrị ekwesị maka ihe atụ ndị achọpụtara.

Ngwọta a nwere ezi uche mgbe ị na-ahọpụta mpaghara ndị a na-amachi nke eweghachiri eweghachi na ajụjụ a.

Dịka ọmụmaatụ, anyị nwere ngwa 500000. Agbanyeghị, enwere naanị ngwa 2000 na-arụ ọrụ. Mgbe ahụ ndepụta ndeksi ahọpụtara nke ọma ga-azọpụta anyị Nyocha INDEX na tebụl buru ibu ma ga-enye gị ohere ịhọrọ data ngwa ngwa site na ndeksi na-enweghị ụyọkọ.

Ọzọkwa, enwere ike ịchọpụta enweghị ndeksi site na mkpali maka ịtụgharị atụmatụ ajụjụ ma ọ bụ ịnakọta ọnụ ọgụgụ nlele sistemụ. Ihe nkesa MS SQL:

Ihe nlele niile nwere ozi gbasara ndenye aha na-efu, ewezuga ndepụta ntụaka oghere.

Agbanyeghị, index na caching na-abụkarị ụzọ a ga-esi lụso nsonaazụ nke edeghị nke ọma ọgụ Ajụjụ LINQ и Ajụjụ SQL.

Dị ka omume siri ike nke ndụ na-egosi, ọ na-adịkarị mkpa maka azụmahịa iji mejuputa atụmatụ azụmahịa site na oge ụfọdụ. Ya mere, a na-ebufekarị arịrịọ dị arọ na ndabere site na iji caching.

Nke a bụ akụkụ ziri ezi, ebe ọ bụ na onye ọrụ anaghị achọ data kachasị ọhụrụ mgbe niile yana enwere ọkwa nnabata nke interface onye ọrụ.

Usoro a na-enye ohere idozi mkpa azụmahịa, mana n'ikpeazụ na-ebelata arụmọrụ nke usoro ozi site na igbu oge ngwọta maka nsogbu.

Ọ dịkwa mma icheta na n'ime usoro nke ịchọ ihe ntanetị dị mkpa iji gbakwunye, aro MS SQL njikarịcha nwere ike ezighi ezi, gụnyere n'okpuru ọnọdụ ndị a:

ma ọ bụrụ na enweelarị ndektị nwere otu ụdị ubi
Ọ bụrụ na enweghị ike ịdepụta mpaghara dị na tebụl n'ihi mmachi indexing (akọwapụtara n'ụzọ zuru ezu ebe a).

2) Ijikọta àgwà n'ime otu àgwà ọhụrụ

Mgbe ụfọdụ, enwere ike dochie ụfọdụ ubi sitere na otu tebụl, nke na-eje ozi dị ka ihe ndabere maka otu ọnọdụ, site n'iwebata otu ubi ọhụrụ.

Nke a bụ eziokwu karịsịa maka ọkwa ọkwa, nke na-abụkarị bit ma ọ bụ integer n'ụdị.

Ihe nlele:

Ismechie = 0 NA Kagbuo = 0 NA Kwanyere = 0 eji dochie ya Ọnọdụ = 1.

Nke a bụ ebe ewebata njirimara ọnọdụ integer iji hụ na ndị a na-ejupụta na tebụl. Na-esote, a na-edepụta njirimara ọhụrụ a.

Nke a bụ isi ngwọta maka nsogbu arụmọrụ, n'ihi na anyị na-enweta data na-enweghị mgbako na-enweghị isi.

3) Nhazi nke echiche

O di nwute, n'ime Ajụjụ LINQ Enweghị ike iji tebụl nwa oge, CTE na mgbanwe tebụl mee ihe ozugbo.

Otú ọ dị, e nwere ụzọ ọzọ iji bulie maka ikpe a - indexed views.

Otu ọnọdụ (site na ihe atụ dị n'elu) Ismechie = 0 NA Kagbuo = 0 NA Kwanyere = 0 (ma ọ bụ usoro nke ọnọdụ ndị ọzọ yiri ya) na-aghọ ezigbo nhọrọ iji ha mee ihe na nlele indexed, na-echekwa ntakịrị data site na nnukwu nhazi.

Mana enwere ọtụtụ mmachi mgbe ị na-egosipụta echiche:

ojiji nke subqueries, nkebiokwu KWES .R. kwesiri iji dochie anya ya BỤ
ị nweghị ike iji ahịrịokwu Union, Union niile, NGANYA, AJTERJTER
Ị nweghị ike iji akara ngosi na nkebiokwu Mgbanwe
enweghị ike ịrụ ọrụ na cycles
Ọ gaghị ekwe omume igosipụta data n'otu echiche sitere na tebụl dị iche iche

Ọ dị mkpa icheta na ezigbo uru nke iji nlele indexed nwere ike nweta naanị site n'ịdepụta ya n'ezie.

Mana mgbe ị na-akpọ nlele, enweghị ike iji ndeksi ndị a, yana iji ya mee ihe nke ọma, ị ga-ezipụtarịrị. NA (NOEXPAND).

Kemgbe Ajụjụ LINQ Ọ gaghị ekwe omume ịkọwa ihe ngosi tebụl, yabụ ị ga-emepụta ihe nnọchianya ọzọ - "ihe mkpuchi" nke ụdị a:

CREATE VIEW ИМЯ_представления AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Iji tebụl ọrụ

Ọtụtụ mgbe na Ajụjụ LINQ Nnukwu ngọngọ nke subqueries ma ọ bụ ngọngọ na-eji echiche nwere usoro dị mgbagwoju anya na-etolite ajụjụ ikpeazụ nwere usoro ogbugbu dị mgbagwoju anya na nke kachasị mma.

Uru dị mkpa nke iji ọrụ tebụl na Ajụjụ LINQ:

Ikike, dị ka ọ dị n'ihe gbasara echiche, a ga-eji ma kọwaa ya dị ka ihe, mana ị nwere ike ịfefe usoro ntinye:
SITE n'ọrụ (@param1, @param2 ...)
N'ihi ya, enwere ike nweta nlele data na-agbanwe agbanwe
N'ihe banyere iji ọrụ tebụl, ọ nweghị mmachi siri ike dị ka ọ dị n'ihe gbasara echiche indexed akọwara n'elu:
1. Atụmatụ tebụl:
  site LINQ Ị nweghị ike ịkọwapụta ndepụta ndeksi kwesịrị iji wee chọpụta ọkwa ikewapụ data mgbe ị na-ajụ ajụjụ.
  Mana ọrụ ahụ nwere ikike ndị a.
  Site na ọrụ ahụ, ị nwere ike nweta atụmatụ ajụjụ igbu oge niile, ebe akọwapụtara iwu maka ịrụ ọrụ na indexes na ọkwa kewapụ data.
2. Iji ọrụ ahụ na-enye ohere, ma e jiri ya tụnyere nlele indexed, iji nweta:
  - mgbanaka nlele data mgbagwoju anya (ọbụlagodi iji loops)
  - na-enweta data site na tebụl dị iche iche
  - eji Union и KWES .R.
Onyinye Mgbanwe bara uru nke ukwuu mgbe anyị kwesịrị ịnye njikwa concurrency Nhọrọ (MAXDOP N), usoro nke atụmatụ mmezu ajụjụ. Ọmụmaatụ:
- ị nwere ike kọwapụta mmegharị mmanye nke atụmatụ ajụjụ Nhọrọ (Ikpokọta)
- ị nwere ike kọwapụta ma ị ga-amanye atụmatụ ajụjụ ka o jiri usoro nsonye akọwapụtara na ajụjụ a Nhọrọ (MAnye iwu)
Nkọwa ndị ọzọ gbasara Mgbanwe kọwara ebe a.
Iji iberi data kacha dị warara na nke achọrọ:
Ọ dịghị mkpa na-echekwa nnukwu data setịpụ na caches (dị ka ọ dị na indexed echiche), site na nke ị ka chọrọ nzacha data site parameter.
Dịka ọmụmaatụ, enwere tebụl nke nzacha ya EBE A na-eji ubi atọ eme ihe (a, b, c).

N'otu oge, arịrịọ niile nwere ọnọdụ mgbe niile a = 0 na b = 0.

Otú ọ dị, arịrịọ maka ubi c ọzọ agbanwe.

Ka ọnọdụ ahụ a = 0 na b = 0 Ọ na-enyere anyị aka n'ezie amachi achọrọ n'ihi setịpụ ka puku kwuru puku ndekọ, ma ọnọdụ na с na-ebelata nhọrọ ahụ ruo otu narị ndekọ.

N'ebe a, ọrụ tebụl nwere ike ịbụ nhọrọ ka mma.

Ọzọkwa, ọrụ tebụl bụ ihe a na-ahụ anya ma na-agbanwe agbanwe na oge igbu.

atụ

Ka anyị lelee mmejuputa ihe atụ site na iji nchekwa data ajụjụ dịka ọmụmaatụ.

Enwere arịrịọ Họrọ, nke na-ejikọta ọtụtụ tebụl ma jiri otu nlele (OperativeQuestions), nke a na-enyocha njikọ site na email (site na KWES .R.) gaa na "Ajụjụ Ọrụ":

Arịrịọ No. 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])
));

Echiche ahụ nwere usoro dị mgbagwoju anya: ọ nwere njikọ subquery na-eji nhazi Dee, nke n'ozuzu bụ a pụtara ihe onwunwe na-akpa ike ọrụ.

Ihe nlele sitere na ajụjụ Operative bụ ihe ndekọ ndekọ puku iri.

Isi nsogbu dị na ajụjụ a bụ na maka ndekọ sitere na ajụjụ mpụta, a na-eme subquery dị n'ime na nlele [OperativeQuestions], nke kwesịrị maka [Email] = @p__linq__0 nyere anyị ohere ịmachi nhọrọ mmepụta (site na KWES .R.) ruo narị narị ndekọ.

Ọ nwekwara ike ịdị ka subquery kwesịrị gbakọọ ndekọ ahụ otu ugboro site na [Email] = @p__linq__0, mgbe ahụ, a ga-ejikọta di na nwunye narị ndekọ ndị a site na Id na ajụjụ, ajụjụ a ga-adị ngwa ngwa.

N'ezie, enwere njikọ n'usoro nke tebụl niile: ịlele akwụkwọ ozi nke ajụjụ Id na Id sitere na ajụjụ ọrụ, yana nzacha site na Email.

N'ezie, arịrịọ a na-arụ ọrụ na iri puku kwuru iri puku OperativeQuestions ndekọ, mana naanị data nke mmasị ka achọrọ site na Email.

Ajuju na-arụ ọrụ lelee ederede:

Arịrịọ No. 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));

Maapụ nlele mbụ na 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");
    }
}

Ajụjụ LINQ mbụ

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();

N'okwu a, anyị na-atụle ihe ngwọta maka nsogbu a na-enweghị mgbanwe akụrụngwa, na-enweghị iwebata tebụl dị iche na nsonaazụ njikere ("Active Queries"), nke ga-achọ usoro iji mejupụta ya na data ma na-eme ka ọ dị ọhụrụ. .

Ọ bụ ezie na nke a bụ ezigbo ngwọta, enwere nhọrọ ọzọ iji bulie nsogbu a.

Ebumnuche bụ isi bụ iji chekwaa ndenye site na [Email] = @p__linq__0 site na nlele OperativeQuestions.

Webata ọrụ tebụl [dbo].[OperativeQuestionsUserMail] n'ime nchekwa data.

Site na izipu ozi-e dị ka ihe ntinye, anyị na-eweghachite tebụl ụkpụrụ:

Arịrịọ No. 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

Nke a na-eweghachite tebụl ụkpụrụ nwere usoro data eburu ụzọ kọwaa.

Ka ajụjụ ndị OperativeQuestionsUserMail wee bụrụ nke kacha mma ma nwee atụmatụ ajụjụ kacha mma, a chọrọ usoro siri ike, ọ bụghịkwa Tebụl na-eweghachi dịka nlọghachi...

N'okwu a, a na-atụgharị ajụjụ 1 achọrọ ka ọ bụrụ ajụjụ 4:

Arịrịọ No. 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]);

Echiche na ọrụ nke eserese na 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})");
}

Ajụjụ LINQ ikpeazụ

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();

Usoro nke igbu oge agbadala site na 200-800 ms, ruo 2-20 ms, wdg, ya bụ ugboro iri ngwa ngwa.

Ọ bụrụ na anyị were ya karịa nkezi, mgbe ahụ kama 350 ms anyị nwetara 8 ms.

Site na uru doro anya anyị na-enwetakwa:

Mbelata ibu ọgụgụ n'ozuzu,
mbelata dị ukwuu na ohere nke igbochi
na-ebelata oge nkwụsị nkezi na ụkpụrụ ndị a na-anabata

nkwubi

Nkwalite na nhazigharị nke oku nchekwa data MS SQL site LINQ bụ nsogbu enwere ike idozi.

Nlebara anya na nkwụsi ike dị oke mkpa na ọrụ a.

Na mmalite nke usoro:

ọ dị mkpa ịlele data nke arịrịọ a na-arụ ọrụ (ụkpụrụ, ụdị data ahọpụtara)
rụpụta ezigbo indexing nke data a
lelee izi ezi nke ọnọdụ njikọ n'etiti tebụl

Ntugharị nkwalite na-esote na-ekpughe:

ndabere nke arịrịọ ma kọwaa isi ihe nzacha arịrịọ
na-emeghachi ihe mgbochi ajụjụ ndị yiri ya na nyochaa njikọ nke ọnọdụ
na SSMS ma ọ bụ GUI ọzọ maka SQL Server optimizes onwe ya Ajụjụ SQL (na-ekenye nchekwa data etiti, jiri nchekwa a wulite ajụjụ a ga-esi na ya pụta (enwere ike inwe ọtụtụ))
na ikpeazụ ogbo, na-ewere dị ka ndabere nke pụta Ajụjụ SQL, a na-ewughachi ihe owuwu ahụ LINQ ajụjụ

Ihe si na ya pụta LINQ ajụjụ kwesịrị ịdị ka n'usoro ya ka ọ kacha mma Ajụjụ SQL site na isi nke 3.

Nkwenye

Ọtụtụ ekele maka ndị ọrụ ibe workgemws и alex_ozr site na ụlọ ọrụ ahụ Fortis maka enyemaka n'ịkwado ihe a.

isi: www.habr.com

Ụzọ maka ịkwalite ajụjụ LINQ na C#.NET