查询 Linq-to-SQL 更新值 [pre-SubmitChanges()]

Question

我有一个场景，我想与已更新但尚未使用“SubmitChanges()”提交回数据库的 Linq-to-Sql 托管数据（VS2008/.NET Framework 3.5/Sql Server 2005 Express）进行交互'。

我在下面给出了一个简单的测试用例，其中我更改了一个简单的“块”对象的颜色属性。我枚举该表（块 9 和 10 最初的颜色为空值），更改值，然后枚举结果，一切似乎都很好。然而，当我尝试查询（.Count）“Blocks”对象时，会出现一些奇怪的情况（或我的理解上的差距），如下面两个粗体输出语句中突出显示的那样。

语句 LinqtoSqlDBDataContext.Blocks.Count(Block => Block.Color == null) 结果为“2”...这很奇怪，因为前面的枚举显示所有 Block 都有一个指定的颜色。该语句是否返回到数据库（其中“2”实际上是一个正确的值，因为尚未提交任何内容）？看起来是这样，因为数据库跟踪确认了 SELECT 语句。

第二个语句 LinqtoSqlDBDataContext.Blocks.ToList().Count(Block => Block.Color == null) 返回正确的值“0”，但我不确定为什么“ToList” ()' 会影响结果。您将注意到，此语句还会生成完全不同的 SQL 语句（没有 WHERE 子句启动）。

如果 SubmitChanges() 行未注释，LinqtoSqlDBDataContext.Blocks.Count(Block => Block.Color == null) 确实会返回正确的值。然而，我不能总是预测我的更改量，并且希望尽可能谨慎地对待我返回数据库进行更新的次数。

所以我想我的问题是……根据我在这里看到的内容，与预先提交的数据进行交互是否安全/有效/推荐。我进行了广泛的搜索，但没有发现太多关于这种情况的信息（如果有的话）。我明显错过了什么吗？任何和所有的想法都感激不尽。

摘要：预先提交的 Linq-to-Sql 数据的枚举似乎返回与预先提交的 Linq-to-Sql 数据的查询不同的结果。

DDL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Block](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Color] [varchar](50) NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

代码

using System;
using System.Collections.Generic;co
using System.Linq;
using System.Text;
using System.Data.Linq;

namespace LinqToSql1
{
    class Program
    {
        static void Main(string[] args)
        {

            LinqtoSqlDBDataContext LinqtoSqlDBDataContext = new LinqtoSqlDBDataContext();
            LinqtoSqlDBDataContext.Log = Console.Out;


            Console.WriteLine("Enumerating Blocks\n==================");
            foreach(Block Block in LinqtoSqlDBDataContext.Blocks)
            {
                Console.WriteLine("Block {0} Color is '{1}'", Block.ID, Block.Color);
            }
            Console.WriteLine();


            Console.WriteLine("Updating Blocks\n===============");
            foreach(Block Block in LinqtoSqlDBDataContext.Blocks.Where(b => b.Color == null))
            {
                Console.WriteLine("Block {0} Color was '{1}'", Block.ID, Block.Color);
                Block.Color = "Gray";
                Console.WriteLine("Block {0} Color is '{1}'", Block.ID, Block.Color);
            }
            Console.WriteLine();


            Console.WriteLine("Enumerating Blocks\n==================");
            foreach(Block Block in LinqtoSqlDBDataContext.Blocks)
            {
                Console.WriteLine("Block {0} Color is '{1}'", Block.ID, Block.Color);
            }
            Console.WriteLine();


            // Console.WriteLine("Submitting Changes\n==================");
            // LinqtoSqlDBDataContext.SubmitChanges();


            Console.WriteLine("Counting Blocks\n===============");
            Console.WriteLine("LinqtoSqlDBDataContext.Blocks.Count(Block => Block.Color == null) is {0}\n", LinqtoSqlDBDataContext.Blocks.Count(Block => Block.Color == null));
            Console.WriteLine("LinqtoSqlDBDataContext.Blocks.ToList().Count(Block => Block.Color == null) is {0}\n", LinqtoSqlDBDataContext.Blocks.ToList().Count(Block => Block.Color == null));


            LinqtoSqlDBDataContext.Dispose();


            Console.WriteLine("Press [Enter] to continue");
            Console.ReadLine();
        }
    }
}

输出

Enumerating Blocks
==================
SELECT [t0].[ID], [t0].[Color]
FROM [dbo].[Block] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

Block 1 Color is 'Red'
Block 2 Color is 'Green'
Block 3 Color is 'Yellow'
Block 4 Color is 'Black'
Block 5 Color is 'Orange'
Block 6 Color is 'Purple'
Block 7 Color is 'Blue'
Block 8 Color is 'White'
Block 9 Color is ''
Block 10 Color is ''

Updating Blocks
===============
SELECT [t0].[ID], [t0].[Color]
FROM [dbo].[Block] AS [t0]
WHERE [t0].[Color] IS NULL
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

Block 9 Color was ''
Block 9 Color is 'Gray'
Block 10 Color was ''
Block 10 Color is 'Gray'

Enumerating Blocks
==================
SELECT [t0].[ID], [t0].[Color]
FROM [dbo].[Block] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

Block 1 Color is 'Red'
Block 2 Color is 'Green'
Block 3 Color is 'Yellow'
Block 4 Color is 'Black'
Block 5 Color is 'Orange'
Block 6 Color is 'Purple'
Block 7 Color is 'Blue'
Block 8 Color is 'White'
Block 9 Color is 'Gray'
Block 10 Color is 'Gray'

Counting Blocks
===============
SELECT COUNT(*) AS [value]
FROM [dbo].[Block] AS [t0]
WHERE [t0].[Color] IS NULL
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

LinqtoSqlDBDataContext.Blocks.Count(Block => Block.Color == null) is 2

SELECT [t0].[ID], [t0].[Color]
FROM [dbo].[Block] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

LinqtoSqlDBDataContext.Blocks.ToList().Count(Block => Block.Color == null) is 0

Press [Enter] to continue

Answer 1

如果您希望保留这些值，则可能必须在进行修改之前序列化结果。 EG

Console.WriteLine("Updating Blocks\n===============");
var blocksToUpdate = LinqtoSqlDBDataContext.Blocks.Where(b => b.Color == null).ToArray();
foreach( var block in blocksToUpdate )
{
 // do something...
}

这样，对象将保留其状态，尽管这可能会导致问题/要求您以不同的方式提交更改。