如何使用Linq to SQL添加到列表中？

I have a table in the database that I'm retrieving using LINQ to SQL, and as a part of my processing I want to add to this list, then update the database with the new items + any changes I've made.

我在数据库中有一个表,我正在使用LINQ to SQL检索,作为我处理的一部分,我想添加到此列表,然后使用新项目+我更改的数据库更新数据库。

What I thought I could do was this:

我以为我能做的就是:

var list = (from item in db.Table
            select item).ToList();

[do processing where I modify items & add to the list]

list = list.Distinct();

db.SubmitChanges();

What happens is that the modifications happed (ie. SQL updates) but any new items I add to the list don't get added.

发生的变化是修改发生(即SQL更新),但我添加到列表中的任何新项目都没有添加。

Obviously I'm doing this wrong, what is the correct way to modify & add to a list of DB entities, then commit all the updates & inserts?

显然我做错了,修改和添加到DB实体列表的正确方法是什么,然后提交所有更新和插入?

4 个解决方案

#1

The List is meaningless. It's just happens to hold objects that the DataContext knows about. We need to make sure that the DataContext knows about the new ones. The important thing is that they don't have to be complete when we alert the DataContext to them:

名单毫无意义。它只是容纳DataContext知道的对象。我们需要确保DataContext知道新的。重要的是,当我们向它们发出DataContext警报时,它们不必完整:

Item item;
if (needNewOne)
{
     item = new Item();
     db.InsertOnSubmit(item);
}
else
{
     item = list[i];
}
///  build new or modify existing item
///   :
db.SubmitChanges();

#2

You can create an extension method for it:

您可以为它创建一个扩展方法:

static void EnsureInsertedOnSubmit<TEntity>( this Table<TEntity>  table
                                            ,IEnumerable<TEntity> entities)
 { foreach(var entity in entities) 
    { if  (   table.GetModifiedMembers(entity).Length == 0     
           && table.GetOriginalEntityState(entity) == default(TEntity))
       { table.InsertOnSubmit(entity);
       }
    }
 }

And then you can do this:

然后你可以这样做:

 var list = db.Table1.ToList();
 list.Add(new Item());
 db.Table1.EnsureInsertedOnSubmit(list);
 db.SubmitChanges();

#3

You have to add the new items via InsertOnSubmit.

您必须通过InsertOnSubmit添加新项目。

#4

You've got to tell LINQ to insert the new row on submit, using InsertOnSubmit:

您必须使用InsertOnSubmit告诉LINQ在提交时插入新行:

db.InsertOnSubmit(newrow);
db.SubmitChanges();

You do not need a new DataContext, you can use the one you are using for updates.

您不需要新的DataContext,您可以使用您用于更新的那个。

Same for delete DeleteOnSubmit(row). Modifications will be tracked, though.

删除DeleteOnSubmit(行)也是如此。但是会跟踪修改。

#1