Chaidh LINQ a-steach gu .NET mar chànan làimhseachaidh dàta cumhachdach ùr. Leigidh LINQ gu SQL mar phàirt dheth dhut conaltradh gu math goireasach le DBMS a’ cleachdadh, mar eisimpleir, Entity Framework. Ach, le bhith ga chleachdadh gu math tric, bidh luchd-leasachaidh a’ dìochuimhneachadh coimhead air dè an seòrsa ceist SQL a ghineas an solaraiche ceasnachail, nad chùis agad Entity Framework.
Bheir sinn sùil air dà phrìomh phuing a’ cleachdadh eisimpleir.
Gus seo a dhèanamh, cruthaich stòr-dàta Deuchainn ann an SQL Server, agus cruthaich dà chlàr ann leis a’ cheist a leanas:
A 'cruthachadh chlàran
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
A-nis leig dhuinn an clàr Ref a lìonadh le bhith a’ ruith an sgriobt a leanas:
A’ lìonadh a’ bhùird 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
Mar an ceudna lìonaidh sinn clàr an Luchd-ceannach leis an sgriobt a leanas:
A 'lìonadh clàr luchd-cleachdaidh
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
Mar sin, fhuair sinn dà chlàr, aon dhiubh le còrr air 1 millean sreath de dhàta, agus am fear eile le còrr air 10 millean sreath de dhàta.
A-nis ann an Visual Studio feumaidh tu pròiseact deuchainn Visual C # Console App (.NET Framework) a chruthachadh:
An ath rud, feumaidh tu leabharlann a chuir ris airson an Frèam Aonad gus eadar-obrachadh leis an stòr-dàta.
Gus a chuir ris, cliog deas air a’ phròiseact agus tagh Stiùirich pacaidean NuGet bhon chlàr co-theacsa:
An uairsin, anns an uinneag riaghlaidh pacaid NuGet a nochdas, cuir a-steach am facal “Entity Framework” san uinneag sgrùdaidh agus tagh am pasgan Entity Framework agus stàlaich e:
An uairsin, anns an fhaidhle App.config, às deidh dhut an eileamaid configSections a dhùnadh, feumaidh tu am bloc a leanas a chuir ris:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Ann an connectionString feumaidh tu an sreang ceangail a chuir a-steach.
A-nis cruthaichidh sinn 3 eadar-aghaidh ann am faidhlichean fa leth:
- Cur an gnìomh an eadar-aghaidh iBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } }
- Cur an gnìomh an eadar-aghaidh iBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } }
- Cur an gnìomh an eadar-aghaidh IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Agus ann am faidhle air leth cruthaichidh sinn BaseEntity clas bunaiteach airson an dà bhuidheann againn, a bheir a-steach raointean cumanta:
Cur an gnìomh a’ bhun-chlas BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
An ath rud, cruthaichidh sinn an dà bhuidheann againn ann am faidhlichean fa leth:
- Cur an gnìomh clas Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } }
- Cur an gnìomh clas Luchd-cleachdaidh
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; } } }
A-nis cruthaichidh sinn co-theacsa UserContext ann am faidhle air leth:
Cur an gnìomh clas 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; }
}
}
Fhuair sinn fuasgladh deiseil airson deuchainnean optimization a dhèanamh le LINQ gu SQL tro EF airson MS SQL Server:
A-nis cuir a-steach an còd a leanas a-steach don fhaidhle Program.cs:
Prògram.cs faidhle
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();
}
}
}
}
An ath rud, leig dhuinn ar pròiseact a chuir air bhog.
Aig deireadh na h-obrach, thèid na leanas a thaisbeanadh air a’ chonsail:
Ceist SQL air a chruthachadh
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])
Is e sin, san fharsaingeachd, chruthaich a’ cheist LINQ ceist SQL don MS SQL Server DBMS gu math.
A-nis atharraichidh sinn an suidheachadh AND gu OR anns a’ cheist LINQ:
Ceist 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 };
Agus leig leinn an tagradh againn a chuir air bhog a-rithist.
Tuitidh an cur gu bàs le mearachd leis gu bheil ùine cur an gnìomh nan òrduighean nas fhaide na 30 diog:
Ma choimheadas tu air a’ cheist a chaidh a chruthachadh le LINQ:
, an uairsin faodaidh tu dèanamh cinnteach gu bheil an taghadh a’ tachairt tro thoradh Cartesian de dhà sheata (clàran):
Ceist SQL air a chruthachadh
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]
Dèanamaid ath-sgrìobhadh air a’ cheist LINQ mar a leanas:
Ceist LINQ as fheàrr
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 });
An uairsin gheibh sinn a’ cheist SQL a leanas:
Ceist 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]
Gu mì-fhortanach, ann an ceistean LINQ chan urrainn ach aon chumha ceangail a bhith ann, agus mar sin an seo tha e comasach ceist co-ionann a dhèanamh a’ cleachdadh dà cheist airson gach cùmhnant agus an uairsin gan cur còmhla tron Aonadh gus dùblaidhean a thoirt air falbh am measg nan sreathan.
Bidh, mar as trice bidh na ceistean neo-ionann, a’ gabhail a-steach gum faodar sreathan dùblaichte iomlan a thilleadh. Ach, ann am fìor bheatha, chan eil feum air loidhnichean dùblaichte iomlan agus bidh daoine a’ feuchainn ri faighinn cuidhteas iad.
A-nis dèanamaid coimeas eadar planaichean cur gu bàs an dà cheist seo:
- airson CROSS JOIN is e an ùine cur gu bàs cuibheasach 195 diogan:
- airson INNER JOIN-UNION tha an ùine cur gu bàs cuibheasach nas lugha na 24 diogan:
Mar a chì thu bho na toraidhean, airson dà chlàr le milleanan de chlàran, tha a’ cheist LINQ làn-leasaichte iomadh uair nas luaithe na am fear neo-leasaichte.
Airson an roghainn le AND anns na cumhaichean, ceist LINQ den fhoirm:
Ceist 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 };
Cha mhòr nach tèid a’ cheist SQL ceart a chruthachadh an-còmhnaidh, a ruitheas gu cuibheasach ann an timcheall air 1 diog:
Cuideachd airson làimhseachadh LINQ to Objects an àite ceist mar:
Ceist LINQ (roghainn 1d)
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 };
faodaidh tu ceist mar:
Ceist LINQ (roghainn 2d)
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 };
far:
A 'mìneachadh dà shreath
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" } };
, agus tha an seòrsa Para air a mhìneachadh mar a leanas:
Seòrsa Mìneachadh Para
class Para
{
public int Key1, Key2;
public string Data;
}
Mar sin, rinn sinn sgrùdadh air cuid de thaobhan ann a bhith ag àrdachadh cheistean LINQ gu MS SQL Server.
Gu mì-fhortanach, tha eadhon luchd-leasachaidh eòlach agus adhartach .NET a 'dìochuimhneachadh gum feum iad tuigsinn dè a tha an stiùireadh a chleachdas iad a' dèanamh air cùl na seallaidhean. Rud eile, bidh iad nan luchd-rèiteachaidh agus faodaidh iad boma ùine a chuir san àm ri teachd an dà chuid nuair a bhios iad a’ sgèileadh fuasgladh bathar-bog agus le atharrachaidhean beaga ann an suidheachaidhean àrainneachd a-muigh.
Chaidh ath-sgrùdadh goirid a dhèanamh cuideachd
Tha na stòran airson an deuchainn - am pròiseact fhèin, cruthachadh chlàran ann an stòr-dàta TEST, a bharrachd air na clàran sin a lìonadh le dàta air an suidheachadh
Cuideachd anns an stòras seo, ann am pasgan nam Planaichean, tha planaichean ann airson ceistean a chur an gnìomh le cumhachan OR.
Source: www.habr.com