I am trying to add data rows to the DB is created using migrations using data in CSV file, but I did not succeeded. The migration is empty. I am 100% sure that the data is loaded from the file, tested it.
Here is what I tried:
public class AppDbContext : DbContext
{
public DbSet<Megye> Megyek { get; set; }
public DbSet<Telepules> Telepulesek { get; set; }
public DbSet<Tanulo> Tanulok { get; set; }
public AppDbContext()
{
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("Server=.\\SQLEXPRESS;Database=DiakokDB;Trusted_Connection=True;TrustServerCertificate=True");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Telepules>()
.HasMany(x => x.Tanulok).WithOne(x => x.SzuletesTelepulese)
.OnDelete(DeleteBehavior.NoAction);
//modelBuilder.ApplyConfiguration(new MegyeConfiguration());
modelBuilder.ApplyConfiguration(new TelepulesConfiguration());
}
}
public abstract class BaseConfiguration
{
protected List<Telepules> telepulesek = new List<Telepules>();
protected Dictionary<int, Megye> megyek = new Dictionary<int, Megye>();
public BaseConfiguration()
{
using FileStream fs = new FileStream("adatok.csv", FileMode.Open, FileAccess.Read, FileShare.None);
using StreamReader sr = new StreamReader(fs, Encoding.UTF8);
//List<Telepules> telepulesek = new List<Telepules>();
Telepules telepules = null;
//Dictionary<int, Megye> megyek = new Dictionary<int, Megye>();
Megye megye = null;
string line = string.Empty;
string[] data = Array.Empty<string>();
sr.ReadLine();
while (!sr.EndOfStream)
{
line = sr.ReadLine();
data = line.Split(",");
megye = new Megye
{
Id = int.Parse(data[3]),
Nev = data[4]
};
telepules = new Telepules
{
Id = int.Parse(data[0]),
Nev = data[5],
Iranyitoszam = int.Parse(data[2]),
MegyeId = int.Parse(data[3])
};
if (!megyek.ContainsKey(megye.Id))
{
megyek.Add(megye.Id, megye);
}
}
}
}
public class MegyeConfiguration : BaseConfiguration, IEntityTypeConfiguration<Megye>
{
public MegyeConfiguration(): base()
{}
public void Configure(EntityTypeBuilder<Megye> builder)
{
builder.ToTable("Megye");
builder.Property(x => x.Id)
.IsRequired(true);
builder.Property(x => x.Nev)
.IsRequired(true);
builder.HasData(megyek.Select(x => x.Value));
}
}
public class TelepulesConfiguration : BaseConfiguration, IEntityTypeConfiguration<Telepules>
{
public TelepulesConfiguration() : base()
{
}
public void Configure(EntityTypeBuilder<Telepules> builder)
{
builder.ToTable("Telepules");
builder.Property(x => x.Iranyitoszam)
.IsRequired(true);
builder.Property(x => x.MegyeId)
.IsRequired(true);
builder.Property(x => x.Nev)
.IsRequired(true);
builder.HasData(telepulesek);
}
}
I need this in a WinForms application. I could call a fucntion from constructor in the MainForm and check if there is any data ... But I am curious is there some similar way?
thnx
CodePudding user response：
There are a number of things I'd change here.
- You're doing input/output (I/O) in a constructor. I wouldn't recommend doing this: constructors should run deterministically, meaning they either succeed or fail, and the ways they do so are consistent across all environments. Constructor I/O makes it impossible to provide such a guarantee, and for your own sanity, you should provide such a guarantee.
- xUnit makes this tractable with the
IAsyncLifetimeinterface: code that needs to perform async initialization is allowed to, and has a well-defined construct for doing so
- xUnit makes this tractable with the
- You're trying to add data to your database in your configuration code. This makes handling things like multiple application instances accessing a shared resource (the database) much harder, and you also need some kind of detection for the fact that the seeding step has already been executed in your startup
- This is fine if the database is always local to a process instance and you never need to worry about restarts
- This is not fine if you have concurrent workers or need to survive restarts
I would recommend decoupling your seeding step from your configuration step: configure and create your database, and seed it afterwards using some logic that can do it in an idempotent fashion. There's no reason why you can't have a workflow similar to:
- Start application
- Configure Dependency Injection (if you're using it)
- This would include things like logging as well
- Run a creation process for your database
- You can do this using DbUp, ef migrations, or similar
- Run your seeding process
- You can plug seeding into a migration process, but in some cases, it's preferable to do them separately
- If you want to run seeding during migrations, then convert your seed data to a SQL script and run that script
What I've outlined isn't the only way to do things (clearly), but in my experience, it's very stable, predictable, and - most importantly - easy to test. The process by which you create and seed your database is something you want to be able to run repeatedly and determine whether or not it completes successfully with all the correct output. If you separate the "create" and "seed" steps, then you can have more complicated logic for the seeding process as well.
One thing you could consider (if you're using SQL Server) is using the SqlBulkCopy class to stream the data in. This would allow you to use a custom data reader for parsing your input and presenting it to the bulk copy API. Another option would be to wrap BCP, though that imposes additional constraints that I doubt you want to take on.
Key takeaways:
- Ensure your constructors are deterministic: given some set of inputs, they succeed or fail the same way every time
- Don't leak resources (files, sockets, etc.) in constructors
- Don't perform I/O-bound work in a constructor
- When additional initialization work is needed, use a well-defined interface that gets called at a well-defined point in program execution
IHostedServiceandIAsyncLifetimeare good examples of this
CodePudding user response：
At the and I created a migration like this
public partial class seed : Migration
{
/// <inheritdoc />
protected override void Up(MigrationBuilder migrationBuilder)
{
List<Telepules> telepulesek = new List<Telepules>();
Dictionary<int, Megye> megyek = new Dictionary<int, Megye>();
using FileStream fs = new FileStream("adatok.csv", FileMode.Open, FileAccess.Read, FileShare.None);
using StreamReader sr = new StreamReader(fs, Encoding.UTF8);
Telepules telepules = null;
Megye megye = null;
string line = string.Empty;
string[] data = Array.Empty<string>();
sr.ReadLine();
while (!sr.EndOfStream)
{
line = sr.ReadLine();
data = line.Split(",");
megye = new Megye
{
Id = int.Parse(data[3]),
Nev = data[4]
};
telepules = new Telepules
{
Id = int.Parse(data[0]),
Nev = data[5],
Iranyitoszam = int.Parse(data[2]),
MegyeId = int.Parse(data[3])
};
if (!megyek.ContainsKey(megye.Id))
{
megyek.Add(megye.Id, megye);
}
telepulesek.Add(telepules);
}
foreach(Megye x in megyek.Select(x => x.Value))
{
migrationBuilder.InsertData(
table: nameof(Megye),
columns: new[] { "Id", "Nev" },
values: new object[] { x.Id, x.Nev });
}
foreach (Telepules x in telepulesek)
{
migrationBuilder.InsertData(
table: nameof(Telepules),
columns: new[] { "Id", "Iranyitoszam", "Nev", "MegyeId" },
values: new object[] { x.Id, x.Iranyitoszam, x.Nev, x.MegyeId });
}
}
/// <inheritdoc />
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("truncate table [Megye]");
migrationBuilder.Sql("truncate table [Telepules]");
}
}
Also in the Up method we could do against igrationBuilder.InsertData( table: nameof(Megye), columns: new[] { "Id", "Nev" }, values: new object[] { x.Id, x.Nev }); do something like this
migrationBuilder.Sql(" do the sql insert statement ");