LINQ te antre nan .NET kòm yon pwisan nouvo langaj manipilasyon done. LINQ to SQL kòm yon pati nan li pèmèt ou kominike byen fasil ak yon DBMS lè l sèvi avèk, pou egzanp, Entity Framework. Sepandan, lè l sèvi avèk li byen souvan, devlopè bliye gade ki kalite rechèch SQL founisè a keryable, nan ka w Entity Framework, pral jenere.
Ann gade de pwen prensipal lè l sèvi avèk yon egzanp.
Pou fè sa, kreye yon baz done tès nan SQL sèvè, epi kreye de tab nan li lè l sèvi avèk rechèch sa a:
Kreye tab
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
Koulye a, ann peple tab la Ref pa kouri script sa a:
Ranpli tab la 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
An menm jan an nou ranpli tab Kliyan an lè l sèvi avèk script sa a:
Peple tab Kliyan an
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
Kidonk, nou te resevwa de tab, youn nan ki gen plis pase 1 milyon ranje done, ak lòt la gen plis pase 10 milyon ranje done.
Koulye a, nan Visual Studio ou bezwen kreye yon tès Visual C# Console App (.NET Framework) pwojè:
Apre sa, ou bezwen ajoute yon bibliyotèk pou Entity Framework pou kominike avèk baz done a.
Pou ajoute li, klike sou pwojè a epi chwazi Jere pakè NuGet nan meni kontèks la:
Lè sa a, nan fenèt jesyon pake NuGet ki parèt, antre mo "Entity Framework" nan fenèt rechèch la epi chwazi pake Entity Framework epi enstale li:
Apre sa, nan dosye a App.config, apre yo fin fèmen eleman nan configSections, ou bezwen ajoute blòk sa a:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Nan connectionString ou bezwen antre nan fisèl koneksyon an.
Koulye a, ann kreye 3 interfaces nan dosye separe:
- Aplike koòdone IBaseEntityID la
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } }
- Aplikasyon koòdone IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } }
- Aplikasyon koòdone IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Ak nan yon dosye separe nou pral kreye yon klas de baz BaseEntity pou de antite nou yo, ki pral gen ladan jaden komen:
Aplikasyon klas de baz BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Apre sa, nou pral kreye de antite nou yo nan dosye separe:
- Aplikasyon klas Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } }
- Aplikasyon klas Kliyan an
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; } } }
Koulye a, ann kreye yon kontèks UserContext nan yon dosye separe:
Aplikasyon klas UserContex la
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; }
}
}
Nou te resevwa yon solisyon pare pou fè tès optimize ak LINQ to SQL atravè EF pou MS SQL sèvè:
Koulye a, antre kòd sa a nan dosye Program.cs la:
Program.cs dosye
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();
}
}
}
}
Apre sa, ann lanse pwojè nou an.
Nan fen travay la, sa ki annapre yo pral parèt sou konsole a:
Jenere SQL Rekèt
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])
Sa vle di, an jeneral, rechèch LINQ la te pwodwi yon rechèch SQL nan MS SQL Server DBMS byen byen.
Koulye a, ann chanje kondisyon AND an OSWA nan rekèt LINQ la:
Rekèt 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 };
Epi ann lanse aplikasyon nou an ankò.
Ekzekisyon an pral fè aksidan ak yon erè akòz tan an egzekisyon lòd ki depase 30 segonn:
Si w gade rechèch ki te pwodwi pa LINQ la:
, Lè sa a, ou ka asire w ke seleksyon an fèt atravè pwodwi katezyen de seri (tab):
Jenere SQL Rekèt
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]
Ann reekri rechèch LINQ la jan sa a:
Optimize rechèch 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 });
Lè sa a, nou jwenn rechèch SQL sa a:
Rekèt 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]
Ay, nan demann LINQ kapab genyen sèlman yon sèl kondisyon rantre, kidonk isit la li posib pou fè yon rechèch ekivalan lè l sèvi avèk de demann pou chak kondisyon ak Lè sa a, konbine yo atravè Inyon pou retire kopi nan mitan ranje yo.
Wi, demann yo pral jeneralman pa ekivalan, lè nou konsidere ke ranje kopi konplè yo ka retounen. Sepandan, nan lavi reyèl, liy kopi konplè yo pa nesesè epi moun eseye debarase m de yo.
Koulye a, ann konpare plan ekzekisyon de demann sa yo:
- pou CROSS JOIN tan an mwayèn ekzekisyon se 195 segonn:
- pou INNER JOIN-UNION tan an mwayèn ekzekisyon se mwens pase 24 segonn:
.
Epitou nan depo sa a, nan katab la Plans, gen plan pou egzekite demann ak kondisyon OSWA.
Sous: www.habr.com