基于Entity Framework设计图片关联表结构的实现方案咨询

1. 核心实体定义 #

首先定义枚举和实体类，匹配你的数据库设计：

// 关联类型枚举
public enum ConnectionType
{
    Movie,
    Actor,
    Director
}

// Photo实体
public class Photo
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public string Url { get; set; }

    // 导航属性：关联的所有连接记录
    public ICollection<PhotoConnection> Connections { get; set; } = new List<PhotoConnection>();
}

// PhotoConnection实体
public class PhotoConnection
{
    public int Id { get; set; }
    public int PhotoId { get; set; }
    public ConnectionType Type { get; set; }
    public int ObjectId { get; set; }

    // 导航属性：关联的图片
    public Photo Photo { get; set; }
}

2. DbContext配置 #

在DbContext中配置实体关系、枚举转换和索引优化：

public class AppDbContext : DbContext
{
    public DbSet<Movie> Movies { get; set; }
    public DbSet<Actor> Actors { get; set; }
    public DbSet<Director> Directors { get; set; }
    public DbSet<Photo> Photos { get; set; }
    public DbSet<PhotoConnection> PhotoConnections { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("你的数据库连接字符串");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // 将枚举转换为字符串存储（也可选择整数，根据需求调整）
        modelBuilder.Entity<PhotoConnection>()
            .Property(pc => pc.Type)
            .HasConversion<string>();

        // 配置Photo与PhotoConnection的一对多级联删除
        modelBuilder.Entity<Photo>()
            .HasMany(p => p.Connections)
            .WithOne(pc => pc.Photo)
            .HasForeignKey(pc => pc.PhotoId)
            .OnDelete(DeleteBehavior.Cascade);

        // 添加组合索引提升查询性能
        modelBuilder.Entity<PhotoConnection>()
            .HasIndex(pc => new { pc.PhotoId, pc.Type, pc.ObjectId });
        modelBuilder.Entity<PhotoConnection>()
            .HasIndex(pc => new { pc.Type, pc.ObjectId });
    }
}

3. 便捷查询实现 #

封装常用查询方法，满足业务场景需求：

查询指定实体关联的所有图片

// 查询某部电影的所有图片
public async Task<List<Photo>> GetPhotosForMovieAsync(int movieId)
{
    using var context = new AppDbContext();
    return await context.Photos
        .Where(p => p.Connections.Any(pc => pc.Type == ConnectionType.Movie && pc.ObjectId == movieId))
        .Include(p => p.Connections)
        .ToListAsync();
}

// 查询某个演员关联的所有图片
public async Task<List<Photo>> GetPhotosForActorAsync(int actorId)
{
    using var context = new AppDbContext();
    return await context.Photos
        .Where(p => p.Connections.Any(pc => pc.Type == ConnectionType.Actor && pc.ObjectId == actorId))
        .ToListAsync();
}

查询单张图片关联的所有实体

// 辅助类：包装图片及关联的所有实体
public class PhotoWithAssociations
{
    public Photo Photo { get; set; }
    public List<Movie> Movies { get; set; } = new List<Movie>();
    public List<Actor> Actors { get; set; } = new List<Actor>();
    public List<Director> Directors { get; set; } = new List<Director>();
}

// 获取图片及所有关联实体
public async Task<PhotoWithAssociations> GetPhotoWithAllAssociationsAsync(int photoId)
{
    using var context = new AppDbContext();
    var photo = await context.Photos.FindAsync(photoId);
    if (photo == null) return null;

    var connections = await context.PhotoConnections
        .Where(pc => pc.PhotoId == photoId)
        .ToListAsync();

    var movieIds = connections.Where(pc => pc.Type == ConnectionType.Movie).Select(pc => pc.ObjectId).ToList();
    var actorIds = connections.Where(pc => pc.Type == ConnectionType.Actor).Select(pc => pc.ObjectId).ToList();
    var directorIds = connections.Where(pc => pc.Type == ConnectionType.Director).Select(pc => pc.ObjectId).ToList();

    return new PhotoWithAssociations
    {
        Photo = photo,
        Movies = await context.Movies.Where(m => movieIds.Contains(m.Id)).ToListAsync(),
        Actors = await context.Actors.Where(a => actorIds.Contains(a.Id)).ToListAsync(),
        Directors = await context.Directors.Where(d => directorIds.Contains(d.Id)).ToListAsync()
    };
}

4. 新增图片及关联关系 #

封装添加逻辑，支持批量关联多个实体：

public async Task AddPhotoWithAssociationsAsync(Photo photo, List<(ConnectionType Type, int ObjectId)> associations)
{
    using var context = new AppDbContext();
    context.Photos.Add(photo);
    await context.SaveChangesAsync();

    foreach (var (type, objectId) in associations)
    {
        context.PhotoConnections.Add(new PhotoConnection
        {
            PhotoId = photo.Id,
            Type = type,
            ObjectId = objectId
        });
    }
    await context.SaveChangesAsync();
}

// 使用示例
var newPhoto = new Photo
{
    Title = "电影海报",
    Description = "官方宣传海报",
    Url = "https://example.com/poster.jpg"
};

var associations = new List<(ConnectionType, int)>
{
    (ConnectionType.Movie, 1),
    (ConnectionType.Actor, 5),
    (ConnectionType.Director, 3)
};

await AddPhotoWithAssociationsAsync(newPhoto, associations);

内容的提问来源于stack exchange，提问作者GrootStack