I have two entities in my model, and they have a one to many relationship like this
public class Analysis : Entity
{
public virtual IList<Process> Processes{ get; set; }
}
public class Process: Entity
{
public virtual Analysis Analysis { get; set; }
}
This works fine, but to optimize my code and limit my model I'd like to expose only the last process in my Analysis, like this:
public class Analysis : Entity
{
public virtual Process Process { get; set; }
}
public class Process: Entity
{
public virtual Analysis Analysis { get; set; }
}
But I don't want the Process property to be a column in my Analysis table, instead of this, I want to load this property with the last Process for this Analysis Id.
How do I implement this in the ModelBuider?
I inspired in Slava Utesinov idea and created a new idea for more performance, his idea would load all processes in the analysis.
first i created a sql function to return the last process
CREATE FUNCTION UDFGetProcess( @Id int ) RETURNS int AS
BEGIN
declare @rint
select top 1 @r = id from Process p where p.analysis_id = @id order by CreationDate desc
return @r
END
then I changed the class like this
public class Analysis : Entity
{
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public virtual Process Process { get; set; }
}
and changed the modelcontext
modelBuilder.Entity<Process>().HasRequired(x => x.Analysis).WithMany().WillCascadeOnDelete(false);
Added the migration and changed the creation of the field to this
ALTER TABLE dbo.Analisys ADD Process_Id AS dbo.UDFGetProcess(id)
this way the return is what I intended and the performance is the same.