LINQ ααΆααααα
αΌα .NET ααΆααΆααΆααααααααααα·ααααααααααΈααααΆαα’αΆαα»ααΆαα LINQ αα
SQL αααααΆααααααα½αααααΆα’αα»ααααΆαα±ααα’αααααααΆαααααααααΆαααΆααααα½αααΆαα½α DBMS αααααααΎα§ααΆα ααα Entity Framework α αααααΆαααΆαααΆααααα ααΆαααααΎααΆααΆααΉαααΆαα α’αααα’αα·ααααααααααα
ααΎαααΆααΎαααααααααααα½α SQL ααααααααΆαααα’ααααααααααααΆαααα’αΆα
αα½αααΆα αααα»αααααΈααααα’ααα Entity Framework ααΉααααααΎαα
ααΌααααα‘ααααΎαα
ααα»α
ααααΆααααΈααααααααΎα§ααΆα ααααα½αα
ααΎααααΈααααΎααΌα
αααα αααααΎα Test database αα
αααα»α SQL Server α αΎααααααΎαααΆααΆαααΈααα
αααα»αααΆαααααααΎαααα½αααΆααααααα
ααΆααααααΎαααΆααΆα
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
α₯α‘αΌαααα α αΌαααΎααααααΎαααΆααΆα 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
α αΌαααΎααααααααΆααΆαα’αα·αα·ααααΌα ααααΆαααααααΎααααααΈαααΆααααααα
ααΆααααα αΌαααΆααΆαα’αα·αα·αα
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
ααΌα ααααα αΎα ααΎαααΆαααα½αααΆααΆαα ααα½αααΈα ααααα½αααΆααα·ααααααα αααΎαααΆα 1 ααΆααα½α αα·ααα½ααααααΆααα·ααααααα αααΎαααΆα 10 ααΆααα½αα
α₯α‘αΌαααααα αααα»α Visual Studio α’αααααααΌααααααΎααααααα Visual C# Console App (.NET Framework) ααΆαααααα
αααααΆαααα α’αααααααΌααααααααααααΆααααααααΆαα Entity Framework ααΎααααΈααααΎα’ααααααααααΆαα½αααΌαααααΆααα·ααααααα
ααΎααααΈααααααααΆ α
α»α
ααααα»αααααΆαααΎαααααα α αΎαααααΎαααΎα ααααααααααααα
αα NuGet ααΈαααΊαα»αααα·ααα
αααααΆαααααα
αααα»ααααα’α½α
ααααααααααααα
αα NuGet ααααααα αΆα ααΌααααα
αΌαααΆααα "Entity Framework" αα
αααα»ααααα’α½α
ααααααα α αΎαααααΎαααΎααααα
αα Entity Framework α αΎαααα‘αΎαααΆα
αααααΆαααααα
αααα»αα―αααΆα App.config αααααΆααααΈαα·αααΆαα» configSections α’αααααααΌααααααααααα»αααΆααααααα
<connectionStrings>
<add name="DBConnection" connectionString="data source=ΠΠΠ―_ΠΠΠΠΠΠΠΠ―Π Π_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
αα αααα»α connectionString α’αααααααΌααααα αΌααααααααααΆααα
α₯α‘αΌααααααΌααααααΎαα ααα»α αααααΆαα 3 αα αααα»αα―αααΆαααΆα ααααα‘ααα
- ααΆαα’αα»ααααα
ααα»α
αααααΆαα IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } }
- ααΆαα’αα»ααααα
ααα»α
αααααΆαα IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } }
- ααΆαα’αα»ααααα
ααα»α
αααααΆαα IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
α αΎααα αααα»αα―αααΆαααΆα ααααα‘αααα½α ααΎαααΉααααααΎαααααΆααααΌαααααΆα BaseEntity αααααΆααα’αααααΆαααΆααααΈαααααααΎα αααααΉααα½ααααα αΌαααΆαααΌαα α
ααΆαα’αα»ααααααααααΆααααΌαααααΆα BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
αααααΆαα ααΎαααΉααααααΎαα’αααααΆαααΆααααΈαααααααΎααα αααα»αα―αααΆαααΆα ααααα‘ααα
- ααΆαα’αα»ααααααααΆαα Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } }
- ααΆαα’αα»ααααααααΆααα’αα·αα·αα
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; } } }
α₯α‘αΌαααα α αΌαααΎααααααΎαααα·αα UserContext αα αααα»αα―αααΆαααΆα ααααα‘αααα½αα
ααΆαα’αα»ααααααααααΆαα 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; }
}
}
ααΎαααΆαααα½ααααααααααΆααααααααααα½α ααΆααααα αααααΆααααΆαααααΎααααααααααΎαααααα·αααααΆαααΆαα½α LINQ αα SQL ααΆαααα EF αααααΆαα MS SQL Serverα
α₯α‘αΌααααα
αΌαααΌαααΆαααααααα
αααα»αα―αααΆα Program.csα
α―αααΆα 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();
}
}
}
}
αααααΆαααα ααΎαα αΆααααααΎαααααααααααααΎαα
αα α α»ααααα ααααααΆαααΆα ααΆαααααααααααΉαααααΌαααΆααααα αΆααα ααΎαα»αααΌαα
ααΆαβαααααΎαβαααα½α 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])
αααααΊααΆααΌαα αααα½α LINQ ααΆααααααΎααααα½α SQL αα ααΆαα MS SQL Server DBMS ααΆααααΆαααα’α
α₯α‘αΌαααα α αΌαααΎαααααΆααααααΌααααααααα AND αα OR αα αααα»ααααα½α LINQα
αααα½α 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 };
α αΎαααΌαααΎαααααΎαααΆααααααα·ααΈααααααΎααααααααα
ααΆαααααα·ααααα·ααΉαααΆαααααααΆαααα α»ααααααΆααααααααααΆααααα·ααααα·ααΆααααααααΆααΎαααΈ 30 αα·ααΆααΈα
ααααα·αααΎα’ααααααα‘ααααΎααααα½αααααααααΎαααα LINQα
αααααΆααααα’αααα’αΆα
ααααΆααααΆααΆαααααΎαααΎαααΎαα‘αΎαααΆααααααα·ααα Cartesian ααΈααα»α (ααΆααΆα):
ααΆαβαααααΎαβαααα½α 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]
α αΌαααΎαααααααααα½α LINQ α‘αΎααα·αααΌα ααΆααααααα
αααα½α 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 });
αααααΆααααααΎαααα½αααΆααααα½α SQL ααΆααααααα
αααα½α 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]
Alas, αα
αααα»ααααα½α LINQ α’αΆα
ααΆαααααααααα
αΌααα½ααααα½αααα»ααααα ααΌα
αααααα
ααΈαααααΆα’αΆα
ααααΎαα
ααΆαααΎααααΈααααΎαααα½αααααΌααααααααΎαααα½αααΈααααααΆααααααααααααΈαα½αα α αΎααααααΆαααααααα
αΌαααααΆααΆαααα Union ααΎααααΈαα»αααααααα½ααααα»αα
αααααα½ααααα
ααΆα/α
αΆα αααα½αααΆααΌαα
ααΉααα·αααααΌααα ααααα·αααΌαααΆαα½αααααααα½αααααααα’αΆα
ααΉαααααΌααααααΌααααα·αα αααααΆαααΆαααΆααααααα
αααα»αααΈαα·ααα·ααααααΆαααααα½ααααααααα·αα
αΆαααΆα
αααα αΎαααα»αααααααΆααΆααααα
αΆαααα½αααα
α₯α‘αΌαααα α αΌαααΎααααααααααααααΆαααααα·ααααα·αααααα½αααΆααααΈααααα
- αααααΆαα CROSS JOIN αααααααΆααααα·ααααα·ααΆαααααααΊ 195 αα·ααΆααΈα
- αααααΆαα INNER JOIN-UNION αααααααΆααααα·ααααα·ααΆαααααααΊαα·α
ααΆα 24 αα·ααΆααΈα
ααΌα αααα’αααα’αΆα ααΎαααΎαααΈαααααα αααααΆααααΆααΆαααΈααααααΆααααααααααΆααΆααααΆα αααα½α LINQ αααααΆαααααΎα±αααααααΎαααΊααΏαααΆαααΆααΆαααααα·αααααΎα±αααααααΎαα αααΎαααα
αααααΆαααααααΎαααΆαα½α AND αα αααα»ααααααααα αααα½α LINQ ααααααααα
αααα½α 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 };
αααα½α SQL ααααΉαααααΌαααΉααααααααααΌαααΆααααααΎα αααααΉαααααΎαααΆαααΆααααααααα»ααααααααααα αα 1 αα·ααΆααΈα
ααααααααααΆαα LINQ to Objects manipulations αααα½αα±αααααα½αααΌα
ααΆα
αααα½α LINQ (αααααΎαααΈ 1)
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 };
α’αααα’αΆα ααααΎαααα½αααΌα ααΆα
αααα½α LINQ (αααααΎαααΈ 2)
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 };
αααααΆαααααα:
ααΆααααααα’αΆααααΈα
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" } };
α αΎααααααα Para ααααΌαααΆααααααααΌα ααΆααααααα
αα·αααααβαααααα Para
class Para
{
public int Key1, Key2;
public string Data;
}
ααΌα αααα ααΎαααΆααα·αα·ααααα·αααααΆααα½αα ααα½ααααα»αααΆααααααΎαααααα·αααααΆααααα½α LINQ αα ααΆαα MS SQL Server α
ααΆα’αα»αα ααΌααααΈααα’αααα’αα·αααααα .NET αααααΆααααα·ααααα αα·αααΆααα»αααααααα ααΆαα½αααααααΌααααααΈα’αααΈαααααΆαααααΆαααααα½αααααααΎααααΎαα ααΈαααααααΆαα ααΎαα·αααΌα αααααα αα½αααααααΆαααΆα’αααααααααα ααΆαααααααα α αΎαα’αΆα ααΆαααααΆααααααααααααΆααΆαααα’ααΆαα ααΆαααα αααααααΎααΆαααααααΆααααααααααΆααααααα·ααΈ αα·αααΆαα½αααΉαααΆαααααΆααααααΌααα·α αα½α αα αααα»αααααααααααα·ααααΆαααΆααααα α
ααΆααα·αα·αααα‘αΎααα·αααααΈαα½αααααααΌαααΆαα’αα»αααααααααα
ααααααααααΆααααΆαααααΎααααα - αααααααααα½αααΆ ααΆααααααΎαααΆααΆααα
αααα»αααΌαααααΆααα·αααααα TEST ααααΌα
ααΆααΆααααααααΆααΆαααΆαααααααΆαα½αααΉααα·ααααααααΆαααΈααΆαααα
ααααααα
αααα»αααααΆααααα αα
αααα»ααααααααΆα ααΆααααααΆααααααΆααααααΎαααΆααααα½αααΆαα½ααααααααα OR α
ααααα: www.habr.com