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:
.
Komanso munkhokwe iyi, mufoda ya Plans, pali mapulani ochitira mafunso ndi OR zikhalidwe.
Source: www.habr.com