Psyfun Psyfun - 22 days ago 10
C# Question

Entity Framework not Lazy Loading FK Collection on Add

I have the following database structure for an Entity Framework (version 5) implementation that has been working for years, but is getting slower and slower because of some kind of circular referencing issues.

[Table("Sensors", Schema = "Ems")]
public class Sensor
{
public Sensor()
{
SensorSamples = new List<SensorSample>() as ICollection<SensorSample>;
}

[Key]
public int Id { get; set; }

[Required, MaxLength(128)]
public string Name { get; set; }

[MaxLength(256)]
public string Description { get; set; }

[MaxLength(128)]
public string Location { get; set; }

[Required]
[MaxLength(15)]
public string IPAddress { get; set; }

[Required]
public int Port { get; set; }

[Required]
public bool Enabled { get; set; }

[Required, ForeignKey("Type")]
public int SensorTypeId { get; set; }

public virtual SensorType Type { get; set; }

[Required, ForeignKey("Network")]
public int SensorNetworkId { get; set; }

public virtual SensorNetwork Network { get; set; }

public virtual ICollection<SensorSample> SensorSamples { get; set; }
}

[Table("SensorSamples", Schema = "Ems")]
public class SensorSample
{
public SensorSample()
{
SampleData = new List<SampleData>() as ICollection<SampleData>;
}

[Key]
public int Id { get; set; }

[Required, ForeignKey("Sensor")]
public int SensorId { get; set; }

public virtual Sensor Sensor { get; set; }

[Required]
public DateTime SampleTime { get; set; }

[Required]
public virtual ICollection<SampleData> SampleData { get; set; }
}

[Table("SampleData", Schema = "Ems")]
public class SampleData
{
public SampleData()
{
}

[Key]
public int Id { get; set; }

[Required, ForeignKey("DataType")]
public int SampleDataTypeId { get; set; }

public virtual SampleDataType DataType { get; set; }

[Required, ForeignKey("Unit")]
public int SampleUnitId { get; set; }

public virtual SampleUnit Unit { get; set; }

[Required, ForeignKey("Sample")]
public int SensorSampleId { get; set; }

public virtual SensorSample Sample { get; set; }

[MaxLength(128)]
public string Value { get; set; }
}


When I add a new
SensorSample
using the following code, it takes forever for the first one to be added because it instantiates a
SensorSample
and adds it to the
Samples
collection on a
Sensor
instance.

Sensor sensor = GetSensor(1);
SensorSample sample = new SensorSample();
sample.SampleTime = d.Timestamp;
sample.SensorId = sensor.Id;
sensor.SensorSamples.Add(sample);


How can I add a sample to
SensorSamples
on
Sensor
without it instantiating the enitire collection of existing
SensorSamples
? I currently have
AutoDetectChangesEnabled
set to false and delay the
DetectChanges
until right before
SaveChanges
. This makes no difference. I have not turned off
LazyLoading
but it doesn't seem to be kicking in as I would expect in this situation. I think all requirements are met for LazyLoading such as having public parameterless constructors. Have I missed something? Any ideas why this is happening? Thanks.

Answer

I have a very good idea as to why this is happening: When EF loads something from the database, it does not return your type, but instead returns a class which derives from yours. If this class has virtual properties from which EF can deduce relationships, then this class implements the virtual properties so that upon access they load the associated objects from storage. Such an operation can become quite long as more and more foreign keys are associated with the instance.

In order to avoid this, just create a sensor sample instance, map between your business and storage models and set the foreign key which associates the sample with the sensor. Once this is done, add the sample to the proper DbSet in the DbContext and save changes.

It would be something along the lines of:

var sample = new SensorSample();

.. Map properties and values from business to storage model

//Map the sensor foreign key for this sample
sample.SensorId = sensor.Id;

context.SensorSamples.Add(sample);
context.SaveChanges();

Sidenote:

Do refrain from using the Lazy virtual collection features in EF unless you know that the resulting set is well bounded.

Comments