Entity Framework, as of version 6, allows the usage of stored procedures for its CUD (Create, Update and Delete) operations. We just need to override the DbContext’s OnModelCreating method and call MapToStoredProcedures.
protected override void OnModelCreating(DbModelBuilder modelBuilder)
//rest goes here
The problem is, this is all or nothing: you cannot use a stored procedure for just one of the operations, you need to use stored procedures for all operations. And, no, it is not possible (yet) to use custom SQL, just stored procedures.
There are two properties that control the ability to lazy load navigation properties:
- LazyLoadingEnabled: controls whether navigation properties marked as virtual will be lazy loaded, that is, loaded on demand when they are accessed;
- ProxyCreationEnabled: Entity Framework will create dynamic proxy classes for unsealed types with virtual properties, whenever it loads an instance from the database or it is asked to create an instance (Create method).
The problem is: for the most cases, we need both properties to be true. If they are not, navigation properties are only loaded if:
- Eagerly loaded through the Include extension method;
- Explicitly loaded through the Load method (collections only).
There are cases, however, when you wouldn’t want to have proxies:
- When exposing entities through WCF, because since it knows nothing about the dynamic generated proxy types;
- When using, for example, AutoMapper, to convert from one type to another, for the same reason.
The one reason why you would want a proxy without lazy loading is for self-tracking changes, that is, the proxy detects changes made to it instead of having the DbChangeTracker class (the DbContext.ChangeTracker property) doing it.
When you deploy a page layout, you may want to add your own master page as well. I find it useful to add my own custom master pages and reference them directly, in the same or a dependent feature. You might be surprised, however, that it doesn’t work exactly how you’d expect!
The issue is, page layouts will ignore silently anything in the MasterPageFile attribute if it isn’t one of the standard tokens for the system or custom master pages. ~masterurl/default.master and ~masterurl/custom.master. The solution is to have a code behind class and specify the master page in the OnPreInit method (anywhere else won’t work):
protected override void OnPreInit(EventArgs e)
this.MasterPageFile = "~site/_catalogs/masterpage/CustomMasterPage.master":
If you want to save a publishing site as a template, so that it can be used to create other sites, you may find it surprising that the option is missing from the site settings page:
I don’t know exactly why, but publishing sites hide this option, however, it’s not difficult to get around it: just navigate to /_layouts/15/savetmpl.aspx. But, wait, what if you get this?
Easy, easy. Open your site in SharePoint Designer, click Site Options:
and change the value of property SaveSiteAsTemplateEnabled from false to true:
And now you will be able to access savetmpl.aspx and save your site.
As of Entity Framework 6.x, Table Valued Functions are not supported, which is really a pity.
For example, the following does not work:
CREATE FUNCTION [dbo].[GetCustomers]()
RETURNS @ReturnTable TABLE
SELECT Id, Email, Phone, Name
//throws an exception complaining about TVF
var customers1 = ctx.Database.SqlQuery(typeof(Customer), "[dbo].[GetCustomers]");
var customers2 = ctx.Database.Customers.SqlQuery("[dbo].[GetCustomers]");
However, you can do this:
var customers3 = ctx.Database.SqlQuery(typeof(Customer), "SELECT * FROM [dbo].[GetCustomers]()");
And it works!
The NHibernate ISession is not thread-safe, that is, it shouldn’t be shared by multiple threads. The ISessionFactory, however, is thread-safe, and thus it should be used to create ISession instances as needed. Do not worry, creating sessions does not mean that connections to the database are immediately open and kept alive, the ISession implementation only opens database connections when they are necessary and closes them when they cease to be.
The IDbCommandInterceptor (sorry, no official documentation) interface was introduced in Entity Framework 6 as part of the new interception and logging API, and allows the interception of the SQL and its parameters that are sent to the database as the result of DbContext CRUD operations.
It offers two methods for each of the basic ADO.NET operations, those defined in DbCommand, one called before the operation is executed, and the other called afterwards:
) –> NonQueryExecuting
returning a single value) –> ScalarExecuting
returning rows) –> ReaderExecuting
As usual, the methods ending with “ing” are executed before and those ending with “ed” are executed afterwards, always synchronously.
One might naively assume that INSERTs would always trigger a NonQueryExecuting/NonQueryExecuted call, and indeed it is so, unless we have an IDENTITY primary key, in which case, Entity Framework will instead call ReaderExecuting/ReaderExecuted. It’s easy to understand why: when we use an IDENTITY, we need to retrieve the generated value immediately after the INSERT, hence Entity Framework will generate code like:
1: INSERT INTO [dbo].[SomeTable] (...)
2: VALUES (...)
3: SELECT [Id]
4: FROM [dbo].[SomeTable]
5: WHERE @@ROWCOUNT > 0 AND [Id] = SCOPE_IDENTITY()
The INSERT and SELECT are combined in the same command, which justifies the choice of ReaderExecuting/ReaderExecuted. Because in Entity Framework we always use IDENTITY – don’t we? –, it’s ReaderExecuting/ReaderExecuted that you should be implementing if you want to change the INSERT SQL or its parameters.
When you use a discriminator column, that is, a column that holds a value that tells Entity Framework what type the row refers to, or what restriction it shall use when querying the entity, you cannot map this column as a property in your entity.
For example, imagine you want to use “soft deletes”, that is, use a database column to represent the “deleted” state of a record instead of actually physically deleting it from the table; you might have an IS_DELETED column of some integer type, that would hold either a 1 or a 0, depending on whether the record is deleted (1) or not (0). In that case, you would build a mapping like this in OnModelCreating:
1: modelBuilder.Entity<MySoftDeletableEntity>().Map<MySoftDeletableEntity>(m => m.Requires("is_deleted").HasValue(0));
However, you won’t be able to have a corresponding IsDeleted property in the MySoftDeletableEntity, because Entity Framework will throw an exception complaining about it. It is somewhat sad, because it might be useful to refer to it, but that’s the way it is.
This is part of a series of posts about NHibernate Pitfalls. See the entire collection here.
This is a problem similar to the one with native id generators. Basically, because there are different versioning strategies – timestamp, counter, native, etc, – NHibernate needs to issue a SELECT after a versioned entity is INSERTEd or UPDATEd. While this wouldn’t be necessary when NHibernate manages the version on the “client side”, like:
1: UPDATE MyEntity SET Version = 2 WHERE ...;
3: UPDATE MyEntity SET Version = GETDATE() WHERE ...;
But not if the version is handled on the database side, like when using SQL Server’s ROWVERSION/TIMESTAMP columns or Oracle’s ORA_ROWSCN pseudo-columns. In these cases, NHibernate needs to issue a SELECT after each INSERT or UPDATE:
1: UPDATE MyEntity SET ... WHERE ...;
2: SELECT Version FROM MyEntity WHERE ...;
This breaks batching, because it needs to be done immediately after each INSERT/UPDATE.