How to query a database when its tables get created at run time
asp.net-core c# entity-framework-core
Question
I have successfully found a way to create a database and tables at runtime, the problem is that I don't know how to query that database given that the tables are created by overriding the OnModelCreating method.
Here is my code for the DbContext:
public class DataDbContext : DbContext
{
public string ConnectionString { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(ConnectionString);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
MyClassBuilder MCB = new MyClassBuilder("Student");
var myclass = MCB.CreateObject(new string[3] { "ID", "Name", "Address" },
new Type[3] { typeof(int), typeof(string), typeof(string) });
Type type = myclass.GetType();
var entityMethod = typeof(ModelBuilder).GetMethod("Entity", new Type[] { });
if (type.IsClass)
{
entityMethod.MakeGenericMethod(type)
.Invoke(modelBuilder, new object[] { });
}
base.OnModelCreating(modelBuilder);
}
}
The first three lines in OnModelCreating are creating a class at runtime with
the specific properties [id , name, address].
After executing
{
dataDbContext.Database.EnsureCreated();
}
the database gets created with a table named Student containing three columns [id , name, address] as expected. The problem now as I said is I don't know how to query that database
Any ideas?
Thanks
Accepted Answer
Sorry phyzia1996 but I can not do this with the DbContext class. The DbSet wants a concrete class and getting around that is not only beyond me beyond me but very complicated. A deep dive into both Reflection and the DbContext class. If I needed to work with dynamic classes against a database I would use something like this. Most of the code below can be extended using Reflection, the query string could be built at runtime by scanning the properties of the dynamic class, etc.
class ReadStudents
{
public string ConnectionString = @"Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True;";
public List<dynamic> GetAllStudents()
{
dynamic student_rec = new ExpandoObject();
List<dynamic> rtn_list = new List<dynamic>();
using (SqlConnection sql_con = new SqlConnection(ConnectionString))
{
using (SqlCommand sql_cmd = new SqlCommand())
{
sql_con.Open();
sql_cmd.Connection = sql_con;
sql_cmd.CommandText = "Select ID, Name, Address from Student";
SqlDataReader sql_rdr = sql_cmd.ExecuteReader();
while ( sql_rdr.Read() )
{
student_rec.Id = sql_rdr[0].ToString();
student_rec.Name = sql_rdr[1].ToString();
student_rec.Address = sql_rdr[2].ToString();
rtn_list.Add(student_rec);
}
}
}
return rtn_list;
}
}
Anyways, sorry I couldn't help but I did enjoy the R&D.
Popular Answer
Update: This doesn't work, the 'object' is defined as singleton, it does not implement any kind IEnumerable and so you can only access a single DbContext ENTITY at a time. It can insert a single row at a time but not much else. I will continue to research this until I find a solution or someone else can provide a proper solution.
For those who are wondering about the MyClassBuilder thing, you can find that here on StackOverflow How use Dynamic created class in List<T>. Kudos to whoever wrote that it is some very cool stuff.
This is still a work in progress but it should help and I am still working on it. For now 1. Define the dynamic class as an 'object' in the DataDbContext class. 2. Create the dynamic class in the constructor of the DataDbContext. 3. Reference the dynamic class, via the 'object' in the OnModelCreating override The new DataDbContext class will look like this
public class DataDbContext : DbContext
{
public string ConnectionString = @"Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True;";
public object myclass; // define the class outside of the OnModelCreating override
public DataDbContext()
{ // create the class in the consrtuctor
MyClassBuilder MCB = new MyClassBuilder("Student");
var type_array = new Type[3] { typeof(int), typeof(string), typeof(string) };
myclass = MCB.CreateObject(new string[3] { "ID", "Name", "Address" }, type_array);
Type type = myclass.GetType();
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{ optionsBuilder.UseSqlServer(ConnectionString); }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
Type type = myclass.GetType();
var entityMethod = typeof(ModelBuilder).GetMethod("Entity", new Type[] { });
if (type.IsClass)
{ entityMethod.MakeGenericMethod(type).Invoke(modelBuilder, new object[] { }); }
base.OnModelCreating(modelBuilder);
}
}
I am still working the 'gets', I will update this answer when I have that, but the 'sets' will use reflection against the dynamic class to get the properties and set them. The DbContext will then save the changes similar to if you had used a DbSet command. The 'AddStudent' method would look like this. UPDATE I skipped the ID field because it was set as an Identity Insert, you can still grab the property but don't set it or Sql Server will complain about the non-null value for Identity Insert column. My 'get' method will pull that, soon as I figure out how.
public static void AddStudent(string student_name, string student_address)
{
using (DataDbContext dataDbContext = new DataDbContext())
{
dataDbContext.Database.EnsureCreated();
PropertyInfo[] fields = dataDbContext.myclass.GetType().GetProperties();
PropertyInfo prop_name = fields[1];
PropertyInfo prop_addr = fields[2];
prop_name.SetValue(dataDbContext.myclass, student_name);
prop_addr.SetValue(dataDbContext.myclass, student_address);
dataDbContext.Add(dataDbContext.myclass);
dataDbContext.SaveChanges();
}
}
As I mentioned above I am still working on fleshing this out but I wanted to say thank you, this was a very cool question, quite challenging :)
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow