(NET) NET (2024)

C# EF code first Backend API test project

In my life I have write millions string of .NET code, of course most of that code I made on VB.NET, because this is my liked language. And I used any existing .NET features in my VB.NET program, for example:



However, I made a number of projects exactly on C#, for example:


About EF code first technologies, I don't very like this library, because :



SO, what I want to said? If I prefer more advanced RAPID language for programming, and if I prefer more advanced data access method, if I have strong understanding of Microsoft final goal to bind .NET and MS SQL with EF - this is not mean that I can not be stupid and this not mean than I can not use more stupid and restricted technology. And in that example I demonstrate how I can be stupid and can looks as full newbie and how I can working with goal to increase sales for MS SQL server:


So, in current test project I have this database (see details description how I made this DB - C# EF code first. How to create DB context and deploy DB in Net Core 8):



In EF Code first this is description of this database. 


   1:  using Microsoft.EntityFrameworkCore;
   2:  using Microsoft.Extensions.Configuration;
   3:  using System;
   4:  using System.Collections.Generic;
   5:  using System.Text;
   6:  using Microsoft.EntityFrameworkCore;
   7:  using TT.Lib.Entities;
   8:  using System.IO;
   9:  using Microsoft.Extensions.Configuration.Json;
  10:   
  11:  namespace TT.Lib
  12:  {
  13:      public class TTDbContext : DbContext
  14:      {
  15:          string connectionString;
  16:          public DbSet<Product> Products { get; set; }
  17:          public DbSet<Property> Properties { get; set; }
  18:          public DbSet<ProductProperty> ProductProperties { get; set; }
  19:          public TTDbContext(IConfiguration configuration)  
  20:          {
  21:              this.connectionString = configuration.GetConnectionString(typeof(TTDbContext).Name);
  22:          }
  23:          public TTDbContext()
  24:          {
  25:          }
  26:          public string ConnectionString
  27:          {
  28:              get
  29:              {
  30:                  return this.connectionString;
  31:              }
  32:          }
  33:          protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  34:          {
  35:              bool manual = false;
  36:              if (manual)
  37:              {
  38:                  IConfigurationRoot configuration = new ConfigurationBuilder()
  39:                     .SetBasePath(Directory.GetCurrentDirectory())
  40:                     .AddJsonFile("appsettings.json")
  41:                     .Build();
  42:                  var connectionString = "Server=localhost;Initial Catalog=tt;Integrated Security=True;TrustServerCertificate=True;";
  43:                  optionsBuilder.UseSqlServer(connectionString);
  44:              }
  45:              else
  46:              {
  47:                  optionsBuilder.UseSqlServer(this.ConnectionString, sqlOptions => sqlOptions
  48:                                 .EnableRetryOnFailure(
  49:                                     maxRetryCount: 10,
  50:                                     maxRetryDelay: TimeSpan.FromSeconds(30),
  51:                                     errorNumbersToAdd: null));
  52:              }
  53:              base.OnConfiguring(optionsBuilder);
  54:          }
  55:      }
  56:  }

   1:  namespace TT.Lib.Entities
   2:  {
   3:      public class EntityBase
   4:      {
   5:          public const string DefaultSchema = "tt";
   6:      }
   7:   
   8:      public class Base : IId
   9:      {
  10:          public int Id { get; set; }
  11:      }
  12:   
  13:      public class BaseName : Base
  14:      { 
  15:          public string Name { get; set; }    
  16:      }
  17:   
  18:      public interface IId
  19:      { 
  20:          public int Id { get; set; }
  21:      }
  22:  }

   1:  using System.Collections.Generic;
   2:  namespace TT.Lib.Entities
   3:  {
   4:      public class Brand : BaseName, IId
   5:      {
   6:          public ICollection<Product> Products { get; set; }
   7:      }
   8:  }

   1:  using System.Collections.Generic;
   2:  using System.ComponentModel.DataAnnotations;
   3:  namespace TT.Lib.Entities
   4:  {
   5:      public class Product : BaseName, IId
   6:      {
   7:          public ICollection<ProductProperty> Properties { get; set; }
   8:          [MaxLength(63)]
   9:          public string Key { get; set; }
  10:          public int BrandId { get; set; }
  11:          public virtual Brand Brand { get; set; }
  12:      }
  13:  }

   1:  namespace TT.Lib.Entities
   2:  {
   3:      public class ProductProperty : Base
   4:      {
   5:          public string Value { get; set; }
   6:          public virtual Product Product { get; set; }
   7:          public int ProductId { get; set; }
   8:          public virtual Property Property { get; set; }
   9:          public int PropertyId { get; set; }
  10:      }
  11:  }

   1:  namespace TT.Lib.Entities
   2:  {
   3:      public class Property : BaseName
   4:      {
   5:          public int ParentId { get; set; }   
   6:      }
   7:  }

What main disadvantage of EF CODE first comparing to clear access with SQL query? We need huge code support instead clear SQL query, for example in this test project this was a thousands code lines additionally to DB definitions above. If fact DB access is separate library with thousands of code lines:



Than we need to add this repository as scoped services:



Other possible troubles is deployment, I spend whole working day while finally my attempt to deployment (even to Ms SQL!) was successful:



Potential customer take me data and my goal was a create data from Database to frontend with special form what customer required:



So, this is my solution Solution uploaded to Github.


   1:  using Microsoft.AspNetCore.Mvc;
   2:  using Microsoft.Identity.Client;
   3:  using Newtonsoft.Json;
   4:  using Newtonsoft.Json.Linq;
   5:  using System;
   6:  using System.Collections;
   7:  using System.Collections.Generic;
   8:  using System.Diagnostics;
   9:  using System.Dynamic;
  10:  using System.Linq;
  11:  using TT.Lib;
  12:   
  13:  namespace TT.Api.Controllers
  14:  {
  15:      [Route("[controller]")]
  16:      [ApiController]
  17:      public class Export : ControllerBase
  18:      {
  19:   
  20:          private readonly TTDbContext _db;
  21:   
  22:          public Export(TTDbContext db)
  23:          {
  24:              _db = db;
  25:          }
  26:   
  27:          // GET Codes
  28:          [HttpGet("/Codes")]
  29:          public IActionResult Codes(string code)
  30:          {
  31:              return new OkObjectResult(_db.Products.Select(x => x.Key).ToList());
  32:   
  33:          }
  34:   
  35:          // GET /Export/Raw/SM2L
  36:          [HttpGet("Raw/{code}")]
  37:          public IActionResult ProductDump(string code)
  38:          {
  39:              var prod1 = _db.Products.Where(x => x.Key.Equals(code)).Select(x => new Title(x.Id, x.Name, x.Key, x.BrandId, x.Brand.Name)).ToList();
  40:   
  41:              Record3 ret1 = new Record3();
  42:              ret1.title = prod1.FirstOrDefault();
  43:              ret1.staticProp = new List<OneStatic>();
  44:   
  45:              foreach (var x in prod1)
  46:              {
  47:                  ret1.staticProp.AddRange(_db.ProductProperties.Where(y => y.ProductId.Equals(x.Id)).Select(z => new OneStatic(z.Id, z.Value, z.PropertyId, new List<OneDynamic>())).ToList());
  48:   
  49:              }
  50:              foreach (OneStatic one in ret1.staticProp)
  51:              {
  52:                  int startId = one.propertyId;
  53:                  do
  54:                  {
  55:                      var ret2 = GetDynProp(startId);
  56:                      if (ret2.Count == 0)
  57:                      {
  58:                          break;
  59:                      }
  60:                      else
  61:                      {
  62:                          one.dynProp.Add(ret2[0]);
  63:                          startId = ret2[0].ParentId;
  64:                      }
  65:                  } while (true);
  66:   
  67:              }
  68:              return new OkObjectResult(ret1);
  69:          }
  70:          private List<OneDynamic> GetDynProp(int startId)
  71:          {
  72:              return _db.Properties.Where(x => x.Id.Equals(startId)).Select(x => new OneDynamic(x.Id, x.ParentId, x.Name)).ToList();
  73:          }
  74:   
  75:   
  76:          // GET ExportRaw/SM2L
  77:          [HttpGet("{code}")]
  78:          public IActionResult ReverseTreeAndMergeNodes(string code)
  79:          {
  80:              var ret1 =(OkObjectResult)ProductDump(code);
  81:              var rawTree = (Record3)ret1.Value;
  82:              var out1 = new JObject();
  83:              out1.Add(new JProperty("id", rawTree.title.Key));
  84:              out1.Add(new JProperty("name", rawTree.title.Name));
  85:              // reversy tree
  86:              var arr1 = new JArray();
  87:              foreach (var oneStatic in rawTree.staticProp)
  88:              {
  89:                  Debug.WriteLine($"val:{oneStatic.value}");
  90:                  Boolean first = true;
  91:                  var save = new JObject();
  92:   
  93:                  foreach (var oneDyn in oneStatic.dynProp.OrderByDescending(x=> x.id))
  94:                  {
  95:                      Debug.WriteLine($"oneDyn:{oneDyn}");
  96:                      if (first)
  97:                      {
  98:                          var dyn = new JObject();
  99:                          dyn.Add(new JProperty(oneDyn.dynName, oneStatic.value));
 100:                          Debug.WriteLine($"dyn:{dyn}");
 101:                          save = dyn;
 102:                          first = false;
 103:                      }
 104:                      else {
 105:                          var dyn = new JObject();
 106:                          dyn.Add(new JProperty(oneDyn.dynName, save));
 107:                          save = dyn;
 108:                          Debug.WriteLine($"dyn:{dyn}");
 109:                      }
 110:   
 111:                  }
 112:                  arr1.Add(save);
 113:                  Debug.WriteLine($"sum:{arr1}");
 114:              }
 115:              // and merge nodes with the same name
 116:              var sum1 = new JObject();
 117:              foreach (var one in arr1)
 118:              {
 119:                  Debug.WriteLine($"one:{one}");
 120:                  sum1.Merge(one);
 121:              }
 122:              out1.Add(new JProperty("prop", sum1));
 123:              return new OkObjectResult(out1);
 124:          }
 125:      }
 126:      record Title(int Id, string Name, string Key, int BrandId, string BrandName);
 127:      struct OneStatic
 128:      {
 129:          public int ProductProperties_Id;
 130:          public string value;
 131:          public int propertyId;
 132:          public List<OneDynamic> dynProp = new List<OneDynamic>();
 133:          private int id;
 134:          private List<OneDynamic> oneDynamics;
 135:   
 136:          public OneStatic(int id, string value, int propertyId, List<OneDynamic> oneDynamics) : this()
 137:          {
 138:              this.id = id;
 139:              this.value = value;
 140:              this.propertyId = propertyId;
 141:              this.oneDynamics = oneDynamics;
 142:          }
 143:      };
 144:      record OneDynamic(int id, int ParentId, string dynName);
 145:      struct Record3
 146:      {
 147:          public Title title;
 148:          public List<OneStatic> staticProp;
 149:      }
 150:  }




Comments ( )
Link to this page: http://www.vb-net.com/EF-Test/Index.htm
< THANKS ME>