🥇 Diẹ ninu awọn aaye ti iṣapeye awọn ibeere LINQ ni C#.NET fun MS SQL Server

LINQ ti tẹ .NET wọle gẹgẹbi ede ifọwọyi data tuntun ti o lagbara. LINQ si SQL gẹgẹbi apakan ti o gba ọ laaye lati ṣe ibaraẹnisọrọ ni irọrun pẹlu DBMS kan nipa lilo, fun apẹẹrẹ, Ilana Ohun elo. Sibẹsibẹ, lilo rẹ ni igbagbogbo, awọn olupilẹṣẹ gbagbe lati wo iru ibeere SQL wo ni olupese ti o le beere, ninu ọran rẹ Ilana Ohun elo, yoo ṣe ipilẹṣẹ.

Jẹ́ ká wo àwọn kókó pàtàkì méjì nípa lílo àpẹẹrẹ kan.
Lati ṣe eyi, ṣẹda aaye data idanwo ni SQL Server, ati ṣẹda awọn tabili meji ninu rẹ nipa lilo ibeere atẹle:

Ṣiṣẹda tabili

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

Bayi jẹ ki a ṣe agbejade tabili Ref nipa ṣiṣe iwe afọwọkọ atẹle yii:

Àgbáye tabili 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

Jẹ ki a bakan naa kun tabili Onibara ni lilo iwe afọwọkọ atẹle:

Population tabili Onibara

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

Nitorinaa, a gba awọn tabili meji, ọkan ninu eyiti o ni diẹ sii ju awọn ori ila miliọnu 1 ti data, ati ekeji ni diẹ sii ju awọn ori ila miliọnu 10 ti data.

Bayi ni Visual Studio o nilo lati ṣẹda idanwo Visual C # Console App (.NET Framework) iṣẹ akanṣe:

Nigbamii, o nilo lati ṣafikun ile-ikawe kan fun Ilana Ohun elo lati ṣe ajọṣepọ pẹlu data data.
Lati ṣafikun rẹ, tẹ-ọtun lori iṣẹ akanṣe naa ki o yan Ṣakoso awọn akopọ NuGet lati inu akojọ ọrọ:

Lẹhinna, ninu window iṣakoso package NuGet ti o han, tẹ ọrọ naa “Ilana Ohun elo” ni window wiwa ki o yan package Framework Ohun elo ki o fi sii:

Nigbamii, ninu faili App.config, lẹhin tiipa awọn apakan atunto, o nilo lati ṣafikun bulọọki atẹle naa:

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

Ni asopọOkun o nilo lati tẹ okun asopọ sii.

Bayi jẹ ki a ṣẹda awọn atọkun mẹta ni awọn faili lọtọ:

Ṣiṣe wiwo IBaseEntityID

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

Imuse ti IBaseEntityName ni wiwo

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

Imuse ti IBaseNameInsertUTCDate ni wiwo

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

Ati ninu faili lọtọ a yoo ṣẹda ipilẹ kilasi BaseEntity fun awọn nkan meji wa, eyiti yoo pẹlu awọn aaye ti o wọpọ:

Imuse ti ipilẹ kilasi BaseEntity

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

Nigbamii, a yoo ṣẹda awọn nkan wa meji ni awọn faili lọtọ:

Imuse ti Ref kilasi

using System.ComponentModel.DataAnnotations.Schema;

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

Imuse ti Onibara kilasi

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

Bayi jẹ ki a ṣẹda ọrọ-ọrọ UserContext ni faili lọtọ:

Imuse ti UserContex kilasi

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

A gba ojutu ti a ti ṣetan fun ṣiṣe awọn idanwo iṣapeye pẹlu LINQ si SQL nipasẹ EF fun MS SQL Server:

Bayi tẹ koodu atẹle sii sinu faili Program.cs:

Program.cs faili

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

Next, jẹ ki ká lọlẹ wa ise agbese.

Ni ipari iṣẹ naa, atẹle naa yoo han lori console:

Ibere SQL ti ipilẹṣẹ

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

Iyẹn ni, ni gbogbogbo, ibeere LINQ ṣe ipilẹṣẹ ibeere SQL kan si MS SQL Server DBMS daradara daradara.

Bayi jẹ ki a yi ipo ATI pada si OR ninu ibeere LINQ:

LINQ ibeere

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

Ki o si jẹ ki ká lọlẹ wa elo lẹẹkansi.

Ipaniyan yoo jamba pẹlu aṣiṣe nitori akoko ipaniyan aṣẹ ti o kọja awọn aaya 30:

Ti o ba wo ibeere ti o jẹ ipilẹṣẹ nipasẹ LINQ:

, lẹhinna o le rii daju pe yiyan waye nipasẹ ọja Cartesian ti awọn eto meji (awọn tabili):

Ibere SQL ti ipilẹṣẹ

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]

Jẹ ki a tun ibeere LINQ kọ bi atẹle:

Ibeere LINQ iṣapeye

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

Lẹhinna a gba ibeere SQL atẹle yii:

SQL ibeere

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]

Alas, ninu awọn ibeere LINQ o le jẹ ipo idapọ kan nikan, nitorinaa nibi o ṣee ṣe lati ṣe ibeere deede nipa lilo awọn ibeere meji fun ipo kọọkan ati lẹhinna apapọ wọn nipasẹ Union lati yọ awọn ẹda-iwe kuro laarin awọn ori ila.
Bẹẹni, awọn ibeere ni gbogbogbo yoo jẹ ti kii ṣe deede, ni akiyesi pe awọn ori ila ẹda pipe le jẹ pada. Sibẹsibẹ, ni igbesi aye gidi, awọn laini ẹda pipe ko nilo ati pe eniyan gbiyanju lati yọ wọn kuro.

Bayi jẹ ki a ṣe afiwe awọn ero ipaniyan ti awọn ibeere meji wọnyi:

fun AGBALAGBA ARA, apapọ akoko ipaniyan jẹ iṣẹju-aaya 195:
fun IINER JOIN-UNION apapọ akoko ipaniyan ko kere ju iṣẹju-aaya 24:

Gẹgẹbi o ti le rii lati awọn abajade, fun awọn tabili meji pẹlu awọn miliọnu awọn igbasilẹ, ibeere LINQ ti iṣapeye jẹ ọpọlọpọ igba yiyara ju ọkan ti a ko mu dara lọ.

Fun aṣayan pẹlu ATI ni awọn ipo, ibeere LINQ ti fọọmu naa:

LINQ ibeere

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

Ibeere SQL ti o pe yoo fẹrẹ jẹ ipilẹṣẹ nigbagbogbo, eyiti yoo ṣiṣẹ ni apapọ ni bii iṣẹju 1:

Paapaa fun LINQ si Awọn ifọwọyi Awọn nkan dipo ibeere bii:

Ibeere LINQ (aṣayan akọkọ)

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

o le lo ibeere bii:

Ibeere LINQ (aṣayan akọkọ)

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

nibo ni:

Asọye meji orun

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

, ati iru Para jẹ asọye bi atẹle:

Para Iru Definition

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

Nitorinaa, a ṣe ayẹwo diẹ ninu awọn aaye ni jijẹ awọn ibeere LINQ si MS SQL Server.

Laanu, paapaa ti o ni iriri ati asiwaju .NET Difelopa gbagbe pe wọn nilo lati ni oye kini awọn ilana ti wọn lo ṣe lẹhin awọn iṣẹlẹ. Bibẹẹkọ, wọn di awọn atunto ati pe wọn le gbin bombu akoko ni ọjọ iwaju mejeeji nigba iwọn ojutu sọfitiwia ati pẹlu awọn ayipada kekere ni awọn ipo ayika ita.

Atunwo kukuru kan tun ṣe nibi.

Awọn orisun fun idanwo naa - iṣẹ akanṣe funrararẹ, ṣiṣẹda awọn tabili ni aaye data TEST, ati kikun awọn tabili wọnyi pẹlu data wa. nibi.
Paapaa ninu ibi ipamọ yii, ninu folda Awọn eto, awọn ero wa fun ṣiṣe awọn ibeere pẹlu awọn ipo OR.

orisun: www.habr.com

Diẹ ninu awọn aaye ti iṣapeye awọn ibeere LINQ ni C #.NET fun MS SQL Server

Fi ọrọìwòye kun Fagilee esi