Entity Framework Core notes for T-SQL

EntityFrameworkCore.T-SQL.Notes.MD

Entity Framework Core for T-SQL notes

To SQL translations

Add + SaveChanges

db.Blogs.Add(new Blog { Url = $"http://blogs.msdn.com/adonet/{Guid.NewGuid()}" });
int count = db.SaveChanges();

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Blogs] ([Url])
VALUES (@p0);
SELECT [BlogId]
FROM [Blogs]
WHERE @@ROWCOUNT = 1 AND [BlogId] = scope_identity();

',N'@p0 nvarchar(4000)',@p0=N'http://blogs.msdn.com/adonet/c28e5bf7-a17e-484c-b07a-9c7ec6c9ad6e'

Comments:

• This SQL contains two statements: the first one INSERTs 1 record, while the second one SELECTs and returns this record back (due to EF's SaveChanges() method protocol).
• @@ROWCOUNT Returns the number of rows affected by the last statement. So, in this example, it ensures that the previous statement has affected exactly 1 row. If it's not true nothing will be SELECTed.
• The scope_identity() function returns the last identity created in the same session and the same scope. So, here it SELECTs only the record created in the current scope.

DbSet<T>

Enumeration

foreach (var blog in db.Blogs)
  ...

SELECT [b].[BlogId], [b].[Url]
FROM [Blogs] AS [b]

Comments:

• Just SELECTs all the records.
• SQL:BatchCompleted occurs immediately on the first iteration of foreach.