🥇Wasu fannoni na inganta tambayoyin LINQ a cikin C#.NET don MS SQL Server

LINQ ya shigar da NET a matsayin sabon harshe mai ƙarfi na sarrafa bayanai. LINQ zuwa SQL a matsayin ɓangare na sa yana ba ku damar sadarwa cikin dacewa tare da DBMS ta amfani da, misali, Tsarin Mahalli. Koyaya, yin amfani da shi sau da yawa, masu haɓakawa suna mantawa don duba wane nau'in tambaya ta SQL mai bada abin tambaya, a cikin yanayin Tsarin Haɗin kai, zai haifar.

Bari mu kalli muhimman abubuwa biyu ta amfani da misali.
Don yin wannan, ƙirƙirar bayanan gwaji a cikin SQL Server, kuma ƙirƙirar tebur biyu a ciki ta amfani da tambaya mai zuwa:

Ƙirƙirar teburi

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

Yanzu bari mu cika teburin Ref ta hanyar gudanar da rubutun mai zuwa:

Cika teburin 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

Hakazalika mu cika teburin Abokin ciniki ta amfani da rubutun mai zuwa:

Yawa teburin abokin ciniki

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

Don haka, mun sami tebur biyu, ɗayan yana da fiye da layuka miliyan 1 na bayanai, ɗayan kuma yana da fiye da layuka miliyan 10 na bayanai.

Yanzu cikin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin), kana buƙatar ƙirƙirar gwajin Kayayyakin C # Console App (.NET Framework):

Na gaba, kuna buƙatar ƙara ɗakin karatu don Tsarin Mahalli don yin hulɗa tare da bayanan bayanai.
Don ƙara shi, danna-dama akan aikin kuma zaɓi Sarrafa Fakitin NuGet daga menu na mahallin:

Bayan haka, a cikin taga sarrafa fakitin NuGet da ke bayyana, shigar da kalmar “Tsarin Tsarin Gida” a cikin taga binciken kuma zaɓi fakitin Tsarin tsari kuma shigar da shi:

Na gaba, a cikin fayil ɗin App.config, bayan rufe sashin daidaitawa, kuna buƙatar ƙara toshe mai zuwa:

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

A danganeString kana buƙatar shigar da igiyoyin haɗin.

Yanzu bari mu ƙirƙiri musaya guda 3 a cikin fayiloli daban:

Aiwatar da hanyar sadarwa ta IBaseEntityID

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

Aiwatar da haɗin gwiwar IBaseEntityName

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

Aiwatar da haɗin gwiwar IBaseNameInsertUTCDate

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

Kuma a cikin wani fayil ɗin daban za mu ƙirƙiri tushe ajin BaseEntity don ƙungiyoyinmu guda biyu, waɗanda zasu haɗa da filayen gama gari:

Aiwatar da rukunin tushe BaseEntity

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

Na gaba, za mu ƙirƙiri abubuwan mu guda biyu a cikin fayiloli daban-daban:

Aiwatar da ajin Ref

using System.ComponentModel.DataAnnotations.Schema;

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

Aiwatar da ajin Abokin ciniki

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

Yanzu bari mu ƙirƙiri mahallin UserContext a cikin wani fayil daban:

Aiwatar da ajin 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; }
    }
}

Mun sami ingantaccen bayani don gudanar da gwaje-gwajen ingantawa tare da LINQ zuwa SQL ta EF don MS SQL Server:

Yanzu shigar da lambar mai zuwa cikin fayil ɗin Program.cs:

Program.cs fayil

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

Na gaba, bari mu ƙaddamar da aikin mu.

A ƙarshen aikin, za a nuna masu zuwa akan na'urar wasan bidiyo:

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

Wato, gabaɗaya, tambayar LINQ ta haifar da tambayar SQL zuwa MS SQL Server DBMS sosai.

Yanzu bari mu canza yanayin DA zuwa OR a cikin tambayar LINQ:

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

Kuma bari mu sake kaddamar da aikace-aikacen mu.

Kisa zai fadi tare da kuskure saboda lokacin aiwatar da umarnin da ya wuce 30 seconds:

Idan ka kalli tambayar da LINQ ta yi:

, to, za ku iya tabbatar da cewa zaɓin ya faru ta hanyar samfurin Cartesian na saiti biyu (tebur):

Neman Tambayar 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]

Bari mu sake rubuta tambayar LINQ kamar haka:

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

Sannan muna samun tambayar SQL mai zuwa:

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

Alas, a cikin tambayoyin LINQ ba za a iya samun yanayin haɗawa ɗaya kawai ba, don haka a nan yana yiwuwa a yi tambaya daidai ta amfani da tambayoyi biyu don kowane yanayi sannan a haɗa su ta hanyar Union don cire kwafi a cikin layuka.
Ee, tambayoyin gabaɗaya ba za su zama daidai ba, la'akari da cewa ana iya dawo da cikakkun layuka da aka kwafi. Koyaya, a cikin rayuwa ta gaske, ba a buƙatar cikakken layukan kwafi kuma mutane suna ƙoƙarin kawar da su.

Yanzu bari mu kwatanta tsare-tsaren aiwatar da waɗannan tambayoyin guda biyu:

don CROSS JOIN matsakaicin lokacin aiwatarwa shine daƙiƙa 195:
don INNER JOIN-UNION matsakaicin lokacin aiwatarwa bai wuce daƙiƙa 24 ba:

Kamar yadda kuke gani daga sakamakon, don tebur biyu tare da miliyoyin bayanai, ingantaccen tambayar LINQ ya ninka sau da yawa fiye da wanda ba a inganta shi ba.

Don zaɓi tare da AND a cikin sharuɗɗa, tambayar LINQ na fom:

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

Madaidaicin tambayar SQL kusan koyaushe za'a samar dashi, wanda zai gudana akan matsakaita cikin kusan daƙiƙa 1:

Hakanan don LINQ zuwa Abubuwan magudi maimakon tambaya kamar:

Tambayar LINQ (zaɓi na farko)

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

zaka iya amfani da tambaya kamar:

Tambayar LINQ (zaɓi na farko)

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

inda:

Ma'anar tsararraki biyu

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

, kuma ana siffanta nau'in Para kamar haka:

Para Nau'in Ma'anar

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

Don haka, mun bincika wasu fannoni wajen inganta tambayoyin LINQ zuwa MS SQL Server.

Abin baƙin ciki, har ma da gogaggen .NET masu haɓakawa sun manta cewa suna buƙatar fahimtar abin da umarnin da suke amfani da su ke yi a bayan al'amuran. In ba haka ba, za su zama masu daidaitawa kuma suna iya dasa bam na lokaci a nan gaba duka lokacin da za a daidaita maganin software kuma tare da ƙananan canje-canje a yanayin muhalli na waje.

An kuma yi ɗan gajeren bita a nan.

Abubuwan da aka samo don gwajin - aikin da kansa, ƙirƙirar tebur a cikin bayanan TEST, da kuma cika waɗannan tebur tare da bayanai suna samuwa. a nan.
Hakanan a cikin wannan ma'ajiyar, a cikin babban fayil ɗin Tsare-tsare, akwai shirye-shiryen aiwatar da tambayoyi tare da OR yanayi.

source: www.habr.com

Wasu fannoni na inganta tambayoyin LINQ a cikin C#.NET don MS SQL Server

Add a comment Отменить ответ