🥇 Kekahi mau hiʻohiʻona o ka ʻimi ʻana i nā nīnau LINQ ma C#.NET no MS SQL Server

Ua komo ʻo LINQ i ka .NET ma ke ʻano he ʻōlelo hoʻopunipuni ʻikepili hou. ʻO LINQ i SQL ma ke ʻano he mea hiki iā ʻoe ke kamaʻilio maʻalahi me kahi DBMS me ka hoʻohana ʻana, no ka laʻana, Entity Framework. Eia nō naʻe, me ka hoʻohana pinepine ʻana iā ia, poina nā mea hoʻomohala e nānā i ke ʻano o ka nīnau SQL a ka mea hoʻolako noiʻi, i kāu hihia Entity Framework.

E nānā kākou i ʻelua mau manaʻo nui me ka hoʻohana ʻana i kahi laʻana.
No ka hana ʻana i kēia, hana i kahi waihona hōʻike hōʻike ma SQL Server, a hana i ʻelua papa i loko me ka hoʻohana ʻana i kēia nīnau:

Ke hana nei i nā papa

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

I kēia manawa, e hoʻopiha i ka papa Ref ma ka holo ʻana i kēia ʻatikala:

Hoʻopiha i ka papa 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

E hoʻopiha like kāua i ka papa Customer me ka hoʻohana ʻana i kēia palapala:

Hoʻopili i ka pākaukau Customer

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

No laila, ua loaʻa iā mākou nā papa ʻelua, ʻo kekahi o ia i ʻoi aku ma mua o 1 miliona mau lālani ʻikepili, a ʻo kekahi ʻoi aku ma mua o 10 miliona mau lālani ʻikepili.

I kēia manawa ma Visual Studio pono ʻoe e hana i kahi papahana Visual C# Console App (.NET Framework):

A laila, pono ʻoe e hoʻohui i kahi waihona no ka Entity Framework e launa pū me ka waihona.
No ka hoʻohui ʻana, kaomi ʻākau i ka papahana a koho i ka Manage NuGet Packages mai ka papa kuhikuhi.

A laila, i ka puka makani hoʻokele ʻo NuGet e hōʻike ʻia, e hoʻokomo i ka huaʻōlelo "Entity Framework" i ka puka hulina a koho i ka pūʻolo Entity Framework a hoʻokomo iā ia:

A laila, i ka faila App.config, ma hope o ka pani ʻana i ka configSections element, pono ʻoe e hoʻohui i kēia poloka:

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

Ma connectionString pono e komo i ke kaula pili.

I kēia manawa, e hana mākou i 3 mau kikowaena i nā faila ʻokoʻa:

Ke hoʻokō nei i ka interface IBaseEntityID

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

Ka hoʻokō ʻana i ka interface IBaseEntityName

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

Ka hoʻokō ʻana i ka interface IBaseNameInsertUTCDate

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

A i loko o kahi faila ʻokoʻa mākou e hana i kahi papa base BaseEntity no kā mākou mau mea ʻelua, e komo pū me nā māla maʻamau:

Ka hoʻokō ʻana i ka papa kumu BaseEntity

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

A laila, e hana mākou i kā mākou mau mea ʻelua i nā faila ʻokoʻa:

Hoʻokō i ka papa Ref

using System.ComponentModel.DataAnnotations.Schema;

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

Hoʻokō i ka papa Customer

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

I kēia manawa, e hana mākou i kahi ʻikepili UserContext i kahi faila ʻokoʻa:

Ka hoʻokō ʻana i ka papa 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; }
    }
}

Ua loaʻa iā mākou kahi hopena i mākaukau no ka hoʻokō ʻana i nā hoʻāʻo ʻoi loa me LINQ i SQL ma o EF no MS SQL Server:

E hoʻokomo i kēia code i loko o ka faila Program.cs:

File program.cs

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

A laila, e hoʻomaka i kā mākou papahana.

I ka pau ʻana o ka hana, e hōʻike ʻia kēia ma ka console:

Hana ʻia ʻo 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])

ʻO ia hoʻi, ma ka laulā, ua hoʻokumu ka nīnau LINQ i kahi nīnau SQL i ka MS SQL Server DBMS maikaʻi loa.

I kēia manawa e hoʻololi i ke kūlana AND i OR i ka nīnau LINQ:

Nīnau 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 };

A e hoʻomaka hou i kā mākou noi.

E hāʻule ka hoʻokō me ka hewa ma muli o ka manawa hoʻokō kauoha ma mua o 30 kekona:

Inā ʻoe e nānā i ka nīnau i hana ʻia e LINQ:

, a laila hiki iā ʻoe ke hōʻoia i ke koho ʻia ʻana ma o ka huahana Cartesian o ʻelua pūʻulu (papa):

Hana ʻia ʻo 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]

E kākau hou i ka nīnau LINQ penei:

ʻO ka hulina LINQ maikaʻi loa

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

A laila loaʻa iā mākou ka nīnau SQL penei:

Nīnau 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]

Auwe, i loko o nā nīnau LINQ hiki ke loaʻa hoʻokahi kūlana hui, no laila hiki ke hana i kahi nīnau like me ka hoʻohana ʻana i ʻelua mau nīnau no kēlā me kēia kūlana a laila hoʻohui iā lākou ma o Union e wehe i nā kope ma waena o nā lālani.
ʻAe, ʻaʻole like nā nīnau, me ka noʻonoʻo e hiki ke hoʻihoʻi ʻia nā lālani pālua. Eia naʻe, i ke ola maoli, ʻaʻole pono nā laina pālua piha a hoʻāʻo nā kānaka e kāpae iā lākou.

I kēia manawa e hoʻohālikelike kākou i nā hoʻolālā hoʻokō o kēia mau nīnau ʻelua:

no CROSS JOIN ka awelika o ka manawa hana he 195 kekona:
no INNER JOIN-UNION ka awelika o ka manawa hoʻokō ma lalo o 24 kekona:

E like me kāu e ʻike ai mai nā hopena, no nā papa ʻelua me nā miliona o nā moʻolelo, ʻoi aku ka wikiwiki o ka hulina LINQ i ʻoi aku ka wikiwiki ma mua o ka mea i hoʻopaʻa ʻole ʻia.

No ke koho me AND i nā kūlana, he nīnau LINQ o ke ʻano:

Nīnau 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 };

Aneane e hana ʻia ka hulina SQL pololei, e holo ana ma ka awelika ma kahi o 1 kekona:

No ka LINQ i nā manipulations ma kahi o kahi nīnau e like me:

Nīnau LINQ (koho mua)

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

hiki iā ʻoe ke hoʻohana i kahi nīnau e like me:

Nīnau LINQ (koho mua)

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

kahi:

ʻO ka wehewehe ʻana i ʻelua papa

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

, a ua wehewehe ʻia ke ʻano Para penei:

Wehewehe ʻano ʻano Para

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

No laila, ua nānā mākou i kekahi mau ʻano i ka hoʻonui ʻana i nā nīnau LINQ i ka MS SQL Server.

ʻO ka mea pōʻino, ʻoiai nā mea hoʻomohala ʻike a alakaʻi i ka .NET poina pono lākou e hoʻomaopopo i nā ʻōlelo a lākou e hoʻohana ai ma hope o nā hiʻohiʻona. A i ʻole, lilo lākou i configurators a hiki ke kanu i kahi pōkā manawa i ka wā e hiki mai ana i ka wā e hoʻonui ai i ka ʻōnaehana polokalamu a me nā loli liʻiliʻi i nā kūlana kūlohelohe o waho.

Ua hana ʻia kekahi loiloi pōkole maanei.

Aia nā kumu no ka hoʻāʻo - ʻo ka papahana ponoʻī, ka hana ʻana i nā papa ma ka waihona TEST, a me ka hoʻopiha ʻana i kēia mau papa me ka ʻikepili. maanei.
Ma kēia waihona, i loko o ka waihona Plans, aia nā hoʻolālā no ka hoʻokō ʻana i nā nīnau me nā kūlana OR.

Source: www.habr.com

ʻO kekahi mau hiʻohiʻona o ka hoʻonui ʻana i nā nīnau LINQ ma C#.NET no MS SQL Server

Pākuʻi i ka manaʻo hoʻopuka ana pane