EF Core 3.0 - Convert SQL to LINQ

CSharp · Console

/Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Data;

public class Program
{
	public static void Main()
	{		
		//var jsonFromAPI = "{\"api\":{\"results\":66,\"Players\":[{\"player_id\":10,\"player_name\":\"Gustavo Ferrareis\"},{\"player_id\":20,\"player_name\":\"Player Name With Id 20\"}]}}";
		var jsonFromAPI = "{\"api\":{\"results\":66,\"Players\":[{\"player_id\":10,\"player_name\":\"Gustavo Ferrareis\",\"shots\":{\"total\":13, \"on\":2 }},{\"player_id\":20,\"player_name\":\"Player Name With Id 20\",\"shots\":{\"total\":13, \"on\":2 }}]}}";
        var jObject = JObject.Parse(jsonFromAPI)["api"];
		var players = jObject["Players"].Children();
		var formattedPlayers = players
			.Select(p => string.Format("Player_id: {0} - Player_name: {1}", p["player_id"], p["player_name"]));	     
		var playersDictionary = players.Select(p => new {player_id = p["player_id"], player_name = p["player_name"]})
			.ToDictionary(x => x.player_id, v => v.player_name);
		Console.WriteLine(string.Join(Environment.NewLine, formattedPlayers));
		Console.WriteLine(Environment.NewLine);
		var allPlayerDetails = new List<Dictionary<string, object>>();
		foreach (JObject player in players)
		{
			var playerDictionary = player.Properties()
				.ToDictionary<JProperty, string, object>(property => property.Name, property => property.Value);

			allPlayerDetails.Add(playerDictionary);
		}

		for (var index = 0; index < allPlayerDetails.Count; index++)
		{
			var playerDictionary = allPlayerDetails[index];
			Console.WriteLine(Environment.NewLine);
			Console.WriteLine(string.Format("Printing Player# {0}", index));
			foreach (var d in playerDictionary)
			{
				Console.WriteLine(d.Key + " - " + d.Value);
			}
		}
		
		Console.WriteLine(Environment.NewLine);
		Console.WriteLine("Beginning to convert list to DataTable");
		DataTable dt = new DataTable();

		foreach (var column in allPlayerDetails.SelectMany(p => p.Keys).Select(k => k.Trim()).Distinct())
		{
			dt.Columns.Add(new DataColumn(column));
		}

		foreach (var details in allPlayerDetails)
		{
			var dr = dt.NewRow();
			foreach (DataColumn dc in dt.Columns)
			{
				dr[dc.ColumnName] = details.ContainsKey(dc.ColumnName) ? details[dc.ColumnName] : null;
			}
			dt.Rows.Add(dr);
		}
		Console.WriteLine("Completed converting list to DataTable");
	}
}