🥇Eminye imiba yokwandisa imibuzo yeLINQ kwi-C#.NET ye-MS SQL Server

I-LINQ ingene .NET njengolwimi olutsha olunamandla lokukhohlisa. I-LINQ ukuya kwiSQL njengenxalenye yayo ikuvumela ukuba unxibelelane ngokulula neDBMS usebenzisa, umzekelo, Isakhelo seQumrhu. Nangona kunjalo, beyisebenzisa rhoqo, abaphuhlisi bayalibala ukujonga ukuba luhlobo luni lombuzo weSQL umnikezeli obuzwayo, kwimeko yakho iSikhokelo seQumrhu, siyakwenza.

Makhe sijonge iingongoma ezimbini eziphambili sisebenzisa umzekelo.
Ukwenza oku, yenza i-database yoVavanyo kwi-SQL Server, kwaye wenze iitafile ezimbini kuyo usebenzisa lo mbuzo ulandelayo:

Ukudala iitafile

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

Ngoku makhe sigcwalise itheyibhile yeRef ngokuqhuba iskripthi esilandelayo:

Ukuzalisa itheyibhile yeRef

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

Masigcwalise ngokufanayo itheyibhile yoMthengi sisebenzisa esi script silandelayo:

Ukuzalisa itafile yoMthengi

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

Ngaloo ndlela, sifumene iitheyibhile ezimbini, enye enemiqolo yedatha engaphezu kwe-1 yezigidi, kwaye enye inezintlu ezingaphezu kwezigidi ezili-10 zedatha.

Ngoku kwiVisual Studio kufuneka wenze uvavanyo lweVisual C# Console App (.NET Framework) iprojekthi:

Okulandelayo, kufuneka ungeze ithala leencwadi leSikhokelo seQumrhu ukuze linxibelelane nesiseko sedatha.
Ukuyongeza, cofa ekunene kwiprojekthi kwaye ukhethe Lawula iiPakethi zeNuGet kwimenyu yomxholo:

Emva koko, kwifestile yolawulo lwephakheji ye-NuGet evelayo, faka igama elithi "I-Entity Framework" kwifestile yokukhangela kwaye ukhethe iphakheji ye-Entity Framework kwaye uyifake:

Okulandelayo, kwifayile ye-App.config, emva kokuvala i-configSections element, kufuneka udibanise ibhloko elandelayo:

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

Kwintambo yoqhagamshelwano kufuneka ufake umtya woqhagamshelwano.

Ngoku masenze ujongano olu-3 kwiifayile ezahlukeneyo:

Ukusebenzisa i-interface ye-IBaseEntityID

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

Ukuphunyezwa kojongano lwe-IBaseEntityName

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

Ukuphunyezwa kojongano lwe-IBaseNameInsertUTCDate

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

Kwaye kwifayile eyahlukileyo siya kudala isiseko seBaseEntity kumacandelo ethu amabini, aya kubandakanya amasimi aqhelekileyo:

Ukuphunyezwa kwesiseko seBaseEntity

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

Okulandelayo, siya kudala amaziko ethu amabini kwiifayile ezahlukeneyo:

Ukuphunyezwa kweklasi yeRef

using System.ComponentModel.DataAnnotations.Schema;

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

Ukuphunyezwa kweklasi yoMthengi

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

Ngoku masenze umxholo womSebenzisi kwifayile eyahlukileyo:

Ukuphunyezwa kweklasi ye-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; }
    }
}

Sifumene isisombululo esele silungisiwe sokuqhuba iimvavanyo zokufezekisa nge-LINQ ukuya kwi-SQL nge-EF ye-MS SQL Server:

Ngoku faka ikhowudi elandelayo kwifayile yeProgram.cs:

Inkqubo.cs ifayile

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

Okulandelayo, masiqalise iprojekthi yethu.

Ekupheleni komsebenzi, oku kulandelayo kuya kuboniswa kwi-console:

Yenziwe i-SQL Umbuzo

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

Oko kukuthi, ngokubanzi, umbuzo weLINQ wenze umbuzo weSQL kwi-MS SQL Server DBMS kakuhle.

Ngoku masitshintshe imeko kunye nemeko OKANYE kumbuzo we-LINQ:

Umbuzo weLINQ

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

Kwaye masiqalise usetyenziso lwethu kwakhona.

Ukuphunyezwa kuya kuphazamiseka ngempazamo ngenxa yexesha lokuphumeza umyalelo elidlula imizuzwana engama-30:

Ukuba ujonga umbuzo owenziwe yi-LINQ:

, emva koko unokuqinisekisa ukuba ukhetho lwenzeka ngemveliso yeCartesian yeeseti ezimbini (iitafile):

Yenziwe i-SQL Umbuzo

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]

Masiphinde sibhale umbuzo we-LINQ ngolu hlobo lulandelayo:

Umbuzo olungiselelwe iLINQ

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

Emva koko sifumana lo mbuzo ulandelayo weSQL:

Umbuzo weSQL

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]

Yeha, kwimibuzo ye-LINQ kunokubakho imeko enye yokudibanisa, ke apha kuyenzeka ukuba wenze umbuzo olinganayo usebenzisa imibuzo emibini kwimeko nganye kwaye emva koko uzidibanise ngeManyano ukususa impinda phakathi kwemigca.
Ewe, imibuzo iya kuba ingalingani, kuthathelwa ingqalelo ukuba irewu eziphindwe kabini zinokubuyiswa. Nangona kunjalo, kubomi bokwenyani, imigca ephindwe kabini ayidingeki kwaye abantu bazama ukuyisusa.

Ngoku makhe sithelekise izicwangciso zokwenza le mibuzo mibini:

ku-CROSS JOIN umndilili wexesha lophumezo yimizuzwana eyi-195:
ku-INNER JOIN-UNION umndilili wexesha lokwenziwa lingaphantsi kwemizuzwana engama-24:

Njengoko unokubona kwiziphumo, kwiitheyibhile ezimbini ezinezigidi zeerekhodi, umbuzo olungiselelwe i-LINQ ukhawuleza ngokuphindwe kaninzi kunolowo ungalungiswanga.

Kukhetho nge AND kwiimeko, LINQ umbuzo wefomu:

Umbuzo weLINQ

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

Umbuzo ochanekileyo weSQL uza kusoloko uveliswa, oya kuqhuba ngokomndilili malunga nomzuzwana omnye:

Kwakhona kwi-LINQ kwi-Objects manipulations endaweni yombuzo onje:

Umbuzo we-LINQ (ukhetho lokuqala)

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

ungasebenzisa umbuzo onje:

Umbuzo we-LINQ (ukhetho lokuqala)

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

apho:

Ukuchaza iindidi ezimbini

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

, kunye nodidi lwePara luchazwa ngolu hlobo lulandelayo:

Para Type Definition

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

Ke, sivavanye imiba ethile ekwandiseni imibuzo ye-LINQ kwi-MS SQL Server.

Ngelishwa, nabaphuhlisi be-NET abanamava nabakhokelayo bayalibala ukuba kufuneka baqonde ukuba yintoni imiyalelo abayisebenzisayo emva kwemifanekiso. Ngaphandle koko, baba ngabaqulunqi kwaye banokutyala ibhombu yexesha kwixesha elizayo xa ulinganisa isisombululo sesoftware kunye notshintsho oluncinci kwiimeko zangaphandle zokusingqongileyo.

Kwaye kwenziwa uphononongo olufutshane apha.

Imithombo yovavanyo - iprojekthi ngokwayo, ukudalwa kweetafile kwi-database ye-TEST, kunye nokuzalisa ezi tafile ngedatha zibekwe apha.
Kwakhona kule ndawo yokugcina, kwifolda yeZicwangciso, kukho izicwangciso zokwenza imibuzo OKANYE neemeko.

umthombo: www.habr.com

Eminye imiba yokuphucula imibuzo ye-LINQ kwi-C#.NET ye-MS SQL Server

Yongeza izimvo Phendula impendulo