🥇 Cuid de thaobhan de bhith ag ùrachadh cheistean LINQ ann an C#.NET airson MS SQL Server

Chaidh LINQ a-steach gu .NET mar chànan làimhseachaidh dàta cumhachdach ùr. Leigidh LINQ gu SQL mar phàirt dheth dhut conaltradh gu math goireasach le DBMS a’ cleachdadh, mar eisimpleir, Entity Framework. Ach, le bhith ga chleachdadh gu math tric, bidh luchd-leasachaidh a’ dìochuimhneachadh coimhead air dè an seòrsa ceist SQL a ghineas an solaraiche ceasnachail, nad chùis agad Entity Framework.

Bheir sinn sùil air dà phrìomh phuing a’ cleachdadh eisimpleir.
Gus seo a dhèanamh, cruthaich stòr-dàta Deuchainn ann an SQL Server, agus cruthaich dà chlàr ann leis a’ cheist a leanas:

A 'cruthachadh chlàran

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

A-nis leig dhuinn an clàr Ref a lìonadh le bhith a’ ruith an sgriobt a leanas:

A’ lìonadh a’ bhùird 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

Mar an ceudna lìonaidh sinn clàr an Luchd-ceannach leis an sgriobt a leanas:

A 'lìonadh clàr luchd-cleachdaidh

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

Mar sin, fhuair sinn dà chlàr, aon dhiubh le còrr air 1 millean sreath de dhàta, agus am fear eile le còrr air 10 millean sreath de dhàta.

A-nis ann an Visual Studio feumaidh tu pròiseact deuchainn Visual C # Console App (.NET Framework) a chruthachadh:

An ath rud, feumaidh tu leabharlann a chuir ris airson an Frèam Aonad gus eadar-obrachadh leis an stòr-dàta.
Gus a chuir ris, cliog deas air a’ phròiseact agus tagh Stiùirich pacaidean NuGet bhon chlàr co-theacsa:

An uairsin, anns an uinneag riaghlaidh pacaid NuGet a nochdas, cuir a-steach am facal “Entity Framework” san uinneag sgrùdaidh agus tagh am pasgan Entity Framework agus stàlaich e:

An uairsin, anns an fhaidhle App.config, às deidh dhut an eileamaid configSections a dhùnadh, feumaidh tu am bloc a leanas a chuir ris:

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

Ann an connectionString feumaidh tu an sreang ceangail a chuir a-steach.

A-nis cruthaichidh sinn 3 eadar-aghaidh ann am faidhlichean fa leth:

Cur an gnìomh an eadar-aghaidh iBaseEntityID

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

Cur an gnìomh an eadar-aghaidh iBaseEntityName

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

Cur an gnìomh an eadar-aghaidh IBaseNameInsertUTCDate

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

Agus ann am faidhle air leth cruthaichidh sinn BaseEntity clas bunaiteach airson an dà bhuidheann againn, a bheir a-steach raointean cumanta:

Cur an gnìomh a’ bhun-chlas BaseEntity

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

An ath rud, cruthaichidh sinn an dà bhuidheann againn ann am faidhlichean fa leth:

Cur an gnìomh clas Ref

using System.ComponentModel.DataAnnotations.Schema;

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

Cur an gnìomh clas Luchd-cleachdaidh

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

A-nis cruthaichidh sinn co-theacsa UserContext ann am faidhle air leth:

Cur an gnìomh clas 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; }
    }
}

Fhuair sinn fuasgladh deiseil airson deuchainnean optimization a dhèanamh le LINQ gu SQL tro EF airson MS SQL Server:

A-nis cuir a-steach an còd a leanas a-steach don fhaidhle Program.cs:

Prògram.cs faidhle

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

An ath rud, leig dhuinn ar pròiseact a chuir air bhog.

Aig deireadh na h-obrach, thèid na leanas a thaisbeanadh air a’ chonsail:

Ceist SQL air a chruthachadh

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

Is e sin, san fharsaingeachd, chruthaich a’ cheist LINQ ceist SQL don MS SQL Server DBMS gu math.

A-nis atharraichidh sinn an suidheachadh AND gu OR anns a’ cheist LINQ:

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

Agus leig leinn an tagradh againn a chuir air bhog a-rithist.

Tuitidh an cur gu bàs le mearachd leis gu bheil ùine cur an gnìomh nan òrduighean nas fhaide na 30 diog:

Ma choimheadas tu air a’ cheist a chaidh a chruthachadh le LINQ:

, an uairsin faodaidh tu dèanamh cinnteach gu bheil an taghadh a’ tachairt tro thoradh Cartesian de dhà sheata (clàran):

Ceist SQL air a chruthachadh

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]

Dèanamaid ath-sgrìobhadh air a’ cheist LINQ mar a leanas:

Ceist LINQ as fheàrr

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

An uairsin gheibh sinn a’ cheist SQL a leanas:

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

Gu mì-fhortanach, ann an ceistean LINQ chan urrainn ach aon chumha ceangail a bhith ann, agus mar sin an seo tha e comasach ceist co-ionann a dhèanamh a’ cleachdadh dà cheist airson gach cùmhnant agus an uairsin gan cur còmhla tron Aonadh gus dùblaidhean a thoirt air falbh am measg nan sreathan.
Bidh, mar as trice bidh na ceistean neo-ionann, a’ gabhail a-steach gum faodar sreathan dùblaichte iomlan a thilleadh. Ach, ann am fìor bheatha, chan eil feum air loidhnichean dùblaichte iomlan agus bidh daoine a’ feuchainn ri faighinn cuidhteas iad.

A-nis dèanamaid coimeas eadar planaichean cur gu bàs an dà cheist seo:

airson CROSS JOIN is e an ùine cur gu bàs cuibheasach 195 diogan:
airson INNER JOIN-UNION tha an ùine cur gu bàs cuibheasach nas lugha na 24 diogan:

Mar a chì thu bho na toraidhean, airson dà chlàr le milleanan de chlàran, tha a’ cheist LINQ làn-leasaichte iomadh uair nas luaithe na am fear neo-leasaichte.

Airson an roghainn le AND anns na cumhaichean, ceist LINQ den fhoirm:

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

Cha mhòr nach tèid a’ cheist SQL ceart a chruthachadh an-còmhnaidh, a ruitheas gu cuibheasach ann an timcheall air 1 diog:

Cuideachd airson làimhseachadh LINQ to Objects an àite ceist mar:

Ceist LINQ (roghainn 1d)

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

faodaidh tu ceist mar:

Ceist LINQ (roghainn 2d)

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

far:

A 'mìneachadh dà shreath

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

, agus tha an seòrsa Para air a mhìneachadh mar a leanas:

Seòrsa Mìneachadh Para

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

Mar sin, rinn sinn sgrùdadh air cuid de thaobhan ann a bhith ag àrdachadh cheistean LINQ gu MS SQL Server.

Gu mì-fhortanach, tha eadhon luchd-leasachaidh eòlach agus adhartach .NET a 'dìochuimhneachadh gum feum iad tuigsinn dè a tha an stiùireadh a chleachdas iad a' dèanamh air cùl na seallaidhean. Rud eile, bidh iad nan luchd-rèiteachaidh agus faodaidh iad boma ùine a chuir san àm ri teachd an dà chuid nuair a bhios iad a’ sgèileadh fuasgladh bathar-bog agus le atharrachaidhean beaga ann an suidheachaidhean àrainneachd a-muigh.

Chaidh ath-sgrùdadh goirid a dhèanamh cuideachd an seo.

Tha na stòran airson an deuchainn - am pròiseact fhèin, cruthachadh chlàran ann an stòr-dàta TEST, a bharrachd air na clàran sin a lìonadh le dàta air an suidheachadh an seo.
Cuideachd anns an stòras seo, ann am pasgan nam Planaichean, tha planaichean ann airson ceistean a chur an gnìomh le cumhachan OR.

Source: www.habr.com

Cuid de thaobhan de bhith ag àrdachadh cheistean LINQ ann an C#.NET airson MS SQL Server

Cuir beachd ann freagairt a chur dheth