I have a Entity Framework Database First Model.
I want to write a MSTest/nUnit test to verify that all the stored procs, tables and views that are defined in my edmx model are still valid on the database.
databaseentity-frameworkunit testing
I have a Entity Framework Database First Model.
I want to write a MSTest/nUnit test to verify that all the stored procs, tables and views that are defined in my edmx model are still valid on the database.
Personally, I've tried making one huge schema for all my entities on a fairly complex but small project(~300 tables) . We had an extremely normalized database (5th form normalization (I say that loosely)) with many "many to many" relationships and extreme referential integrity enforcement.
We also used a "single instance per request" strategy which I'm not convinced helped either.
When doing simple, reasonably flat "explicitly defined" listings, lookups and saves the performance was generally acceptable. But when we started digging into deep relationships the performance seemed to take drastic dips. Compared to a stored proc in this instance, there was no comparison (of course). I'm sure we could've tweaked the code base here and there to get the performance improved, however, in this case we just needed performance boost without analysis due to time constraints, and we fell back to the stored proc (still mapped it through EF, because EF provided strongly typed results), we only needed that as a fall back in a few area's. When we had to traverse all over the database to create a collection (using .include() unsparingly), the performance was noticeably degrading, but maybe we were asking too much..
So based on my experience, i would recommend creating a separate .edmx per intent. Only generate what you'll be using based on the scope of that need. You may have some smaller scoped .edmx files for purposed tasks, and then some large ones where you need to traverse complex relationships to build objects. I'm not sure where that magic spot is, but I'm sure there is one... lol...
Honestly though, aside from a few pitfalls which we kind of saw coming (complex traversing), the huge .edmx worked fine from a "working" perspective. But you'll have to watch out for the "fixup" magic that the context does behind the scene's if you don't explicitly disable it. As well as keeping the .edmx in sync when changes to the database are made.. it was sometimes easier to wipe the entire surface and re-create the entities, which took like 3 minutes so it wasn't a big deal.
This was all with EntityFramework 4.1. I'd be really interested in hearing about your end choice and experience as well.
And regarding you're question on nHibernate, that's a can of worms question in my opinion, you'll get barking on both sides of the fence... I hear a lot of people bashing EF for the sake of bashing without working through the challenges and understanding the nuances unique to EF itself.. and although I've never used nHibernate in production, generally, if you have to manually and explicitly create things like mappings, you're going to get more finite control, however, if you can drag n' drop , generate, and start CRUD'ing and querying using LINQ, I could give a crap about granularity.
I hope this helps.
I have similar problem: I have several users with different permissions. Some of them can do everything and some of them can select/update only certain columns in tables. The solution I have found is the using of OnModelCreating
method.
Now I'd like to explain the general logic. I have database with users who are authenticated by SQL login. These users are mapped to one of custom database roles, and these roles, in their turn, are granted permissions. If you use Windows Authentication, there's really no difference for code since the idea would be the same: you would create Windows Groups, map your users to those groups and, finally, grant permissions to groups.
The core of this idea is Ignore
method of EntityTypeConfiguration<T>
used in OnModelCreating
method (which you override in your custom context class). This method lets us ignore the columns in entities which won't be used by context. So, you should set some condition which branches to code with filtering:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
// Get the entity to which we want to apply filter
var detailEntity = modelBuilder.Entity<Detail>();
// Check the condtion
if (someCondition)
{
// If condition is met, these two properties will be ignored by context.
// From now on, these properties will get their default values
// when selecting data from database:
// for numeric types - zero;
// for reference types - null.
detailEntity.Ignore(entity => entity.Price);
detailEntity.Ignore(entity => entity.Discount);
}
}
The condition for filtering is the name of Database Role in SQL Server (if you use SQL Login) or Windows Group the user belongs to (if you use Windows Authentication).
1. Windows Groups
For Windows Groups it's easy - just fetch all groups the user belongs to and compare it to the one you need. Here's helper class which would fetch user's Windows Groups and check whether he belongs to the group passed as parameter:
static class User
{
internal static bool IsInGroup(string groupName)
{
return GetWindowsGroups().Any(g => g.ToLower() == groupName.ToLower());
}
internal static List<string> GetWindowsGroups()
{
List<string> groups = new List<string>();
WindowsIdentity user = WindowsIdentity.GetCurrent();
user.Groups.ToList().ForEach(ir =>
{
try
{
IdentityReference irt = ir.Translate(typeof(NTAccount));
groups.Add(irt.Value);
}
catch { /* just ignore */ }
});
return groups;
}
}
Now we can use it in OnModelCreating
:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
var detailEntity = modelBuilder.Entity<Detail>();
if (User.IsInGroup("DB_OPERATOR"))
{
// Fully ignore properties in model
detailEntity.Ignore(entity => entity.Price);
detailEntity.Ignore(entity => entity.Discount);
}
}
Now any selection from Detail table will not select these two properties (although, as I said earlier, you can use them in your entity class - they just will be initialized to their default values, but changes to them won't propagate to database).
2. SQL Logins
With SQL Logins, the user's permissions depend on his database role, so you have to obtain it before any interaction with context. Here's where things become a bit complicated. Before going further, first, I will use dbo.GetUserRole
stored procedure which just fetches a single value - role a user belongs to:
CREATE PROCEDURE dbo.GetUserRole
AS
BEGIN
SET NOCOUNT ON;
SELECT dp.[name] --, us.[name]
FROM sys.sysusers AS us
RIGHT JOIN sys.database_role_members AS rm ON us.uid = rm.member_principal_id
JOIN sys.database_principals AS dp ON rm.role_principal_id = dp.principal_id
WHERE us.name = CURRENT_USER;
END;
Second, I will add static Role
property indicating user's database role:
class TestContext : DbContext
{
internal static string Role { get; private set; }
}
Now there's one caveat that awaits us: You can't fetch role inside OnModelCreating
method.
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
Role = base.Database.SqlQuery<string>("dbo.GetUserRole", new object[] { }).First();
var detailEntity = modelBuilder.Entity<Detail>();
if (Role == "operator")
{
detailEntity.Ignore(entity => entity.Price);
detailEntity.Ignore(entity => entity.Discount);
}
}
Just because EF will throw an exception:
The context cannot be used while the model is being created. This exception may be thrown if the context is used inside the
OnModelCreating
method or if the same context instance is accessed by multiple threads concurrently. Note that instance members ofDbContext
and related classes are not guaranteed to be thread safe.
Well, if you can't use context, then we can use context's DbConnection
directly from constructor:
class TestContext : DbContext
{
public DbSet<Detail> Details { get; set; }
internal static string Role { get; private set; }
public TestContext(string nameOrConnectionString) : base(nameOrConnectionString)
{
var conn = base.Database.Connection;
conn.Open();
using (var comm = conn.CreateCommand())
{
comm.CommandText = "dbo.GetUserRole";
comm.CommandType = CommandType.StoredProcedure;
Role = comm.ExecuteScalar() as string;
}
conn.Close();
}
}
So, for now we have the following. The OnModelCreating
is called in one of two cases:
1) When you call DbContext.Database.Initialize
method.
2) When you make a query against database (in this case Database.Initialize
runs implicitly).
As a side note, the documentation says:
This method is called only once when the first instance of a derived context is created.
However, this is not true.
So, the solution is to use context's connection and request a role. This:
1) must be done before any query or Database.Initialize
call
or
2) can be done in context's constructor.
Now when we have a role, we can use it in our code. I chose String
type for storing a role, but you can also use enumeration for convenience:
enum Role
{
Admin,
Regular,
Operator
}
I don't know whether this pattern I chose is correct, but I haven't found any info over this theme - and I'm surprised, because in reality this is how databases are designed in mind - with granting different permissions to different users. So, I hope this will help you.
Good luck!
Best Answer
Query the system tables. This will verify the presence of all of the required objects in your database.
For example, the following query uses the sys.objects catalog view to return all database objects that have been modified in the last 10 days.
See Also
Querying the SQL Server System Catalog
ADO.NET Code Examples