🥇Likarolo tse ling tsa ho ntlafatsa lipotso tsa LINQ ho C#.NET bakeng sa MS SQL Server

LINQ e kentse .NET joalo ka puo e ncha e matla ea ho thetsa data. LINQ ho SQL e le karolo ea eona e u lumella ho buisana hamonate le DBMS u sebelisa, mohlala, Entity Framework. Leha ho le joalo, ha ba e sebelisa khafetsa, bahlahisi ba lebala ho sheba hore na ke potso ea mofuta ofe ea SQL eo mofani a ka e botsang, molemong oa hau Entity Framework, e tla hlahisa.

A re shebeng lintlha tse peli tsa sehlooho re sebelisa mohlala.
Ho etsa sena, theha database ea Teko ho SQL Server, 'me u thehe litafole tse peli ho eona u sebelisa potso e latelang:

Ho theha litafole

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

Joale a re kenyeng tafole ea Ref ka ho sebelisa mongolo o latelang:

Ho tlatsa tafole ea 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

Ka mokhoa o tšoanang, re tlatse tafole ea Bareki re sebelisa mongolo o latelang:

Ho tlatsa tafole ea Bareki

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

Kahoo, re amohetse litafole tse peli, e 'ngoe ea eona e na le mela e fetang limilione tse 1,' me e 'ngoe e na le mela e fetang limilione tse 10 ea data.

Hona joale ho Visual Studio o hloka ho etsa tlhahlobo ea Visual C# Console App (.NET Framework) projeke:

Ka mor'a moo, u lokela ho eketsa laebrari bakeng sa Entity Framework ho sebelisana le database.
Ho e eketsa, tobetsa ka ho le letona morerong ebe u khetha Laola liphutheloana tsa NuGet ho tsoa ho menyu ea litaba:

Ebe, ka fensetere ea tsamaiso ea sephutheloana sa NuGet e hlahang, kenya lentsoe "Entity Framework" fensetereng ea ho batla ebe u khetha sephutheloana sa Entity Framework ebe u se kenya:

Ka mor'a moo, faeleng ea App.config, ka mor'a ho koala karolo ea configSections, u lokela ho eketsa thibela e latelang:

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

Ho connectionString o hloka ho kenya khoele ea khokahano.

Joale ha re theheng likhokahano tse 3 ka lifaele tse arohaneng:

Ho kenya tshebetsong sebopeho sa IBaseEntityID

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

Ts'ebetsong ea IBaseEntityName interface

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

Ho kenngwa tshebetsong ha IBaseNameInsertUTCDate interface

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

Mme ka faeleng e arohaneng re tla theha sehlopha sa mantlha sa BaseEntity bakeng sa mekhatlo ea rona e 'meli, e tla kenyelletsa likarolo tse tloaelehileng:

Ts'ebetsong ea sehlopha sa motheo sa BaseEntity

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

E latelang, re tla theha mekhatlo ea rona e 'meli ka lifaele tse arohaneng:

Ts'ebetsong ea sehlopha sa Ref

using System.ComponentModel.DataAnnotations.Schema;

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

Phethahatso ea sehlopha sa Bareki

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; }
    }
}

Joale ha re theheng moelelo oa UserContext faeleng e fapaneng:

Ts'ebetsong ea sehlopha sa 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; }
    }
}

Re fumane tharollo e lokiselitsoeng ea ho etsa liteko tsa optimization le LINQ ho SQL ka EF bakeng sa MS SQL Server:

Joale kenya khoutu e latelang faeleng ea Program.cs:

Faele ea Program.cs

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

E latelang, ha re thakholeng morero oa rona.

Qetellong ea mosebetsi, tse latelang li tla hlahisoa ho console:

Potso ea SQL e hlahisitsoeng

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])

Ke hore, ka kakaretso, potso ea LINQ e hlahisitse potso ea SQL ho MS SQL Server DBMS hantle.

Joale ha re fetoleng LE boemo ho OR ho potso ea LINQ:

LINQ potso

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 };

Mme ha re qaleng kopo ya rona hape.

Ts'ebetso e tla senyeha ka phoso ka lebaka la nako ea ho phethahatsa taelo e fetang metsotsoana e 30:

Haeba u sheba potso e hlahisitsoeng ke LINQ:

, joale o ka etsa bonnete ba hore khetho e etsahala ka sehlahisoa sa Cartesian sa lihlopha tse peli (litafole):

Potso ea SQL e hlahisitsoeng

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]

Ha re ngole potso ea LINQ ka tsela e latelang:

E ntlafalitse potso ea LINQ

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

Ebe re fumana potso e latelang ea SQL:

Potso ea 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]

Oho, lipotsong tsa LINQ ho ka ba le boemo bo le bong feela ba ho kopanya, kahoo mona ho ka khoneha ho etsa potso e lekanang le lipotso tse peli bakeng sa boemo bo bong le bo bong ebe u li kopanya ka Union ho tlosa tse kopitsoang har'a mela.
E, lipotso ka kakaretso li ke ke tsa lekana, ho nahanoa hore ho ka khutlisoa mela e felletseng. Leha ho le joalo, bophelong ba 'nete, ha ho hlokahale mela e felletseng e kopitsoang mme batho ba leka ho e tlosa.

Joale a re bapiseng merero ea ts'ebetso ea lipotso tsena tse peli:

bakeng sa CROSS JOIN nako e tloaelehileng ea ho phetha ke metsotsoana e 195:
bakeng sa INNER JOIN-UNION nako e tloaelehileng ea ho phetha e ka tlase ho metsotsoana e 24:

Joalokaha u bona ho tsoa liphethong, bakeng sa litafole tse peli tse nang le lirekoto tse limilione, potso e ntlafalitsoeng ea LINQ e potlakile ka makhetlo a mangata ho feta e sa ntlafatsoang.

Bakeng sa khetho ka LE maemong, potso ea LINQ ea foromo:

LINQ potso

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 };

Potso e nepahetseng ea SQL e tla lula e hlahisoa, e tla sebetsa ka karolelano ka motsotsoana o le mong:

Hape bakeng sa LINQ to Objects manipulations ho fapana le potso e kang:

Potso ea LINQ (khetho ea pele)

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 };

u ka sebelisa potso e kang:

Potso ea LINQ (khetho ea pele)

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 };

moo:

Ho hlalosa lihlopha tse peli

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" } };

, mme mofuta oa Para o hlalosoa ka tsela e latelang:

Para Type Tlhaloso

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

Kahoo, re hlahlobile likarolo tse ling tsa ho ntlafatsa lipotso tsa LINQ ho MS SQL Server.

Ka bomalimabe, esita le baetsi ba .NET ba nang le phihlelo le ba etellang pele ba lebala hore ba hloka ho utloisisa hore na litaelo tseo ba li sebelisang li etsa eng ka mor'a lipapali. Ho seng joalo, ba fetoha li-configurator 'me ba ka lema bomo ea nako nakong e tlang ha ba lekanya tharollo ea software le ka liphetoho tse nyane maemong a kantle a tikoloho.

Ho ile ha boela ha etsoa tlhahlobo e khutšoanyane mona.

Mehloli ea tlhahlobo - morero ka boeona, ho thehoa ha litafole sebakeng sa polokelo ea TEST, hammoho le ho tlatsa litafole tsena ka data. mona.
Hape sebakeng sena sa polokelo, foldareng ea Meralo, ho na le merero ea ho etsa lipotso ka OR maemo.

Source: www.habr.com

Lintlha tse ling tsa ho ntlafatsa lipotso tsa LINQ ho C#.NET bakeng sa MS SQL Server

Eketsa ka tlhaloso hlakola karabo