Pulogalamu ya ProHoster > Blog > Ulamuliro > Zina mwa kukhathamiritsa mafunso a LINQ mu C#.NET pa MS SQL Server

Zina mwa kukhathamiritsa mafunso a LINQ mu C#.NET pa MS SQL Server

LINQ idalowa mu .NET ngati chilankhulo chatsopano champhamvu chosinthira deta. LINQ ku SQL monga gawo lake limakupatsani mwayi wolumikizana bwino ndi DBMS pogwiritsa ntchito, mwachitsanzo, Entity Framework. Komabe, kugwiritsa ntchito nthawi zambiri, opanga amaiwala kuyang'ana mtundu wa SQL wofunsayo yemwe angakufunseni, kwa inu Entity Framework, angapange.

Tiyeni tione mfundo zikuluzikulu ziwiri pogwiritsa ntchito chitsanzo.
Kuti muchite izi, pangani database ya Test mu SQL Server, ndikupanga matebulo awiri mmenemo pogwiritsa ntchito funso ili:

Kupanga matebulo

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

Tsopano tiyeni tiwonjezere tebulo la Ref poyendetsa script iyi:

Kudzaza Ref table

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

Tidzazenso tebulo la Makasitomala pogwiritsa ntchito script:

Kudzaza tebulo la Makasitomala

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

Choncho, tinalandira matebulo awiri, imodzi yomwe ili ndi mizere yoposa 1 miliyoni, ndipo ina ili ndi mizere yoposa 10 miliyoni.

Tsopano mu Visual Studio muyenera kupanga kuyesa Visual C# Console App (.NET Framework) pulojekiti:

Zina mwa kukhathamiritsa mafunso a LINQ mu C#.NET pa MS SQL Server

Kenako, muyenera kuwonjezera laibulale ya Entity Framework kuti igwirizane ndi database.
Kuti muwonjezere, dinani kumanja pa polojekitiyo ndikusankha Sinthani Phukusi la NuGet kuchokera pazosankha:

Zina mwa kukhathamiritsa mafunso a LINQ mu C#.NET pa MS SQL Server

Kenako, pawindo loyang'anira phukusi la NuGet lomwe likuwoneka, lowetsani mawu oti "Entity Framework" pawindo losakira ndikusankha phukusi la Entity Framework ndikuyiyika:

Zina mwa kukhathamiritsa mafunso a LINQ mu C#.NET pa MS SQL Server

Chotsatira, mu fayilo ya App.config, mutatseka chinthu cha configSections, muyenera kuwonjezera chipika chotsatirachi:

<connectionStrings>
    <add name="DBConnection" connectionString="data source=ИМЯ_Π­ΠšΠ—Π•ΠœΠŸΠ›Π―Π Π_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>

MuConnectString muyenera kulowa chingwe cholumikizira.

Tsopano tiyeni tipange zolumikizira 3 mumafayilo osiyanasiyana:

  1. Kukhazikitsa mawonekedwe a IBaseEntityID 
    namespace TestLINQ
{
    public interface IBaseEntityID
    {
        int ID { get; set; }
    }
}

  2. Kukhazikitsa mawonekedwe a IBaseEntityName 
    namespace TestLINQ
{
    public interface IBaseEntityName
    {
        string Name { get; set; }
    }
}

  3. Kukhazikitsa mawonekedwe a IBaseNameInsertUTCDate 
    namespace TestLINQ
{
    public interface IBaseNameInsertUTCDate
    {
        DateTime InsertUTCDate { get; set; }
    }
}

Ndipo mu fayilo ina tidzapanga BaseEntity yoyambira m'mabungwe athu awiri, omwe aziphatikiza magawo wamba:

Kukhazikitsidwa kwa BaseEntity Base class

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

Kenako, tipanga mabungwe athu awiri m'mafayilo osiyana:

  1. Kukhazikitsidwa kwa Ref class 
    using System.ComponentModel.DataAnnotations.Schema;

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

  2. Kukhazikitsa kalasi ya Makasitomala 
    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; }
    }
}

Tsopano tiyeni tipange UserContext mufayilo ina:

Kukhazikitsa kalasi ya 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; }
    }
}

Tidalandira yankho lomwe lapangidwa kale poyesa kukhathamiritsa ndi LINQ kupita ku SQL kudzera pa EF ya MS SQL Server:

Zina mwa kukhathamiritsa mafunso a LINQ mu C#.NET pa MS SQL Server

Tsopano lowetsani code zotsatirazi mu fayilo ya Program.cs:

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

Kenako, tiyeni tiyambitse polojekiti yathu.

Pamapeto pa ntchito, zotsatirazi zikuwonetsedwa pa console:

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

Izi zikutanthauza kuti, funso la LINQ linapanga funso la SQL ku MS SQL Server DBMS bwino.

Tsopano tiyeni tisinthe AND chikhalidwe kukhala OR mufunso la LINQ:

LINQ funso

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

Ndipo tiyeni tiyambitsenso pulogalamu yathu.

Kuphaku kudzawonongeka ndi cholakwika chifukwa cha nthawi yoperekera lamulo yopitilira masekondi 30:

Zina mwa kukhathamiritsa mafunso a LINQ mu C#.NET pa MS SQL Server

Mukayang'ana funso lomwe linapangidwa ndi LINQ:

Zina mwa kukhathamiritsa mafunso a LINQ mu C#.NET pa MS SQL Server
, ndiye mutha kuwonetsetsa kuti kusankha kukuchitika kudzera muzinthu za Cartesian zamagulu awiri (matebulo):

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

Tiyeni tilembenso funso la LINQ motere:

Konzani funso la 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 });

Kenako timapeza funso lotsatira la SQL:

SQL funso

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]

Tsoka, mumafunso a LINQ pakhoza kukhala cholumikizira chimodzi chokha, kotero apa ndizotheka kupanga funso lofanana pogwiritsa ntchito mafunso awiri pamtundu uliwonse ndikuphatikiza kudzera mu Union kuti muchotse zobwereza pakati pa mizere.
Inde, mafunsowo sadzakhala ofanana, poganizira kuti mizere yobwereza ikhoza kubwezedwa. Komabe, m'moyo weniweni, mizere yobwereza yathunthu sifunikira ndipo anthu amayesa kuwachotsa.

Tsopano tiyeni tifanizire mapulani a mafunso awiriwa:

  1. kwa CROSS JOIN nthawi yapakati yophedwa ndi masekondi 195:
    Zina mwa kukhathamiritsa mafunso a LINQ mu C#.NET pa MS SQL Server
  2. kwa INNER JOIN-UNION nthawi yapakati yopha ndi yochepera masekondi 24:
    Zina mwa kukhathamiritsa mafunso a LINQ mu C#.NET pa MS SQL Server

Monga mukuwonera pazotsatira, pamagome awiri okhala ndi mamiliyoni a zolemba, funso lokongoletsedwa la LINQ limathamanga nthawi zambiri kuposa lomwe silinakwaniritsidwe.

Pakusankha ndi NDI mumikhalidwe, funso la LINQ la fomu:

LINQ funso

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

Funso lolondola la SQL lidzapangidwa nthawi zonse, lomwe limayenda pafupifupi mphindi imodzi:

Zina mwa kukhathamiritsa mafunso a LINQ mu C#.NET pa MS SQL Server
Komanso kwa LINQ to Objects manipulations m'malo mofunsa ngati:

LINQ funso (njira yoyamba)

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

mutha kugwiritsa ntchito funso ngati:

LINQ funso (njira yoyamba)

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

kumene:

Kufotokozera mitundu iwiri

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

, ndipo mtundu wa Para umafotokozedwa motere:

Para Type Tanthauzo

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

Chifukwa chake, tidasanthula mbali zina pakuwongolera mafunso a LINQ ku MS SQL Server.

Tsoka ilo, ngakhale odziwa bwino komanso otsogola a .NET amayiwala kuti akuyenera kumvetsetsa zomwe malangizo omwe amagwiritsa ntchito amachita kumbuyo kwazithunzi. Kupanda kutero, amakhala okonza ndipo amatha kubzala bomba lanthawi mtsogolo powonjezera njira yothetsera pulogalamuyo komanso kusintha pang'ono kwa chilengedwe chakunja.

Ndemanga yachidule idachitikanso apa.

Magwero a mayeso - polojekiti yokha, kupanga matebulo mu database ya TEST, komanso kudzaza matebulo awa ndi data. apa.
Komanso munkhokwe iyi, mufoda ya Plans, pali mapulani ochitira mafunso ndi OR zikhalidwe.

Source: www.habr.com

Kuwonjezera ndemanga