I'm writing unit tests for a c#>Sql Server application. MS recommends using SQLite when not using a production db for testing (https://learn.microsoft.com/en-us/ef/core/testing/testing-without-the-database#inmemory-provider). I have SQLite setup and I'm trying to fill the db with test data. I exported a sample of production data into JSON format and saving that with the test project so I can update the test data fairly easily. The code to setup the SQLite context I took from the MS article
There's no line numbers to ref but this block
var memoryData = new ReqestDataInMemory();
var data = memoryData.AllRepositoryRequests();
gets the json and returns the complex object structure as an IEnumberable. I then try and save it to the db context.
public class TestDbContext
{
public SqliteConnection _connection {get; set;}
public DbContextOptions<TaxonomyContext> _contextOptions { get; set;}
public TestDbContext()
{
// Create and open a connection. This creates the SQLite in-memory database, which will persist until the connection is closed
// at the end of the test (see Dispose below).
_connection = new SqliteConnection("Filename=:memory:");
_connection.Open();
// These options will be used by the context instances in this test suite, including the connection opened above.
_contextOptions = new DbContextOptionsBuilder<TaxonomyContext>()
.UseSqlite(_connection)
.Options;
// Create the schema and seed some data
using var context = new TaxonomyContext(_contextOptions);
if (context.Database.EnsureCreated())
{
using var viewCommand = context.Database.GetDbConnection().CreateCommand();
viewCommand.CommandText = @"
CREATE VIEW AllRequests AS
SELECT Url
FROM Request";
viewCommand.ExecuteNonQuery();
}
var memoryData = new ReqestDataInMemory();
var data = memoryData.AllRepositoryRequests();
context.AddRange(data);
context.SaveChanges();
}
The AddRange() throw the error
System.InvalidOperationException : The instance of entity type 'RequestType' cannot be tracked because another instance with the same key value for {'RequestTypeId'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached.
which makes sense if its trying to save the lookup table over and over. Hence my problem.
Here is the object I'm working with. RequestType is where I'm running into a blocker and its a simple lookup table RequestType.Id and RequestType.Name. Status is a one-to-many-lookup table. All have FK relationships back to Request
The ChangeDetail object is details about the request. It has unique IDs and a FK to the Request.
I tried NULLing both RequestType and Statuses but it s
public class Request
{
public int RequestId { get; set; }
/// <summary>changed from "navarchar(max)" to "text" for sqlite testing db </summary>
[Required]
[MinLength(50)]
[Column(TypeName = "text")]
public string Justification { get; set; } = string.Empty;
[Required]
public int SubmitUser { get; set; }
[Required]
public DateTime SubmitDate { get; set; }
public int? ModifyUser { get; set; }
public DateTime? ModifyDate { get; set; }
public virtual IEnumerable<ChangeDetail> ChangeDetail { get; set; } = new List<ChangeDetail>();
public virtual IEnumerable<Status> Statuses { get; set; } = new List<Status>();
public virtual RequestType RequestType { get; set; } = new RequestType();
}
My queries work great so I think all my relationships are solid. Just in case it helps, here are the relationships.
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Request>()
.HasOne(x => x.RequestType)
.WithMany(x => x.Requests);
modelBuilder.Entity<Request>()
.HasMany(x => x.ChangeDetail)
.WithOne(x => x.Request);
modelBuilder.Entity<Request>()
.HasMany(x => x.Statuses)
.WithMany(x => x.Requests);
modelBuilder.Entity<Status>()
.HasOne(x => x.RequestStatusType)
.WithMany(x => x.Statuses);
}
To solve the problem I tried this: LINQ query to return distinct field values from list of objects
I thought this showed promise, using Entity.Unmodified but couldn't get it to work Save detached entity in Entity Framework 6
This talks about the FK, which I have setup but maybe its not referencing them on save? How do I stop Entity Framework from trying to save/insert child objects?
I appreciate any suggestions you may have.
