LINQ mlebu .NET minangka basa manipulasi data anyar sing kuat. LINQ kanggo SQL minangka bagΓ©an saka iku ngijini sampeyan kanggo komunikasi cukup trep karo DBMS nggunakake, contone, Entity Framework. Nanging, nggunakake cukup kerep, pangembang lali kanggo ndeleng apa jenis query SQL panyedhiya queryable, ing kasus Entity Framework, bakal generate.
Ayo katon ing rong titik utama nggunakake conto.
Kanggo nindakake iki, nggawe database Test ing SQL Server, lan nggawe loro tabel ing nggunakake pitakonan ing ngisor iki:
Nggawe tabel
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
Saiki ayo isi tabel Ref kanthi nglakokake skrip ing ngisor iki:
Ngisi tabel 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
Ayo padha ngisi tabel Pelanggan kanthi nggunakake skrip ing ngisor iki:
Populating Tabel Pelanggan
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
Mangkono, kita nampa loro tabel, siji kang wis luwih saka 1 yuta larik data, lan liyane wis luwih saka 10 yuta larik saka data.
Saiki ing Visual Studio sampeyan kudu nggawe proyek Visual C# Console App (.NET Framework):
Sabanjure, sampeyan kudu nambah perpustakaan kanggo Entity Framework kanggo sesambungan karo database.
Kanggo nambah, klik-tengen ing proyek banjur pilih Ngatur Paket NuGet saka menu konteks:
Banjur, ing jendhela manajemen paket NuGet sing katon, ketik tembung "Entity Framework" ing jendela telusuran banjur pilih paket Entity Framework lan instal:
Sabanjure, ing file App.config, sawise nutup unsur configSections, sampeyan kudu nambah blok ing ngisor iki:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ΠΠΠ―_ΠΠΠΠΠΠΠΠ―Π Π_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Ing connectionString sampeyan kudu ngetik string sambungan.
Saiki ayo nggawe 3 antarmuka ing file sing kapisah:
- Ngleksanakake antarmuka IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } }
- Implementasi antarmuka IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } }
- Implementasi antarmuka IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Lan ing file sing kapisah, kita bakal nggawe BaseEntity kelas dhasar kanggo rong entitas kita, sing bakal kalebu lapangan umum:
Implementasi kelas basis BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Sabanjure, kita bakal nggawe rong entitas ing file sing kapisah:
- Implementasi kelas Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } }
- Implementasi kelas Pelanggan
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; } } }
Saiki ayo nggawe konteks UserContext ing file sing kapisah:
Implementasi kelas 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; }
}
}
Kita nampa solusi sing wis siap kanggo nganakake tes optimasi karo LINQ menyang SQL liwat EF kanggo MS SQL Server:
Saiki ketik kode ing ngisor iki menyang file 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();
}
}
}
}
Sabanjure, ayo miwiti proyek kita.
Ing pungkasan karya, ing ngisor iki bakal ditampilake ing console:
Query SQL sing digawe
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])
Yaiku, umume, pitakon LINQ ngasilake pitakon SQL menyang MS SQL Server DBMS kanthi apik.
Saiki ayo ngganti kondisi AND dadi UTAWA ing pitakon LINQ:
pitakon 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 };
Lan ayo miwiti aplikasi maneh.
Eksekusi bakal nabrak amarga kesalahan amarga wektu eksekusi perintah ngluwihi 30 detik:
Yen sampeyan ndeleng pitakon sing digawe dening LINQ:
, banjur sampeyan bisa nggawe manawa pilihan dumadi liwat produk Cartesian saka rong set (tabel):
Query SQL sing digawe
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]
Ayo nulis ulang pitakon LINQ kaya ing ngisor iki:
Pitakon LINQ sing dioptimalake
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 });
Banjur kita entuk pitakon SQL ing ngisor iki:
pitakon 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]
Sayange, ing pitakon LINQ mung ana siji syarat gabung, mula ing kene sampeyan bisa nggawe pitakon sing padha nggunakake rong pitakon kanggo saben kondisi banjur gabungke liwat Union kanggo mbusak duplikat ing antarane baris.
Ya, pitakon umume ora padha, kanthi nganggep manawa baris duplikat lengkap bisa dibalekake. Nanging, ing urip nyata, garis duplikat lengkap ora dibutuhake lan wong nyoba nyingkirake.
Saiki ayo mbandhingake rencana eksekusi saka rong pitakon iki:
- kanggo CROSS JOIN, wektu eksekusi rata-rata yaiku 195 detik:
- kanggo INNER JOIN-UNION wektu eksekusi rata-rata kurang saka 24 detik:
ΠΠ°ΠΊ Π²ΠΈΠ΄Π½ΠΎ ΠΈΠ· ΡΠ΅Π·ΡΠ»ΡΡΠ°ΡΠΎΠ², Π΄Π»Ρ Π΄Π²ΡΡ ΡΠ°Π±Π»ΠΈΡ Ρ ΠΌΠΈΠ»Π»ΠΈΠΎΠ½Π°ΠΌΠΈ Π·Π°ΠΏΠΈΡΠ΅ΠΉ ΠΎΠΏΡΠΈΠΌΠΈΠ·ΠΈΡΠΎΠ²Π°Π½Π½ΡΠΉ LINQ-Π·Π°ΠΏΡΠΎΡ ΡΠ°Π±ΠΎΡΠ°Π΅Ρ Π² ΡΠ°Π·Ρ Π±ΡΡΡΡΠ΅Π΅, ΡΠ΅ΠΌ Π½Π΅ΠΎΠΏΡΠΈΠΌΠΈΠ·ΠΈΡΠΎΠ²Π°Π½Π½ΡΠΉ.
ΠΠ»Ρ Π²Π°ΡΠΈΠ°Π½ΡΠ° Ρ Π Π² ΡΡΠ»ΠΎΠ²ΠΈΡΡ LINQ-Π·Π°ΠΏΡΠΎΡ Π²ΠΈΠ΄Π°:
pitakon 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 };
ΠΏΠΎΡΡΠΈ Π²ΡΠ΅Π³Π΄Π° Π±ΡΠ΄Π΅Ρ ΡΠ³Π΅Π½Π΅ΡΠΈΡΠΎΠ²Π°Π½ ΠΏΡΠ°Π²ΠΈΠ»ΡΠ½ΡΠΉ 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 };
ing pundi:
ΠΠΏΡΠ΅Π΄Π΅Π»Π΅Π½ΠΈΠ΅ Π΄Π²ΡΡ ΠΌΠ°ΡΡΠΈΠ²ΠΎΠ²
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, Π° ΡΠ°ΠΊΠΆΠ΅ Π½Π°ΠΏΠΎΠ»Π½Π΅Π½ΠΈΠ΅ Π΄Π°Π½Π½ΡΠΌΠΈ ΡΡΠΈΡ
ΡΠ°Π±Π»ΠΈΡ Π½Π°Ρ
ΠΎΠ΄ΠΈΡΡΡ
Uga ing repositori iki, ing folder Plans, ana rencana kanggo nglakokake pitakon kanthi kahanan UTAWA.
Source: www.habr.com