当前位置: 动力学知识库 > 问答 > 编程问答 >

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

问题描述:

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?

网友答案:

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()
    }
    );
分享给朋友:
您可能感兴趣的文章:
随机阅读: