Querying Linq-to-SQL updated values [pre-SubmitChanges()]

Question

I have a scenario in which I would like to interact with Linq-to-Sql managed data (VS2008/.NET Framework 3.5/Sql Server 2005 Express) that has been updated, but not yet committed back to the database with 'SubmitChanges()'.

I have given a simple test case below in which I'm changing a color property on a simple 'Block' object. I enumerate the table (Blocks 9 & 10 initially have null values for Color), change values, and enumerate the results and all appears to be well. However, there is some weirdness (or gaps in my understanding) going on when I try to query(.Count) the 'Blocks' object as highlighted in two bold output statements below.

The statement LinqtoSqlDBDataContext.Blocks.Count(Block => Block.Color == null) results in a value of '2' ... which is odd as the previous enumeration shows that all Blocks have a color assigned. Is this statement reaching back out to the database (where indeed '2' would be a correct value as nothing yet has been committed)? It would seem so as a db trace confirms the SELECT statement.

The second statement LinqtoSqlDBDataContext.Blocks.ToList().Count(Block => Block.Color == null) returns the correct value of '0', but I am unsure as to why the 'ToList()' would affect the results so. As you'll note this statement also generates a completely different SQL statement (with no WHERE clause to boot).

If the SubmitChanges() line is uncommented LinqtoSqlDBDataContext.Blocks.Count(Block => Block.Color == null) does return the correct value. However, I can't always predict the volume of my changes and would like to tread as lightly as I can with respect to how many times I go back to the database with updates.

So I guess my question is ... is it safe/valid/recommended to interact with pre-submitted data in light of what I'm seeing here. I have searched far and wide and haven't found much (if any) regarding this scenario. Is there something blindingly obviously that I'm missing? Any and all thoughts gratefully received.

Summary: Enumeration of pre-submitted Linq-to-Sql data seemingly returns different results than does querying of pre-submitted Linq-to-Sql data.

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

Code

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();
        }
    }
}

Output

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

I'm thinking it has to be a bug in the way they build the list. It looks like the difference between collecting and iterating over the delta (updated) object list vs. the original members. Or maybe it checks the "hasChanged" flag and defaults to the original or vise verse. — Brad Christie

TJB TJB · Accepted Answer · 2010-11-23T18:25:50

You may have to serialize the results before you do the modification if you want the values to be persisted. E.G.

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

That way the objects will preserve their state, though this may cause problems / require you to do the submit changes differently.

Querying Linq-to-SQL updated values [pre-SubmitChanges()]

DDL

Code

Output

1 Answers