Linq to SQL FirstOrDefault does not execute the SQL query

Question

I have a Windows Forms. I instanciate my data context in the form's constructor. I want to display information about article when I click on a button. Data can be updated externally. The problem is that, in this case, data is not refreshed.

I already know that re-instanciate my data context each time is an option. ToList would force to get the refreshed data. But I really thought that FirstOrDefault() would execute SQL each time...

I did some log and this is not the case. Is there an option over there to force the SQL execution? Is there a sort of cache here?

using (var log = new System.IO.StreamWriter("D:\\LOG.TXT", true))
{
    DB.Log = log;

    var article = DB.T_Articles.FirstOrDefault(x => x.NumArticle == 11);
    MessageBox.Show(article.Description);

    var articles = DB.T_Articles.ToList();

    log.WriteLine("----------------------------------");

    DB.Log = null;
}

Here is the log file. We can see that the SQL behind the FirstOrDefault method is here. We can see that it disappears the second time

SELECT TOP (1) [t0].[NumArticle], [t0].[EAN13], [t0].[Minimum], [t0].[Emplacement], [t0].[Fournisseur], [t0].[Qte], [t0].[Description], [t0].[Photo], [t0].[Prix], [t0].[IsEmail]
FROM [dbo].[T_Article] AS [t0]
WHERE [t0].[NumArticle] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [11]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.7.2046.0

SELECT [t0].[NumArticle], [t0].[EAN13], [t0].[Minimum], [t0].[Emplacement], [t0].[Fournisseur], [t0].[Qte], [t0].[Description], [t0].[Photo], [t0].[Prix], [t0].[IsEmail]
FROM [dbo].[T_Article] AS [t0]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.7.2046.0

----------------------------------
SELECT [t0].[NumArticle], [t0].[EAN13], [t0].[Minimum], [t0].[Emplacement], [t0].[Fournisseur], [t0].[Qte], [t0].[Description], [t0].[Photo], [t0].[Prix], [t0].[IsEmail]
FROM [dbo].[T_Article] AS [t0]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.7.2046.0

----------------------------------

What tool are you using to generate that log file based on the executing SQL? If you run a SQL Trace do you see the TOP 1 query executing multiple times? — mjwills, Jul 11 '17 at 12:54
@mjwills I use a dbml file I'm not sure it would help. The TOP(1) is only present the first time — pjaaar, Jul 11 '17 at 13:01
I did some Googling on this, and apparently Linq2Sql is smart enough to check its cache for the primary key that you're looking for. So in this case, it assumes nothing has changed and doesn't bother with the query. You can use a `Refresh()` to get around this. See [here](https://stackoverflow.com/questions/2664910/linq-to-sql-does-not-update-when-data-has-changed-in-database). — Jon B, Jul 11 '17 at 13:03
@JonB well that sucks :) Smart enough but in this case it display inconsistent data... — pjaaar, Jul 11 '17 at 13:13

score 3 · Answer 1 · answered Jul 11 '17 at 13:02

3

You may try to refresh your context each time before FirstOrDefault():

Context.Refresh(RefreshMode.StoreWins, [table_name]);

answered Jul 11 '17 at 13:02

VDN

717
4
12

score 1 · Answer 2 · answered Jul 11 '17 at 13:36

1

You can reload the found entry , read more about cache busting

  var article = DB.T_Articles.FirstOrDefault(x => x.NumArticle == 11);
  DB.Entry(article).Reload();

answered Jul 11 '17 at 13:36

William Han

78
1
7

Linq to SQL FirstOrDefault does not execute the SQL query

2 Answers2