Entity Framework 6 one-to-many with a reference to last record

Question

Entity Framework 6 one-to-many with a reference to last record

c# entity-framework entity-framework-6

Question

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?

Luiz Bicalho

View more on Stack Overflow

Luiz Bicalho · Accepted Answer · 7/28/2018 5:48:32 PM

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.

Entity Framework 6 one-to-many with a reference to last record

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related