I uru mai a LINQ ki te .NET hei reo kaha raweke raraunga hou. Ko te LINQ ki te SQL hei waahanga ka taea e koe te whakawhitiwhiti korero me te DBMS ma te whakamahi, hei tauira, Anga Hanganga. Heoi, ma te whakamahi i te nuinga o nga wa, ka wareware nga kaiwhakawhanake ki te titiro he aha te ahua o te patai SQL ka mahia e te kaiwhakarato patai, i roto i to keehi anga Hinonga.
Kia titiro tatou ki nga kaupapa matua e rua ma te whakamahi i tetahi tauira.
Hei mahi i tenei, hangahia he papaunga raraunga Whakamatau ki te SQL Server, ka hanga kia rua nga ripanga ki roto ma te whakamahi i te patai e whai ake nei:
Te hanga ripanga
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
Inaianei kia whakakiia te ripanga Ref ma te whakahaere i te tuhinga e whai ake nei:
Te whakakī i te ripanga 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
Kia pera ano te whakakii i te ripanga Kiritaki ma te whakamahi i te tuhinga e whai ake nei:
Whakanuia te ripanga Kiritaki
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
No reira, e rua nga ripanga i riro mai i a maatau, ko tetahi he neke atu i te 1 miriona rarangi raraunga, ko tetahi atu neke atu i te 10 miriona rarangi raraunga.
Inaianei kei Visual Studio me hanga e koe he kaupapa whakamatautau Visual C# Console App (.NET Framework):
Whai muri, me taapiri he whare pukapuka mo te Anga Hinonga ki te mahi tahi me te paataka raraunga.
Hei taapiri, paato-matau ki te kaupapa ka kowhiria Manage NuGet Packages mai i te tahua horopaki:
Na, i roto i te matapihi whakahaere kete NuGet ka puta, whakauruhia te kupu "Anga Hinonga" ki te matapihi rapu ka kowhiri i te kete Anga Hinonga me te whakauru:
I muri mai, i roto i te konae App.config, i muri i te kati i te huānga configSections, me tapiri e koe te poraka e whai ake nei:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
In connectionString me whakauru koe i te aho hononga.
Inaianei me hanga e 3 nga atanga ki nga konae motuhake:
- Te whakatinana i te atanga IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Te whakatinanatanga o te atanga IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Te whakatinanatanga o te atanga IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
A, i roto i tetahi konae motuhake ka hangaia e matou he akomanga turanga BaseEntity mo a maatau hinonga e rua, ka uru ki nga mara noa:
Te whakatinanatanga o te akomanga turanga BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
I muri mai, ka hangahia e maatau nga hinonga e rua ki nga konae motuhake:
- Te whakatinanatanga o te akomanga Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Te whakatinanatanga o te akomanga Kiritaki
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; } } }
Inaianei me hanga he horopaki KaiwhakamahiWhakamahinga ki tetahi konae motuhake:
Te whakatinanatanga o te akomanga KaiwhakamahiContex
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; }
}
}
I whiwhi matou i tetahi otinga kua rite mo te whakahaere i nga whakamatautau arotautanga me te LINQ ki te SQL ma te EF mo te MS SQL Server:
Inaianei whakauruhia te waehere e whai ake nei ki te konae Program.cs:
Kōnae 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();
}
}
}
}
Ka mutu, ka whakarewahia ta tatou kaupapa.
I te mutunga o te mahi, ka whakaatuhia nga mea e whai ake nei ki te papatohu:
Uiui SQL Hangaia
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])
Arā, i te nuinga o te wā, i hangaia e te uiui LINQ he uiui SQL ki te MS SQL Server DBMS he pai.
Inaianei me huri te ahua ME ki OR i te patai LINQ:
Uiui 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 };
A me whakarewa ano ta tatou tono.
Ka tukituki te mahi me te hapa na te wa mahi whakahau neke atu i te 30 hēkona:
Mena ka titiro koe ki te patai i hangaia e LINQ:
, ka taea e koe te whakarite ka puta te kowhiringa ma te hua Cartesian o nga huinga e rua (tepu):
Uiui SQL Hangaia
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]
Me tuhi ano te patai LINQ penei:
Uiui LINQ kua arotau
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 });
Na ka whiwhi tatou i te patai SQL e whai ake nei:
Uiui 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]
Aue, i roto i nga patai LINQ kotahi noa te herenga hono, na konei ka taea te hanga i tetahi patai rite ma te whakamahi i nga patai e rua mo ia ahuatanga katahi ka whakakotahi ma te Uniana hei tango i nga taarua i waenga i nga rarangi.
Ae, ko nga patai ka kore e rite, me te mahara ka taea te whakahoki mai i nga rarangi taarua. Heoi, i roto i te ora tonu, kaore e hiahiatia nga raina taarua katoa ka ngana nga tangata ki te whakakore.
Inaianei me whakatauritea nga mahere mahi o enei patai e rua:
- mo te CROSS JOIN ko te 195 hēkona te wa mahi:
- mo INNER JOIN-UNION he iti iho i te 24 hēkona te wa mahi toharite:
Ka taea e koe te kite mai i nga hua, mo nga tepu e rua me nga miriona rekoata, he maha nga wa tere ake te patai LINQ kua pai ake i te mea kaore i arotauhia.
Mo te whiringa me AND i roto i nga tikanga, he patai LINQ o te puka:
Uiui 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 };
Ko te patai SQL tika ka tata tonu ka mahia, ka haere i te toharite i roto i te 1 hēkona:
Ano hoki mo te LINQ ki nga mahi whanoke hei utu mo te patai penei:
Uiui LINQ (kōwhiringa tuatahi)
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 };
Ka taea e koe te whakamahi i tetahi patai penei:
Uiui LINQ (kōwhiringa tuatahi)
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 };
kei hea:
Te tautuhi i nga huinga e rua
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" } };
, a ko te momo Para kua tautuhia e whai ake nei:
Whakamaramatanga Momo Para
class Para
{
public int Key1, Key2;
public string Data;
}
Na, i tirotirohia e matou etahi waahanga ki te arotau i nga patai LINQ ki te MS SQL Server.
Kia aroha mai, ahakoa nga kaiwhakawhanake .NET mohio me te rangatira ka wareware me mohio ratou he aha nga tohutohu e whakamahia ana e ratou i muri i nga whakaaturanga. Ki te kore, ka noho hei kaiwhakariterite ka taea te whakato i te poma a muri ake nei i te wa e whakatauhia ana te otinga rorohiko me nga huringa iti o nga ahuatanga taiao o waho.
I whakahaeretia ano he arotake poto .
Ko nga puna mo te whakamatautau - ko te kaupapa ake, ko te hanga i nga ripanga i roto i te paataka TEST, me te whakakii i enei ripanga me nga raraunga kei reira .
Kei roto ano i tenei putunga, kei te kōpaki Mahere, he mahere mo te whakahaere i nga patai me nga tikanga OR.
Source: will.com