Andrey Zavadskiy – SQL and .NET developer, trainer, speaker – Page 3 – Give me an URL to rely on, and I will shift the world

May 31, 2017

SqlBulkCopy

SQL Server have great commands like BULK INSERT or OPENROWSET(BULK…) to insert a huge amount of data into a database. .NET Framework has a class SqlBulkCopy that provides similar capabilities in C#. Full documentation is available on MSDN article SqlBulkCopy Class. But SqlBulkCopy has some restrictions on input data. It can read from:

DataRow[] array;
DataTable;
IDataReader.

A simple example of usage can be as follows:

// input data structure
DataTable dtClients = new DataTable();
dtClients.Columns.Add("Name");
dtClients.Columns.Add("CountryCode");

// fill data
var row = dtClients.NewRow();
row["Name"] = "Jeff";
row["CountryCode"] = "UK";
dtClients.Rows.Add(row);

// bulk insert to SQL Server
using (SqlConnection conn1 = new SqlConnection("..."))
{
    using (SqlBulkCopy bc = new SqlBulkCopy(conn1))
    {
        conn1.Open();

        bc.DestinationTableName = "dbo.Clients";

        bc.ColumnMappings.Add("Name", "Name");
        bc.ColumnMappings.Add("CountryCode", "CountryCode");

        bc.WriteToServer(dtClients);
    }
}

// input data structure

DataTable dtClients = new DataTable();

dtClients.Columns.Add("Name");

dtClients.Columns.Add("CountryCode");

// fill data

var row = dtClients.NewRow();

row["Name"] = "Jeff";

row["CountryCode"] = "UK";

dtClients.Rows.Add(row);

// bulk insert to SQL Server

using (SqlConnection conn1 = new SqlConnection("..."))

{

using (SqlBulkCopy bc = new SqlBulkCopy(conn1))

{

conn1.Open();

bc.DestinationTableName = "dbo.Clients";

bc.ColumnMappings.Add("Name", "Name");

bc.ColumnMappings.Add("CountryCode", "CountryCode");

bc.WriteToServer(dtClients);

}

SQL Server Profiler shows that the correct command was really executed.

insert bulk dbo.Clients (
    [Name] NVarChar(50) COLLATE Cyrillic_General_CI_AS, 
    [CountryCode] NVarChar(3) COLLATE Cyrillic_General_CI_AS
)

insert bulk dbo.Clients (

[Name] NVarChar(50) COLLATE Cyrillic_General_CI_AS,

[CountryCode] NVarChar(3) COLLATE Cyrillic_General_CI_AS

)

In order to use SqlBulkCopy class elegantly, it would better to have a method or extension to convert a generic list to DataTable or IDataReader. But it’s a topic for my next post.

April 19, 2017April 19, 2017

Calculating Size Of A Database Table

If you want to get a statistics on a number of database pages used by a specific table in SQL Server, you can query DMV sys.dm_db_partition_stats.

The following query will show you the number of pages per table and each heap/clustered/nonclustered index.

SELECT 
	OBJECT_SCHEMA_NAME(s.object_id) AS [Schema name],
	OBJECT_NAME(s.object_id) AS [Table name],
	s.index_id AS [Index id],
	CASE s.index_id
		WHEN 0 THEN N'Heap'
		WHEN 1 THEN N'Clustered index'
		ELSE N'Nonclustered index'
	END AS [Index type],
	s.used_page_count AS [Total pages],
	s.row_count AS [Total rows],
	CASE s.row_count 
		WHEN 0 THEN NULL
		ELSE 8192 * s.used_page_count / s.row_count
	END AS [Bytes per row]
FROM sys.dm_db_partition_stats s
ORDER BY [Schema name], [Table name], s.index_id;

SELECT

OBJECT_SCHEMA_NAME(s.object_id) AS [Schema name],

OBJECT_NAME(s.object_id) AS [Table name],

s.index_id AS [Index id],

CASE s.index_id

WHEN 0 THEN N'Heap'

WHEN 1 THEN N'Clustered index'

ELSE N'Nonclustered index'

END AS [Index type],

s.used_page_count AS [Total pages],

s.row_count AS [Total rows],

CASE s.row_count

WHEN 0 THEN NULL

ELSE 8192 * s.used_page_count / s.row_count

END AS [Bytes per row]

FROM sys.dm_db_partition_stats s

ORDER BY [Schema name], [Table name], s.index_id;

March 30, 2017April 19, 2017

Updating XML Data In SQL Server

Inspired by this excellent article Examples of using XQuery to update XML Data in SQL Server, I wrote a quick memo how to update XML data in SQL Server.

1. modify command can update only the first occurrence it finds. The command looks as following.

SET @x.modify('replace value of (/clients/client/Name/text())[1] with ("***")');

1	SET @x.modify('replace value of (/clients/client/Name/text())[1] with ("***")');

2. If you need to update all occurrences, make a loop to iterate all of them.

DECLARE @node int;
SELECT @node = MAX(@x.value('count(/clients/client/Name)', 'int'))
WHILE @node > 0
BEGIN
    SELECT @x = @x
    WHERE @x.exist('(/clients/client/Name)[sql:variable("@node")]') = 1;
    SET @x.modify('replace value of ((/clients/client/Name)[sql:variable("@node")]/text())[1] with ("***")');
    SET @node = @node - 1;
END

DECLARE @node int;

SELECT @node = MAX(@x.value('count(/clients/client/Name)', 'int'))

WHILE @node > 0

BEGIN

SELECT @x = @x

WHERE @x.exist('(/clients/client/Name)[sql:variable("@node")]') = 1;

SET @x.modify('replace value of ((/clients/client/Name)[sql:variable("@node")]/text())[1] with ("***")');

SET @node = @node - 1;

END

3. You can substitute a hard-coded path with a variable, but only for the last tag.

DECLARE @path nvarchar(100) = 'Name';
SET @x.modify('replace value of ((/clients/client/*[local-name() = sql:variable("@path")]/text())[1]) with ("***")');

1 2	DECLARE @path nvarchar(100) = 'Name'; SET @x.modify('replace value of ((/clients/client/[local-name() = sql:variable("@path")]/text())[1]) with ("**")');

Source code for this example update_xml.zip

March 9, 2017June 24, 2017

Offline Installer For Visual Studio 2017

Microsoft does not offer an ISO image for Visual Studio 2017, we need to make it ourselves by running web installer with some command-line arguments. Here are the links to official pages:

Create an offline installer for Visual Studio 2017

Visual Studio 2017 workload and component IDs

Example to grab community edition:

vs_community.exe --layout C:\folder --add workload_id_list --lang en-US

1	vs_community.exe --layout C:\folder --add workload_id_list --lang en-US

Layout parameter is mandatory, it points to a folder where the downloaded files would be saved.
Workload_id_list is a list of component IDs, separated by space. If you skip it, all components will be downloaded.

[UPDATE 2017-06-21]
Workloads contain only the recommended components. As mentioned by geoyar, you need to include optional components manually.

Component	ID	Description
Visual Studio core editor (included with Visual Studio Community 2017)	Microsoft.VisualStudio.Workload.CoreEditor	The Visual Studio core shell experience, including syntax-aware code editing, source code control and work item management.
Azure development	Microsoft.VisualStudio.Workload.Azure	Azure SDK, tools, and projects for developing cloud apps and creating resources.
Data storage and processing	Microsoft.VisualStudio.Workload.Data	Connect, develop and test data solutions using SQL Server, Azure Data Lake, Hadoop or Azure ML.
.NET desktop development	Microsoft.VisualStudio.Workload.ManagedDesktop	Build WPF, Windows Forms and console applications using the .NET Framework.
Game development with Unity	Microsoft.VisualStudio.Workload.ManagedGame	Create 2D and 3D games with Unity, a powerful cross-platform development environment.
Linux development with C++	Microsoft.VisualStudio.Workload.NativeCrossPlat	Create and debug applications running in a Linux environment.
Desktop development with C++	Microsoft.VisualStudio.Workload.NativeDesktop	Build classic Windows-based applications using the power of the Visual C++ toolset, ATL, and optional features like MFC and C++/CLI.
Game development with C++	Microsoft.VisualStudio.Workload.NativeGame	Use the full power of C++ to build professional games powered by DirectX, Unreal, or Cocos2d.
Mobile development with C++	Microsoft.VisualStudio.Workload.NativeMobile	Build cross-platform applications for iOS, Android or Windows using C++.
.NET Core cross-platform development	Microsoft.VisualStudio.Workload.NetCoreTools	Build cross-platform applications using .NET Core, ASP.NET Core, HTML, JavaScript, and CSS
Mobile development with .NET	Microsoft.VisualStudio.Workload.NetCrossPlat	Build cross-platform applications for iOS, Android or Windows using Xamarin.
ASP.NET and web development	Microsoft.VisualStudio.Workload.NetWeb	Build web applications using ASP.NET, ASP.NET Core, HTML, JavaScript, and CSS.
Node.js development	Microsoft.VisualStudio.Workload.Node	Build scalable network applications using Node.js, an asynchronous event-driven JavaScript runtime.
Office/SharePoint development	Microsoft.VisualStudio.Workload.Office	Create Office and SharePoint add-ins, SharePoint solutions, and VSTO add-ins using C#, VB, and JavaScript.
Universal Windows Platform development	Microsoft.VisualStudio.Workload.Universal	Create applications for the Universal Windows Platform with C#, VB, JavaScript, or optionally C++.
Visual Studio extension development	Microsoft.VisualStudio.Workload.VisualStudioExtension	Create add-ons and extensions for Visual Studio, including new commands, code analyzers and tool windows.
Mobile development with JavaScript	Microsoft.VisualStudio.Workload.WebCrossPlat	Build Android, iOS and UWP apps using Tools for Apache Cordova.
Unaffiliated components	Component.GitHub.VisualStudio	GitHub extension for Visual Studio
Unaffiliated components	Microsoft.Component.Blend.SDK.WPF	Blend for Visual Studio SDK for .NET
Unaffiliated components	Microsoft.Component.HelpViewer	Help Viewer
Unaffiliated components	Microsoft.Net.Component.3.5.DeveloperTools	.NET Framework 3.5 development tools
Unaffiliated components	Microsoft.VisualStudio.Component.DependencyValidation.Community	Dependency Validation
Unaffiliated components	Microsoft.VisualStudio.Component.LinqToSql	LINQ to SQL tools
Unaffiliated components	Microsoft.VisualStudio.Component.TestTools.Core	Testing tools core features
Unaffiliated components	Microsoft.VisualStudio.Component.TypeScript.2.0	TypeScript 2.0 SDK

February 2, 2017March 30, 2017

OBJECTPROPERTY Function In SQL Server

SQL Server has an excellent metadata function OBJECTPROPERTY() that allows to view a lot of properties of database objects. For example, if table has a trigger, clustered key, check constraint or default. The full list is available on MSDN article OBJECTPROPERTY (Transact-SQL).

For example, if you want to list all tables that has no primary key, you can do this with a simple query:

SELECT SCHEMA_NAME(schema_id) AS [schema name], 
    name AS [table name]
FROM sys.tables
WHERE OBJECTPROPERTY(object_id, 'TableHasPrimaryKey') = 0
ORDER BY [schema name], [table name]

SELECT SCHEMA_NAME(schema_id) AS [schema name],

name AS [table name]

FROM sys.tables

WHERE OBJECTPROPERTY(object_id, 'TableHasPrimaryKey') = 0

ORDER BY [schema name], [table name]

December 28, 2016January 11, 2017

SQL Server Service Accounts

When you install SQL Server, you need to choose accounts for SQL Server services. Here are some usefull links that can help you to make a correct decision:

As overall installation manual you can use Jonathan Kehayias’ guide described in his post SQL Server Installation Checklist.

December 3, 2016December 3, 2016

Pagination And Total Number Of Rows From One SELECT

If you have a business requirement to implement a pagination in user interface, you could use OFFSET/FETCH or ROW_NUMBER() to retrieve rows from SQL Server. But when you need to show the total number of rows that can be retrieved from database, it becomes harder.

You can use two queries – one for counting the total number, second for extracting the page, but this is not a good idea. Why? First, you’ll have negative impact on performance because you need 2 queries, second, you can get different results cause data could change between these two queries.

Luckily, there are some solutions to do this in one query. Because SQL Server could not return rows and assign a value in one query, we need to put the total number as an additional column into a resultset.

1. Using COUNT(*) OVER()

DECLARE
    @PageSize INT = 10, 
    @PageNum  INT = 5,
    @TotalRows INT;

SELECT 
    [name], object_id, 
    TotalRows = COUNT(*) OVER()
FROM sys.all_objects
--WHERE [name] LIKE 'fn_%'
ORDER BY [name]
OFFSET (@PageNum - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

DECLARE

@PageSize INT = 10,

@PageNum INT = 5,

@TotalRows INT;

SELECT

[name], object_id,

TotalRows = COUNT(*) OVER()

FROM sys.all_objects

--WHERE [name] LIKE 'fn_%'

ORDER BY [name]

OFFSET (@PageNum - 1) * @PageSize ROWS

FETCH NEXT @PageSize ROWS ONLY;

It’s a very simple query to write. As for performance, I’ll compare all solutions later.

2. Using CTE to count all records

WITH Data_CTE 
AS
(
    SELECT [name], object_id
    FROM sys.all_objects
    --WHERE [name] LIKE 'fn_%'
), 
Count_CTE 
AS 
(
    SELECT COUNT(*) AS TotalRows FROM Data_CTE
)
SELECT *
FROM Data_CTE
CROSS JOIN Count_CTE
ORDER BY [name]
OFFSET (@PageNum - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

WITH Data_CTE

(

SELECT [name], object_id

FROM sys.all_objects

--WHERE [name] LIKE 'fn_%'

Count_CTE

(

SELECT COUNT(*) AS TotalRows FROM Data_CTE

)

SELECT *

FROM Data_CTE

CROSS JOIN Count_CTE

ORDER BY [name]

OFFSET (@PageNum - 1) * @PageSize ROWS

FETCH NEXT @PageSize ROWS ONLY;

3. Extracting keys to temporary table

The idea is to extract primary keys to temporary table or CTE, get a page of rows, then join it with the original table. We need to use ROW_NUMBER() to assign ordinal number to every row in order to get a page. There are 2 ways to implement pagination.

3a. Extracting keys and filtering row numbers in WHERE

WITH Data_CTE
AS
(
    SELECT object_id,
        ROW_NUMBER() OVER (
            ORDER BY [name]
            ) AS row_num
    FROM sys.all_objects
    --WHERE [name] LIKE 'fn_%'
),
Count_CTE 
AS 
(
    SELECT COUNT(*) AS TotalRows FROM Data_CTE
)
SELECT src.object_id, src.[name], Count_CTE.TotalRows
FROM sys.all_objects src
JOIN Data_CTE t ON src.object_id = t.object_id
CROSS JOIN Count_CTE
WHERE (@PageNum - 1) * @PageSize < t.row_num
    AND t.row_num <= @PageNum * @PageSize
ORDER BY t.row_num;

WITH Data_CTE

(

SELECT object_id,

ROW_NUMBER() OVER (

ORDER BY [name]

) AS row_num

FROM sys.all_objects

--WHERE [name] LIKE 'fn_%'

Count_CTE

(

SELECT COUNT(*) AS TotalRows FROM Data_CTE

)

SELECT src.object_id, src.[name], Count_CTE.TotalRows

FROM sys.all_objects src

JOIN Data_CTE t ON src.object_id = t.object_id

CROSS JOIN Count_CTE

WHERE (@PageNum - 1) * @PageSize < t.row_num

AND t.row_num <= @PageNum * @PageSize

ORDER BY t.row_num;

3b. Extracting keys and using OFFSET/FETCH

WITH Data_CTE
AS
(
    SELECT object_id,
        ROW_NUMBER() OVER (
            ORDER BY [name]
            ) AS row_num
    FROM sys.all_objects
    --WHERE [name] LIKE 'fn_%'
),
Count_CTE 
AS 
(
    SELECT COUNT(*) AS TotalRows FROM Data_CTE
)
SELECT src.object_id, src.[name], Count_CTE.TotalRows
FROM sys.all_objects src
JOIN Data_CTE t ON src.object_id = t.object_id
CROSS JOIN Count_CTE
ORDER BY t.row_num
OFFSET (@PageNum - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

WITH Data_CTE

(

SELECT object_id,

ROW_NUMBER() OVER (

ORDER BY [name]

) AS row_num

FROM sys.all_objects

--WHERE [name] LIKE 'fn_%'

Count_CTE

(

SELECT COUNT(*) AS TotalRows FROM Data_CTE

)

SELECT src.object_id, src.[name], Count_CTE.TotalRows

FROM sys.all_objects src

JOIN Data_CTE t ON src.object_id = t.object_id

CROSS JOIN Count_CTE

ORDER BY t.row_num

OFFSET (@PageNum - 1) * @PageSize ROWS

FETCH NEXT @PageSize ROWS ONLY;

Now I’ll compare these queries for IO statistics and query optimizer costs.

		1. Using COUNT(*) OVER()	2. Using CTE to count all records	3a. Extracting keys and filtering row numbers in WHERE	3b. Extracting keys and using OFFSET/FETCH
Select all rows	IO statistics	Table ‘Worktable’. Scan count 3, logical reads 4728… Table ‘sysschobjs’. Scan count 1, logical reads 25	Table ‘sysschobjs’. Scan count 2, logical reads 26	Table ‘sysschobjs’. Scan count 2, logical reads 46	Table ‘sysschobjs’. Scan count 2, logical reads 138
	Query cost	15%	11%	25%	50%
Select with WHERE clause	IO statistics	Table ‘Worktable’. Scan count 3, logical reads 169… Table ‘sysschobjs’. Scan count 1, logical reads 4	Table ‘sysschobjs’. Scan count 2, logical reads 8	Table ‘sysschobjs’. Scan count 2, logical reads 28	Table ‘Worktable’. Scan count 0, logical reads 0… Table ‘Workfile’. Scan count 0, logical reads 0… Table ‘sysschobjs’. Scan count 3, logical reads 31
	Query cost	4%	6%	35%	55%

From my viewpoint, the second solution is the best cause it needs the least number of pages to be read and it does not create a table in TempDB.

November 28, 2016December 31, 2017

Monitoring SQL Server Backups With DMV

I have a rather long-running backup that was started via Maintenance plan. So I have no output about the progress of a particular backup. In this case you can query DMV to get the information needed.

SELECT 
    r.session_id, 
    r.command, 
    a.text AS Query, 
    CAST(r.percent_complete AS decimal(6,2)) AS [Percent Complete],
    CONVERT(varchar(20), r.start_time, 20) AS [Start Time],
    CONVERT(varchar(20), DATEADD(second, r.estimated_completion_time/1000, GETDATE()), 20) [Estimated Complete Time]
FROM sys.dm_exec_requests r 
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
WHERE r.command in ('BACKUP DATABASE', 'RESTORE DATABASE')
ORDER BY [Start Time]

SELECT

r.session_id,

r.command,

a.text AS Query,

CAST(r.percent_complete AS decimal(6,2)) AS [Percent Complete],

CONVERT(varchar(20), r.start_time, 20) AS [Start Time],

CONVERT(varchar(20), DATEADD(second, r.estimated_completion_time/1000, GETDATE()), 20) [Estimated Complete Time]

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a

WHERE r.command in ('BACKUP DATABASE', 'RESTORE DATABASE')

ORDER BY [Start Time]

The output would be like this.

As you see, one backup is running while the others are pending.

November 22, 2016August 14, 2017

Code First. Executing Stored Procedures With Table-Valued Parameter

Today I need to retrieve data from Entity Framework using stored procedure, and passing input values from .NET collection through the table-valued parameter. As for technologies that will be used here, I wrote in these posts:

Using Table-Valued Parameters in SQL Server Stored Procedures – how to create SQL Server type and stored procedure;
Calling SQL Server Stored Procedure With Table-Valued Parameter in C# – calling stored procedure via ADO.NET;
Code First. Executing Stored Procedures – calling stored procedures via DbContext.Database.SqlClient().

As stated in MSDN article Table-Valued Parameters, table-valued parameter can be populated from:

DataTable
DbDataReader
IEnumerable

If you have a .NET collection with parameters, you could transform it to IEnumerable. Here is a good article on C# Corner Passing Table Valued Parameter to Stored Procedure: Part 2 that shows how to make this conversion.

Let’s start to write the code.

1. Create SQL Server type UserType to store the row of a table-valued parameter

CREATE TYPE [dbo].[SqlUserType] AS TABLE
(
    UserId int NOT NULL,
    AccessTypeId smallint NOT NULL
)

CREATE TYPE [dbo].[SqlUserType] AS TABLE

(

UserId int NOT NULL,

AccessTypeId smallint NOT NULL

)

2. Create stored procedure with a readonly parameter of type UserType

CREATE PROCEDURE [dbo].[ListUsers]
    @UserList [dbo].[SqlUserType] READONLY
AS
BEGIN
    ...

END

CREATE PROCEDURE [dbo].[ListUsers]

@UserList [dbo].[SqlUserType] READONLY

BEGIN

...

END

3. Create .NET class UserEntry to store the row of a table-valued parameter

public class UserEntry
{
    public int UserId { get; set; }
    public short AccessTypeId { get; set; }
}

public class UserEntry

{

public int UserId { get; set; }

public short AccessTypeId { get; set; }

}

4. Create .NET class UserParamCollection that must be inherited from the preceding .NET class and also implements IEnumerable

public class UserParamCollection : List<UserEntry>, IEnumerable<SqlDataRecord>
{
    IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
    {
        var sqlRow = new SqlDataRecord(
              new SqlMetaData("UserId", SqlDbType.Int),
              new SqlMetaData("AccessTypeId", SqlDbType.SmallInt));

        foreach (UserEntry entry in this)
        {
            sqlRow.SetInt32(0, entry.UserId);
            sqlRow.SetInt16(1, entry.AccessTypeId);

            yield return sqlRow;
        }
    }
}

public class UserParamCollection : List<UserEntry>, IEnumerable<SqlDataRecord>

{

IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()

{

var sqlRow = new SqlDataRecord(

new SqlMetaData("UserId", SqlDbType.Int),

new SqlMetaData("AccessTypeId", SqlDbType.SmallInt));

foreach (UserEntry entry in this)

{

sqlRow.SetInt32(0, entry.UserId);

sqlRow.SetInt16(1, entry.AccessTypeId);

yield return sqlRow;

}

5. Convert List<UserEnty> to a newly created class UserParamCollection

UserParamCollection sqlUserList = new UserParamCollection();
sqlUserList.AddRange(userList);

1 2	UserParamCollection sqlUserList = new UserParamCollection(); sqlUserList.AddRange(userList);

6. Create SqlParameter

SqlParameter paramUserList = new SqlParameter("@UserList", SqlDbType.Structured)
{
    TypeName = "SqlUserType",
    Value = sqlUserList
};

SqlParameter paramUserList = new SqlParameter("@UserList", SqlDbType.Structured)

{

TypeName = "SqlUserType",

Value = sqlUserList

};

Here you need to specify parameter type SqlDbType.Structured. TypeName must be the same as SQL Server type defined in step 1.

7. Call stored procedure and pass the preceding SqlParameter

var resultSet = context.Database.SqlQuery<T>("[dbo].[ListUsers] @UserList", paramUserList);
var resultCollection = resultSet as IEnumerable<T>;

1 2	var resultSet = context.Database.SqlQuery<T>("[dbo].[ListUsers] @UserList", paramUserList); var resultCollection = resultSet as IEnumerable<T>;

You must place the parameter name of the SQL Server stored procedure right after the name of the stored procedure as following: “[dbo].[ListUsers] @UserList”. If you omit it, you’ll get an error:
Procedure or function 'ListUsers' expects parameter '@UserList', which was not supplied.

Because SqlQuery() returns a collection of type System.Data.Entity.Infrastructure.DbRawSqlQuery, it would better to cast it to IEnumerable to conform to application interfaces.

November 18, 2016November 22, 2016

Querying Entity Framework. Part 22 – Multiple Fields In Join, GroupBy And OrderBy

Now I want to summarize the info about how to use multiple fields in LINQ queries. You know that LINQ to Entities allows to make joins, grouping and sorting based on single field or multiple fields.

Generally speaking, when the query operation is based on a single field, you use this single field alone. When the operation is based on multiple fields, you would use a construct as the following:
new { field1, field2... }

Here are examples of how to do it in C#.

Join

var query41 = ctx.Clients
    .Join(ctx.Phones,
        client => new { Id = client.Id, client.CountryCode },
        phone => new { Id = phone.ClientId, phone.CountryCode },
        (client, phone) => new { client.Name, phone.Number, client.CountryCode }
    );

var query41 = ctx.Clients

.Join(ctx.Phones,

client => new { Id = client.Id, client.CountryCode },

phone => new { Id = phone.ClientId, phone.CountryCode },

(client, phone) => new { client.Name, phone.Number, client.CountryCode }

);

There are identical key selectors in inner and outer parts of the join.

GroupBy

var query221 = ctx.Clients
    .GroupBy(c => new
    {
        FirstLetter = c.Name.Substring(0, 1),
        CountryCode = c.CountryCode
    });

var query221 = ctx.Clients

.GroupBy(c => new

{

FirstLetter = c.Name.Substring(0, 1),

CountryCode = c.CountryCode

});

OrderBy

var query222 = ctx.Clients
    .OrderBy(c => new { c.CountryCode, c.Name });

1 2	var query222 = ctx.Clients .OrderBy(c => new { c.CountryCode, c.Name });

In this case EF will sort the rows by both fields in ascending order. The last row in T-SQL will look like this:
ORDER BY [Extent1].[CountryCode] ASC, [Extent1].[Name] ASC

If you use OrderByDescending() method, so the output will be sorted by both fields, but in descending order.
In a situation when you need different sorting, you should use ThenBy()/ThenByDescending() for the second and consequent fields.