I have three tables. Word -> WordForm -> SampleSentence. Each Word has different WordForms and then each form can have one or more SampleSentence
CREATE TABLE [dbo].[Word] (
[WordId] VARCHAR (20) NOT NULL,
[CategoryId] INT DEFAULT ((1)) NOT NULL,
[GroupId] INT DEFAULT ((1)) NOT NULL,
PRIMARY KEY CLUSTERED ([WordId] ASC),
CONSTRAINT [FK_WordWordCategory] FOREIGN KEY ([CategoryId]) REFERENCES [dbo].[WordCategory] ([WordCategoryId]),
CONSTRAINT [FK_WordWordGroup] FOREIGN KEY ([GroupId]) REFERENCES [dbo].[WordGroup] ([WordGroupId])
);
CREATE TABLE [dbo].[WordForm] (
[WordFormId] VARCHAR (20) NOT NULL,
[WordId] VARCHAR (20) NOT NULL,
[Primary] BIT DEFAULT ((0)) NOT NULL,
[PosId] INT NOT NULL,
[Definition] VARCHAR (MAX) NULL,
PRIMARY KEY CLUSTERED ([WordFormId] ASC),
CONSTRAINT [FK_WordFormPos] FOREIGN KEY ([PosId]) REFERENCES [dbo].[Pos] ([PosId]),
CONSTRAINT [FK_WordFormWord] FOREIGN KEY ([WordId]) REFERENCES [dbo].[Word] ([WordId])
);
CREATE TABLE [dbo].[SampleSentence] (
[SampleSentenceId] INT IDENTITY (1, 1) NOT NULL,
[WordFormId] VARCHAR (20) NOT NULL,
[Text] VARCHAR (MAX) NOT NULL,
CONSTRAINT [PK_SampleSentence] PRIMARY KEY CLUSTERED ([SampleSentenceId] ASC),
CONSTRAINT [FK_SampleSentenceWordForm] FOREIGN KEY ([WordFormId]) REFERENCES [dbo].[WordForm] ([WordFormId])
);
I am taking the data from these tables to a front-end client and this then modifies the data and adds or deletes WordForms and SampleSentences.
I then bring the data back to the server.
Is there some way that Entity Framework can check to see changes in the object that I bring back to the server and make changes to the database or do I have to do some form of comparison where I check the before and after of the Word, WordForm and Sample Sentence objects?
For reference here are the C# objects I'm using:
public class Word
{
public string WordId { get; set; } // WordId (Primary key) (length: 20)
public int CategoryId { get; set; } // CategoryId
public int GroupId { get; set; } // GroupId
// Reverse navigation
public virtual System.Collections.Generic.ICollection<WordForm> WordForms { get; set; } // WordForm.FK_WordFormWord
// Foreign keys
public virtual WordCategory WordCategory { get; set; } // FK_WordWordCategory
public virtual WordGroup WordGroup { get; set; } // FK_WordWordGroup
public Word()
{
CategoryId = 1;
GroupId = 1;
WordForms = new System.Collections.Generic.List<WordForm>();
}
}
public class WordForm
{
public string WordFormId { get; set; } // WordFormId (Primary key) (length: 20)
public string WordId { get; set; } // WordId (length: 20)
public bool Primary { get; set; } // Primary
public int PosId { get; set; } // PosId
public string Definition { get; set; } // Definition
// Reverse navigation
public virtual System.Collections.Generic.ICollection<SampleSentence> SampleSentences { get; set; } // SampleSentence.FK_SampleSentenceWordForm
// Foreign keys
public virtual Pos Pos { get; set; } // FK_WordFormPos
public virtual Word Word { get; set; } // FK_WordFormWord
public WordForm()
{
Primary = false;
SampleSentences = new System.Collections.Generic.List<SampleSentence>();
}
}
public class SampleSentence : AuditableTable
{
public int SampleSentenceId { get; set; } // SampleSentenceId (Primary key)
public string WordFormId { get; set; } // WordFormId (length: 20)
public string Text { get; set; } // Text
// Foreign keys
public virtual WordForm WordForm { get; set; } // FK_SampleSentenceWordForm
}
Here is what I have been able to come up with so far but this does not include checking for the SampleSentence and I am not sure how to do that:
public async Task<IHttpActionResult> Put([FromBody]Word word)
{
var oldObj = db.WordForms
.Where(w => w.WordId == word.WordId)
.AsNoTracking()
.ToList();
var newObj = word.WordForms.ToList();
var upd = newObj.Where(n => oldObj.Any(o =>
(o.WordFormId == n.WordFormId) && (o.PosId != n.PosId || !o.Definition.Equals(n.Definition) )))
.ToList();
var add = newObj.Where(n => oldObj.All(o => o.WordFormId != n.WordFormId))
.ToList();
var del = oldObj.Where(o => newObj.All(n => n.WordFormId != o.WordFormId))
.ToList();
foreach (var wordForm in upd)
{
db.WordForms.Attach(wordForm);
db.Entry(wordForm).State = EntityState.Modified;
}
foreach (var wordForm in add)
{
db.WordForms.Add(wordForm);
}
foreach (var wordForm in del)
{
db.WordForms.Attach(wordForm);
db.WordForms.Remove(wordForm);
}
db.Words.Attach(word);
db.Entry(word).State = EntityState.Modified;
await db.SaveChangesAsync(User, DateTime.UtcNow);
return Ok(word);
}
