LINQ idalowa mu .NET ngati chilankhulo chatsopano champhamvu chosinthira deta. LINQ ku SQL monga gawo lake limakupatsani mwayi wolumikizana bwino ndi DBMS pogwiritsa ntchito, mwachitsanzo, Entity Framework. Komabe, kugwiritsa ntchito nthawi zambiri, opanga amaiwala kuyang'ana mtundu wa SQL wofunsayo yemwe angakufunseni, kwa inu Entity Framework, angapange.
Tiyeni tione mfundo zikuluzikulu ziwiri pogwiritsa ntchito chitsanzo.
Kuti muchite izi, pangani database ya Test mu SQL Server, ndikupanga matebulo awiri mmenemo pogwiritsa ntchito funso ili:
Kupanga matebulo
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
Tsopano tiyeni tiwonjezere tebulo la Ref poyendetsa script iyi:
Kudzaza Ref table
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
Tidzazenso tebulo la Makasitomala pogwiritsa ntchito script:
Kudzaza tebulo la Makasitomala
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
Choncho, tinalandira matebulo awiri, imodzi yomwe ili ndi mizere yoposa 1 miliyoni, ndipo ina ili ndi mizere yoposa 10 miliyoni.
Tsopano mu Visual Studio muyenera kupanga kuyesa Visual C# Console App (.NET Framework) pulojekiti:
Kenako, muyenera kuwonjezera laibulale ya Entity Framework kuti igwirizane ndi database.
Kuti muwonjezere, dinani kumanja pa polojekitiyo ndikusankha Sinthani Phukusi la NuGet kuchokera pazosankha:
Kenako, pawindo loyang'anira phukusi la NuGet lomwe likuwoneka, lowetsani mawu oti "Entity Framework" pawindo losakira ndikusankha phukusi la Entity Framework ndikuyiyika:
Chotsatira, mu fayilo ya App.config, mutatseka chinthu cha configSections, muyenera kuwonjezera chipika chotsatirachi:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ΠΠΠ―_ΠΠΠΠΠΠΠΠ―Π Π_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
MuConnectString muyenera kulowa chingwe cholumikizira.
Tsopano tiyeni tipange zolumikizira 3 mumafayilo osiyanasiyana:
- Kukhazikitsa mawonekedwe a IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } }
- Kukhazikitsa mawonekedwe a IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } }
- Kukhazikitsa mawonekedwe a IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Ndipo mu fayilo ina tidzapanga BaseEntity yoyambira m'mabungwe athu awiri, omwe aziphatikiza magawo wamba:
Kukhazikitsidwa kwa BaseEntity Base class
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Kenako, tipanga mabungwe athu awiri m'mafayilo osiyana:
- Kukhazikitsidwa kwa Ref class
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } }
- Kukhazikitsa kalasi ya Makasitomala
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; } } }
Tsopano tiyeni tipange UserContext mufayilo ina:
Kukhazikitsa kalasi ya 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; }
}
}
Tidalandira yankho lomwe lapangidwa kale poyesa kukhathamiritsa ndi LINQ kupita ku SQL kudzera pa EF ya MS SQL Server:
Tsopano lowetsani code zotsatirazi mu fayilo ya Program.cs:
Pulogalamu.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();
}
}
}
}
Kenako, tiyeni tiyambitse polojekiti yathu.
Pamapeto pa ntchito, zotsatirazi zikuwonetsedwa pa console:
Yapangidwa 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])
Izi zikutanthauza kuti, funso la LINQ linapanga funso la SQL ku MS SQL Server DBMS bwino.
Tsopano tiyeni tisinthe AND chikhalidwe kukhala OR mufunso la LINQ:
LINQ funso
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 };
Ndipo tiyeni tiyambitsenso pulogalamu yathu.
Kuphaku kudzawonongeka ndi cholakwika chifukwa cha nthawi yoperekera lamulo yopitilira masekondi 30:
Mukayang'ana funso lomwe linapangidwa ndi LINQ:
, ndiye mutha kuwonetsetsa kuti kusankha kukuchitika kudzera muzinthu za Cartesian zamagulu awiri (matebulo):
Yapangidwa 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]
Tiyeni tilembenso funso la LINQ motere:
Konzani funso la 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 });
Kenako timapeza funso lotsatira la SQL:
SQL funso
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]
Tsoka, mumafunso a LINQ pakhoza kukhala cholumikizira chimodzi chokha, kotero apa ndizotheka kupanga funso lofanana pogwiritsa ntchito mafunso awiri pamtundu uliwonse ndikuphatikiza kudzera mu Union kuti muchotse zobwereza pakati pa mizere.
Inde, mafunsowo sadzakhala ofanana, poganizira kuti mizere yobwereza ikhoza kubwezedwa. Komabe, m'moyo weniweni, mizere yobwereza yathunthu sifunikira ndipo anthu amayesa kuwachotsa.
Tsopano tiyeni tifanizire mapulani a mafunso awiriwa:
- kwa CROSS JOIN nthawi yapakati yophedwa ndi masekondi 195:
- kwa INNER JOIN-UNION nthawi yapakati yopha ndi yochepera masekondi 24:
Monga mukuwonera pazotsatira, pamagome awiri okhala ndi mamiliyoni a zolemba, funso lokongoletsedwa la LINQ limathamanga nthawi zambiri kuposa lomwe silinakwaniritsidwe.
Pakusankha ndi NDI mumikhalidwe, funso la LINQ la fomu:
LINQ funso
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 };
Funso lolondola la SQL lidzapangidwa nthawi zonse, lomwe limayenda pafupifupi mphindi imodzi:
Komanso kwa LINQ to Objects manipulations m'malo mofunsa ngati:
LINQ funso (njira yoyamba)
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 };
mutha kugwiritsa ntchito funso ngati:
LINQ funso (njira yoyamba)
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 };
kumene:
Kufotokozera mitundu iwiri
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" } };
, ndipo mtundu wa Para umafotokozedwa motere:
Para Type Tanthauzo
class Para
{
public int Key1, Key2;
public string Data;
}
Chifukwa chake, tidasanthula mbali zina pakuwongolera mafunso a LINQ ku MS SQL Server.
Tsoka ilo, ngakhale odziwa bwino komanso otsogola a .NET amayiwala kuti akuyenera kumvetsetsa zomwe malangizo omwe amagwiritsa ntchito amachita kumbuyo kwazithunzi. Kupanda kutero, amakhala okonza ndipo amatha kubzala bomba lanthawi mtsogolo powonjezera njira yothetsera pulogalamuyo komanso kusintha pang'ono kwa chilengedwe chakunja.
Ndemanga yachidule idachitikanso
Magwero a mayeso - polojekiti yokha, kupanga matebulo mu database ya TEST, komanso kudzaza matebulo awa ndi data.
Komanso munkhokwe iyi, mufoda ya Plans, pali mapulani ochitira mafunso ndi OR zikhalidwe.
Source: www.habr.com