I-LINQ ingene .NET njengolwimi olutsha olunamandla lokukhohlisa. I-LINQ ukuya kwiSQL njengenxalenye yayo ikuvumela ukuba unxibelelane ngokulula neDBMS usebenzisa, umzekelo, Isakhelo seQumrhu. Nangona kunjalo, beyisebenzisa rhoqo, abaphuhlisi bayalibala ukujonga ukuba luhlobo luni lombuzo weSQL umnikezeli obuzwayo, kwimeko yakho iSikhokelo seQumrhu, siyakwenza.
Makhe sijonge iingongoma ezimbini eziphambili sisebenzisa umzekelo.
Ukwenza oku, yenza i-database yoVavanyo kwi-SQL Server, kwaye wenze iitafile ezimbini kuyo usebenzisa lo mbuzo ulandelayo:
Ukudala iitafile
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
Ngoku makhe sigcwalise itheyibhile yeRef ngokuqhuba iskripthi esilandelayo:
Ukuzalisa itheyibhile yeRef
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
Masigcwalise ngokufanayo itheyibhile yoMthengi sisebenzisa esi script silandelayo:
Ukuzalisa itafile yoMthengi
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
Ngaloo ndlela, sifumene iitheyibhile ezimbini, enye enemiqolo yedatha engaphezu kwe-1 yezigidi, kwaye enye inezintlu ezingaphezu kwezigidi ezili-10 zedatha.
Ngoku kwiVisual Studio kufuneka wenze uvavanyo lweVisual C# Console App (.NET Framework) iprojekthi:
Okulandelayo, kufuneka ungeze ithala leencwadi leSikhokelo seQumrhu ukuze linxibelelane nesiseko sedatha.
Ukuyongeza, cofa ekunene kwiprojekthi kwaye ukhethe Lawula iiPakethi zeNuGet kwimenyu yomxholo:
Emva koko, kwifestile yolawulo lwephakheji ye-NuGet evelayo, faka igama elithi "I-Entity Framework" kwifestile yokukhangela kwaye ukhethe iphakheji ye-Entity Framework kwaye uyifake:
Okulandelayo, kwifayile ye-App.config, emva kokuvala i-configSections element, kufuneka udibanise ibhloko elandelayo:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ΠΠΠ―_ΠΠΠΠΠΠΠΠ―Π Π_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Kwintambo yoqhagamshelwano kufuneka ufake umtya woqhagamshelwano.
Ngoku masenze ujongano olu-3 kwiifayile ezahlukeneyo:
- Ukusebenzisa i-interface ye-IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } }
- Ukuphunyezwa kojongano lwe-IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } }
- Ukuphunyezwa kojongano lwe-IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Kwaye kwifayile eyahlukileyo siya kudala isiseko seBaseEntity kumacandelo ethu amabini, aya kubandakanya amasimi aqhelekileyo:
Ukuphunyezwa kwesiseko seBaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Okulandelayo, siya kudala amaziko ethu amabini kwiifayile ezahlukeneyo:
- Ukuphunyezwa kweklasi yeRef
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } }
- Ukuphunyezwa kweklasi yoMthengi
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; } } }
Ngoku masenze umxholo womSebenzisi kwifayile eyahlukileyo:
Ukuphunyezwa kweklasi ye-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; }
}
}
Sifumene isisombululo esele silungisiwe sokuqhuba iimvavanyo zokufezekisa nge-LINQ ukuya kwi-SQL nge-EF ye-MS SQL Server:
Ngoku faka ikhowudi elandelayo kwifayile yeProgram.cs:
Inkqubo.cs ifayile
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();
}
}
}
}
Okulandelayo, masiqalise iprojekthi yethu.
Ekupheleni komsebenzi, oku kulandelayo kuya kuboniswa kwi-console:
Yenziwe i-SQL Umbuzo
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])
Oko kukuthi, ngokubanzi, umbuzo weLINQ wenze umbuzo weSQL kwi-MS SQL Server DBMS kakuhle.
Ngoku masitshintshe imeko kunye nemeko OKANYE kumbuzo we-LINQ:
Umbuzo weLINQ
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 };
Kwaye masiqalise usetyenziso lwethu kwakhona.
Ukuphunyezwa kuya kuphazamiseka ngempazamo ngenxa yexesha lokuphumeza umyalelo elidlula imizuzwana engama-30:
Ukuba ujonga umbuzo owenziwe yi-LINQ:
, emva koko unokuqinisekisa ukuba ukhetho lwenzeka ngemveliso yeCartesian yeeseti ezimbini (iitafile):
Yenziwe i-SQL Umbuzo
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]
Masiphinde sibhale umbuzo we-LINQ ngolu hlobo lulandelayo:
Umbuzo olungiselelwe iLINQ
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 });
Emva koko sifumana lo mbuzo ulandelayo weSQL:
Umbuzo weSQL
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]
Yeha, kwimibuzo ye-LINQ kunokubakho imeko enye yokudibanisa, ke apha kuyenzeka ukuba wenze umbuzo olinganayo usebenzisa imibuzo emibini kwimeko nganye kwaye emva koko uzidibanise ngeManyano ukususa impinda phakathi kwemigca.
Ewe, imibuzo iya kuba ingalingani, kuthathelwa ingqalelo ukuba irewu eziphindwe kabini zinokubuyiswa. Nangona kunjalo, kubomi bokwenyani, imigca ephindwe kabini ayidingeki kwaye abantu bazama ukuyisusa.
Ngoku makhe sithelekise izicwangciso zokwenza le mibuzo mibini:
- ku-CROSS JOIN umndilili wexesha lophumezo yimizuzwana eyi-195:
- ku-INNER JOIN-UNION umndilili wexesha lokwenziwa lingaphantsi kwemizuzwana engama-24:
ΠΠ°ΠΊ Π²ΠΈΠ΄Π½ΠΎ ΠΈΠ· ΡΠ΅Π·ΡΠ»ΡΡΠ°ΡΠΎΠ², Π΄Π»Ρ Π΄Π²ΡΡ ΡΠ°Π±Π»ΠΈΡ Ρ ΠΌΠΈΠ»Π»ΠΈΠΎΠ½Π°ΠΌΠΈ Π·Π°ΠΏΠΈΡΠ΅ΠΉ ΠΎΠΏΡΠΈΠΌΠΈΠ·ΠΈΡΠΎΠ²Π°Π½Π½ΡΠΉ LINQ-Π·Π°ΠΏΡΠΎΡ ΡΠ°Π±ΠΎΡΠ°Π΅Ρ Π² ΡΠ°Π·Ρ Π±ΡΡΡΡΠ΅Π΅, ΡΠ΅ΠΌ Π½Π΅ΠΎΠΏΡΠΈΠΌΠΈΠ·ΠΈΡΠΎΠ²Π°Π½Π½ΡΠΉ.
ΠΠ»Ρ Π²Π°ΡΠΈΠ°Π½ΡΠ° Ρ Π Π² ΡΡΠ»ΠΎΠ²ΠΈΡΡ LINQ-Π·Π°ΠΏΡΠΎΡ Π²ΠΈΠ΄Π°:
Umbuzo weLINQ
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 };
ΠΏΠΎΡΡΠΈ Π²ΡΠ΅Π³Π΄Π° Π±ΡΠ΄Π΅Ρ ΡΠ³Π΅Π½Π΅ΡΠΈΡΠΎΠ²Π°Π½ ΠΏΡΠ°Π²ΠΈΠ»ΡΠ½ΡΠΉ SQL-Π·Π°ΠΏΡΠΎΡ, ΠΊΠΎΡΠΎΡΡΠΉ Π±ΡΠ΄Π΅Ρ Π²ΡΠΏΠΎΠ»Π½ΡΡΡΡΡ Π² ΡΡΠ΅Π΄Π½Π΅ΠΌ ΠΏΡΠΈΠΌΠ΅ΡΠ½ΠΎ 1 ΡΠ΅ΠΊ:
Π’Π°ΠΊΠΆΠ΅ Π΄Π»Ρ ΠΌΠ°Π½ΠΈΠΏΡΠ»ΡΡΠΈΠΉ LINQ to Objects Π²ΠΌΠ΅ΡΡΠΎ Π·Π°ΠΏΡΠΎΡΠ° Π²ΠΈΠ΄Π°:
LINQ-Π·Π°ΠΏΡΠΎΡ (1-ΠΉ Π²Π°ΡΠΈΠ°Π½Ρ)
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 };
ΠΌΠΎΠΆΠ½ΠΎ ΠΈΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΡ Π·Π°ΠΏΡΠΎΡ Π²ΠΈΠ΄Π°:
LINQ-Π·Π°ΠΏΡΠΎΡ (2-ΠΉ Π²Π°ΡΠΈΠ°Π½Ρ)
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 };
apho:
ΠΠΏΡΠ΅Π΄Π΅Π»Π΅Π½ΠΈΠ΅ Π΄Π²ΡΡ ΠΌΠ°ΡΡΠΈΠ²ΠΎΠ²
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" } };
, Π° ΡΠΈΠΏ Para ΠΎΠΏΡΠ΅Π΄Π΅Π»ΡΠ΅ΡΡΡ ΡΠ»Π΅Π΄ΡΡΡΠΈΠΌ ΠΎΠ±ΡΠ°Π·ΠΎΠΌ:
ΠΠΏΡΠ΅Π΄Π΅Π»Π΅Π½ΠΈΠ΅ ΡΠΈΠΏΠ° Para
class Para
{
public int Key1, Key2;
public string Data;
}
Π’Π°ΠΊΠΈΠΌ ΠΎΠ±ΡΠ°Π·ΠΎΠΌ ΠΌΡ ΡΠ°ΡΡΠΌΠΎΡΡΠ΅Π»ΠΈ Π½Π΅ΠΊΠΎΡΠΎΡΡΠ΅ Π°ΡΠΏΠ΅ΠΊΡΡ Π² ΠΎΠΏΡΠΈΠΌΠΈΠ·Π°ΡΠΈΠΈ LINQ-Π·Π°ΠΏΡΠΎΡΠΎΠ² ΠΊ MS SQL Server.
Π ΡΠΎΠΆΠ°Π»Π΅Π½ΠΈΡ Π΄Π°ΠΆΠ΅ ΠΎΠΏΡΡΠ½ΡΠ΅ ΠΈ Π²Π΅Π΄ΡΡΠΈΠ΅ .NET-ΡΠ°Π·ΡΠ°Π±ΠΎΡΡΠΈΠΊΠΈ Π·Π°Π±ΡΠ²Π°ΡΡ ΠΎ ΡΠΎΠΌ, ΡΡΠΎ Π½Π΅ΠΎΠ±Ρ ΠΎΠ΄ΠΈΠΌΠΎ ΠΏΠΎΠ½ΠΈΠΌΠ°ΡΡ ΡΡΠΎ Π΄Π΅Π»Π°ΡΡ Π·Π° ΠΊΠ°Π΄ΡΠΎΠΌ ΡΠ΅ ΠΈΠ½ΡΡΡΡΠΊΡΠΈΠΈ, ΠΊΠΎΡΠΎΡΡΠ΅ ΠΎΠ½ΠΈ ΠΈΡΠΏΠΎΠ»ΡΠ·ΡΡΡ. ΠΠ½Π°ΡΠ΅ ΠΎΠ½ΠΈ ΡΡΠ°Π½ΠΎΠ²ΡΡΡΡ ΠΊΠΎΠ½ΡΠΈΠ³ΡΡΠ°ΡΠΎΡΠ°ΠΌΠΈ ΠΈ ΠΌΠΎΠ³ΡΡ Π·Π°Π»ΠΎΠΆΠΈΡΡ Π±ΠΎΠΌΠ±Ρ Π·Π°ΠΌΠ΅Π΄Π»Π΅Π½Π½ΠΎΠ³ΠΎ Π΄Π΅ΠΉΡΡΠ²ΠΈΡ Π² Π±ΡΠ΄ΡΡΠ΅ΠΌ ΠΊΠ°ΠΊ ΠΏΡΠΈ ΠΌΠ°ΡΡΡΠ°Π±ΠΈΡΠΎΠ²Π°Π½ΠΈΠΈ ΠΏΡΠΎΠ³ΡΠ°ΠΌΠΌΠ½ΠΎΠ³ΠΎ ΡΠ΅ΡΠ΅Π½ΠΈΡ, ΡΠ°ΠΊ ΠΈ ΠΏΡΠΈ Π½Π΅Π·Π½Π°ΡΠΈΡΠ΅Π»ΡΠ½ΡΡ ΠΈΠ·ΠΌΠ΅Π½Π΅Π½ΠΈΡΡ Π²Π½Π΅ΡΠ½ΠΈΡ ΡΡΠ»ΠΎΠ²ΠΈΠΉ ΡΡΠ΅Π΄Ρ.
Π’Π°ΠΊΠΆΠ΅ Π½Π΅Π±ΠΎΠ»ΡΡΠΎΠΉ ΠΎΠ±Π·ΠΎΡ ΠΏΡΠΎΠ²ΠΎΠ΄ΠΈΠ»ΡΡ ΠΈ
ΠΡΡ
ΠΎΠ΄Π½ΠΈΠΊΠΈ Π΄Π»Ρ ΡΠ΅ΡΡΠ°-ΡΠ°ΠΌ ΠΏΡΠΎΠ΅ΠΊΡ, ΡΠΎΠ·Π΄Π°Π½ΠΈΠ΅ ΡΠ°Π±Π»ΠΈΡ Π² Π±Π°Π·Π΅ Π΄Π°Π½Π½ΡΡ
TEST, Π° ΡΠ°ΠΊΠΆΠ΅ Π½Π°ΠΏΠΎΠ»Π½Π΅Π½ΠΈΠ΅ Π΄Π°Π½Π½ΡΠΌΠΈ ΡΡΠΈΡ
ΡΠ°Π±Π»ΠΈΡ Π½Π°Ρ
ΠΎΠ΄ΠΈΡΡΡ
Kwakhona kule ndawo yokugcina, kwifolda yeZicwangciso, kukho izicwangciso zokwenza imibuzo OKANYE neemeko.
umthombo: www.habr.com