Linq2SQL:仅选择一些列，但仍然能够提交更改 [英] Linq2SQL: Select only some columns, but still able to submit changes

问题描述

我需要更新包含很多行的表中的列.每行中都有一些大的TEXT列，我不需要进行更新.

I need to update a column in a table which contains a lot of rows. Each row has a some large TEXT columns in it, which i do not need for my update.

我正在使用LinqPAD，这大致就是我想做的事情:

I'm using LinqPAD, and this is roughly, what i wanna do:

(from s in Table
where s.FK_ID == null 
select new{s.FK_ID, s.Datum, s.PBNummer}).ToList()

.ForEach(s => s.FK_ID = new Guid(...some new guid here...));

SubmitChanges();

由于匿名类类型的属性是只读的，因此无法编译.

This does not compile, as the properties of an anonymous class type are read-only.

如果我愿意

(from s in Table
where s.FK_ID == null 
select s).ToList()

然后我可以更新和保存，但是所有列均已加载，这需要很长时间，并且会导致内存问题.

then I can update and save, but all columns are loaded, which takes a very long time and causes memory problems.

有没有一种方法可以只加载某些列，但是仍然有一个对象，我可以使用SubmitChanges更新和保存该对象?还是我必须切换到SQL语句?

Is there a way to only load some columns but still have an object that i can update and save using SubmitChanges? Or do i have to switch to SQL statements?

推荐答案

首先，如果数据库中没有主键，则将无法通过Linq-To-Sql进行更新.如果您有主键，但是不知道它是哪一个，则可以通过执行以下操作在Linqpad中找到它

Firstly, if you don't have a primary key in the database, then you wouldn't be able to update via Linq-To-Sql. If you have a primary key, but just don't know which it is, you can find it in Linqpad by doing something like

var table =  (from t in Mapping.GetTables() 
              where t.TableName == "[Table]" select t).SingleOrDefault();

(from dm in table.RowType.DataMembers 
           where dm.DbType != null && dm.IsPrimaryKey 
           select dm.Name)
          .Dump("Primary Key");

了解主键后，您可以执行以下操作(假设主键称为ID)

Once you know the primary key, you can do something like the following, (I'm assuming the primary key is called Id)

var oldList = (from s in Table
          where s.FK_ID == null 
          select new{s.Id , s.FK_ID, s.Datum, s.PBNummer}).ToList() ;

这类似于您的查询，除了我添加了主键

This is similar to your query, except I have added the primary key

foreach(var r in oldList)
{
    Table t = new Table();
    t.Id        = r.Id ;

    Table.Attach(t);
    t.FK_ID   = new Guid(...some new guid here...));
}
SubmitChanges();

这篇关于Linq2SQL:仅选择一些列，但仍然能够提交更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！