🥇Nga tikanga mo te arotau i nga patai LINQ i C#.NET

Whakataki

В tenei tuhinga i whakaarohia etahi tikanga arotautanga Uiui LINQ.
I konei ka whakaatuhia e matou etahi atu huarahi ki te arotautanga waehere e pa ana ki Uiui LINQ.

E mohiotia ana LINQHe reo ngawari, he watea hoki mo te uiui i tetahi puna raraunga.

А LINQ ki SQL he hangarau mo te uru ki nga raraunga i roto i te DBMS. He taputapu kaha tenei mo te mahi me nga raraunga, ka hangaia nga patai ma te reo korero, ka huri hei Uiui SQL tūāpapa ka tukuna ki te tūmau pātengi raraunga mō te mahi. I roto i to maatau, na DBMS te tikanga MS SQL Tūmau.

Engari, Uiui LINQ karekau e huri ki te tuhi tino pai Uiui SQL, ka taea e te DBA mohio te tuhi me nga ahuatanga katoa o te arotautanga Uiui SQL:

nga hononga tino pai (Hono) me te tātari i ngā hua (HEA)
he maha nga ahuatanga o te whakamahi i nga hononga me nga tikanga roopu
he maha nga rereketanga o nga tikanga whakakapi IN i runga i HE WHAKAMAHIи KAUA I ROTO, <> runga HE WHAKAMAHI
keteroki takawaenga o nga hua ma nga ripanga rangitahi, CTE, taurangi ripanga
te whakamahi i te rerenga korero (OPTION) me nga tohutohu me nga tohu ripanga MAU (...)
te whakamahi i nga tirohanga kua taurangitia hei huarahi ki te whakakore i nga panui raraunga nui i te wa e whiriwhiri ana

Ko nga mahi matua o nga kohanga o te hua Uiui SQL i te wa e whakahiato ana Uiui LINQ :

te whakakotahitanga o te tikanga whiriwhiri raraunga katoa i roto i te tono kotahi
te taarua i nga poraka orite o te waehere, i te mutunga ka arahi ki te maha o nga panui raraunga koretake
nga roopu o nga tikanga waahanga-maha (arorau "me" me "ranei") - ME и OR, ka whakakotahi ki nga ahuatanga uaua, ka puta ko te kaihoroi, he pai nga tohu-kore-kore mo nga mara e tika ana, i te mutunga ka timata ki te matawai ki te taurangi kua rapoi (KAUPAPA KAUPAPA) na nga roopu tikanga
Ko te kohanga hohonu o nga riipene ka tino raru te porotiti Nga korero SQL me te tātaritanga o te mahere patai a nga kaihanga me te DBA

Nga tikanga arotautanga

Inaianei me neke tika ki nga tikanga arotautanga.

1) Taupūtanga taapiri

He pai ake te whakaaro ki nga whiriwhiringa i runga i nga ripanga kowhiringa matua, na te mea he maha nga wa ka hangaia te patai katoa ki te taha o tetahi, e rua ranei nga ripanga matua (tono-tangata-mahi) me te huinga tikanga paerewa (Kua Katia, Kua Whakakorea, Kua Whakahohe, Tūnga). He mea nui ki te hanga taurangi tika mo nga tauira kua tautuhia.

Ka whai tikanga tenei otinga ina kowhiria enei mara ka tino whakawhāitihia te huinga kua whakahokia ki te patai.

Hei tauira, 500000 a matou tono. Heoi, 2000 noa nga tono kaha. Na te taupū kua tohua tika ka whakaora ia tatou mai KAUPAPA KAUPAPA i runga i te tepu nui ka taea e koe te kowhiri tere i nga raraunga na roto i te taurangi-kore.

Ano hoki, ko te kore o nga taurangi ka taea te tautuhi ma nga akiaki mo te tarai i nga mahere patai me te kohi tatauranga tirohanga punaha. MS SQL Tūmau:

Ko nga raraunga tirohanga katoa kei roto nga korero mo nga tohu kua ngaro, haunga nga tohu mokowā.

Heoi, ko nga tohu me te keteroki he tikanga ki te whawhai i nga hua o te tuhi kino Uiui LINQ и Uiui SQL.

E ai ki nga mahi kino o te ao, he mea nui mo te pakihi ki te whakatinana i nga ahuatanga pakihi i etahi wa mutunga. Na reira, he maha nga wa ka tukuna nga tono taumaha ki te papamuri me te keteroki.

He waahanga tika tenei, na te mea kaore e hiahiatia e te kaiwhakamahi nga raraunga hou i nga wa katoa, a he taumata e whakaaetia ana o te atanga kaiwhakamahi.

Ka taea e tenei huarahi te whakaoti i nga hiahia pakihi, engari i te mutunga ka whakaitihia te mahinga o te punaha korero ma te whakaroa noa i nga otinga ki nga raru.

He mea tika ano kia mahara kei te rapu i nga tohu e tika ana hei taapiri, he whakaaro MS SQL kei te he te arotautanga, tae atu ki nga tikanga e whai ake nei:

mena he taupuke kua rite te huinga mara
ki te kore e taea te tohu i nga mara o te ripanga na runga i nga here o te tohu tohu (kua whakaahuatia ake konei).

2) Hanumi huanga ki tetahi huanga hou

I etahi wa ka taea te whakakapi etahi mara mai i te tepu kotahi, ka waiho hei turanga mo te roopu tikanga, ma te whakauru i tetahi mara hou.

He tino tika tenei mo nga mara mana, he moka, he tauoti ranei te momo.

Hei tauira:

Kua Kati = 0 ME Kua Whakakorea = 0 ME Whakahohe = 0 kua whakakapihia e Tūnga = 1.

Koinei te waahi ka whakauruhia te huanga Tūnga tauoti hei whakarite kia noho enei mana ki te ripanga. I muri mai, ka tohua tenei huanga hou.

He otinga taketake tenei mo te raru o te mahi, na te mea ka uru matou ki nga raraunga kaore he tatauranga kore.

3) Whakaritenga o te tirohanga

Kia aroha mai i roto i Uiui LINQ Kaore e taea te whakamahi tika nga ripanga rangitahi, CTE, me nga taurangi ripanga.

Heoi ano, he huarahi kee hei arotau mo tenei keehi - nga tirohanga kua taurangi.

Rōpū tikanga (mai i te tauira i runga ake nei) Kua Kati = 0 ME Kua Whakakorea = 0 ME Whakahohe = 0 (he huinga ranei o etahi atu ahuatanga rite) ka waiho hei whiringa pai ki te whakamahi i roto i te tirohanga taupū, te keteroki i tetahi waahanga iti o nga raraunga mai i tetahi huinga nui.

Engari he maha nga here i te wa e hangai ana he tirohanga:

te whakamahi i nga patai, rarangi HE WHAKAMAHI me whakakapi ma te whakamahi Hono
e kore e taea e koe te whakamahi i nga rerenga korero UNION, UNION KATOA, HE WHAKAMAHI, PANUI
Kaore e taea e koe te whakamahi i nga tohu tohu me nga rara OPTION
kaore e taea te mahi me nga huringa
Kaore e taea te whakaatu raraunga i te tirohanga kotahi mai i nga ripanga rereke

He mea nui kia maumahara ko te tino painga o te whakamahi i te tirohanga kua taurangi ka taea anake ma te tino tohu.

Engari i te wa e karanga ana he tirohanga, kare pea enei tohu e whakamahia, a ki te whakamahi marama, me tohu koe ME(KOREROHA).

Mai i roto i Uiui LINQ Kaore e taea te tautuhi i nga tohu ripanga, na me hanga e koe tetahi atu tohu - he "takai" o te ahua e whai ake nei:

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

4) Te whakamahi i nga mahi ripanga

I roto i te nuinga o nga wa Uiui LINQ Ko nga poraka nui o nga paatai, o nga poraka ranei e whakamahi ana i nga tirohanga me te hanganga matatini ka hanga he uiuinga whakamutunga me te hanganga mahi tino uaua me te tino pai.

Nga Painga Matua o te Whakamahi i nga Mahi Ripanga i roto Uiui LINQ:

Ko te kaha, pera i te ahua o nga tirohanga, ki te whakamahi me te tohu hei ahanoa, engari ka taea e koe te tuku i tetahi huinga tawhā whakauru:
MAI MAHI(@param1, @param2 ...)
Ko te mutunga, ka taea te whakatau tauira raraunga ngawari
Mo te whakamahi i te mahi ripanga, karekau he here kaha pera i te keehi o nga tirohanga kua taurangi kua whakaahuatia i runga ake nei:
1. Tohu Ripanga:
  i roto i LINQ Kaore e taea e koe te tautuhi ko wai nga tohu hei whakamahi me te whakatau i te taumata wehe raraunga ina patai ana.
  Engari kei te mahi enei kaha.
  Ma te mahi, ka taea e koe te whakatutuki i tetahi mahere uiui mahi tonu, ka whakatauhia nga ture mo te mahi me nga tohu tohu me nga taumata wehe raraunga.
2. Ma te whakamahi i te mahi ka taea, ki te whakataurite ki nga tirohanga kua tohua, ki te whiwhi:
  - arorau whakatauira raraunga matatini (ara te whakamahi i nga koropiko)
  - te tiki raraunga mai i nga ripanga maha
  - te whakamahi o UNION и HE WHAKAMAHI
Tuku OPTION tino whai hua ina hiahia tatou ki te whakarato i te mana concurrency KŌWHIRINGA(MAXDOP N), te raupapa o te mahere mahi uiui. Hei tauira:
- ka taea e koe te whakarite i te hanga-a-hou i te mahere patai KŌWHIRINGA (HĀPIHIA)
- ka taea e koe te tohu mena ka akiakihia te mahere uiui ki te whakamahi i te ota whakauru kua tohua ki te patai KŌWHIRINGA ( WHAKATAUTANGA KAUPAPA)
Ētahi atu kōrero mō OPTION whakaahuatia konei.
Ma te whakamahi i te waahanga raraunga whaiti me te tino hiahiatia:
Kaore he take ki te penapena i nga huinga raraunga nui i roto i nga keteroki (penei me nga tirohanga kua tohua), mai i te mea ka hiahia tonu koe ki te tarai i nga raraunga ma te taapiri.
Hei tauira, he ripanga tona tātari HEA e toru nga mara e whakamahia ana (a, b, c).

Ko te tikanga, he ahua tonu nga tono katoa a = 0 me b = 0.

Heoi, ko te tono mo te mara c taurangi ake.

Waiho te tikanga a = 0 me b = 0 Ka tino awhina i a maatau ki te whakawhāiti i nga hua e hiahiatia ana ki nga mano o nga rekoata, engari kei te noho tonu с ka whakawhāitihia te kōwhiringa ki te kotahi rau rekoata.

I konei ka pai ake pea te mahi tepu.

Ano hoki, he pai ake te matapae me te riterite o te mahi ripanga ki te wa mahi.

tauira

Kia titiro tatou ki tetahi tauira whakatinanatanga ma te whakamahi i te paataka patai hei tauira.

He tono Tīpakohia, e whakakotahi ana i nga ripanga maha me te whakamahi i te tirohanga kotahi (OperativeQuestions), ka tirohia te hononga ma te imeera (ma te HE WHAKAMAHI) ki "Nga Uiui Whakahaere":

Tono Nama 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])
));

He tino uaua te hanganga o te tirohanga: he hononga taapiri me te whakamahi tohatoha Tuhinga, i te nuinga o te waa he mahi tino kaha rawa.

Ko tetahi tauira mai i OperativeQuestions tata ki te tekau mano rekoata.

Ko te raru nui mo tenei patai ko te mea mo nga rekoata mai i te patai o waho, ka mahia he uiuinga a-roto ki te tirohanga [OperativeQuestions], ma te [Imeera] = @p__linq__0 ka taea te whakawhāiti i te kowhiringa putanga (ma HE WHAKAMAHI) tae atu ki nga rau rekoata.

A ko te ahua pea me tatau te patai i nga rekoata kia kotahi ma te [Imeera] = @p__linq__0, katahi ka hono enei rau rekoata e Id me nga Uiui, ka tere te patai.

Inaa, he hononga raupapa o nga ripanga katoa: te tirotiro i nga reta o nga Uiui Id me te Id mai i OperativeQuestions, me te tātari ma te Email.

Inaa, ka mahi te tono me nga tekau mano katoa o nga rekoata OperativeQuestions, engari ko nga raraunga o te paanga anake e hiahiatia ana ma te Email.

Kuputuhi Tirohia nga patai:

Tono Nama 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));

Mahere tirohanga tuatahi ki 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");
    }
}

Uiui LINQ tuatahi

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

I tenei keehi, kei te whakaaro matou ki tetahi otinga mo tenei raru kaore he huringa hangahanga, me te kore e whakauru i tetahi ripanga motuhake me nga hua kua rite ("Uiui Hohe"), ka hiahiatia he tikanga mo te whakaki i nga raraunga me te pupuri tonu i te waa. .

Ahakoa he otinga pai tenei, tera ano tetahi atu whiringa hei arotau i tenei raru.

Ko te kaupapa matua ko te keteroki i nga urunga na [Imeera] = @p__linq__0 mai i te tirohanga OperativeQuestions.

Whakauruhia te mahinga ripanga [dbo].[OperativeQuestionsUserMail] ki roto i te papanga raraunga.

Ma te tuku Īmēra hei tawhā whakauru, ka hoki mai he ripanga uara:

Tono Nama 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

Ka whakahokia mai he ripanga uara me te hanganga raraunga kua tautuhia.

Kia pai ai nga patai ki te OperativeQuestionsUserMail me te whai i nga mahere patai tino pai, me whai hanganga tino, kaua WHAKAHOKI TEPUA HEI WHAKAHOKI...

I tenei take, ka hurihia te Uiui 1 hei Uiui 4:

Tono Nama 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]);

Mahere tirohanga me nga mahi ki 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})");
}

Uiui LINQ Whakamutunga

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

Ko te raupapa o te wa mahi kua heke mai i te 200-800 ms, ki te 2-20 ms, me etahi atu, ara he tekau nga wa tere ake.

Mena ka kaha ake taatau ki te tango i te waa, kare i te 350 ms ka whiwhi 8 ms.

Mai i nga painga kitea ka whiwhi ano tatou:

te whakaiti whanui i te taumaha panui,
te whakaiti nui i te tupono o te aukati
te whakaiti i te wa aukati toharite ki nga uara e whakaaetia ana

mutunga

Te arotautanga me te whakatikatika i nga waea putunga raraunga MS SQL i roto i LINQ he raruraru ka taea te whakatau.

He mea tino nui te aro me te riterite ki tenei mahi.

I te timatanga o te tukanga:

he mea tika kia tirohia nga raraunga e mahi ana te tono (uara, momo raraunga kua tohua)
whakahaere i te taupūtanga tika o enei raraunga
tirohia te tika o nga tikanga hono i waenga i nga ripanga

Ko te whitiwhiti arotautanga e whai ake nei ka whakaatu:

pūtake o te tono me te tautuhi i te tātari tono matua
te tukurua i nga poraka uiui rite me te tātari i te whitinga o nga tikanga
i SSMS, i etahi atu GUI ranei mo Tūmau SQL arotau ana ano Uiui SQL (te tohatoha i te rokiroki raraunga takawaenga, te hanga i te patai ka puta ma te whakamahi i tenei rokiroki (he maha pea))
i te waahanga whakamutunga, ka mau i nga hua ka puta Uiui SQL, kei te hanga ano te hanga Uiui LINQ

Ko te hua Uiui LINQ kia rite te hanganga ki te tino pai kua tautuhia Uiui SQL mai i te wahanga 3.

Nga Mihi

Nga mihi nui ki nga hoa mahi jobgemws и alex_ozr mai i te kamupene Fortis mo te awhina ki te whakarite i tenei rauemi.

Source: will.com

Nga tikanga mo te arotau i nga patai LINQ i C#.NET