🥇Qaar ka mid ah wanaajinta su'aalaha LINQ ee C#.NET ee Server-ka MS SQL

LINQ waxay u gashay .NET sidii luqad cusub oo xogeed oo xoog badan. LINQ ilaa SQL qayb ka mid ah waxay kuu ogolaanaysaa inaad si ku habboon ula xiriirto DBMS adoo isticmaalaya, tusaale ahaan, Qaab-dhismeedka Hay'adda. Si kastaba ha ahaatee, isticmaalka marar badan, horumariyayaashu waxay illoobaan inay eegaan nooca su'aalaha SQL ee bixiyaha la waydiin karo, kiiskaaga Qaab-dhismeedka Hay'adda, ayaa dhalin doona.

Aynu eegno laba qodob oo muhiim ah inagoo tusaale u soo qaadanayna.
Si tan loo sameeyo, ku samee kaydka xogta tijaabada gudaha SQL Server, oo ku dhex samee laba jadwal adoo isticmaalaya su'aalahan soo socda:

Abuuritaanka miisaska

USE [TEST]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Ref](
	[ID] [int] NOT NULL,
	[ID2] [int] NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[InsertUTCDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Ref] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Ref] ADD  CONSTRAINT [DF_Ref_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO

USE [TEST]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer](
	[ID] [int] NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[Ref_ID] [int] NOT NULL,
	[InsertUTCDate] [datetime] NOT NULL,
	[Ref_ID2] [int] NOT NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Customer] ADD  CONSTRAINT [DF_Customer_Ref_ID]  DEFAULT ((0)) FOR [Ref_ID]
GO

ALTER TABLE [dbo].[Customer] ADD  CONSTRAINT [DF_Customer_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO

Hadda aynu buuxino miiska Ref annagoo wadno qoraalka soo socda:

Buuxinta miiska Ref

USE [TEST]
GO

DECLARE @ind INT=1;

WHILE(@ind<1200000)
BEGIN
	INSERT INTO [dbo].[Ref]
           ([ID]
           ,[ID2]
           ,[Name])
    SELECT
           @ind
           ,@ind
           ,CAST(@ind AS NVARCHAR(255));

	SET @ind=@ind+1;
END 
GO

Aynu si la mid ah u buuxino miiska macaamiisha anagoo adeegsanayna qoraalkan soo socda:

Buuxinta miiska macaamiisha

USE [TEST]
GO

DECLARE @ind INT=1;
DECLARE @ind_ref INT=1;

WHILE(@ind<=12000000)
BEGIN
	IF(@ind%3=0) SET @ind_ref=1;
	ELSE IF (@ind%5=0) SET @ind_ref=2;
	ELSE IF (@ind%7=0) SET @ind_ref=3;
	ELSE IF (@ind%11=0) SET @ind_ref=4;
	ELSE IF (@ind%13=0) SET @ind_ref=5;
	ELSE IF (@ind%17=0) SET @ind_ref=6;
	ELSE IF (@ind%19=0) SET @ind_ref=7;
	ELSE IF (@ind%23=0) SET @ind_ref=8;
	ELSE IF (@ind%29=0) SET @ind_ref=9;
	ELSE IF (@ind%31=0) SET @ind_ref=10;
	ELSE IF (@ind%37=0) SET @ind_ref=11;
	ELSE SET @ind_ref=@ind%1190000;
	
	INSERT INTO [dbo].[Customer]
	           ([ID]
	           ,[Name]
	           ,[Ref_ID]
	           ,[Ref_ID2])
	     SELECT
	           @ind,
	           CAST(@ind AS NVARCHAR(255)),
	           @ind_ref,
	           @ind_ref;


	SET @ind=@ind+1;
END
GO

Haddaba, waxaanu helnay laba miis oo mid ka mid ah uu leeyahay in ka badan 1 milyan oo saf oo xog ah, midna waxa uu leeyahay in ka badan 10 milyan oo saf oo xog ah.

Hadda gudaha Visual Studio waxaad u baahan tahay inaad ku abuurto tijaabo Visual C # Console App (.NET Framework) mashruuca:

Marka xigta, waxaad u baahan tahay inaad ku darto maktabad loogu talagalay Qaab-dhismeedka Hay'adda si ay ula falgalaan xogta.
Si aad ugu darto, midig-guji mashruuca oo ka dooro Maaree Xirmooyinka NuGet ee liiska macnaha guud:

Kadibna, daaqada maaraynta xirmada NuGet ee soo muuqata, gali kelmada "Qaabka Qaabdhismeedka" daaqada raadinta oo dooro xirmada Qaab dhismeedka oo ku rakib:

Marka xigta, faylka App.config, ka dib markaad xidhid qaybta qaabaynta, waxaad u baahan tahay inaad ku darto block soo socda:

<connectionStrings>
    <add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>

ConnectString waxaad u baahan tahay inaad geliso xadhigga xidhiidhka.

Hadda aan abuurno 3 interfaces faylal gaar ah:

Hirgelinta is dhexgalka IBaseEntityID

namespace TestLINQ
{
    public interface IBaseEntityID
    {
        int ID { get; set; }
    }
}

Hirgelinta is dhexgalka IBaseEntityName

namespace TestLINQ
{
    public interface IBaseEntityName
    {
        string Name { get; set; }
    }
}

Hirgelinta is dhexgalka IBaseNameInsertUTCDate

namespace TestLINQ
{
    public interface IBaseNameInsertUTCDate
    {
        DateTime InsertUTCDate { get; set; }
    }
}

Fayl gaar ah waxaan u abuuri doonaa saldhigga aasaasiga ah ee BaseEntity labadayada hay'adood, kaas oo ay ku jiraan meelaha la wadaago:

Hirgelinta fasalka aasaasiga ah BaseEntity

namespace TestLINQ
{
    public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public DateTime InsertUTCDate { get; set; }
    }
}

Marka xigta, waxaan ku abuuri doonaa labadayada hay'ad faylal gaar ah:

Hirgelinta fasalka Ref

using System.ComponentModel.DataAnnotations.Schema;

namespace TestLINQ
{
    [Table("Ref")]
    public class Ref : BaseEntity
    {
        public int ID2 { get; set; }
    }
}

Hirgelinta fasalka macaamiisha

using System.ComponentModel.DataAnnotations.Schema;

namespace TestLINQ
{
    [Table("Customer")]
    public class Customer: BaseEntity
    {
        public int Ref_ID { get; set; }
        public int Ref_ID2 { get; set; }
    }
}

Hadda aan ku abuurno macnaha guud ee UserContext fayl gaar ah:

Hirgelinta fasalka UserContex

using System.Data.Entity;

namespace TestLINQ
{
    public class UserContext : DbContext
    {
        public UserContext()
            : base("DbConnection")
        {
            Database.SetInitializer<UserContext>(null);
        }

        public DbSet<Customer> Customer { get; set; }
        public DbSet<Ref> Ref { get; set; }
    }
}

Waxaan helnay xal diyaarsan oo lagu samaynayo imtixaanada hagaajinta ee LINQ ilaa SQL iyada oo loo sii marayo EF ee MS SQL Server:

Hadda geli koodka soo socda faylka Program.cs:

Program.cs faylka

using System;
using System.Collections.Generic;
using System.Linq;

namespace TestLINQ
{
    class Program
    {
        static void Main(string[] args)
        {
            using (UserContext db = new UserContext())
            {
                var dblog = new List<string>();
                db.Database.Log = dblog.Add;

                var query = from e1 in db.Customer
                            from e2 in db.Ref
                            where (e1.Ref_ID == e2.ID)
                                 && (e1.Ref_ID2 == e2.ID2)
                            select new { Data1 = e1.Name, Data2 = e2.Name };

                var result = query.Take(1000).ToList();

                Console.WriteLine(dblog[1]);

                Console.ReadKey();
            }
        }
    }
}

Marka xigta, aynu bilowno mashruucayaga.

Dhamaadka shaqada, kuwan soo socda ayaa lagu soo bandhigi doonaa console-ka:

La soo saaray weydiinta SQL

SELECT TOP (1000) 
    [Extent1].[Ref_ID] AS [Ref_ID], 
    [Extent1].[Name] AS [Name], 
    [Extent2].[Name] AS [Name1]
    FROM  [dbo].[Customer] AS [Extent1]
    INNER JOIN [dbo].[Ref] AS [Extent2] ON ([Extent1].[Ref_ID] = [Extent2].[ID]) AND ([Extent1].[Ref_ID2] = [Extent2].[ID2])

Taasi waa, guud ahaan, weydiinta LINQ waxay si fiican u soo saartay su'aal SQL ah MS SQL Server DBMS.

Hadda aan u bedelno iyo shuruudda AMA ku jira weydiinta LINQ:

Weydiinta LINQ

var query = from e1 in db.Customer
                            from e2 in db.Ref
                            where (e1.Ref_ID == e2.ID)
                                || (e1.Ref_ID2 == e2.ID2)
                            select new { Data1 = e1.Name, Data2 = e2.Name };

Oo aan mar kale soo saarno codsigayaga.

Fulintu waxay ku burburi doontaa khalad sababtoo ah wakhtiga fulinta amarka ee ka badan 30 ilbiriqsi:

Haddii aad eegto weydiinta ay abuurtay LINQ:

, markaa waxaad hubin kartaa in doorashadu ku dhacdo badeecada Kartisia ee laba qaybood (miisaska):

La soo saaray weydiinta SQL

SELECT TOP (1000) 
    [Extent1].[Ref_ID] AS [Ref_ID], 
    [Extent1].[Name] AS [Name], 
    [Extent2].[Name] AS [Name1]
    FROM  [dbo].[Customer] AS [Extent1]
    CROSS JOIN [dbo].[Ref] AS [Extent2]
    WHERE [Extent1].[Ref_ID] = [Extent2].[ID] OR [Extent1].[Ref_ID2] = [Extent2].[ID2]

Aan dib u qorno weydiinta LINQ sida soo socota:

Weydiinta LINQ ee la hagaajiyay

var query = (from e1 in db.Customer
                   join e2 in db.Ref
                   on e1.Ref_ID equals e2.ID
                   select new { Data1 = e1.Name, Data2 = e2.Name }).Union(
                        from e1 in db.Customer
                        join e2 in db.Ref
                        on e1.Ref_ID2 equals e2.ID2
                        select new { Data1 = e1.Name, Data2 = e2.Name });

Kadib waxaan helnaa su'aalaha SQL ee soo socda:

Su'aasha SQL

SELECT 
    [Limit1].[C1] AS [C1], 
    [Limit1].[C2] AS [C2], 
    [Limit1].[C3] AS [C3]
    FROM ( SELECT DISTINCT TOP (1000) 
        [UnionAll1].[C1] AS [C1], 
        [UnionAll1].[Name] AS [C2], 
        [UnionAll1].[Name1] AS [C3]
        FROM  (SELECT 
            1 AS [C1], 
            [Extent1].[Name] AS [Name], 
            [Extent2].[Name] AS [Name1]
            FROM  [dbo].[Customer] AS [Extent1]
            INNER JOIN [dbo].[Ref] AS [Extent2] ON [Extent1].[Ref_ID] = [Extent2].[ID]
        UNION ALL
            SELECT 
            1 AS [C1], 
            [Extent3].[Name] AS [Name], 
            [Extent4].[Name] AS [Name1]
            FROM  [dbo].[Customer] AS [Extent3]
            INNER JOIN [dbo].[Ref] AS [Extent4] ON [Extent3].[Ref_ID2] = [Extent4].[ID2]) AS [UnionAll1]
    )  AS [Limit1]

Hooggay, su'aalaha LINQ waxaa jiri kara hal shuruud oo ku biirista, markaa halkan waxaa suurtagal ah in la sameeyo su'aal u dhiganta iyadoo la adeegsanayo laba su'aalood oo xaalad kasta ah ka dibna la isku daro Ururka si looga saaro nuqullada safafka ah.
Haa, weydiimaha guud ahaan ma noqon doonaan kuwo u dhigma, iyada oo la tixgelinayo in safafka nuqulka ah ee dhammaystiran la soo celin karo. Si kastaba ha ahaatee, nolosha dhabta ah, khadadka nuqul ka buuxa looma baahna oo dadku waxay isku dayaan inay ka takhalusaan.

Haddaba aan is barbar dhigno qorshayaasha fulinta labadan weydiimood:

Isku xirka CROSS celceliska wakhtiga fulinta waa 195 ilbiriqsi:
Ku biiritaanka GUDAHA ee midowga celceliska wakhtiga fulinta waa wax ka yar 24 sekan:

Sida aad ka arki karto natiijooyinka, laba miis oo leh malaayiin rikoor ah, weydiinta LINQ ee la hagaajiyay ayaa marar badan ka dhaqso badan tan aan la hagaajin.

Xulashada leh IYO shuruudaha, LINQ weydiinta foomka:

Weydiinta LINQ

var query = from e1 in db.Customer
                            from e2 in db.Ref
                            where (e1.Ref_ID == e2.ID)
                                 && (e1.Ref_ID2 == e2.ID2)
                            select new { Data1 = e1.Name, Data2 = e2.Name };

Weydiinta saxda ah ee SQL ayaa had iyo jeer la soo saari doonaa, taasoo socon doonta celcelis ahaan 1 ilbiriqsi gudahood:

Sidoo kale loogu talagalay LINQ inay u diido wax-is-daba-marinta beddelka su'aasha sida:

Weydiinta LINQ (doorashada 1aad)

var query = from e1 in seq1
                            from e2 in seq2
                            where (e1.Key1==e2.Key1)
                               && (e1.Key2==e2.Key2)
                            select new { Data1 = e1.Data, Data2 = e2.Data };

waxaad isticmaali kartaa su'aal sida:

Weydiinta LINQ (doorashada 2aad)

var query = from e1 in seq1
                            join e2 in seq2
                            on new { e1.Key1, e1.Key2 } equals new { e2.Key1, e2.Key2 }
                            select new { Data1 = e1.Data, Data2 = e2.Data };

meesha:

Qeexida laba hannaan

Para[] seq1 = new[] { new Para { Key1 = 1, Key2 = 2, Data = "777" }, new Para { Key1 = 2, Key2 = 3, Data = "888" }, new Para { Key1 = 3, Key2 = 4, Data = "999" } };
Para[] seq2 = new[] { new Para { Key1 = 1, Key2 = 2, Data = "777" }, new Para { Key1 = 2, Key2 = 3, Data = "888" }, new Para { Key1 = 3, Key2 = 5, Data = "999" } };

, iyo nooca Para waxaa lagu qeexay sida soo socota:

Para Nooca Qeexida

class Para
{
        public int Key1, Key2;
        public string Data;
}

Markaa, waxaanu eegnay qaybo ka mid ah hagaajinta su'aalaha LINQ ee MS SQL Server.

Nasiib darro, xitaa kuwa khibradda leh iyo kuwa hormuudka ka ah NET horumariyayaashu waxay illoobaan inay u baahan yihiin inay fahmaan waxa tilmaamaha ay isticmaalaan ay sameeyaan muuqaallada. Haddii kale, waxay noqonayaan configurators waxayna ku dhejin karaan bam waqti mustaqbalka labadaba marka la cabbirayo xalka software iyo isbeddello yaryar oo ku yimaada xaaladaha deegaanka dibadda.

Dib u eegis gaaban ayaa sidoo kale la sameeyay halkan.

Ilaha imtixaanka - mashruuca laftiisa, abuurista miisaska xogta TEST, iyo sidoo kale buuxinta miisaska xogta ayaa ku yaal halkan.
Sidoo kale kaydkan, galka Qorshayaasha, waxa jira qorshayaal lagu fulinayo su'aalaha leh AMA shuruudo.

Source: www.habr.com

Qaybaha qaar ee wanaajinta su'aalaha LINQ ee C#.NET ee MS SQL Server

Add a comment cancel reply