从CSV导入数据时如何正确获取对应外键值？

方法一：预加载BookType映射字典（最直接高效） #

在导入Book数据前，先从数据库查询所有已导入的BookType记录，构建文本到GUID的映射字典，后续处理CSV行时直接通过字典匹配外键ID：

// 在你的导入应用服务中
public async Task ImportBooksAsync(Stream csvStream)
{
    // 1. 预加载BookType映射字典
    var bookTypeDict = await _bookTypeRepository
        .GetListAsync()
        .ToDictionaryAsync(bt => bt.Description.ToLower(), bt => bt.Id);

    // 2. 使用CSVHelper读取数据
    using var reader = new StreamReader(csvStream);
    using var csv = new CsvReader(reader, new CsvConfiguration(CultureInfo.InvariantCulture)
    {
        Delimiter = ";"
    });

    var bookRecords = csv.GetRecords<CsvBookDto>().ToList();

    // 3. 转换为Book实体并赋值外键
    var books = bookRecords.Select(record => new Book
    {
        BookDescription = record.Description,
        FK_BookType = bookTypeDict.TryGetValue(record.BookType.ToLower(), out var typeId) 
            ? typeId 
            : throw new InvalidOperationException($"未找到BookType: {record.BookType}")
    }).ToList();

    // 4. 批量插入Book
    await _bookRepository.InsertManyAsync(books);
}

// 定义CSV对应的Dto
public class CsvBookDto
{
    [Name("Book_ID")]
    public int BookId { get; set; }
    [Name("描述")]
    public string Description { get; set; }
    [Name("BookType")]
    public string BookType { get; set; }
}

注意：建议统一大小写（比如转小写）匹配，避免因大小写差异导致匹配失败

方法二：自定义AutoMapper ValueResolver（适配AutoMapper场景） #

如果一定要用AutoMapper处理映射，可以自定义一个ValueResolver，通过ABP的依赖注入获取BookType仓库，实现文本到GUID的转换：

1. 创建自定义ValueResolver：

public class BookTypeTextToIdResolver : IValueResolver<CsvBookDto, Book, Guid>
{
    private readonly IRepository<BookType, Guid> _bookTypeRepository;
    private Dictionary<string, Guid> _cache;

    public BookTypeTextToIdResolver(IRepository<BookType, Guid> bookTypeRepository)
    {
        _bookTypeRepository = bookTypeRepository;
    }

    public async Task<Guid> ResolveAsync(CsvBookDto source, Book destination, Guid destMember, ResolutionContext context)
    {
        // 缓存字典，避免重复查询数据库
        _cache ??= await _bookTypeRepository
            .GetListAsync()
            .ToDictionaryAsync(bt => bt.Description.ToLower(), bt => bt.Id);

        if (_cache.TryGetValue(source.BookType.ToLower(), out var typeId))
        {
            return typeId;
        }
        throw new InvalidOperationException($"未找到BookType: {source.BookType}");
    }

    // 同步Resolve方法（适配AutoMapper同步映射场景）
    public Guid Resolve(CsvBookDto source, Book destination, Guid destMember, ResolutionContext context)
    {
        return ResolveAsync(source, destination, destMember, context).GetAwaiter().GetResult();
    }
}

2. 在AutoMapper配置中注册这个Resolver：

public class BookProfile : Profile
{
    public BookProfile()
    {
        CreateMap<CsvBookDto, Book>()
            .ForMember(dest => dest.FK_BookType, opt => opt.MapFrom<BookTypeTextToIdResolver>());
    }
}

ABP会自动注入ValueResolver的依赖，无需手动实例化

方法三：批量查询匹配（适合大数据量导入） #

如果CSV数据量很大，预加载字典可能占用内存过多，可以先收集所有唯一的BookType文本，批量查询数据库获取映射关系：

public async Task ImportBooksAsync(Stream csvStream)
{
    // 1. 读取CSV并收集所有BookType文本
    using var reader = new StreamReader(csvStream);
    using var csv = new CsvReader(reader, new CsvConfiguration(CultureInfo.InvariantCulture) { Delimiter = ";" });
    var bookRecords = csv.GetRecords<CsvBookDto>().ToList();
    var uniqueBookTypes = bookRecords.Select(r => r.BookType.ToLower()).Distinct().ToList();

    // 2. 批量查询对应的BookType ID
    var bookTypes = await _bookTypeRepository
        .GetListAsync(bt => uniqueBookTypes.Contains(bt.Description.ToLower()));
    var bookTypeDict = bookTypes.ToDictionary(bt => bt.Description.ToLower(), bt => bt.Id);

    // 3. 检查是否有未匹配的BookType
    var missingTypes = uniqueBookTypes.Except(bookTypeDict.Keys).ToList();
    if (missingTypes.Any())
    {
        throw new InvalidOperationException($"以下BookType未找到: {string.Join(", ", missingTypes)}");
    }

    // 4. 转换并批量插入Book
    var books = bookRecords.Select(r => new Book
    {
        BookDescription = r.Description,
        FK_BookType = bookTypeDict[r.BookType.ToLower()]
    }).ToList();
    await _bookRepository.InsertManyAsync(books);
}

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