LINQ e kentse .NET joalo ka puo e ncha e matla ea ho thetsa data. LINQ ho SQL e le karolo ea eona e u lumella ho buisana hamonate le DBMS u sebelisa, mohlala, Entity Framework. Leha ho le joalo, ha ba e sebelisa khafetsa, bahlahisi ba lebala ho sheba hore na ke potso ea mofuta ofe ea SQL eo mofani a ka e botsang, molemong oa hau Entity Framework, e tla hlahisa.
A re shebeng lintlha tse peli tsa sehlooho re sebelisa mohlala.
Ho etsa sena, theha database ea Teko ho SQL Server, 'me u thehe litafole tse peli ho eona u sebelisa potso e latelang:
Ho theha litafole
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
Joale a re kenyeng tafole ea Ref ka ho sebelisa mongolo o latelang:
Ho tlatsa tafole ea 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
Ka mokhoa o tšoanang, re tlatse tafole ea Bareki re sebelisa mongolo o latelang:
Ho tlatsa tafole ea Bareki
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
Kahoo, re amohetse litafole tse peli, e 'ngoe ea eona e na le mela e fetang limilione tse 1,' me e 'ngoe e na le mela e fetang limilione tse 10 ea data.
Hona joale ho Visual Studio o hloka ho etsa tlhahlobo ea Visual C# Console App (.NET Framework) projeke:
Ka mor'a moo, u lokela ho eketsa laebrari bakeng sa Entity Framework ho sebelisana le database.
Ho e eketsa, tobetsa ka ho le letona morerong ebe u khetha Laola liphutheloana tsa NuGet ho tsoa ho menyu ea litaba:
Ebe, ka fensetere ea tsamaiso ea sephutheloana sa NuGet e hlahang, kenya lentsoe "Entity Framework" fensetereng ea ho batla ebe u khetha sephutheloana sa Entity Framework ebe u se kenya:
Ka mor'a moo, faeleng ea App.config, ka mor'a ho koala karolo ea configSections, u lokela ho eketsa thibela e latelang:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Ho connectionString o hloka ho kenya khoele ea khokahano.
Joale ha re theheng likhokahano tse 3 ka lifaele tse arohaneng:
- Ho kenya tshebetsong sebopeho sa IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Ts'ebetsong ea IBaseEntityName interface
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Ho kenngwa tshebetsong ha IBaseNameInsertUTCDate interface
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Mme ka faeleng e arohaneng re tla theha sehlopha sa mantlha sa BaseEntity bakeng sa mekhatlo ea rona e 'meli, e tla kenyelletsa likarolo tse tloaelehileng:
Ts'ebetsong ea sehlopha sa motheo sa BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
E latelang, re tla theha mekhatlo ea rona e 'meli ka lifaele tse arohaneng:
- Ts'ebetsong ea sehlopha sa Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Phethahatso ea sehlopha sa Bareki
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; } } }
Joale ha re theheng moelelo oa UserContext faeleng e fapaneng:
Ts'ebetsong ea sehlopha sa 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; }
}
}
Re fumane tharollo e lokiselitsoeng ea ho etsa liteko tsa optimization le LINQ ho SQL ka EF bakeng sa MS SQL Server:
Joale kenya khoutu e latelang faeleng ea Program.cs:
Faele ea 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();
}
}
}
}
E latelang, ha re thakholeng morero oa rona.
Qetellong ea mosebetsi, tse latelang li tla hlahisoa ho console:
Potso ea SQL e hlahisitsoeng
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])
Ke hore, ka kakaretso, potso ea LINQ e hlahisitse potso ea SQL ho MS SQL Server DBMS hantle.
Joale ha re fetoleng LE boemo ho OR ho potso ea LINQ:
LINQ potso
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 };
Mme ha re qaleng kopo ya rona hape.
Ts'ebetso e tla senyeha ka phoso ka lebaka la nako ea ho phethahatsa taelo e fetang metsotsoana e 30:
Haeba u sheba potso e hlahisitsoeng ke LINQ:
, joale o ka etsa bonnete ba hore khetho e etsahala ka sehlahisoa sa Cartesian sa lihlopha tse peli (litafole):
Potso ea SQL e hlahisitsoeng
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]
Ha re ngole potso ea LINQ ka tsela e latelang:
E ntlafalitse potso ea 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 });
Ebe re fumana potso e latelang ea SQL:
Potso ea 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]
Oho, lipotsong tsa LINQ ho ka ba le boemo bo le bong feela ba ho kopanya, kahoo mona ho ka khoneha ho etsa potso e lekanang le lipotso tse peli bakeng sa boemo bo bong le bo bong ebe u li kopanya ka Union ho tlosa tse kopitsoang har'a mela.
E, lipotso ka kakaretso li ke ke tsa lekana, ho nahanoa hore ho ka khutlisoa mela e felletseng. Leha ho le joalo, bophelong ba 'nete, ha ho hlokahale mela e felletseng e kopitsoang mme batho ba leka ho e tlosa.
Joale a re bapiseng merero ea ts'ebetso ea lipotso tsena tse peli:
- bakeng sa CROSS JOIN nako e tloaelehileng ea ho phetha ke metsotsoana e 195:
- bakeng sa INNER JOIN-UNION nako e tloaelehileng ea ho phetha e ka tlase ho metsotsoana e 24:
.
Hape sebakeng sena sa polokelo, foldareng ea Meralo, ho na le merero ea ho etsa lipotso ka OR maemo.
Source: www.habr.com