Geekly articles weekly

MS SQL рд╕рд░реНрд╡рд░ рдХреЗ рд▓рд┐рдП C # .NET рдореЗрдВ LINQ рдкреНрд░рд╢реНрдиреЛрдВ рдХреЗ рдЕрдиреБрдХреВрд▓рди рдХреЗ рдХреБрдЫ рдкрд╣рд▓реВ

LINQ рдиреЗ .NET рдХреЛ рдПрдХ рд╢рдХреНрддрд┐рд╢рд╛рд▓реА рдирдП рдбреЗрдЯрд╛ рд╣реЗрд░рдлреЗрд░ рднрд╛рд╖рд╛ рдХреЗ рд░реВрдк рдореЗрдВ рджрд░реНрдЬ рдХрд┐рдпрд╛ рд╣реИред LINQ to SQL рдЗрд╕рдХреЗ рд╣рд┐рд╕реНрд╕реЗ рдХреЗ рд░реВрдк рдореЗрдВ рдЖрдкрдХреЛ DBMS рдХрд╛ рдЙрдкрдпреЛрдЧ рдХрд░рдХреЗ рдЖрд╕рд╛рдиреА рд╕реЗ рд╕рдВрд╡рд╛рдж рдХрд░рдиреЗ рдХреА рдЕрдиреБрдорддрд┐ рджреЗрддрд╛ рд╣реИ, рдЙрджрд╛рд╣рд░рдг рдХреЗ рд▓рд┐рдП, рдЗрдХрд╛рдИ рдврд╛рдВрдЪрд╛ред рд╣рд╛рд▓рд╛рдВрдХрд┐, рдЕрдХреНрд╕рд░ рдЗрд╕рдХрд╛ рдЙрдкрдпреЛрдЧ рдХрд░рддреЗ рд╣реБрдП, рдбреЗрд╡рд▓рдкрд░реНрд╕ рдпрд╣ рджреЗрдЦрдирд╛ рднреВрд▓ рдЬрд╛рддреЗ рд╣реИрдВ рдХрд┐ рдХреНрд╡реЗрд░реА рдкреНрд░рджрд╛рддрд╛ рдХрд┐рд╕ рд╡рд┐рд╢реЗрд╖ SQL рдХреНрд╡реЗрд░реА рдХреЛ рдЙрддреНрдкрдиреНрди рдХрд░реЗрдЧрд╛, рдЖрдкрдХреЗ рдорд╛рдорд▓реЗ рдореЗрдВ, рдПрдВрдЯрд┐рдЯреА рдлреНрд░реЗрдорд╡рд░реНрдХред

рдЖрдЗрдП рдПрдХ рдЙрджрд╛рд╣рд░рдг рдХрд╛ рдЙрдкрдпреЛрдЧ рдХрд░рдХреЗ рджреЛ рдореБрдЦреНрдп рдмрд┐рдВрджреБрдУрдВ рдХрд╛ рд╡рд┐рд╢реНрд▓реЗрд╖рдг рдХрд░реЗрдВред
рдРрд╕рд╛ рдХрд░рдиреЗ рдХреЗ рд▓рд┐рдП, SQL рд╕рд░реНрд╡рд░ рдореЗрдВ рдкрд░реАрдХреНрд╖рдг рдбреЗрдЯрд╛рдмреЗрд╕ рдмрдирд╛рдПрдБ рдФрд░ рдирд┐рдореНрди рдХреНрд╡реЗрд░реА рдХрд╛ рдЙрдкрдпреЛрдЧ рдХрд░рдХреЗ рдЗрд╕рдореЗрдВ рджреЛ рддрд╛рд▓рд┐рдХрд╛рдПрдБ рдмрдирд╛рдПрдБ:

рдЯреЗрдмрд▓ рдмрдирд╛рдПрдВ
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 :

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


Customer :

Customer
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.
, Manage NuGet Packages:



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 :

  1. IBaseEntityID
    namespace TestLINQ
{
    public interface IBaseEntityID
    {
        int ID { get; set; }
    }
}

  2. IBaseEntityName
    namespace TestLINQ
{
    public interface IBaseEntityName
    {
        string Name { get; set; }
    }
}

  3. 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; }
    }
}


:

  1. Ref
    using System.ComponentModel.DataAnnotations.Schema;

namespace TestLINQ
{
    [Table("Ref")]
    public class Ref : BaseEntity
    {
        public int ID2 { get; set; }
    }
}

  2. Customer
    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 to 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.

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:


, , ():

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]


, LINQ- , Union .
, , . , .

:

  1. CROSS JOIN 195 :
  2. INNER JOIN-UNION 24 :

    .
    Plans .

Source: https://habr.com/ru/post/hi459716/

More articles:


All Articles