LINQ banyere .NET ka asụsụ njikwa data ọhụrụ siri ike. LINQ ka SQL dị ka akụkụ nke ya na-enye gị ohere iji DBMS na-ekwurịta okwu nke ọma nke ọma site na iji, dịka ọmụmaatụ, Asambodo Ụlọ ọrụ. Agbanyeghị, na-eji ya ọtụtụ oge, ndị mmepe na-echefu ileba anya n'ụdị ajụjụ SQL onye na-eweta ajụjụ, n'ọnọdụ gị, Framework, ga-ewepụta.
Ka anyị were ihe atụ leba anya n'isi ihe abụọ.
Iji mee nke a, mepụta nchekwa data ule na SQL Server, wee mepụta tebụl abụọ n'ime ya site na iji ajụjụ ndị a:
Ịmepụta tebụl
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
Ugbu a, ka anyị mejupụta tebụl Ref site na iji edemede a:
Na-ejuputa tebụl 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 anyị were edemede ndị a mejupụta tebụl ahịa n'otu aka ahụ:
Na-ebupụta tebụl ndị ahịa
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
Ya mere, anyị nwetara tebụl abụọ, otu n'ime ha nwere ihe karịrị 1 nde ahịrị data, na nke ọzọ nwere ihe karịrị 10 nde ahịrị data.
Ugbu a na Visual Studio, ị ga-emepụta ihe nlele Visual C # Console App (.NET Framework):
Na-esote, ị ga-agbakwunye ọbá akwụkwọ maka Nhazi Nrụpụta ka iji na nchekwa data emekọrịta ihe.
Iji tinye ya, pịa aka nri na ọrụ ahụ wee họrọ Jikwaa ngwugwu NuGet site na menu onodu:
Mgbe ahụ, na mpio njikwa ngwugwu NuGet nke pụtara, tinye okwu “Entity Framework” na mpio ọchụchọ wee họrọ ngwugwu ihe nrụnye wee wụnye ya:
Na-esote, na faịlụ App.config, mgbe imechichara ihe nhazi ngalaba, ịkwesịrị ịgbakwunye ngọngọ ndị a:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Na njikọ eriri ịkwesịrị itinye eriri njikọ.
Ugbu a, ka anyị mepụta 3 interfaces na faịlụ dị iche iche:
- Na-emejuputa interface IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Mmejuputa nke interface IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Mmejuputa nke interface IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Na faịlụ dị iche, anyị ga-emepụta ntọala klaasị BaseEntity maka ụlọ ọrụ abụọ anyị, nke ga-agụnye mpaghara nkịtị:
Mmejuputa nke klas isi BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Ọzọ, anyị ga-ekepụta ihe abụọ anyị na faịlụ dị iche iche:
- Mmejuputa nke klas Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Mmejuputa nke klas ndị ahịa
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; } } }
Ugbu a ka anyị mepụta ọnọdụ UserContext na faịlụ dị iche:
Mmejuputa klaasị 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; }
}
}
Anyị nwetara ngwọta dị njikere maka iji LINQ gaa SQL na-eduzi ule njikarịcha site na EF maka MS SQL Server:
Ugbu a tinye koodu a n'ime faịlụ Program.cs:
Program.cs faịlụ
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();
}
}
}
}
Ọzọ, ka anyị malite ọrụ anyị.
Na njedebe nke ọrụ ahụ, a ga-egosipụta ihe ndị a na console:
Emepụtara ajụjụ 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])
Ya bụ, n'ozuzu, ajụjụ LINQ mepụtara ajụjụ SQL na MS SQL Server DBMS nke ọma.
Ugbu a, ka anyị gbanwee ọnọdụ NA ka ọ bụrụ OR na ajụjụ LINQ:
LINQ ajụjụ
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 };
Ka anyị malite ngwa anyị ọzọ.
Mgbu ahụ ga-ada na njehie n'ihi oge mmezu iwu gafere 30 sekọnd:
Ọ bụrụ na ị lelee ajụjụ LINQ weputara:
, mgbe ahụ, ị nwere ike ijide n'aka na nhọrọ ahụ sitere na ngwaahịa Cartesian nke nhazi abụọ (tebụl):
Emepụtara ajụjụ 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]
Ka anyị degharịa ajụjụ LINQ dị ka ndị a:
Ajuju LINQ kachasị
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 });
Mgbe ahụ anyị nwetara ajụjụ SQL:
Ajụjụ 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]
Ewoo, na ajụjụ LINQ enwere ike ịnwe naanị otu ọnọdụ njikọ, yabụ ebe a, ọ ga-ekwe omume ịme ajụjụ nhata site na iji ajụjụ abụọ maka ọnọdụ ọ bụla wee jikọta ha site na Union iji wepụ oyiri n'etiti ahịrị.
Ee, ajụjụ ndị a ga-abụkarị ndị na-abụghị otu, na-eburu n'uche na enwere ike iweghachite ahịrị oyiri zuru oke. Otú ọ dị, na ndụ n'ezie, a dịghị mkpa ahịrị abụọ zuru ezu ma ndị mmadụ na-agbalị ikpochapụ ha.
Ugbu a, ka anyị tulee atụmatụ mmezu nke ajụjụ abụọ a:
- maka CROSS JOIN, nkezi oge ogbugbu bụ 195 sekọnd:
- maka INNER JOIN-UNION nkezi oge ogbugbu na-erughị 24 sekọnd:
Dị ka ị na-ahụ site na nsonaazụ ya, maka tebụl abụọ nwere nde ndekọ, ajụjụ LINQ kachasị mma na-agba ọsọ karịa nke na-adịghị mma.
Maka nhọrọ nwere AND na ọnọdụ, ajụjụ LINQ nke ụdị:
LINQ ajụjụ
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 };
Ajuju SQL ziri ezi ga-fọrọ nke nta ka ọ bụrụ mgbe niile, nke ga-agba ọsọ na nkezi n'ihe dịka 1 sekọnd:
Ọzọkwa maka LINQ na-eme atụmatụ aghụghọ kama ịjụ ajụjụ dịka:
Ajụjụ LINQ (nhọrọ nke mbụ)
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 };
ị nwere ike iji ajụjụ dịka:
Ajụjụ LINQ (nhọrọ nke mbụ)
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 };
ebe:
Na-akọwa ụzọ abụọ
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" } };
, na a kọwapụtara ụdị Para dị ka ndị a:
Para Ụdị Nkọwa
class Para
{
public int Key1, Key2;
public string Data;
}
Ya mere, anyị nyochara akụkụ ụfọdụ na ịkwalite ajụjụ LINQ na MS SQL Server.
N'ụzọ dị mwute, ọbụna ahụmahụ na-eduga .NET mmepe na-echefu na ha kwesịrị ịghọta ihe ntụziaka ha na-eme n'azụ Mpaghara. Ma ọ bụghị ya, ha na-aghọ ndị nhazi ma nwee ike ịkụnye bọmbụ oge n'ọdịnihu ma mgbe ị na-eme ka ngwọta software na obere mgbanwe na ọnọdụ gburugburu ebe obibi.
E mekwara nyocha dị mkpirikpi .
Isi mmalite maka ule ahụ - ọrụ ahụ n'onwe ya, ịmepụta tebụl na nchekwa data TEST, yana jupụta tebụl ndị a na data dị. .
Nakwa na ebe nchekwa a, na nchekwa Plans, enwere atụmatụ maka iji ọnọdụ OR mezuo ajụjụ.
isi: www.habr.com