C# - Entity Framework code-first - map object into two tables

Question

Assuming I have data model defined like this:

class Geometry
{
   public ICollection<Point> Points { get; set; }
}

class Point
{
   public int X { get; set; }
   public int Y { get; set; }
}

I would like to map it into the following database tables:

GeometriesTable
-- [GeometryId] int, key
-- [GeometryData] <-- a BLOB here!!!

PointsTable
-- [PointId] int, key
-- [GeometryId] int, foreign key
-- [X] int
-- [Y] int

My [GeometryData] BLOB basically consists of Points IDs that make up the geometry and a couple of additional meta-information.

Taking the meta stuff away, those BLOBs would look like this:

[ N (points count) - 4 bytes ]
[ Points[0].Id     - 4 bytes ] 
[ Points[1].Id     - 4 bytes ] 
[ Points[2].Id     - 4 bytes ] 
... 
[ Points[N-1].Id   - 4 bytes ]

(that is (N+1)*4 bytes for each BLOB.)

BLOB is used (instead of a simple one-to-many relation) for a couple of performance reasons. That is something that can not be changed.

I created the following entities to project the model:

class GeometryEntity
{
   [Key]
   [Column("GeometryId")]
   public int Id { get; set; } 

   [Column("GeometryData")]
   public byte[] Data { get; set; }
}

class PointEntity
{
   [Key]
   [Column("PointId")]
   public int Id { get; set; }

   [Column("GeometryId")]
   public int GeometryId { get; set; }

   [Column("X")]
   public int X { get; set; }

   [Column("Y")]
   public int Y { get; set; }
}

Now, provided I have some IEnumerable<Geometry> I would like to be able to populate the tables (a DbSet<GeometryEntity> and a DbSet<PointEntity>). The particular problem is that I don't know how to assign points IDs from the PointsTable to the corresponding points IDs in my geometry BLOBs.

Any ideas how a mapping like this could be resolved?

Answer 1

Now, provided I have some IEnumerable<Geometry> I would like to be able to populate the tables (a DbSet<GeometryEntity> and a DbSet<PointEntity>).

Ok, let's define

IEnumerable<Geometry> ProvidedGeometries =
    new Geometry[]
    {
        new Geometry()
        {
            Points = new PointEntity[]
    {
        new PointEntity() { GeometryId=1, X=1, Y=1, Id = 1},
        new PointEntity() { GeometryId=1, X=1, Y=2, Id = 2},
        new PointEntity() { GeometryId=1, X=2, Y=1, Id = 3},
        new PointEntity() { GeometryId=1, X=2, Y=2, Id = 4}
    }
        },
    new Geometry()
        {
            Points = new PointEntity[]
    {
        new PointEntity() { GeometryId=2, X=1, Y=1, Id = 5},
        new PointEntity() { GeometryId=2, X=1, Y=2, Id = 6}
    }
        }
    };

I think you can populate the tables using the Configuration.Seed.

with the following pseudo code

protected override void Seed(YourContext context)
{
    context.GeometryEntities.AddOrUpdate(
        // see the following Linq part
        );

    context.PointEntities.AddOrUpdate(
        // see the following Linq part
        );
}

or by a standard update with SaveChanges

using (var ctx = new YourContext())
{
   ctx.PointEntities.AddRange(Points);       //see below
   ctx.GeometryEntites.AddRange(Geometries); // " "
   ctx.SaveChanges();
}

The particular problem is that I don't know how to assign points IDs from the PointsTable to the corresponding points IDs in my geometry BLOBs.

Besides the specific EF implementation of your context, I think that the following code could give you an idea of the Linq part.

IEnumerable<PointEntity> Points =ProvidedGeometries
                    .SelectMany(g => g.Points);


var geometries = Points.GroupBy(
    p => p.GeometryId,
    p => BitConverter.GetBytes(p.Id),
    (id, points) =>
    new GeometryEntity()
    {
        Id = id,
        Data = 
        BitConverter.GetBytes(points.Count()).Concat(
            points.Aggregate( (a,b) => a.Concat(b).ToArray() )
        ).ToArray()
    }
    );