🥇Mamwe maitiro ekugonesa LINQ mibvunzo muC#.NET yeMS SQL Server

LINQ yakapinda .NET semutauro mutsva une simba wekushandisa data. LINQ kuSQL sechikamu chayo inokutendera kuti utaure zviri nyore neDBMS uchishandisa, semuenzaniso, Entity Framework. Nekudaro, nekuishandisa kazhinji, vanogadzira vanokanganwa kutarisa kuti rudzii rweSQL query iyo inobvunzwa mupi, mune yako Entity Framework, ichagadzira.

Ngatitarisei pfungwa huru mbiri tichishandisa muenzaniso.
Kuti uite izvi, gadzira dhatabhesi yeTest muSQL Server, uye gadzira matafura maviri mairi uchishandisa mubvunzo unotevera:

Kugadzira matafura

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

Zvino ngatizadzei Ref tafura nekumhanyisa inotevera script:

Kuzadza tafura 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

Ngatizadzewo tafura yeMutengi tichishandisa script inotevera:

Kuzadza tafura yeMutengi

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

Nokudaro, takagamuchira matafura maviri, imwe ine mitsara inopfuura mamiriyoni e data, uye imwe ine mitsara inopfuura mamiriyoni gumi yedata.

Iye zvino muVisual Studio unofanirwa kugadzira bvunzo Visual C# Console App (.NET Framework) chirongwa:

Tevere, iwe unofanirwa kuwedzera raibhurari yeEntity Framework kuti iwirirane nedhatabhesi.
Kuti uiwedzere, tinya-kurudyi pane purojekiti uye sarudza Manage NuGet Packages kubva pane yemukati menyu:

Zvadaro, muNuGet package management hwindo rinoonekwa, isa izwi rekuti "Entity Framework" muhwindo rekutsvaga uye sarudza Entity Framework package woiisa:

Tevere, muApp.config faira, mushure mekuvhara configSections element, unofanirwa kuwedzera chivharo chinotevera:

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

MuConnectString unoda kuisa tambo yekubatanidza.

Iye zvino ngatigadzirei 3 interfaces mumafaira akasiyana:

Kuita iyo IBaseEntityID interface

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

Kuitwa kweiyo IBaseEntityName interface

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

Kuitwa kweiyo IBaseNameInsertUTCDate interface

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

Uye mune yakaparadzana faira isu tichagadzira base kirasi BaseEntity kune edu maviri masangano, ayo anosanganisira akajairwa minda:

Kuitwa kweiyo base kirasi BaseEntity

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

Tevere, isu tichagadzira masangano edu maviri mumafaira akasiyana:

Kuitwa kweRef kirasi

using System.ComponentModel.DataAnnotations.Schema;

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

Kuitwa kweMutengi kirasi

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

Zvino ngatigadzirei UserContext mamiriro mune imwe faira rakasiyana:

Kuitwa kweiyo UserContex kirasi

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

Takagamuchira yakagadzirira-yakagadziriswa mhinduro yekuitisa optimization bvunzo neLINQ kuenda kuSQL kuburikidza neEF yeMS SQL Server:

Zvino isa kodhi inotevera muProgram.cs faira:

Program.cs file

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

Tevere, ngatitangei chirongwa chedu.

Pakupera kwebasa, zvinotevera zvicharatidzwa pane console:

Yakagadzirwa SQL Query

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

Izvi zvinoreva, kazhinji, mubvunzo weLINQ wakagadzira mubvunzo weSQL kuMS SQL Server DBMS zvakanaka.

Zvino ngatichinje uye mamiriro kuti OR mumubvunzo weLINQ:

LINQ mubvunzo

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

Uye ngatitangei application yedu zvakare.

Iko kuurayiwa kuchapunzika nekukanganisa nekuda kwenguva yekuraira inodarika masekonzi makumi matatu:

Kana iwe ukatarisa mubvunzo wakagadzirwa neLINQ:

, ipapo iwe unogona kuve nechokwadi chekuti kusarudzwa kunoitika kuburikidza neCartesian chigadzirwa chemaseti maviri (matafura):

Yakagadzirwa SQL Query

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]

Ngatinyoreizve mubvunzo weLINQ sezvinotevera:

Yakakwenenzvera LINQ mubvunzo

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

Ipapo tinowana inotevera SQL mubvunzo:

SQL mubvunzo

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]

Maiwe, muLINQ mibvunzo panogona kuve nechimiro chimwe chete chekubatanidza, saka pano zvinokwanisika kuita mubvunzo wakaenzana uchishandisa mibvunzo miviri pamamiriro ega ega uye wozoisanganisa kuburikidza ne Union kubvisa zvakapetwa pakati pemitsara.
Hongu, iyo mivhunzo kazhinji inenge isina kuenzana, tichifunga kuti mitsara yakazara inodzokororwa inogona kudzoserwa. Nekudaro, muhupenyu chaihwo, mitsara yakazara yakadhindwa haidiwe uye vanhu vanoedza kuibvisa.

Zvino ngatienzanisei zvirongwa zvekuuraya zveiyi mibvunzo miviri:

yeCROSS JOIN avhareji yenguva yekuuraya ndeye 195 masekonzi:
ye INNER JOIN-UNION nguva yeavhareji yekuuraya isingasviki masekonzi makumi maviri nemana:

Sezvauri kuona kubva pane zvawanikwa, kumatafura maviri ane mamirioni emarekodhi, iyo yakagadziridzwa LINQ muvhunzo inokurumidza kakawanda kupfuura iyo isina kunyatsogadziriswa.

Kune iyo sarudzo ine UYE mumamiriro ezvinhu, mubvunzo weLINQ wefomu:

LINQ mubvunzo

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

Iyo chaiyo SQL query inogara ichigadzirwa, iyo inomhanya paavhareji mukati mesekondi imwe:

Zvakare zveLINQ kuZvinhu manipulations pachinzvimbo chemubvunzo senge:

LINQ mubvunzo (yekutanga sarudzo)

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

unogona kushandisa mubvunzo wakafanana:

LINQ mubvunzo (yekutanga sarudzo)

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

kupi:

Kutsanangura mitsara miviri

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

, uye Para type inotsanangurwa seizvi:

Para Type Definition

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

Nekudaro, isu takaongorora zvimwe zvinhu mukugadzirisa LINQ mibvunzo kune MS SQL Server.

Nehurombo, kunyangwe vane ruzivo uye vanotungamira .NET vagadziri vanokanganwa kuti vanofanirwa kunzwisisa izvo mirairo yavanoshandisa kuseri kwezviitiko. Zvikasadaro, ivo vanove vanogadzirisa uye vanogona kudyara bhomba renguva mune ramangwana kana kuyera software mhinduro uye nediki shanduko mumamiriro ekunze ekunze.

Ongororo pfupi yakaitwa zvakare pano.

Izvo zvitubu zvebvunzo - iyo purojekiti pachayo, kusikwa kwematafura muTEST dhatabhesi, pamwe nekuzadza aya matafura nedata anowanikwa. pano.
Zvakare mune ino repository, muZvirongwa folda, pane zvirongwa zvekuita mibvunzo neOR mamiriro.

Source: www.habr.com

Zvimwe zvekugadzirisa LINQ mibvunzo muC#.NET yeMS SQL Server

Voeg kukanzura mhinduro