如何从IEnumerable集合中向数据库添加对象

本文关键字：数据库 添加 对象 集合 IEnumerable | 更新日期: 2023-09-27 18:02:38

我有一个可枚举实体的集合要添加到数据库中，但似乎需要一些转换。有人能给我指个方向吗?

bool InsertDetails(DataTable detailTable, string fileName)
{
    using (SunseapEBTContext context = new SunseapEBTContext())
    {
        if (InsertMaster(fileName))//if creating master record successful
        {                    
            int masterId = GetSPReadingM(m => m.FileName == fileName).SPReadingMasterId; //get MasterID of file uploaded
             var details = detailTable.AsEnumerable().Select(row => new LeasingSPReadingDetailEntity()//new entity from datatable
             {
                //SPReadingId = row.Field<long>("ProductID"),
                        SPReadingMasterId = masterId,
                        BillCycleYear = int.Parse(row.Field<int>("Bill Cycle").ToString().Substring(0, 4)),
                        BillCycleMonth = byte.Parse(row.Field<byte>("Bill Cycle").ToString().Substring(4))
            });
            foreach(IEnumerable<LeasingSPReadingDetailEntity> detail  in details)
            {                       
                context.LeasingSPReadingDetailEntities.AddObject(detail);
            }
            context.SaveChanges();                    
        }
        return true;
    }
}

在foreach循环中抛出异常

CS1503参数1:不能从'System.Collections.Generic '转换。IEnumerable' to 'SunseapEBT.Domain.BillingModule.LeasingContract.Entity.LeasingSPReadingDetailEntity'

LeasingSPReadingDetailEntity类:

public class LeasingSPReadingDetailEntity
{
    public long SPReadingId { get; set; }
    public int SPReadingMasterId { get; set; }
    public int BillCycleYear { get; set; }
    public byte BillCycleMonth { get; set; }
}

更多信息:上传一个包含详细信息的文件，并在一个表中创建一个主记录。文件中的详细信息将添加到一个单独的表中，并使用从第一个表自动生成的masterId。问题是无法将详细信息添加到数据库中。

编辑:我已经查出错误的原因了。错误是由文件的内容引起的。有些行没有输入值，最后一行没有遵循其他行的格式，因为它显示的是行总数。谢谢大家的帮助!

如何从IEnumerable集合中向数据库添加对象

@slawekwin评论就是答案。但我认为有一个更好的解决方案，因为它看起来像你的代码迭代2x:第一次生成新的Enumerable(浪费内存)，第二次添加对象到上下文。

不妨在迭代每一行时直接添加对象。

foreach(var row in detailTable.AsEnumerable())
{                       
    context.LeasingSPReadingDetailEntities.AddObject(
        new LeasingSPReadingDetailEntity()//new entity from datatable
        {
           //SPReadingId = row.Field<long>("ProductID"),
           SPReadingMasterId = masterId,
           BillCycleYear = int.Parse(row.Field<string>("Bill Cycle").Substring(0, 4)),
           BillCycleMonth = byte.Parse(row.Field<string>("Bill Cycle").Substring(4)),
           AccountNumber = row.Field<string>("Account No."),
           PeriodStart = row.Field<DateTime>("Period Start"),
           PeriodEnd = row.Field<DateTime>("Period End"),
           TownCouncil = row.Field<string>("Customer Name"),
           Service = row.Field<string>("Service Type"),
           Adjustment = row.Field<string>("Adjustment"),
           Block = row.Field<string>("Blk"),
           AddressLine1 = row.Field<string>("Adress Line 1"),
           AddressLine2 = row.Field<string>("Adress Line 2"),
           AddressLine3 = row.Field<string>("Postal Code"),
           Usage = row.Field<decimal>("Usage"),
           Rate = row.Field<decimal>("Rate"),
           Amount = row.Field<decimal>("Amount")
        }
    );
}

—EDIT—

我不确定，但我可以猜测"Bill Cycle"字段既不是int也不是byte。因此，您应该将其作为字符串检索，然后将其解析为新对象。这是我修改的部分:

BillCycleYear = int.Parse(row.Field<string>("Bill Cycle").Substring(0, 4)),
BillCycleMonth = byte.Parse(row.Field<string>("Bill Cycle").Substring(4)),

您可以将有问题的foreach循环更改为以下任意一种。我更喜欢第一个，因为它不那么啰嗦。

            foreach(var detail  in details)
            {                       
                context.LeasingSPReadingDetailEntities.AddObject(detail);
            }

或

            foreach(LeasingSPReadingDetailEntity detail  in details)
            {                       
                context.LeasingSPReadingDetailEntities.AddObject(detail);
            }

如果您查看foreach循环的语法，第一个构造variableType是存储在集合中的元素的类型(在您的示例中是LeasingSPReadingDetailEntity)，而不是集合的类型。您执行了后一种操作，这就是为什么您得到无效强制转换错误。

foreach(variableType currentElementBeingIterated in collection){
    //code block to operate on currentElement
  }

将其改为" List "，然后添加项目。

var details = detailTable.AsEnumerable().Select(row => new  LeasingSPReadingDetailEntity()//new entity from datatable
         {
            SPReadingId = row.Field<long>("ProductID"),
            SPReadingMasterId = masterId,
            BillCycleYear = int.Parse(row.Field<int>("Bill Cycle").ToString().Substring(0, 4)),
            BillCycleMonth = byte.Parse(row.Field<byte>("Bill Cycle").ToString().Substring(4)),
        }).ToList();
        foreach(var detail  in details)
        {                       
            context.LeasingSPReadingDetailEntities.Add(detail);
        }

或者更好:

var details = detailTable.AsEnumerable().Select(row => new  LeasingSPReadingDetailEntity()//new entity from datatable
         {
            SPReadingId = row.Field<long>("ProductID"),
            SPReadingMasterId = masterId,
            BillCycleYear = int.Parse(row.Field<int>("Bill Cycle").ToString().Substring(0, 4)),
            BillCycleMonth = byte.Parse(row.Field<byte>("Bill Cycle").ToString().Substring(4)),
        }).ToList();
context.LeasingSPReadingDetailEntities.AddRange(details);
相关文章：