C# Flatten data by Linq

When I work on the project, I usually run into a problem about flatting data.
Because the original data could in the nested property, I have to get it out.
The following is an example about nested properties, and the requirement is that I must get all OrderDetailNumber.

Finally, I found an easy method that is use SelectMany.

LINQ SelectMany

Projects each element of a sequence to an IEnumerable and flattens the resulting sequences into one sequence.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
using System;
using System.Linq;
using System.Collections.Generic;

namespace FlattenResult
{
internal class Program
{
public class Order
{
public string OrderNumber { get; set; }

public List<OrderDetail> orderDetails { get; set; }

public class OrderDetail
{
public string OrderDetailNumber { get; set; }
}
}

static void Main(string[] args)
{
var orders = new List<Order>
{
new Order
{
OrderNumber = "202305121111",
orderDetails = new List<Order.OrderDetail>
{
new Order.OrderDetail
{
OrderDetailNumber = "OD0001",
},
new Order.OrderDetail
{
OrderDetailNumber = "OD0001",
}
}
},
new Order
{
OrderNumber = "202305121112",
orderDetails = new List<Order.OrderDetail>
{
new Order.OrderDetail
{
OrderDetailNumber = "OD0003",
},
new Order.OrderDetail
{
OrderDetailNumber = "OD0004",
}
}
}
};

var orderDetailNumbers = orders.SelectMany(s => s.orderDetails)
.Select(s => s.OrderDetailNumber)
.ToList();

orderDetailNumbers.ForEach(f =>
{
Console.WriteLine(f);
});
}
}
}

Output:

1
2
3
4
OD0001
OD0001
OD0003
OD0004

C# Use AutoMapper to map data model

When writing data to SQL Server, there is always a process about mapping model.
The process is a transformation, such as A model transforming B model.
If the model contains too many properties, the code could became very long.
So we can use a plugin, AutoMapper, to avoid that.

AutoMapper : https://docs.automapper.org/

The following is an example of implementation about converting PersonModel to PersonDto, which is a list convert.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
using System;
using System.Collections.Generic;

using AutoMapper;

namespace AutoMapExample
{
public class Program
{
public class PersonModel
{
public string PersionalID { get; set; }
public string Name { get; set; }
public int? Age { get; set; }
}
public class PersonDto
{
public int? Id { get; set; }
public string Pid { get; set; }
public string Name { get; set; }
public int? Age { get; set; }
}
public static void Main()
{
var personlist = new List<PersonModel>
{
new PersonModel
{
PersionalID = "E111111111",
Name = "TOM",
Age = 20,
},
new PersonModel
{
PersionalID = "B111111111",
Name = "J.Cole",
Age = 26,
}
};

var config = new MapperConfiguration(cfg =>
{
cfg.CreateMap<PersonModel, PersonDto>()
.ForMember(dest => dest.Pid, opt => opt.MapFrom(src => src.PersionalID))
.ForMember(dest => dest.Id, opt => opt.Ignore());
});

// Check for missing mapped properties.
config.AssertConfigurationIsValid();

var mapper = config.CreateMapper();
var dtoList = mapper.Map<List<PersonModel>, List<PersonDto>>(personlist);

dtoList.ForEach(x =>
{
Console.WriteLine($"id:{x.Id},pid:{x.Pid},name:{x.Name},age:{x.Age}");
});
}
}
}

output

1
2
id:,pid:E111111111,name:TOM,age:20
id:,pid:B111111111,name:J.Cole,age:26

Because id was set to ignore, it’s null when the console prints them.

The following is the mapping of the different name properity. Due to the name of the properity is different so I have to define a rule to map, such as PersionalID => Pid.

1
ForMember(dest => dest.Pid, opt => opt.MapFrom(src => src.PersionalID))

The following is a check for missing mapped properties.

1
config.AssertConfigurationIsValid()

Use C# SemaphoreSlim to lock async functions

When writing an async function or method, I encountered a problem with accessing data one-by-one, such as starting 100000 async APIs, which could lead to bugs.

Below is a problem example. I expect the result to be “Sum:100000”, but the actual result is “Sum:99995” or other abnormal results.

Example 1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
namespace SemaphoreSlimExample;

public class Program
{
private static int _sum;

public static void Main(string[] args)
{
Task.WaitAll(Enumerable
.Range(0, 100000)
.Select(x => Add(1))
.ToArray());
Console.WriteLine($"Sum:{_sum}");
}

private static async Task Add(int num)
{
await Task.Run(() =>
{
_sum += num;
});
}
}

Result:

1
Sum:99995

To fix this problem, I added the SemaphoreSlim to code, and the result always is correct “Sum:100000”.
It helps us lock and limit the asynchronous function’s data to one-by-one.

What is Semaphoreslim:
https://learn.microsoft.com/en-us/dotnet/api/system.threading.semaphoreslim?view=net-7.0

Represents a lightweight alternative to Semaphore that limits the number of threads that can access a resource or pool of resources concurrently.

Example2:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
namespace SemaphoreSlimExample;

public class Program
{
private static int _sum;
private static readonly SemaphoreSlim locker = new SemaphoreSlim(1, 1);

public static void Main(string[] args)
{
Task.WaitAll(Enumerable
.Range(0, 100000)
.Select(x => Add(1))
.ToArray());
Console.WriteLine($"Sum:{_sum}");
}

private static async Task Add(int num)
{
await locker.WaitAsync();

await Task.Run(() =>
{
_sum += num;
});

locker.Release();
}
}

Result:

1
Sum:100000

C# Distinct objects

In the case, I want to remove the duplicate objects of the list.
I use the object key, UserId and Type, to distinguish them in the list.
After I distinct the objects, the count of the list becomes 2.
The numbers of the list are 2023021403 and 2023021401.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
using System;
using System.Collections.Generic;
using System.Linq;

namespace App
{
internal class Program
{
private class OrderModel
{
public int UserId { get; set; }
public int Type { get; set; }
public string Number { get; set; }
}

static void Main(string[] args)
{
var orders = new List<OrderModel>
{
new OrderModel
{
UserId = 1,
Type = 1,
Number = "2023021401"
},
new OrderModel
{
UserId = 1,
Type = 1,
Number = "2023021402"
}
,
new OrderModel
{
UserId = 1,
Type = 2,
Number = "2023021403"
}
};

var distinctList = orders
.GroupBy(x => new { x.Type, x.UserId })
.Select(x => x.FirstOrDefault())
.ToList();

foreach (var o in distinctList)
{
Console.WriteLine($"Number:{o.Number}");
Console.WriteLine($"UserId:{o.UserId}");
Console.WriteLine($"Type:{o.Type}");
Console.WriteLine("===");
}
}
}
}

Output:

1
2
3
4
5
6
7
8
Number:2023021401
UserId:1
Type:1
===
Number:2023021403
UserId:1
Type:2
===

Simplify queries in SQL Server using CTE

If you use queries in SQL, you could involve complex statements that could cause some maintenance or reading problems. You can still do them, but you can choose a new way, CTE.

What is CTE?
The common table expression (CTE) is a powerful construct in SQL that helps simplify a query.

With CTE, you can split the query into different temporary tables and use them.

Example

If I want to create a report about the total amount of the user’s order, I could use CTE to simplify it.

[dbo].[Orders]

1
2
3
4
5
6
OrderNumber	UserId	Amount
2023020300000001 1 500
2023020300000002 3 100
2023020300000003 1 300
2023020300000004 2 400
2023020300000005 1 100

[dbo].[Users]

1
2
3
4
Id	Name	Age
1 CK 30
2 RR 29
3 NC 25

Then I use the CTE to create a temp table and join them.

Result:

1
2
3
4
Id	Name	Age	OrderTotalAmount
1 CK 30 900
2 RR 29 400
3 NC 25 100

SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
USE OrderDB
GO
WITH Order_User_Total_CTE (UserId, OrderTotalAmount)
AS
-- Define the User Order Total CTE query.
(
SELECT UserId, SUM(Amount) AS OrderTotalAmount
FROM Orders
GROUP BY UserId
)
-- Query
SELECT
U.*,
OUTC.OrderTotalAmount
FROM Users U
LEFT JOIN Order_User_Total_CTE OUTC ON U.Id = OUTC.UserId
GO

Now, if I need to create complex reports, summaries or stored procedures , I will use CTE to help me.

Improve SQL speed by adding index in SQL Server

In the case, I have a simple sql about select, which sorts by date, OrderDate. At the same time, there are 5,000,000 records in the table, Orders. Due to the large amount of data, the SQL speed becomes very slow, about 6 seconds.

1
2
3
4
5
6
7
8
SELECT TOP 1000 [Id]
,[CreatedOn]
,[UpdatedOn]
,[IsValid]
,[OrderDate]
,[OrderNumber]
FROM [OrderDB].[dbo].[Orders]
ORDER BY [OrderDate] DESC

After running the sql script, the speed of the result was very slow, about 6 seconds.

So to imporve this problem, I add the index, NONCLUSTERED INDEX.

1
2
3
4
5
USE OrderDB;  
GO
CREATE NONCLUSTERED INDEX [IX_Orders_OrderDate]
ON Orders ([OrderDate]);
GO

After running the script, I found the speed has been improved by index, about 0 seconds, and there’re still 5,000,000 records in the table.

Generate the test data in SQL Server by stored procedure example

In the case, I want to create a simple script about creating the test data of 100000 records in SQL Server stored procedure.

Today, I have a table, Orders, and I want to generate the test data to it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
USE [OrderDB]
GO
/****** Object: Table [dbo].[Orders] Script Date: 2/2/2023 3:14:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CreatedOn] [datetime] NULL,
[UpdatedOn] [datetime] NULL,
[IsValid] [bit] NULL,
[OrderDate] [datetime] NULL,
[OrderNumber] [varchar](50) NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Then I write a simple stored procedure.
You can control the data count you want to generate.

If I want to create the 100000 records, I can do this.

1
2
3
use OrderDB
go
execute dbo.CreateTestData 100000

Below is the complete script, I wrote.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
USE [OrderDB]
GO
/****** Object: StoredProcedure [dbo].[CreateTestData] Script Date: 2/2/2023 2:46:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateTestData] @Number int
AS
BEGIN
DECLARE
@Counter int = 1
WHILE @Counter <= @Number
BEGIN
INSERT INTO Orders (OrderDate,OrderNumber,IsValid,CreatedOn,UpdatedOn)
VALUES
(
DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2000-01-01'),
CONVERT(CHAR(8), FORMAT(GetDate(),'yyyyMMdd')) + right('00000000'+cast(@Counter as varchar(8)),8),
1,
GETDATE(),
GETDATE()
)
SET @Counter= @Counter + 1
END
END

The stored procedure inserts a random date into OrderDate and a sequence number into OrderNumber.

1
2
3
4
5
6
7
Id	CreatedOn	UpdatedOn	IsValid	OrderDate	OrderNumber
1 2023-02-02 15:27:27.763 2023-02-02 15:27:27.763 1 2002-11-30 00:00:00.000 2023020200000001
2 2023-02-02 15:27:27.763 2023-02-02 15:27:27.763 1 2007-04-29 00:00:00.000 2023020200000002
3 2023-02-02 15:27:27.763 2023-02-02 15:27:27.763 1 2002-03-18 00:00:00.000 2023020200000003
4 2023-02-02 15:27:27.763 2023-02-02 15:27:27.763 1 2003-03-27 00:00:00.000 2023020200000004
5 2023-02-02 15:27:27.763 2023-02-02 15:27:27.763 1 2007-11-07 00:00:00.000 2023020200000005
...

簡易後端處理商業邏輯的方式-系統架構篇

在常見的API設計中,透過接收參數到後端服務中,再透過一連串資料操作的邏輯再寫入資料庫或做額外的操作。

這時情況會像是下面這樣:

因為有過多的操作,且這些操作可能在其他的API也會遇到
此時,就可以把他們抽出來成:處理程序(Handler)

透過Handler,把相同的服務可以用的Method或Function整理在一起。

舉個例子:
有支API叫做建立訂單(CreateOrder)
他可能的商業邏輯操作如:建立訂單、修正庫存…等等。
那我們就可以把它抽出來叫做OrderHandler,把複雜的商業邏輯封裝在裡面。

這時如果要使用到建立訂單時就能透過這Handler去幫我們處理。

1
OrderHandler.CreateOrder()

比如API叫做:

1
/Order/CreateOrder

那他的Controller裡面就可以這樣寫

OrderController裡的Action(CreateOrder)就能呼叫OrderHandler.CreateOrder()去協助建立訂單的操作。

這樣做的好處是能集中管理服務的功能,比較不會遇到全都散在Controller的情況。此外,其他Controller要使用到相同的功能,只要呼叫該Handler,而不用整段複製過去。

C#中使用雙問號??取值用法

在C#中可以運用??來判斷前者是否為null,若為null則將後面值遞補上去。
如下,因Amount為空,所以會套用後面的default。
而default就是decimal的預設值0。
因此,在最後會得到amount結果為0。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
namespace Application
{
public class OrderDetail
{
/// <summary>
/// amount
/// </summary>
public decimal? Amount;
}

internal class Program
{
static void Main(string[] args)
{
var orderDetail = new OrderDetail();
var amount = orderDetail.Amount ?? default;
}
}
}

在C#中何時使用問號?去拿資料

在處理C#語言中的物件,常會去拿物件底下子物件的值,常常會遇到錯誤即空物件錯誤,如底下程式碼的amount1變數。因為我們在拿amount1這個值時前面的OrderDetail物件還沒被初始化。
這時就要使用問號”?”去取值避免拋錯,如amount2變數。
資料在處理時總會拿到髒資料,所以先預判,避免出錯。

程式碼如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
namespace Application
{
/// <summary>
/// DataProvider
/// </summary>
public class DataProvider
{
/// <summary>
/// OrderDetail
/// </summary>
public OrderDetail OrderDetail { get; set; }
}
public class OrderDetail
{
/// <summary>
/// amount
/// </summary>
public decimal? Amount;
}

internal class Program
{
static void Main(string[] args)
{
var data = new DataProvider();

// amount1: error
// It will be error. Due to the object, OrderDetail, is null and doesn't initialize.
// var amount1 = data.OrderDetail.Amount ?? default;

// amount2: 0
// we can add the symbol "?" after the null object, and it can avoid being an error.
var amount2 = data.OrderDetail?.Amount ?? default;
}
}
}