EntityWorker.Core - An Alternative to Entity Framework

Introduction

EntityWorker.Core is an object-relation mapper that enables .NET developers to work with relations data using objects. EntityWorker is an alternative to entityframework, is more flexible and much faster than entity framework.

Update

1. Rename Attribute StringFy => Stringify
2. Remove EnabledMigration and adding InitializeMigration() method instead
3. Implementing OnModuleStart where we could create our database and apply db changes
4. Making Transaction Thread Safe
5. Implementing OnModuleConfiguration where we could configrate our modules
6. Json Handler and JsonIgnore Attribute
7. Added ColumnType attribute to handle custom datatype
8. Making transaction, OnModuleConfiguration and OnModuleStart abstract
9. XML handler that doesn't require the object to be Serializable
10. EntityWorker.Core Package Handler
11. Example of Many to Many Relationships
12. Add attribute KnownType for unknown PropertyTypes like interfaces
13. Logger
14. Added JsonDocument Attribute to save data as JsonObject in the database
15. Added XmlDocument Attribute to save data as Xml in the database
16. Implement db Schema [Table("TableName", "dbo")]
17. Implementing EntityType Builder
18. Using store procedure in entityworker.core
19. Able to ignore updating some properties when saving an item. exist only in EntityWorker.Core >= 2.2.8

Code

Github

Nuget

EntityWorker.Core

Test Project to Download

LightData.CMS

IProduct

EntityFrameWork vs EntityWorker.Core Performance Test

This is a test between EntityFramework and EntityWorker.Core.

Debug State is Release

Background

Entityframework is a great library, but managing migrations and implementing an existing structure with entityframework is really not so flexible.

So I thought about building a library that could compete with entityframework, but also think of all the things that entityframework is missing.

EntityWorker.Core has a great performance executing query, but is also much flexible to use.

I am going to show you how you could use EntityWorker.Core to build and manage your data with ease.

Database Providers

1. Mssql
2. PostgreSql
3. Sqlite

Using the Code

Configurate GlobalConfiguration

// Set those settings under Application_Start
// Those two first settings is for DataEnode Attribute
/// Set the key size for dataEncoding 128 or 256 Default is 128
EntityWorker.Core.GlobalConfiguration.DataEncode_Key_Size = DataCipherKeySize.Key_128;

/// Set the secret key for encoding Default is "EntityWorker.Default.Key.Pass"
EntityWorker.Core.GlobalConfiguration.DataEncode_Key = "the key used to Encode the data ";

/// <summary>
/// The Default Value for package encryption
/// </summary>
EntityWorker.Core.GlobalConfiguration.PackageDataEncode_Key = "packageSecurityKey"

/// Last set the culture for converting the data
EntityWorker.Core.GlobalConfiguration.CultureInfo = new CultureInfo("en");

Now we will start building our Modules from the start and let EntityWorker.Core build our tables.

We will start building, e.g., User, role and address.

    public abstract class Entity{

        [PrimaryKey]
        public Guid? Id { get; set; }
   }

    [Table("Roles")]
    public class Role : Entity{ 
        [NotNullable]
        public string Name { get; set; }

        [Stringify]
        public EnumHelper.RoleDefinition RoleDefinition { get; set; }
    }

    [Table("Users")]
    public class User : Entity {
        [NotNullable]
        [DataEncode]
        public string UserName { get; set; }

        [NotNullable]
        [DataEncode]
        public string Password { get; set; }

        [ForeignKey(typeof(Role))]
        public Guid RoleId { get; set; }

        [IndependentData]
        public Role Role { get; set; }

        [ForeignKey(typeof(Person))]
        public Guid PersonId { get; set; }

        public Person Person { get; set; }
    }

    public class Person : Entity {
    
        public string FirstName { get; set; }

        public string LastName { get; set; }

        public string SureName { get; set; }

        public List<Address> Addresses { get; set; }
    }
    
    public class Address : Entity
    {        
        [NotNullable]
        public string Name { get; set; }

        public string AddressLine1 { get; set; }

        public string AddressLine2 { get; set; }

        public string TownOrCity { get; set; }

        public string PostalCode { get; set; }

        public string Area { get; set; }

        public Country Country { get; set; }

        [ForeignKey(typeof(Person))]
        public Guid PersonId { get; set; }
    }

Now let's build our migrations. We will have one migration, MigrationStartUp that will add some data to the database, like default user and role.

    public class MigrationStartUp : Migration
    {
        public override void ExecuteMigration(IRepository repository)
        {
        // See here by saving the user, all under classes will be created and
        // ForeignKeys will be assigned automatically
         var users = new List<User>();
            users.AddRange(new List<User>()
            {
                new User()
                {
                UserName = "Admin",
                Password = Methods.Encode("Admin"),
                Role = new Role(){Name = "Admin",
                RoleDefinition= EnumHelper.RoleDefinition.Developer},
                Person = new Person()
                {
                    FirstName = "Alen",
                    LastName = "Toma",
                    SureName = "Nather",
                    Addresses = new List<Address>()
                    {
                        new Address()
                        {
                            Name = "test"
                        }
                    }
                }
                }
            });

            users.ForEach(x => repository.Save(x));
            base.ExecuteMigration(repository);
            repository.SaveChanges();
        }        
    }

Now, we will have to specify which migration should be executed in wich order, by creating a migrationConfig that will include our migrations.

    // in database will be created Generic_LightDataTable_DBMigration 
    // this table will keep an eye on which migrations have been executed already.
    public class MigrationConfig : IMigrationConfig
    {
        public IList<Migration> GetMigrations(IRepository repository)
        {
            return new List<Migration>()
            {
                new MigrationStartUp()
            };
        }
    }  

Now, we will create our Repository that will include the transaction.

    // Here we inherit from Transaction which contains the database 
    // logic for handling the transaction.
    // well that's all we need right now.
    public class Repository : Transaction
    {
        // there is three databases types mssql, Sqllight and postgresql
        public Repository(DataBaseTypes dbType = DataBaseTypes.Mssql) : 
        base(GetConnectionString(dbType), dbType) 
        { 

        }

        protected override void OnModuleStart()
        {
            if (!base.DataBaseExist())
                base.CreateDataBase();

            /// Limited support for sqlite
            // Get the latest change between the code and the database. 
            // Property Rename is not supported. renaming property x will end up
            // removing the x and adding y so there will be dataloss
            // Adding a primary key is not supported either
            var latestChanges = GetCodeLatestChanges();
            if (latestChanges.Any())
                latestChanges.Execute(true);

            // Start the migration
            InitializeMigration();
        }

        // We could configrate our modules here instead of adding attributes in the class, 
        // of course, it upp to you to decide.
        protected override void OnModuleConfiguration(IModuleBuilder moduleBuilder)
        {
   
         moduleBuilder.Entity<User>()
                .TableName("Users", "dbo")
                .HasPrimaryKey(x => x.Id, false)
                .NotNullable(x => x.UserName)
                .HasDataEncode(x => x.UserName)
                .HasDataEncode(x => x.Password)
                .HasForeignKey<Role, Guid>(x => x.RoleId)
                .HasIndependentData(x => x.Role)
                .HasForeignKey<Person, Guid>(x => x.PersonId)
                .HasRule<UserRule>()
                .HasJsonIgnore(x=> x.Password)
                .HasXmlIgnore(x=> x.Password);
                
            moduleBuilder.Entity<Person>()
               .HasColumnType(x => x.FirstName, "varchar(100)");
                 
                 // OR
            moduleBuilder.EntityType(typeof(User))
                .TableName("Users", "geto")
                .HasKnownType("Person", typeof(Person))
                .HasPrimaryKey("Id", false)
                .NotNullable("UserName")
                .HasDataEncode("UserName")
                .HasDataEncode("Password")
                .HasForeignKey<Role>("RoleId")
                .HasIndependentData("Role")
                .HasForeignKey<Person>("PersonId")
                .HasRule<UserRule>()
                .HasJsonIgnore("Password");
         }

        // get the full connection string
        public static string GetConnectionString(DataBaseTypes dbType)
        {
          if (dbType == DataBaseTypes.Mssql)
            return  @"Server=.\SQLEXPRESS; Database=CMS; User Id=root; Password=root;";
          else if (dbType == DataBaseTypes.Sqlite)
            return  @"Data Source=D:\Projects\CMS\source\App_Data\CMS.db";
          else return @"Host=localhost;Username=postgres;Password=root;Database=CMS";
        }
    }    

Now let's test and execute some queries.

Delete Operation

Entityworker delete items hierarki. lets se how this works

using (var rep = new Repository())
{
int userId = 1;
// See here i made sure to load children. Delete method will make sure to delete all children 
// that dose not contain IndependedData Attribute
rep.Get<User>().Where(x=> x.Id == userId).LoadChildren().Delete().SaveChanges();
}

Save and Ignore some Propeties

Some time we would want to save some object but ignore some properties. 

this is usefull when we retrive some data from json that containes some old data, that we dont want them in the db.  Only exist in EntityWorker.Core >= 2.2.8

     using (var rep = new Repository())
            {
                var us = rep.Get<User>().OrderBy(x=> x.Id).LoadChildren().ExecuteFirstOrDefault();
                us.Role.Name = "Yedsfsdft";
                // Se here we have execluded both RoleName and AdressName from our Update operation
                rep.Save(us, x=> x.Role.Name, x => x.Person.Addresses.Select(a=> a.Name));
                var m = rep.Get<User>().OrderBy(x => x.Id).LoadChildren().ExecuteFirstOrDefault();
                Console.WriteLine("New Value for RoleName is " + m.Role.Name);
                rep.SaveChanges();
            } 

 

Query and Expression

Like EntityframeWork, you could Include and Ignore loading children. Let's see how the query won't be executed until Execute or ExecuteAsync is called.

   using (var rep = new Repository())
   {
        // LoadChildren indicates to load all children hierarchy.
        // It has no problem handling circular references.
        // The query does not call the database before we invoke Execute or ExecuteAsync
        var users = rep.Get<User>().Where(x => 
                (x.Role.Name.EndsWith("SuperAdmin") &&
                 x.UserName.Contains("alen")) ||
                 x.Address.Any(a=> a.AddressName.StartsWith("st"))
                ).LoadChildren().Execute(); 
                
        // let's say that we need only to load some
        // children and ignore some others, then our select will be like this instead
          var users = rep.Get<User>().Where(x =>
                (x.Role.Name.EndsWith("SuperAdmin") &&
                 x.UserName.Contains("alen")) ||
                 x.Address.Any(a=> a.AddressName.StartsWith("st"))
                ).LoadChildren(x=> x.Role.Users.Select(a=> a.Address),
                 x=> x.Address)
                .IgnoreChildren(x=> x.Role.Users.Select(a=> a.Role))
                .OrderBy(x=> x.UserName).Skip(20).Take(100).Execute();        
        Console.WriteLine(users.ToJson());
        Console.ReadLine();
   }

LinqToSql Result Example

     using (var rep = new Repository())
     {
       var id = Guid.NewGuid();
       ISqlQueriable<User> users =rep.Get<Person>().Where(x => x.FirstName.Contains("Admin") ||
       string.IsNullOrEmpty(x.FirstName) || string.IsNullOrEmpty(x.FirstName) == false && x.Id != id)
       List<User> userList = users.Execute();
       string sql = users.ParsedLinqToSql;
      }

-- And here is the generated Sql Query
SELECT
   [Person].[Id],
   [Person].[FirstName],
   [Person].[LastName],
   [Person].[SureName] 
FROM
   [Person] 
WHERE
   (
((
      CASE
         WHEN
            [Person].[FirstName] LIKE String[ % Admin % ] 
         THEN
            1 
         ELSE
            0 
      END
) = 1 
      OR 
      (
(
         CASE
            WHEN
               [Person].[FirstName] IS NULL 
            THEN
               1 
            ELSE
               CASE
                  WHEN
                     [Person].[FirstName] = String[] 
                  THEN
                     1 
                  ELSE
                     0 
               END
         END
)
      )
      = 1) 
      OR 
      (
(((
         CASE
            WHEN
               [Person].[FirstName] IS NULL 
            THEN
               1 
            ELSE
               CASE
                  WHEN
                     [Person].[FirstName] = String[] 
                  THEN
                     1 
                  ELSE
                     0 
               END
         END
)) = 0) 
         AND 
         (
            [Person].[Id] <> Guid[d82d1a00 - 5eb9 - 4017 - 8c6e - 23a631757532]
         )
      )
   )
GROUP BY
   [Person].[Id], [Person].[FirstName], [Person].[LastName], [Person].[SureName] 
ORDER BY
   Id OFFSET 0 ROWS FETCH NEXT 2147483647 ROWS ONLY;
-- All String[], Date[] and Guid[] will be translated to Parameters later on.   

Dynamic Linq

EntityWorker is able to execute querys of type string and convert it back to Sql, here is how it works

using (var rep = new Repository())
{
string expression ="x.Person.FirstName.EndsWith(\"n\") AND (x.Person.FirstName.Contains(\"a\") OR x.Person.FirstName.StartsWith(\"a\"))";
var users = rep.Get<User>().Where(expression).LoadChildren().Execute();
}

Create Custom ISqlQueryable/IList

We could create custom SQL or even stored procedure and convert its data to objects or to ISqlQueryable.

   using (var rep = new Repository())
   {
            //Create a custom ISqlQueryable, you could have store proc or a row sql query
            var cmd = rep.GetSqlCommand("SELECT * FROM Users WHERE UserName = @userName");
            AddInnerParameter(cmd, "userName", userName, System.Data.SqlDbType.NVarChar);
            // Convert the result to Data 
            List<Users> users = DataReaderConverter<User>(cmd).LoadChildren().Execute(); 
            // Or use this to convert an unknown object eg custom object
            List<Users> users = (List<Users>)DataReaderConverter(cmd, typeof(User)); 
    }

Json Serializing and Deserializing

Entityworker.Core has its own json handler. Let's ses how it works.

 using (var rep = new Repository())
 {
  var usersJsonString = rep.Get<User>().LoadChildren().Json();
  // Json() will exclude all properties that has JsonIgnore Attributes
  // Convert it Back
  // All JsonIgnore attributes will be loaded back from the database if Primary key exist within 
  // the json string
  ISqlQueryable<User> users = rep.FromJson<User>(usersJsonString).LoadChildren();
  List<User> userList = users.Execute();
  /// Or
  users.Save();
  user.SaveChanges()
 }

XML Serializing and Deserializing

Entityworker.Core has its own XML handler that doesn't require the object to be Serializable. Let's see how it works:

 using (var rep = new Repository())
 {
  var usersXmlString = rep.Get<User>().LoadChildren().Xml();
  // Xml() will exclude all properties that has XmlIgnore Attributes
  // Convert it Back
  // AllProperties with XmlIgnore attributes will be loaded back from 
  // the database if Primary key exist within the Xml string
  ISqlQueryable<User> users = rep.FromXml<User>(usersXmlString).LoadChildren();
  List<User> userList = users.Execute();
  /// Or
  users.Save();
  user.SaveChanges()
 }

Package Handler

Create Protected package that contains files and data for backup purpose or moving data from one location to another.

Note that this package can only be read by EntityWorker.Core.

    // Create class that inherit from PackageEntity
    public class Package : EntityWorker.Core.Object.Library.PackageEntity
    {
        // List of objects
        public override List<object> Data { get; set; }
        // List of files
        public override List<byte[]> Files { get; set; }
    }
    using (var rep = new Repository())
    {
        var users = rep.Get<User>().LoadChildren().Execute();
        // You could save the result to a file or even database
        byte[] package = rep.CreatePackage(new Package() { Data = users.Cast<object>().ToList() });
        // read the package, convert the byte[] to Package 
        var readerPackage = rep.GetPackage<Package>(package);
        Console.WriteLine((readerPackage.Data.Count <= 0 ? "Failed" : "Success"));
    }

Example of Many to Many Relationships

This is an example of how to use Many to Many Relationships in EntityWorker.Core. We will create two classes, Menus and Article.

    public class Menus
    {
        [PrimaryKey]
        public Guid? Id { get; set; }

        [NotNullable]

        public string DisplayName { get; set; }

        [ForeignKey(typeof(Menus))]
        public Guid? ParentId { get; set; }


        /// <summary>
        /// This is a list so the where sats will be 
        /// Select * from Menus where ParentId = Id
        /// the parentId in this list will be set to Id automatically and this list will 
        /// be children to the current Menus
        /// </summary>
        public List<Menus> Children { get; set; }

        [NotNullable]
        public string Uri { get; set; }

        public bool Publish { get; set; }

        public string Description { get; set; }
 
        /// <summary>
        /// This is optional if you want to search or include articles in your queries
        /// </summary>
        public List<Article> Articles { get; set;}
    }

    [Table("Articles")]
    public class Article
    {
        [PrimaryKey]
        public Guid? Id { get; set; }

        [NotNullable]
        public string ArticleName { get; set; }

        public bool Published { get; set; }

        /// Its important to se propertyName in Manytomany relations
        [ForeignKey( type: typeof(Menus), propertyName: "Menus")]
        public Guid MenusId { get; set; }

        // Reference to menus 
        [IndependentData]
        public Menus Menus { get; set; }

        [ForeignKey(typeof(Article))]
        public System.Guid? ArticleId { get; set; }

        // edited but not published yet
        public List<Article> ArticleTemp { get; set; }
    }
}

Procedure

Here is how you could use store procedure in entityworker

-- DB
CREATE PROCEDURE [dbo].[GetPerson]
                    @FirstName varchar(50)
                AS
                BEGIN
                    SET NOCOUNT ON;
                    select * from Person where FirstName like @FirstName +'%'
                END

  // Code
using (var rep = new Repository()) {
   var cmd = rep.GetStoredProcedure("GetPerson");
      rep.AddInnerParameter(cmd, "FirstName", "Admin");
      ISqlQueryable<Person> data = rep.DataReaderConverter<person>(cmd).LoadChildren();
      List<Person> persons = data.Execute();
      // Or custom Class
      List<Person> persons = (List<Person>)rep.DataReaderConverter(cmd, typeof(Person));
      }

Logger

Here is how you could get all entityworker logs

using EntityWorker.Core.Helper;
using EntityWorker.Core.Interface;
using System;
using System.IO;
// create a class that inherit from EntityWorker.Core.Interface.ILog 
public class Logger : EntityWorker.Core.Interface.Ilog
{
        private string logIdentifier = $"{DateTime.Now.ToString("yyyy-MM-dd")} Ilog.txt";
        private string logPath = AppDomain.CurrentDomain.BaseDirectory;
        public Logger()
        {
            DirectoryInfo dinfo = new DirectoryInfo(logPath);
            var files = dinfo.GetFiles("*.txt");
            foreach (FileInfo file in files)
            {

                var name = file.Name.Split(' ')[0];
                if (name.ConvertValue<DateTime?>().HasValue && file.Name.Contains("Ilog"))
                {

                    if (name.ConvertValue<DateTime>().Date == DateTime.Now.Date)
                    {
                        logIdentifier = file.Name;
                        break;
                    }
                }
            }

            logIdentifier = Path.Combine(logPath, logIdentifier);
            File.Open(logIdentifier, FileMode.OpenOrCreate).Close();

        }

        public void Dispose()
        {
        }

        public void Error(Exception exception)
        {
            lock (this){
             using (StreamWriter stream = new StreamWriter(logIdentifier, append:true))
                stream.WriteLine($"{DateTime.Now} - {exception.Message}");
                }
        }

        public void Info(string message, object infoData)
        {
#if DEBUG
           lock (this){
            using (StreamWriter stream = new StreamWriter(logIdentifier, append:true))
                stream.WriteLine($"{DateTime.Now} - {message} - \n {infoData}");
                }
#endif
        }
    }

}
// now that we created the logg class we can now tell Entityworker to begin logging
// in GlobalConfiguration we assign the new created class to ILog. only exist in nuget => 2.0.0
 GlobalConfiguration.Log = new Logger();
 // thats all 

Attributes

/// <summary>
/// Save the property as Json object in the database
/// For the moment those values cant be searched by linq.
/// you will have to use row sql(JSON_VALUE) to seach them
/// </summary>
[JsonDocument]

/// <summary>
/// Save the property as xml object in the database
/// For the moment those values cant be searched by linq.
/// </summary>
[XmlDocument]

/// <summary>
/// Use this when you have types that are unknown like interface which it can takes more than one type
/// </summary>
[KnownType]

/// <summary>
/// Assign a different database type for the property. 
/// Attributes Stringify, DataEncode and ToBase64String will override this attribute. 
/// </summary>
/// <param name="dataType">The database type ex nvarchar(4000)</param>
/// <param name="dataBaseTypes">(Optional)null for all providers</param>
[ColumnType]

/// <summary> /// Ignore serializing and deserializing property 
/// when deserializing using entityWorker.Json all Xml ignored properties will be loaded back 
/// from the database as long as primary key exist within the xml string. 
/// </summary> 
[XmlIgnore]

/// <summary>
/// Ignore serializing and deserializing property
/// when deserializing using entityWorker.Json all Json ignored properties will be loaded back
/// from the database as long as primary key exist within the json string.
/// </summary>
[JsonIgnore]

/// <summary>
/// This indicates that the prop will not be saved to the database.
/// </summary>
[ExcludeFromAbstract]

/// <summary>
/// Will be saved to the database as base64string 
/// and converted back to its original string when its read
/// </summary>
[ToBase64String]

/// <summary>
/// Property is a ForeignKey in the database.
/// </summary>
[ForeignKey]

/// <summary>
/// This attr will tell EntityWorker.Core abstract 
/// to not auto Delete this object when deleting parent,
/// it will however try to create new or update  
/// </summary>
[IndependentData]

/// This attribute is most used on properties with type string
/// in-case we don't want them to be nullable
/// </summary>
[NotNullable]

/// <summary>
/// Property is a primary key
/// PrimaryId could be System.Guid or number eg long and int
/// </summary>
[PrimaryKey]

/// <summary>
/// Have different Name for the property in the database
/// </summary>
[PropertyName]

/// <summary>
/// Define class rule by adding this attribute
/// ruleType must inherit from IDbRuleTrigger
/// ex UserRule : IDbRuleTrigger<User/>
/// </summary>
/// <param name="ruleType"></param>
[Rule]

/// <summary>
/// Save the property as string in the database
/// mostly used when we don't want an enum to be saved as integer in the database
/// </summary>
[Stringify]

/// <summary>
/// Define different name for the table
/// </summary>
[Table]

/// <summary>
/// Assign Default Value when Property is null
/// </summary>
[DefaultOnEmpty]

 /// <summary>
 /// Choose to protect the data in the database so no one could read or decrypt 
 /// it without knowing the key. Those data will be decrypted when you read it from the database.
 /// LinqToSql will also Encode the value when you select a Search those columns.
 /// <Example>
 /// .Where(x=> x.UserName == "test" || x.UserName.StartWith("a") ) 
 /// Will be equal to 
 /// .Where(x=> x.UserName == Encode("test") || x.UserName.StartWith(Encode("a")))
 /// So no need to worry when you search those column in the dataBase 
 /// you could Encode Address, bankAccount information and so on with ease.
 /// entityWorker uses a default key to both encode and decode the data but you could
 /// also change it.
 /// </Example>
 /// </summary>
[DataEncode]

Points of Interest

Please feel free to write what you think, and also check the project site to see the full documentation.