I'm facing a situation where an entity in my database has a foreign key with a null value.
Upon expanding via JavaScript or Including in EF, I noticed that the rows containing the null foreign key are omitted.
Here's the SQL setup:
CREATE TABLE Entity
(
Id BIGINT NOT NULL PRIMARY KEY
);
CREATE TABLE EntityType
(
Id BIGINT IDENTITY NOT NULL PRIMARY KEY,
EntityId BIGINT NULL REFERENCES Entity(Id)
);
INSERT INTO Entity(Id) VALUES (0);
INSERT INTO Entity(Id) VALUES (1);
INSERT INTO EntityType(EntityId) VALUES (0);
INSERT INTO EntityType(EntityId) VALUES (1);
INSERT INTO EntityType(EntityId) VALUES (NULL);
And here is the C# code:
public class Entity
{
public long Id { get; set; }
}
public class EntityType
{
public long Id { get; set; }
public long EntityId { get; set; }
public Entity Entity { get; set; }
}
public class EntityMap : EntityTypeConfiguration<Entity>
{
public EntityMap()
{
HasKey(t => t.Id);
}
}
public class EntityTypeMap : EntityTypeConfiguration<EntityType>
{
public EntityTypeMap()
{
HasKey(t => t.Id);
HasRequired(t => t.Entity)
.WithMany()
.HasForeignKey(t => t.EntityId);
}
}
[BreezeController]
public class EntityController : ApiController
{
private readonly EFContextProvider<EntityContext> _contextProvider =
new EFContextProvider<EntityContext>();
[HttpGet]
public IQueryable<Entity> Entities()
{
return _contextProvider.Context.Entities;
}
[HttpGet]
public IQueryable<EntityType> EntityType()
{
return _contextProvider.Context.EntityTypes;
}
}
Additionally, this is the JavaScript snippet:
angular.module('App').factory('EntityTypeService', function(serviceBase) {
function getAll {
return serviceBase.manager.executeQuery(
serviceBase.query.
from('EntityTypes').
expand('Entity')
).to$q();
}
return {
getAll: getAll
};
});
angular.module('App').controller('HomeCtrl', function($scope, EntityTypeService) {
EntityTypeService.getAll().then(function(data) {
$scope.entityTypes = data.results;
});
});
While inspecting $scope.entityTypes, only rows with a non-null EntityId appear to be present.