C# – Linq to SQL Strange Caching

clinqlinq-to-sql

I've got a strange (correct me if I'm wrong) bug/feature with Linq to SQL that's puzzling me and trying to understand what's happening.

So I've taken the whole process procedurally in the hope of trying to find a resolution and have been unable to fix so far.

Here's a repro of what is happening:

Record change made to database table "ETickets". Changed column "availability" from 9
to 8 (see below)
Change made to database http://www.composeandshoot.com/soverflow/changemadetodb.jpg
Refresh page which has an ASP repeater databound to the ETickets table. This still displayed "9".
Looked at quickwatch on the databinding to see what value was being returned from database (see below):
quickwatch http://www.composeandshoot.com/soverflow/linqtosqlquickwatch.jpg
So I then took a look at the query running on the SQL server. Fired up SQL Server Profiler and got the following (see below):
SQL profiler http://www.composeandshoot.com/soverflow/linqquery.jpg
Ran the query

exec sp_executesql N'SELECT [t0].[id], [t0].[eventid], [t0].[availability], [t0].[cost]
FROM [dbo].[ETickets] AS [t0]
WHERE [t0].[id] = @p0',N'@p0 uniqueidentifier',@p0='2642C0EF-C0C5-4881-9AC8-53C023B444D2'
SQL Server returns "8" as the availability! Yet the databinding still shows "9". I've not enabled any SQL Cache Dependency.

Please could someone point me in the right direction here? Thanks.

Best Answer

What is the life-cycle of the data-context? Is it the same data-context, or a new one each time? (it should probably be the latter).

If you re-use a data-context, it is obliged to always give you back the same object when ever it seems the same identity. So if it has previously given you the version with 9, it'll keep giving you back that reference even when the underlying data changes.

At the moment, this still does a round-trip; to avoid the round-trip, you need to use:

var obj = ctx.SomeTable.Single(x=>x.Id == id);

i.e. if it already has the object with Id == id in the local identity cache, it won't perform a databae query at all.

any other construct will do a round trip. There is a fix in 4.0 so that .Where(x=>x.Id == id).Single() will avoid the round-trip, which should have with query syntax, i.e.

var obj = (from x in ctx.SomeTable
           where x.Id == id
           select x).Single();

Related Solutions

.net – Entity Framework vs LINQ to SQL

LINQ to SQL only supports 1 to 1 mapping of database tables, views, sprocs and functions available in Microsoft SQL Server. It's a great API to use for quick data access construction to relatively well designed SQL Server databases. LINQ2SQL was first released with C# 3.0 and .Net Framework 3.5.

LINQ to Entities (ADO.Net Entity Framework) is an ORM (Object Relational Mapper) API which allows for a broad definition of object domain models and their relationships to many different ADO.Net data providers. As such, you can mix and match a number of different database vendors, application servers or protocols to design an aggregated mash-up of objects which are constructed from a variety of tables, sources, services, etc. ADO.Net Framework was released with the .Net Framework 3.5 SP1.

This is a good introductory article on MSDN: Introducing LINQ to Relational Data

C# – LINQ query on a DataTable

You can't query against the DataTable's Rows collection, since DataRowCollection doesn't implement IEnumerable<T>. You need to use the AsEnumerable() extension for DataTable. Like so:

var results = from myRow in myDataTable.AsEnumerable()
where myRow.Field<int>("RowNo") == 1
select myRow;

And as @Keith says, you'll need to add a reference to System.Data.DataSetExtensions

AsEnumerable() returns IEnumerable<DataRow>. If you need to convert IEnumerable<DataRow> to a DataTable, use the CopyToDataTable() extension.

Below is query with Lambda Expression,

var result = myDataTable
    .AsEnumerable()
    .Where(myRow => myRow.Field<int>("RowNo") == 1);

Best Answer

Related Solutions

.net – Entity Framework vs LINQ to SQL

C# – LINQ query on a DataTable

Related Topic