I-LINQ ifake i-.NET njengolimi olusha olunamandla lokukhohlisa idatha. I-LINQ kuya ku-SQL njengengxenye yayo ikuvumela ukuthi uxhumane kalula ne-DBMS usebenzisa, isibonelo, i-Entity Framework. Kodwa-ke, uma beyisebenzisa kaningi, abathuthukisi bayakhohlwa ukubheka ukuthi hlobo luni lombuzo we-SQL umhlinzeki ongabuzwa, esimweni sakho I-Entity Framework, azoyenza.
Ake sibheke amaphuzu amabili abalulekile sisebenzisa isibonelo.
Ukuze wenze lokhu, dala isizindalwazi sokuhlola ku-SQL Server, bese udala amatafula amabili kuyo usebenzisa lo mbuzo olandelayo:
Ukudala amatafula
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
Manje ake sigcwalise ithebula le-Ref ngokusebenzisa umbhalo olandelayo:
Ukugcwalisa ithebula leRef
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 ithebula Lekhasimende sisebenzisa umbhalo olandelayo:
Ukugcwalisa ithebula Lekhasimende
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
Ngakho, sithole amathebula amabili, elilodwa linemigqa yedatha engaphezu kwesigidi, kanti elinye linemigqa yedatha engaphezu kwezigidi ezingu-1.
Manje ku-Visual Studio udinga ukudala iphrojekthi yokuhlola ye-Visual C# Console App (.NET Framework):
Okulandelayo, udinga ukwengeza umtapo wolwazi ukuze Uhlaka Lwebhizinisi luhlanganyele nesizindalwazi.
Ukuze uyengeze, chofoza kwesokudla kuphrojekthi bese ukhetha Phatha amaphakheji we-NuGet kumenyu yokuqukethwe:
Bese, efasiteleni lokuphatha iphakheji le-NuGet elivelayo, faka igama elithi βEntity Frameworkβ efasiteleni lokusesha bese ukhetha iphakethe le-Entity Framework bese ulifaka:
Okulandelayo, kufayela le-App.config, ngemva kokuvala isici se-configSections, udinga ukwengeza ibhulokhi elandelayo:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ΠΠΠ―_ΠΠΠΠΠΠΠΠ―Π Π_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Ku-ConnectionString udinga ukufaka intambo yokuxhuma.
Manje ake sakhe izixhumanisi ezi-3 kumafayela ahlukene:
- Ukusebenzisa isixhumi esibonakalayo se-IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Ukuqaliswa kokusetshenziswa kubonwa kwe-IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Ukuqaliswa kokusetshenziswa kubonwa kwe-IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Futhi efayeleni elihlukile sizodala i-BaseEntity yesigaba sesisekelo sezinhlangano zethu ezimbili, ezizofaka izinkambu ezivamile:
Ukuqaliswa kwe-base class BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Okulandelayo, sizodala izinhlangano zethu ezimbili ngamafayela ahlukene:
- Ukuqaliswa kwesigaba seRef
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Ukuqaliswa kwekilasi leKhasimende
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; } } }
Manje ake sakhe umongo we-UserContext efayeleni elihlukile:
Ukuqaliswa kwekilasi le-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; }
}
}
Sithole isixazululo esenziwe ngomumo sokwenza izivivinyo zokuthuthukisa nge-LINQ kuya ku-SQL nge-EF ye-MS SQL Server:
Manje faka ikhodi elandelayo efayeleni le-Program.cs:
Ifayela le-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();
}
}
}
}
Okulandelayo, ake sethule iphrojekthi yethu.
Ekupheleni komsebenzi, okulandelayo kuzovezwa kukhonsoli:
Kwenziwe Umbuzo we-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])
Okusho ukuthi, ngokujwayelekile, umbuzo we-LINQ ukhiqize umbuzo we-SQL ku-MS SQL Server DBMS kahle kakhulu.
Manje ake siguqule isimo KANYE sibe NOMA embuzweni we-LINQ:
Umbuzo we-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 };
Futhi masiqalise uhlelo lwethu lokusebenza futhi.
Ukwenza kuzophahlazeka ngephutha ngenxa yesikhathi sokwenza umyalo esidlula imizuzwana engama-30:
Uma ubheka umbuzo owenziwe yi-LINQ:
, khona-ke ungaqiniseka ukuthi ukukhetha kwenzeka ngomkhiqizo weCartesian wamasethi amabili (amatafula):
Kwenziwe Umbuzo we-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]
Masibhale kabusha umbuzo we-LINQ kanje:
Kulungiselelwe umbuzo we-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 });
Bese sithola umbuzo olandelayo we-SQL:
Umbuzo we-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]
Maye, emibuzweni ye-LINQ kungaba nesimo sokujoyina esisodwa kuphela, ngakho-ke lapha kungenzeka ukwenza umbuzo olinganayo usebenzisa imibuzo emibili kusimo ngasinye bese uyihlanganisa nge-Union ukuze ukhiphe izimpinda phakathi kwemigqa.
Yebo, imibuzo ngokuvamile ngeke ilingane, kucatshangelwa ukuthi imigqa eyimpinda ephelele ingase ibuyiswe. Kodwa-ke, empilweni yangempela, imigqa eyimpinda ephelele ayidingeki futhi abantu bazama ukuyisusa.
Manje ake siqhathanise izinhlelo zokusebenza zale mibuzo emibili:
- kokuthi CROSS JOIN isikhathi sokwenza esimaphakathi amasekhondi angu-195:
- ku-INNER JOIN-UNION isikhathi sokwenza esimaphakathi singaphansi kwamasekhondi angu-24:
Njengoba ubona emiphumeleni, kumathebula amabili anezigidi zamarekhodi, umbuzo we-LINQ olungiselelwe ushesha izikhathi eziningi kunalowo ongalungiselelwe.
Ngenketho ethi KANYE ngaphansi kwezimo, umbuzo we-LINQ wefomu:
Umbuzo we-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 };
Umbuzo olungile we-SQL uzohlale ukhiqizwa, okuzosebenza ngokwesilinganiso cishe kusekhondi elingu-1:
Futhi nge-LINQ kuya ku-Objects manipulations esikhundleni sombuzo ofana:
Umbuzo we-LINQ (inketho yokuqala)
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 };
ungasebenzisa umbuzo onjengokuthi:
Umbuzo we-LINQ (inketho yokuqala)
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 };
lapho:
Ichaza amaqembu afanayo amabili
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" } };
, futhi uhlobo lwe-Para luchazwa kanje:
Para Type Definition
class Para
{
public int Key1, Key2;
public string Data;
}
Ngakho-ke, sihlole izici ezithile ekulungiseleleni imibuzo ye-LINQ ku-MS SQL Server.
Ngeshwa, ngisho nabathuthukisi be-.NET abanolwazi nabaholayo bayakhohlwa ukuthi badinga ukuqonda ukuthi imiyalelo abayisebenzisayo yenzani ngemuva kwezigcawu. Uma kungenjalo, baba abahleli futhi bangatshala ibhomu lesikhathi esikhathini esizayo kokubili lapho bekala isisombululo sesofthiwe kanye nezinguquko ezincane ezimweni zemvelo zangaphandle.
Ukubuyekezwa okufushane nakho kwenziwa .
Imithombo yokuhlolwa - iphrojekthi ngokwayo, ukwakhiwa kwamatafula kusizindalwazi se-TEST, kanye nokugcwalisa lawa mathebula ngedatha atholakala. .
Futhi kule nqolobane, kufolda Yezinhlelo, kunezinhlelo zokwenza imibuzo ngemibandela NOMA.
Source: www.habr.com