LINQ to SQL SubmitChanges not working FILESTREAM Table

Question

I have some code that inserts data into some tables (one table has a FILESTREAM column) and then calls SubmitChanges after it is done.

db.Log = new System.IO.StreamWriter(@"c:\windows\temp\linq.log") { AutoFlush = true };
db.SubmitChanges(ConflictMode.FailOnFirstConflict);

I have referenced the following links but they appear to not be my issue:

http://stackoverflow.com/questions/3441241/linq-to-sql-submitchanges-does-not-work

http://stackoverflow.com/questions/206532/linq-not-updating-on-submitchanges

Each table has a primary key.

I have 14 tables and I see 14 IsPrimaryKey=true contained in the linq.designer.cs

Though I do see that "AutoSync=AutoSync.OnInsert" is missing from the Files table but I think that is because IDENTITY is not set (using guid as primary key).

When I check the database, nothing was inserted into it. The linq.log shows:

INSERT INTO [dbo].[Files]([fileID], [FileContents], [dteCreated], [dteLastModified], [txtDesc], [mimeID], [txtName], [txtTitle], [dteDeleted])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8)
-- @p0: Input UniqueIdentifier (Size = -1; Prec = 0; Scale = 0) [92190315-1107-4848-94b3-dd9badf4465b]
-- @p1: Input VarBinary (Size = -1; Prec = 0; Scale = 0) [SqlBinary(24064)]
-- @p2: Input DateTime2 (Size = -1; Prec = 0; Scale = 0) [10/26/2010 1:54:55 PM]
-- @p3: Input DateTime2 (Size = -1; Prec = 0; Scale = 0) [10/26/2010 1:54:55 PM]
-- @p4: Input VarChar (Size = 8000; Prec = 0; Scale = 0) [This is my new file]
-- @p5: Input Int (Size = -1; Prec = 0; Scale = 0) [3]
-- @p6: Input VarChar (Size = 8000; Prec = 0; Scale = 0) [MySecondTestDoc.doc]
-- @p7: Input VarChar (Size = 8000; Prec = 0; Scale = 0) [My Other Test Document]
-- @p8: Input DateTime2 (Size = -1; Prec = 0; Scale = 0) []
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

INSERT INTO [dbo].[UserFiles]([fileID], [userID], [rightsID])
VALUES (@p0, @p1, @p2)

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
-- @p0: Input UniqueIdentifier (Size = -1; Prec = 0; Scale = 0) [92190315-1107-4848-94b3-dd9badf4465b]
-- @p1: Input Int (Size = -1; Prec = 0; Scale = 0) [1]
-- @p2: Input Int (Size = -1; Prec = 0; Scale = 0) [3]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

When I look at Server Profiler it records RPC:Completed exec sp_executesql "Good Insert Statement"

When I run the sql manually through Server Manager Studio, everything works fine. I can do that successfully either by logging in with SA or with the credentials used by the web service.

LINQ SubmitChanges is not throwing an error though. Any reason why it is not working? I have not overloaded any Insert/Update/DeleteFile functions in any partial classes.

Modifying other tables seem to work fine though. I am wondering if it has to do with the FILESTREAM column.

My connection string is:

Data Source=IP;Initial Catalog=MyDB;User ID=myLogin;Password=myPswd

Once I manually put the record into the table (FILESTREAM table), I can read that data just fine.

Lastly, I have FILESTREAM configured for Transact-SQL access and not for file i/o streaming access. From how I understood it, I didn't see a need to have a shared folder to the data location. I wanted everything to go through the database to get to the FILESTREAM data.

Thanks!

FIXED

I have the following code:

public TransactionScope CreateTransactionScope(TransactionScopeOption scope, IsolationLevel level)
{
  var transactionOptions = new TransactionOptions();
  transactionOptions.IsolationLevel = level;
  transactionOptions.Timeout = TimeSpan.MaxValue;
  return new TransactionScope(scope, transactionOptions);
}
public TransactionScope CreateTransactionScope(TransactionScopeOption scope)
{
  return CreateTransactionScope(scope, IsolationLevel.ReadCommitted);
}

and my submitchanges looked like:

using (var ts = db.CreateTransactionScope(TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted))
    {
      db.SubmitChanges(ConflictMode.FailOnFirstConflict);
    }

anyone? anyone? I forgot the complete statement... lol. I was thinking that I might add more in the submitchanges area and might want to rollback so I left the using statement there but never commit my changes... Thanks everyone!

Answer 1

About a month ago I sent an insert query from code to the sql server. And watched absolutely nothing happen. No records were inserted and no errors recorded. Profiler showed the query being submitted. If I ran the query that profiler had, then it worked. Absolutely boggled my mind.

Then, on a lark, I checked on the security. Lo and behold the user the site was executing under did not have permissions to perform the insert. After updating the security, everything started working just fine.

Weird thing is that absolutely no error was thrown. When I tried to replicate the problem, I started getting security errors. I figured SQL server just decided not to report any that day.

You might start there.

Answer 2

I forgot the completed statement inside of the transaction