Environment
- .Net Framework 4.6.1
- EF 6.1.3
- Ms Visual Studio 2015
Technique Applied
- Model First
- Fluent API
- Eager loading
public MyContext() : base("DLSContext")
{
Configuration.LazyLoadingEnabled = false;
}
- 1 to many configuration
modelBuilder.Entity<User>()
.HasOptional(e => e.Creator)
.WithMany()
.HasForeignKey(m => m.CreatedBy);
- many to many configuration ( relationship table )
// User2Right Table
modelBuilder.Entity<User>()
.HasMany<UserRight>(r => r.UserRights)
.WithMany(u => u.PermittedUsers)
.Map(m =>
{
m.MapLeftKey("UserId");
m.MapRightKey("RightId");
m.ToTable("User2Right");
}
);
.Map => generate relationship table in database
- virtual sub-collection
public virtual ICollection<UserRight> UserRights { get; set; }
- pluralizing
public DbSet<User> Users { get; set; }
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
- lower camelCase
modelBuilder.Properties().Configure(c =>
{
var name = c.ClrPropertyInfo.Name;
var newName = char.ToLower(name[0]) + name.Substring(1);
c.HasColumnName(newName);
});
- Key and Index Attribute
[Index]
[Key]
public int Id { get; set; }
Model
User
public class User
{
public User()
{
if (this.UserRights == null)
{
this.UserRights = new HashSet<UserRight>();
}
}
[Index]
[Key]
public int Id { get; set; }
[Column(TypeName = "nvarchar")]
[StringLength(255)]
[Required]
public string Name { get; set; }
[Column(TypeName = "nvarchar")]
[StringLength(125)]
[Required]
[Index("IX_Login", IsUnique = true)]
public string Login { get; set; }
[Column(TypeName = "nvarchar")]
[StringLength(500)]
[Required]
public string Password { get; set; }
[Column(TypeName = "bit")]
public bool IsDisabled { get; set; }
[Column(TypeName = "nvarchar")]
[StringLength(255)]
[Required]
public string Email { get; set; }
[Required]
public int FailedLoginAttempts { get; set; }
public DateTime CreatedOn { get; set; }
public int? CreatedBy { get; set; }
public virtual User Creator { get; set; }
public DateTime ModifiedOn { get; set; }
public int? ModifiedBy { get; set; }
public virtual User Modifier { get; set; }
[Column(TypeName = "bit")]
[Required]
public bool IsDeleted { get; set; }
public virtual ICollection<UserRight> UserRights { get; set; }
public virtual ICollection<Status> StatusesCreated { get; set; }
public virtual ICollection<Status> StatusesModified { get; set; }
}
UserRight
public class UserRight
{
public UserRight()
{
if (this.PermittedUsers == null)
{
this.PermittedUsers = new HashSet<User>();
}
}
[Index]
[Key]
[Required]
public int Id { get; set; }
[Column(TypeName = "nvarchar")]
[StringLength(255)]
[Required]
public string Name { get; set; }
[Column(TypeName = "nvarchar")]
[StringLength(255)]
public string Description { get; set; }
public DateTime CreatedOn { get; set; }
public int? CreatedBy { get; set; }
public User Creator { get; set; }
public DateTime ModifiedOn { get; set; }
public int? ModifiedBy { get; set; }
public virtual User Modifier { get; set; }
[Column(TypeName = "bit")]
[Required]
public bool IsDeleted { get; set; }
public virtual ICollection<User> PermittedUsers { get; set; }
}
Status
public class Status
{
[Index]
[Key]
public int Id { get; set; }
[Column(TypeName = "nvarchar")]
[StringLength(255)]
[Required]
public string Name { get; set; }
public DateTime CreatedOn { get; set; }
public int? CreatedBy { get; set; }
public virtual User Creator { get; set; }
public DateTime ModifiedOn { get; set; }
public int? ModifiedBy { get; set; }
public virtual User Modifier { get; set; }
[Column(TypeName = "bit")]
[Required]
public bool IsDeleted { get; set; }
public virtual ICollection<Contact> Contacts { get; set; }
}
DAL
Context
public class MyContext : DbContext
{
public MyContext() : base("MyContext")
{
Configuration.LazyLoadingEnabled = false;
}
public DbSet<User> Users { get; set; }
public DbSet<UserRight> UserRights { get; set; }
public DbSet<Status> Statuses { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
// Pluralizing
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
// lower camelCase
modelBuilder.Properties().Configure(c =>
{
var name = c.ClrPropertyInfo.Name;
var newName = char.ToLower(name[0]) + name.Substring(1);
c.HasColumnName(newName);
});
// User Table
modelBuilder.Entity<User>()
.HasOptional(e => e.Creator)
.WithMany()
.HasForeignKey(m => m.CreatedBy);
modelBuilder.Entity<User>()
.HasOptional(e => e.Modifier)
.WithMany()
.HasForeignKey(m => m.ModifiedBy);
// User2Right Table
modelBuilder.Entity<User>()
.HasMany<UserRight>(r => r.UserRights)
.WithMany(u => u.PermittedUsers)
.Map(m =>
{
m.MapLeftKey("UserId");
m.MapRightKey("RightId");
m.ToTable("User2Right");
}
);
// Status Table
modelBuilder.Entity<Status>()
.HasRequired(c => c.Creator)
.WithMany(u => u.StatusesCreated)
.HasForeignKey(c => c.CreatedBy)
.WillCascadeOnDelete(false);
modelBuilder.Entity<Status>()
.HasRequired(c => c.Modifier)
.WithMany(u => u.StatusesModified)
.HasForeignKey(c => c.ModifiedBy)
.WillCascadeOnDelete(false);
}
}
Initializer
public class MyInitializer : System.Data.Entity.DropCreateDatabaseIfModelChanges<MyContext>
{
protected override void Seed(MyContext context)
{
// Create Master User
var systemUser = new User { Name = "System Admin", Login = "System Admin", Password = "1234567890", IsDisabled = false, Email = "abc", FailedLoginAttempts = 0, CreatedOn = DateTime.Today, ModifiedOn = DateTime.Today, IsDeleted = false };
context.Users.Add(systemUser);
context.SaveChanges();
// Create User Right
var userRights = new List<UserRight>
{
new UserRight { Name = "Administrative", Description = "Administrative Permission", CreatedOn=DateTime.Today, Creator=systemUser, ModifiedOn=DateTime.Today, Modifier=systemUser, IsDeleted=false },
new UserRight { Name = "Super User", Description = "Super User Permission", CreatedOn=DateTime.Today, Creator=systemUser, ModifiedOn=DateTime.Today, Modifier=systemUser, IsDeleted=false },
new UserRight { Name = "Dashboard Read", Description = "Read Permission for Dashboard", CreatedOn=DateTime.Today, Creator=systemUser, ModifiedOn=DateTime.Today, Modifier=systemUser, IsDeleted=false },
new UserRight { Name = "Dashboard Write", Description = "Write Permission for Dashboard", CreatedOn=DateTime.Today, Creator=systemUser, ModifiedOn=DateTime.Today, Modifier=systemUser, IsDeleted=false },
};
userRights.ForEach(u => context.UserRights.Add(u));
context.SaveChanges();
// Create Status
var statuses = new List<Status>
{
new Status { Name = "Active", CreatedOn=DateTime.Today, Creator=systemUser, ModifiedOn=DateTime.Today, Modifier=systemUser, IsDeleted=false },
new Status { Name = "InActive", CreatedOn=DateTime.Today, Creator=systemUser, ModifiedOn=DateTime.Today, Modifier=systemUser, IsDeleted=false },
new Status { Name = "Pending", CreatedOn=DateTime.Today, Creator=systemUser, ModifiedOn=DateTime.Today, Modifier=systemUser, IsDeleted=false }
};
statuses.ForEach(u => context.Statuses.Add(u));
context.SaveChanges();
// Create User
var users = new List<User>
{
new User { Name = "User A", Login = "userA", Password = "1234567890", IsDisabled = false, Email = "userA@mydomain.com", FailedLoginAttempts = 0, Creator=systemUser, CreatedOn = DateTime.Today, Modifier=systemUser, ModifiedOn = DateTime.Today, IsDeleted = false },
new User { Name = "User B", Login = "userB", Password = "1234567890", IsDisabled = false, Email = "userB@mydomain.com", FailedLoginAttempts = 0, Creator=systemUser, CreatedOn = DateTime.Today, Modifier=systemUser, ModifiedOn = DateTime.Today, IsDeleted = false },
};
users.ForEach(u => u.UserRights.Add(context.UserRights.Where(ur => ur.Name == "Administrative").FirstOrDefault()));
users.ForEach(u => context.Users.Add(u));
context.SaveChanges();
}
}
DAO
constant
public enum SearchOperatorOption
{
And,
Or
};
public enum SearchDateTimeOption
{
Inclusive,
Exclusive
};
UserService
static public class UserService
{
private static MyContext dataContext = new MyContext();
static public IQueryable<User> SearchUser(SearchOperatorOption searchOperatorOption = SearchOperatorOption.And,
SearchDateTimeOption searchDateTimeOption = SearchDateTimeOption.Inclusive,
bool includedChild = false,
int? id = null,
string name = null,
string login = null,
bool? isDisabled = null,
string email = null,
int? failedLoginAttempts = null,
DateTime? createdFrom = null,
DateTime? createdTo = null,
int? createdBy = null,
DateTime? modifiedFrom = null,
DateTime? modifiedTo = null,
int? modifiedBy = null,
bool? isDeleted = null)
{
var predicate = PredicateBuilder.New<User>();
switch (searchOperatorOption)
{
#region SearchOperatorOption.And
case SearchOperatorOption.And:
{
// Id
if (id != null)
{
predicate = predicate.And(u => u.Id == id);
}
// Name
if (!string.IsNullOrWhiteSpace(name))
{
predicate = predicate.And(u => u.Name.Contains(name));
}
// Login
if (!string.IsNullOrWhiteSpace(login))
{
predicate = predicate.And(u => u.Login.Contains(login));
}
// IsDisabled
if (isDisabled != null)
{
predicate = predicate.And(u => u.IsDisabled == isDisabled);
}
// Email
if (!string.IsNullOrWhiteSpace(email))
{
predicate = predicate.And(u => u.Email.Contains(email));
}
// FailedLoginAttempts
if (failedLoginAttempts != null)
{
predicate = predicate.And(u => u.FailedLoginAttempts >= failedLoginAttempts);
}
// CreatedOn
if (createdFrom != null)
{
switch (searchDateTimeOption)
{
case SearchDateTimeOption.Inclusive:
{
predicate = predicate.And(u => u.CreatedOn >= createdFrom);
break;
}
case SearchDateTimeOption.Exclusive:
{
predicate = predicate.And(u => u.CreatedOn > createdFrom);
break;
}
}
}
// CreatedOn
if (createdTo != null)
{
switch (searchDateTimeOption)
{
case SearchDateTimeOption.Inclusive:
{
predicate = predicate.And(u => u.CreatedOn <= createdTo);
break;
}
case SearchDateTimeOption.Exclusive:
{
predicate = predicate.And(u => u.CreatedOn < createdTo);
break;
}
}
}
// CreatedBy
if (createdBy != null)
{
predicate = predicate.And(u => u.CreatedBy == createdBy);
}
// ModifiedOn
if (modifiedFrom != null)
{
switch (searchDateTimeOption)
{
case SearchDateTimeOption.Inclusive:
{
predicate = predicate.And(u => u.ModifiedOn >= modifiedFrom);
break;
}
case SearchDateTimeOption.Exclusive:
{
predicate = predicate.And(u => u.ModifiedOn > modifiedFrom);
break;
}
}
}
// ModifiedOn
if (modifiedTo != null)
{
switch (searchDateTimeOption)
{
case SearchDateTimeOption.Inclusive:
{
predicate = predicate.And(u => u.ModifiedOn <= modifiedTo);
break;
}
case SearchDateTimeOption.Exclusive:
{
predicate = predicate.And(u => u.ModifiedOn < modifiedFrom);
break;
}
}
}
// ModifiedBy
if (modifiedBy != null)
{
predicate = predicate.And(u => u.ModifiedBy == modifiedBy);
}
// IsDeleted
if (isDeleted != null)
{
predicate = predicate.And(u => u.IsDeleted == isDeleted);
}
break;
}
#endregion
#region SearchOperatorOption.Or
case SearchOperatorOption.Or:
{
// Id
if (id != null)
{
predicate = predicate.Or(u => u.Id == id);
}
// Name
if (!string.IsNullOrWhiteSpace(name))
{
predicate = predicate.Or(u => u.Name.Contains(name));
}
// Login
if (!string.IsNullOrWhiteSpace(login))
{
predicate = predicate.Or(u => u.Login.Contains(login));
}
// IsDisabled
if (isDisabled != null)
{
predicate = predicate.Or(u => u.IsDisabled == isDisabled);
}
// Email
if (!string.IsNullOrWhiteSpace(email))
{
predicate = predicate.Or(u => u.Email.Contains(email));
}
// FailedLoginAttempts
if (failedLoginAttempts != null)
{
predicate = predicate.Or(u => u.FailedLoginAttempts >= failedLoginAttempts);
}
// CreatedOn
if (createdFrom != null)
{
switch (searchDateTimeOption)
{
case SearchDateTimeOption.Inclusive:
{
predicate = predicate.Or(u => u.CreatedOn >= createdFrom);
break;
}
case SearchDateTimeOption.Exclusive:
{
predicate = predicate.Or(u => u.CreatedOn > createdFrom);
break;
}
}
}
// CreatedOn
if (createdTo != null)
{
switch (searchDateTimeOption)
{
case SearchDateTimeOption.Inclusive:
{
predicate = predicate.Or(u => u.CreatedOn <= createdTo);
break;
}
case SearchDateTimeOption.Exclusive:
{
predicate = predicate.Or(u => u.CreatedOn < createdTo);
break;
}
}
}
// CreatedBy
if (createdBy != null)
{
predicate = predicate.Or(u => u.CreatedBy == createdBy);
}
// ModifiedOn
if (modifiedFrom != null)
{
switch (searchDateTimeOption)
{
case SearchDateTimeOption.Inclusive:
{
predicate = predicate.Or(u => u.ModifiedOn >= modifiedFrom);
break;
}
case SearchDateTimeOption.Exclusive:
{
predicate = predicate.Or(u => u.ModifiedOn > modifiedFrom);
break;
}
}
}
// ModifiedOn
if (modifiedTo != null)
{
switch (searchDateTimeOption)
{
case SearchDateTimeOption.Inclusive:
{
predicate = predicate.Or(u => u.ModifiedOn <= modifiedTo);
break;
}
case SearchDateTimeOption.Exclusive:
{
predicate = predicate.Or(u => u.ModifiedOn < modifiedFrom);
break;
}
}
}
// ModifiedBy
if (modifiedBy != null)
{
predicate = predicate.Or(u => u.ModifiedBy == modifiedBy);
}
// IsDeleted
if (isDeleted != null)
{
predicate = predicate.Or(u => u.IsDeleted == isDeleted);
}
break;
}
#endregion
}
if (includedChild)
{
return dataContext.Users.AsExpandable().Where(predicate).Include(u => u.Creator).Include(u => u.Modifier);
}
else
{
return dataContext.Users.AsExpandable().Where(predicate);
}
}
#region Basic Method
static public IEnumerable<User> GetAllUser(bool includedChild = false)
{
return SearchUser(includedChild: includedChild, isDeleted: false).ToList();
}
static public User GetUserById(int id, bool includedChild = false)
{
return SearchUser(includedChild: includedChild, id: id).FirstOrDefault();
}
static public IEnumerable<User> GetUserByName(string name, bool includedChild = false)
{
return SearchUser(includedChild: includedChild, name: name);
}
static public IEnumerable<User> GetUsersByName(bool includedChild = false, params string[] name)
{
throw new NotImplementedException();
}
static public User GetUserByLogin(string login, bool includedChild = false)
{
return SearchUser(includedChild: includedChild, login: login).FirstOrDefault();
}
static public IEnumerable<User> GetAllDisabledUser(bool includedChild = false)
{
return SearchUser(includedChild: includedChild, isDisabled: true).ToList();
}
static public IEnumerable<User> GetUserByEmail(string email, bool includedChild = false)
{
return SearchUser(includedChild: includedChild, email: email);
}
static public IEnumerable<User> GetUserByCreationDate(DateTime? from, DateTime? to, bool includedChild = false)
{
return SearchUser(includedChild: includedChild, createdFrom: from, createdTo: to);
}
static public IEnumerable<User> GetUserByCreator(int creatorId, bool includedChild = false)
{
return SearchUser(includedChild: includedChild, createdBy: creatorId);
}
static public IEnumerable<User> GetUserByCreator(User creator, bool includedChild = false)
{
return GetUserByCreator(includedChild: includedChild, creatorId: creator.Id);
}
static public IEnumerable<User> GetUserByModificationDate(DateTime? from, DateTime? to, bool includedChild = false)
{
return SearchUser(includedChild: includedChild, modifiedFrom: from, modifiedTo: to);
}
static public IEnumerable<User> GetUserByModifier(int modifierId, bool includedChild = false)
{
return SearchUser(includedChild: includedChild, modifiedBy: modifierId);
}
static public IEnumerable<User> GetUserByModifier(User modifier, bool includedChild = false)
{
return GetUserByModifier(includedChild: includedChild, modifierId: modifier.Id);
}
static public IEnumerable<User> GetAllDeletedUser(bool includedChild = false)
{
return SearchUser(includedChild: includedChild, isDeleted: true).ToList();
}
#endregion
#region Extend Method
#endregion
}
