Niditra .NET ho fiteny fanodikodinana angona vaovao mahery vaika ny LINQ. Ny LINQ mankany SQL amin'ny maha-ampahany azy dia ahafahanao mifandray tsara amin'ny DBMS mampiasa, ohatra, Entity Framework. Na izany aza, amin'ny fampiasana azy matetika dia hadinon'ny mpamorona ny mijery ny karazana fangatahana SQL izay havoakan'ny mpanome fangatahana fanontaniana, raha ny momba anao Entity Framework, no hamokatra.
Andeha isika hijery hevi-dehibe roa mampiasa ohatra.
Mba hanaovana izany, mamorona angon-drakitra Test ao amin'ny SQL Server, ary mamorona tabilao roa ao anatiny amin'ny fampiasana ity fanontaniana manaraka ity:
Famoronana tabilao
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
Andeha isika hameno ny latabatra Ref amin'ny alΓ lan'ny fampandehanana ity script manaraka ity:
Famenoana ny latabatra 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
Andeha hofenointsika toy izany koa ny latabatra mpanjifa amin'ny fampiasana ity script manaraka ity:
Mameno ny latabatra mpanjifa
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
Noho izany, nahazo latabatra roa izahay, ny iray manana andalana maherin'ny 1 tapitrisa, ary ny iray kosa manana andalana maherin'ny 10 tapitrisa.
Ao amin'ny Visual Studio izao dia mila mamorona tetikasa Visual C# Console App (.NET Framework) ianao:
Manaraka, mila manampy tranomboky ho an'ny Entity Framework ianao hifaneraserana amin'ny angon-drakitra.
Mba hanampiana azy, tsindrio havanana amin'ny tetikasa ary mifidiana Manage NuGet Packages amin'ny menu context:
Avy eo, ao amin'ny varavarankely fitantanana fonosana NuGet izay miseho, ampidiro ny teny hoe "Entity Framework" ao amin'ny varavarankely fikarohana ary safidio ny fonosana Entity Framework ary apetraho izany:
Manaraka, ao amin'ny fisie App.config, aorian'ny fanakatonana ny singa configSections, dia mila manampy ity sakana manaraka ity ianao:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ΠΠΠ―_ΠΠΠΠΠΠΠΠ―Π Π_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Amin'ny connectionString dia mila miditra ny tady fifandraisana ianao.
Andeha isika hamorona interface 3 amin'ny rakitra samihafa:
- Mampihatra ny interface IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } }
- Fampiharana ny interface IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } }
- Fampiharana ny interface IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Ary amin'ny rakitra misaraka dia hamorona BaseEntity kilasy fototra ho an'ny sampana roa izahay, izay ahitana saha iraisana:
Fampiharana ny kilasy fototra BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Manaraka, hamorona ny entintsika roa amin'ny rakitra misaraka isika:
- Fampiharana ny kilasy Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } }
- Fampiharana ny kilasin'ny mpanjifa
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; } } }
Andeha isika hamorona contexte UserContext amin'ny rakitra misaraka:
Fampiharana ny kilasy 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; }
}
}
Nahazo vahaolana efa vita izahay amin'ny fanaovana andrana fanatsarana miaraka amin'ny LINQ mankany SQL amin'ny alΓ lan'ny EF ho an'ny MS SQL Server:
Ampidiro ao amin'ny rakitra Program.cs izao kaody manaraka izao:
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();
}
}
}
}
Manaraka, andao hanomboka ny tetikasantsika.
Amin'ny fiafaran'ny asa dia hiseho amin'ny console ireto manaraka ireto:
Namorona 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])
Izany hoe, amin'ny ankapobeny, ny fangatahana LINQ dia niteraka fangatahana SQL ho an'ny MS SQL Server DBMS tsara.
Andeha isika hanova ny fepetra AND ho OR amin'ny fangatahana LINQ:
Fanontaniana 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 };
Ary andao hanomboka indray ny fampiharana.
Ny famonoana dia hianjera miaraka amin'ny hadisoana noho ny fotoana famonoana baiko mihoatra ny 30 segondra:
Raha mijery ny fangatahana novokarin'ny LINQ ianao:
, dia azonao antoka fa ny fifantenana dia mitranga amin'ny alΓ lan'ny vokatra Cartesian amin'ny andiany roa (tabilao):
Namorona 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]
Andao averina soratana ny fangatahana LINQ toy izao manaraka izao:
Optimized LINQ fanontaniana
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 });
Avy eo dia mahazo ity fangatahana SQL manaraka ity izahay:
SQL fanontaniana
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]
Indrisy, amin'ny fangatahana LINQ dia tsy misy afa-tsy fepetra iray ihany, noho izany dia azo atao ny manao fangatahana mitovy amin'ny fampiasana fanontaniana roa ho an'ny fepetra tsirairay ary avy eo manambatra azy ireo amin'ny Union mba hanesorana ireo dika mitovy amin'ireo andalana.
Eny, ny fanontaniana dia tsy mitovy amin'ny ankapobeny, amin'ny fiheverana fa mety haverina ny andalana feno dika mitovy. Na izany aza, amin'ny tena fiainana dia tsy ilaina ny andalana dika mitovy tanteraka ary miezaka ny manala azy ireo ny olona.
Andeha isika hampitaha ny drafitra fanatanterahana ireto fanontaniana roa ireto:
- ho an'ny CROSS JOIN ny salan'ny fotoana famonoana dia 195 segondra:
- ho an'ny INNER JOIN-UNION ny salan'isan'ny fotoana famonoana dia latsaky ny 24 segondra:
ΠΠ°ΠΊ Π²ΠΈΠ΄Π½ΠΎ ΠΈΠ· ΡΠ΅Π·ΡΠ»ΡΡΠ°ΡΠΎΠ², Π΄Π»Ρ Π΄Π²ΡΡ ΡΠ°Π±Π»ΠΈΡ Ρ ΠΌΠΈΠ»Π»ΠΈΠΎΠ½Π°ΠΌΠΈ Π·Π°ΠΏΠΈΡΠ΅ΠΉ ΠΎΠΏΡΠΈΠΌΠΈΠ·ΠΈΡΠΎΠ²Π°Π½Π½ΡΠΉ LINQ-Π·Π°ΠΏΡΠΎΡ ΡΠ°Π±ΠΎΡΠ°Π΅Ρ Π² ΡΠ°Π·Ρ Π±ΡΡΡΡΠ΅Π΅, ΡΠ΅ΠΌ Π½Π΅ΠΎΠΏΡΠΈΠΌΠΈΠ·ΠΈΡΠΎΠ²Π°Π½Π½ΡΠΉ.
ΠΠ»Ρ Π²Π°ΡΠΈΠ°Π½ΡΠ° Ρ Π Π² ΡΡΠ»ΠΎΠ²ΠΈΡΡ LINQ-Π·Π°ΠΏΡΠΎΡ Π²ΠΈΠ΄Π°:
Fanontaniana 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 };
izay:
ΠΠΏΡΠ΅Π΄Π΅Π»Π΅Π½ΠΈΠ΅ Π΄Π²ΡΡ ΠΌΠ°ΡΡΠΈΠ²ΠΎΠ²
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, Π° ΡΠ°ΠΊΠΆΠ΅ Π½Π°ΠΏΠΎΠ»Π½Π΅Π½ΠΈΠ΅ Π΄Π°Π½Π½ΡΠΌΠΈ ΡΡΠΈΡ
ΡΠ°Π±Π»ΠΈΡ Π½Π°Ρ
ΠΎΠ΄ΠΈΡΡΡ
Ao amin'ity tahiry ity ihany koa, ao amin'ny lahatahiry Plans, misy drafitra amin'ny fanatanterahana fanontaniana miaraka amin'ny fepetra OR.
Source: www.habr.com