Ehsan Ghanbari

Experience, DotNet, Solutions

Why you can't project mapped entity in entity framework

If you have worked with entity framework for a long time with and LINQ of course, you probably have seen the Error: The entity cannot be constructed in a LINQ to Entities query. So when it happens and why? Think about the following piece of code:

public IQueryable<Lesson> GetAllLessons(int termId)

{

    return from p in db.Lessons

           where p.termId == termId

           select new Lesson{ Name = p.Name};

}

Note: a Lesson is an Object that has been mapped via the entity framework

Now if you run the code you will see the error we talked about, you should use DTO objects instead of the mapped object:

public IQueryable<LessonDTO> GetAllLessons(int termId)

{

    return from p in db.Lessons

           where p.termId == termId

           select new LessonDTO { Name = p.Name};

}

So what’s happening in the back? As you know the mapped entities in EF represent database tables. If you project onto a mapped entity, you partially load an entity, which is not a valid state. Entity Framework won't have any clue how to handle an update of the entity. So if EF would project the mapped object, you would risk losing some of your data in the Database, therefore it is not allowed to partially load entities.



Using entity framework Code First migration

While using Entity Framework, definitely you will need to migrate your database! In SQL server, you need to write a query to do that. Entity framework is doing the same task but in your application via C# code. Create a simple console application and install entity framework on it and C# class with the following properties:

 

 public class ClassRoom

    {

        public int Id { get; set; }

        public string Title { get; set; }

        public DateTime BeginDate { get; set; }

    }

 

Map the above class via EntityFramework:

 

class ClassRoomMapping : EntityTypeConfiguration<ClassRoom>
    {
        public ClassRoomMapping()
        {
            ToTable("ClassRoom", "MySchema");
            Property(c => c.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            Property(c => c.Title).HasColumnType(SqlDbType.NVarChar.ToString()).HasMaxLength(200);
            Property(c => c.BeginDate).HasColumnType(SqlDbType.DateTime.ToString());
        }
    }

 

Now let's create our Context Class:

 

 class OurContextClass : DbContext
    {
        public OurContextClass() : base("SampleConnection")
        {
          
        }

        public DbSet<ClassRoom> ClassRooms { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new ClassRoomMapping());
            base.OnModelCreating(modelBuilder);
        }
    }

 

 

As you see, we created a property to access the ClassRoom object via DbSet and we defined the ClassRoomMapping in modelBuilder configuration to let entity framework read from our custom mapping. Now add connection string into the app.config file: 

 

 <connectionStrings>
    <add name="SampleConnection" connectionString="Data Source=DESKTOP-VIsdsJ30;Initial Catalog=SampleDataBase;Integrated Security=True" providerName="System.Data.SqlClient"/>
  </connectionStrings>

 

Now in the Main method call the ClassRoom to create the database in SQL server:

 

 static void Main(string[] args)

        {

            OurContextClass occ = new OurContextClass();

            occ.ClassRooms.Add(new ClassRoom { Id = 1, Title = "First One", BeginDate = DateTime.Now });

            occ.SaveChanges();

        }

 

By running the application the DataBase will be created and you will have a table and two table:

  1. ClassRoom
  2. __MigrationHistory

The second table will contain you migration histories. Now For enabling the migration, first you need to execute the following command in Console Package Manager: 

enable-migrations

If you do it successfully, a configuration class will be added inside a folder named Migrations in your project with the following definition:

 

 internal sealed class Configuration : DbMigrationsConfiguration<ConsoleApp1.OurContextClass>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = false;
            ContextKey = "ConsoleApp1.OurContextClass";
        }

        protected override void Seed(ConsoleApp1.OurContextClass context)
        {
            //  This method will be called after migrating to the latest version.

            //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
            //  to avoid creating duplicate seed data.
        }
    }

 

Now we want to add some extra field too, the new ClassRoom and its mapping would be like this:

 

 public class ClassRoom
    {
        public int Id { get; set; }

        public string Title { get; set; }

        public DateTime BeginDate { get; set; }

        public int ClassNumeber { get; set; }
    }

    class ClassRoomMapping : EntityTypeConfiguration<ClassRoom>
    {
        public ClassRoomMapping()
        {
            ToTable("ClassRoom", "MySchema");
            Property(c => c.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            Property(c => c.Title).HasColumnType(SqlDbType.NVarChar.ToString()).HasMaxLength(200);
            Property(c => c.BeginDate).HasColumnType(SqlDbType.DateTime.ToString());
            Property(c => c.ClassNumeber).HasColumnType(SqlDbType.Int.ToString()).IsRequired();
        }
    }

 

The ClassNumber is new the field and notices that it's required and we should initialize it while migrating, execute the following command in Console Package Manager:

PM> Add-Migration AddClassNumberField

Add-Migration is the command that adds the Migration class into Migrations folder with the named you to give it:

 

 public partial class AddClassNumberField : DbMigration
    {
        public override void Up()
        {
            AddColumn("MySchema.ClassRoom", "ClassNumeber", c => c.Int(nullable: false, defaultValue: 1));
        }

        public override void Down()
        {
            DropColumn("MySchema.ClassRoom", "ClassNumeber");
        }
    }

 

As you can see I added the defaultValue because it's the required field. And finally, you should run the Update-Database command to update the database with the new field. For summarizing the command we used:

 

 



Repository Pattern practice in data access layer via entity framework

To read about definitions of Repository patter refer to here or here and then let's begin! I'm gonna to show the using of repository pattern with entityframework in Data access layer. Sorry about the example because it's about product , category , brand , blah blah again! First of all create an interface called IAggregateRoot

  1.    public interface IAggregateRoot
  2.     {
  3.     }

It has no any member , you will find out what's that for later in this article , then create a generic type interface for introducing most common operation used in enterprise DAL such as CRUD …

  1.  public interface IRepository<T>
  2.     {
  3.         IEnumerable<T> GetAll();
  4.         T FindBy(params Object[] keyValues);
  5.         void Add(T entity);
  6.         void Update(T entity);
  7.         void Delete(T entity);
  8.         void SaveChanges();
  9.     }

Now create the Product class and derive it from or so called implement the IAggregateRoot member for it!

  1.     public class Product : IAggregateRoot
  2.     {
  3.         public Guid Id { get; set; }
  4.         public string Name { get; set; }
  5.         public decimal Price { get; set; }
  6.         public string Description { get; set; }
  7.         public DateTime CreationTime { get; set; }
  8.         public string Picture { get; set; }
  9.     }

And now create the IProductRepository interface like this :

  1.     public interface IProductRepository : IRepository<Product>
  2.     {
  3.     }

I have to notice that all we have done above are in Model layer if you are using layered application , now refer to your DAL or Repository layer and create your DbContextClass:

  1.    public class YourProjectContext : DbContext
  2.     {
  3.           public DbSet<Product> Product { get; set; }
  4.     }

And now create the GenericRepoitory class like this :

  1.     public class Repository<T> : IRepository<T> where T : class, IAggregateRoot
  2.     {
  3.         private readonly DbSet<T> _entitySet;
  4.         private readonly YourProjectContext _yourProjectContext ;
  5.         public Repository(YourProjectContext yourProjectContext )
  6.         {
  7.             _yourProjectContext = yourProjectContext ;
  8.             _entitySet = yourProjectContext .Set<T>();
  9.         }
  10.         public IEnumerable<T> GetAll()
  11.         {
  12.             return _entitySet;
  13.         }
  14.         public T FindBy(params Object[] keyValues)
  15.         {
  16.             return _entitySet.Find(keyValues);
  17.         }
  18.         public void Add(T entity)
  19.         {
  20.             _entitySet.Add(entity);
  21.         }
  22.         public void Update(T entity)
  23.         {
  24.             _entitySet.Attach(entity);
  25.            _yourProjectContext.Entry(entity).State=EntityState.Modified;
  26.         }
  27.         public void Delete(T entity)
  28.         {
  29.             var e = _yourProjectContext.Entry(entity);
  30.             if (e.State == EntityState.Detached)
  31.             {
  32.                 _yourProjectContext.Set<T>().Attach(entity);
  33.                 e = _yourProjectContext.Entry(entity);
  34.             }
  35.             e.State=EntityState.Deleted;   
  36.         }
  37.         public void SaveChanges()
  38.         {
  39.             _yourProjectContext.SaveChanges();
  40.         }
  41.     }

 you have all members of IRepository<T> implemented in this class , Now create the ProductRepository class like this :

  1.     public class ProductRepository :Repository<Product>,IProductRepository
  2.     {
  3.       public ProductRepository(YourProjectContext yourProjectContext)
  4.             : base(yourProjectContext)
  5.         {
  6.         }
  7.     }

Now by calling IProductRepository interface in service or application layer you have full access to whole generic method of Repository and also for all members of ProductRepository , for example spouse this service class

  1.     public class ProductService : IProductService
  2.     {
  3.         private readonly IProductRepository _productRepository;
  4.         public ProductService(IProductRepository productRepository)
  5.         {
  6.             _productRepository = productRepository;
  7.         }
  8.      }

You can access the repository members by _productRepository readonly field.

If want to create an specific method in ProductRepository such as FindAllProductsByCategory  you have to first define it in IProductRepository :

  1.     public interface IProductRepository : IRepository<Product>
  2.     {
  3.         IEnumerable<Product> FindAllProductsByCategory(Guid categoryId);
  4.     }

And Then in ProductRepository you have to implement the interface member

  1.     public class ProductRepository :Repository<Product>,IProductRepository
  2.     {
  3.         private readonly StatosContext _statosContext;
  4.         public ProductRepository(StatosContext statosContext)
  5.             : base(statosContext)
  6.         {
  7.             _statosContext = statosContext;
  8.         }
  9.         public IEnumerable<Product> FindAllProductsByCategory(Guid categoryId)
  10.         {
  11.             var query =
  12.                 _statosContext.Product.Where(p => p.Category.Id == categoryId).OrderByDescending(p => p.CreationTime);
  13.             return query.ToList();
  14.         }

Thanks for reading!

Question!  is there any need to use unitOfWork pattern when you using repository pattern with entity framework ??

Basically NOT because entityframework supports the UnitOfWork pattern and in other words entityfrmawork code first has been built based on UnitOfwork pattern and adding unitOfWork again is  typically redundant (because of using DbContext) , but if want to have more control over disposal of context you can write your own IDbContextFactory and implement the unitOfwork pattern ! Cheers



Nhibernate vs Entity Framework

Entity framework and nhibernate are two famous ORM for developers. Both of them have been used in many projects and each of them has it's own cons and pros and common features as well. I'm gonna list some of them here. I personally like both of them and experienced in several projects. both Frameworks are used for processing relational data to domain specific objects. Both NHibernate and the Entity Framework provide the capabilities required to implement an ORM using the Model First approach.

 

How they work?

nHibernate: The ISessionFactory holds model and metadata that is tied to a particular database and to the settings that came from the Configuration object. Finally, you create instances of ISession from the ISessionFactory.

Entity Framework: the ObjectContext or DbContext  holds the configuration, model and acts as the Unit of Work, holding references to all of the known entity instances. 

 

Migrations

Entity Framework: Built-in schema migration support since version 6 and supports seeding of the initial database

NHibernate: Supports only initial schema generation

 

Code First Mapping?

Both NHibernate and Entity Framework support the Code First Mapping feature.

 

Database Support?

NHibernate: SQL Server, SQL Server Compact, SQL Server Azure, Oracle, DB2, PostgreSQL, MySQL, Firebird, SQLLite, Informix

Entity Framework: Entity famework only supports SQL Server (but by using some extensions you can use Oracle and MySQL too).

 

Caching?

Both of them support for first level cache. Nhibernate also support for second level caching.

 

References:

I tried to list the features I've worked with and for complete information you can see the following post:

https://weblogs.asp.net/ricardoperes/differences-between-nhibernate-and-entity-framework



Getting started with Entity Framework

 What is Entity framework !?

MSDN answers to this question :"Entity Framework is an Object Relational Mapper (ORM). It basically generates business objects and entities according to the database tables provides ability to do CRUD operations , relations and etc."

And also about wikipedia's definition : "The Entity Framework is a set of technologies in ADO.NET that support the development of data-oriented software applications. Architects and developers of data-oriented applications have struggled with the need to achieve two very different objectives. They must model the entities, relationships, and logic of the business problems they are solving, and they must also work with the data engines used to store and retrieve the data. The data may span multiple storage systems, each with its own protocols; even applications that work with a single storage system must balance the requirements of the storage system against the requirements of writing efficient and maintainable application code. The Entity Framework enables developers to work with data in the form of domain-specific objects and properties, such as customers and customer addresses, without having to concern themselves with the underlying database tables and columns where this data is stored. With the Entity Framework, developers can work at a higher level of abstraction when they deal with data, and can create and maintain data-oriented applications with less code than in traditional applications. Because the Entity Framework is a component of the .NET Framework, Entity Framework applications can run on any computer on which the .NET Framework starting with version 3.5 SP1 is installed."

 

Forget about definitions and just Make your hands dirty to learn!

I just Use the code first feature of entity framework , it means  creating our model and then creating  database based on Model , in other words you can use C# or VB class to generate database , and also you can map your model to an existing database (if your database has been designed earlier).

there are lots of reason why I'm interested in to use Code first .in entity framework you can customize persistence easily , it is better when you are going to write Unit test , and more about persistence of Ignorance (I'm not going to talk about this one in current article)

Note :You can use code first feature via at least version 4.1 or later .

I'm using Asp.net MVC in this article ,Create a class with this definition :

  1.     public class Account
  2.      {
  3.         public int Id { get; set; }
  4.         public string Name { get; set; }
  5.         public decimal Balance { get; set; }
  6.       }

Now create your context class:

  1.  public class ContextClass : DbContext
  2.     {
  3.        public ContextClass(string connectionString) :base(connectionString)
  4.         {
  5.         }
  6.        public DbSet<Account> Accounts { get; set; }
  7.     }

And in Web.config  :

  1. <connectionStrings>
  2.     <add name="ContextClass" connectionString="Data Source=EHSAN-PC;Initial Catalog=BlogArticle;Integrated Security=SSPI" providerName="System.Data.SqlClient" />
  3.  </connectionStrings>

Now create a simple controller to calling the context class to finalize the creating the database in your management studio

  1.     public class EntityFrameworkController : Controller
  2.     {
  3.         private ContextClass _contextClass;
  4.  
  5.         public EntityFrameworkController()
  6.         {
  7.             _contextClass = new ContextClass();
  8.         }
  9.       
  10.         public ActionResult Index()
  11.         {
  12.             Account account= _contextClass.Accounts.First();
  13.             return View(account);
  14.         }     
  15.     }

Just run the project and request this Index action In Url , you can also create some fake data by hard coding in the action .If you go to sql management studio , you have created the BlogArticle database successfully !So ,database tables  built by DbContext based on model properties by running the project , now if we make any change in our domain model , databebase tables and our model won't be compatible , to solve this , change  your ContextClass like this :

  1.     public class ContextClass : DbContext
  2.     {
  3.         public ContextClass()
  4.         {
  5.             if (this.Database.Exists() && !this.Database.CompatibleWithModel(false))
  6.                 this.Database.Delete();
  7.             if (!this.Database.Exists()) this.Database.Create();
  8.         }
  9.         protected override void OnModelCreating(DbModelBuilder modelBuilder)
  10.         {
  11.             base.OnModelCreating(modelBuilder);
  12.         }
  13.         public DbSet<Account> Accounts { get; set; }

Initializing the database :

Entity framework provides to initialize data base with fake data , to do this add a new class to project with this definition:

  1.     public class InitializerClass :DropCreateDatabaseIfModelChanges<ContextClass>
  2.     {
  3.         protected override void Seed(ContextClass context)
  4.         {
  5.             new List<Account>
  6.                 {
  7.                     new Account {Id = 1, Balance = 34523, Name = "Mark"}
  8.                 };
  9.             base.Seed(context);
  10.         }
  11.     }

And also you  have to add this in global.asax

  1.         protected void Application_Start()
  2.         {
  3.             AreaRegistration.RegisterAllAreas();
  4.  
  5.             Database.SetInitializer(new InitializerClass());
  6.  
  7.          }

Fluent Configuration

you can define any kind of database field by EF built in mapping feature , for example about mapping the account 

  1.  public class AccountMapping : EntityTypeConfiguration<Account>
  2.     {
  3.         public AccountMapping()
  4.         {
  5.             this.ToTable("Account");
  6.             this.HasKey(a => a.Id);
  7.             this.Property(a => a.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
  8.             this.Property(a => a.Name).IsOptional();
  9.             this.Property(a => a.Balance).IsRequired();
  10.          }
  11.     }

 and then you have to add this line of code to your context class in OnModelCreating

  1.   modelBuilder.Configurations.Add(new AccountMapping());

 

RelationShips in EF  

You can define one-to-one , one-to-many , many-to-one and many-to-many mapping  by entity framework , lets implement them.Add a new class to your Model

  1.  public class AccountOwner
  2.     {
  3.         public int Id { get; set; }
  4.         public string Name { get; set; }
  5.         public string  Email { get; set; }
  6.         public virtual Account Account { get; set; }
  7.     }

And change the Account class like this :

  1.  public class Account
  2.     {
  3.         public int Id { get; set; }
  4.         public string Name { get; set; }
  5.         public decimal Balance { get; set; }
  6.         public virtual AccountOwner AccountOwner { get; set; }
  7.     }

And Change the mapping class of Account like this :

  1.  public class AccountMapping : EntityTypeConfiguration<Account>
  2.     {
  3.         public AccountMapping()
  4.         {
  5.             this.ToTable("Account");
  6.             this.HasKey(a => a.Id);
  7.             this.Property(a => a.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
  8.             this.Property(a => a.Name).IsOptional();
  9.             this.Property(a => a.Balance).IsRequired();
  10.             this.HasRequired(a=>a.AccountOwner).WithOptional().WillCascadeOnDelete();
  11.         }
  12.     }

And about mapping class of AccountOwner :

  1.  public class AccountOwnerMapping : EntityTypeConfiguration<AccountOwner>
  2.     {
  3.         public AccountOwnerMapping()
  4.         {
  5.             this.ToTable("AccountOwner");
  6.             this.HasKey(a => a.Id);
  7.             this.Property(a => a.Id).HasColumnName("AccountOwnerId").HasDatabaseGeneratedOption(
  8.                 DatabaseGeneratedOption.Identity);
  9.             this.Property(a => a.Email).HasMaxLength(100).IsRequired();
  10.         }
  11.     }

 and in Order to have your mapping class influence in database you should change the ContextClass :

  1.  public class ContextClass : DbContext
  2.     {
  3.         public ContextClass()
  4.         {
  5.             if (this.Database.Exists() && !this.Database.CompatibleWithModel(false))
  6.                 this.Database.Delete();
  7.             if (!this.Database.Exists()) this.Database.Create();
  8.         }
  9.         protected override void OnModelCreating(DbModelBuilder modelBuilder)
  10.         {
  11.             base.OnModelCreating(modelBuilder);
  12.  
  13.             modelBuilder.Configurations.Add(new AccountMapping());
  14.             modelBuilder.Configurations.Add(new AccountOwnerMapping());
  15.         }
  16.  
  17.         public DbSet<Account> Accounts { get; set; }
  18.         public DbSet<AccountOwner> AccountOwners { get; set; }

one-to-many 

create these classes 

  1.  public class Blog
  2.     {
  3.         public int Id { get; set; }
  4.         public String Title { get; set; }
  5.         public string Text { get; set; }
  6.         public virtual  ICollection<Category> Categories { get; set; }
  7.     }
  8.  
  9.  public class Category
  10.     {
  11.        public int Id { get; set; }
  12.        public string Name { get; set; }
  13.        public virtual Blog Blog { get; set; }
  14.     }

Now Add two Mapping class for this Models:

  1.  public class CategoryMapping :EntityTypeConfiguration<Category>
  2.     {
  3.         public CategoryMapping()
  4.         {
  5.             this.ToTable("Category");
  6.             this.HasKey(c => c.Id);
  7.             this.Property(c => c.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
  8.             this.Property(c => c.Name);
  9.         }
  10.     }
  11.  
  12.   public class BlogMapping : EntityTypeConfiguration<Blog>
  13.     {
  14.         public BlogMapping()
  15.         {
  16.             this.ToTable("Blog");
  17.             this.HasKey(b => b.Id);
  18.             this.Property(b => b.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
  19.             this.Property(b => b.Title).IsRequired();
  20.             this.Property(b => b.Text).HasColumnType("text").IsMaxLength().IsOptional().HasColumnName("Text");
  21.             this.HasMany(b=>b.Categories).WithRequired(b=>b.Blog).WillCascadeOnDelete();
  22.         }
  23.     }

And just like one to one sample add the blog and category configurations to Context class

  1.     public class ContextClass : DbContext
  2.     {
  3.         public ContextClass()
  4.         {
  5.             if (this.Database.Exists() && !this.Database.CompatibleWithModel(false))
  6.                 this.Database.Delete();
  7.             if (!this.Database.Exists()) this.Database.Create();
  8.         }
  9.         protected override void OnModelCreating(DbModelBuilder modelBuilder)
  10.         {
  11.             base.OnModelCreating(modelBuilder);
  12.  
  13.             modelBuilder.Configurations.Add(new AccountMapping());
  14.             modelBuilder.Configurations.Add(new AccountOwnerMapping());
  15.             modelBuilder.Configurations.Add(new CategoryMapping());
  16.             modelBuilder.Configurations.Add(new BLogMapping());
  17.         }
  18.         public DbSet<Account> Accounts { get; set; }
  19.         public DbSet<AccountOwner> AccountOwners { get; set; }
  20.         public DbSet<Blog> Blogs { get; set; }
  21.         public DbSet<Category> Categories { get; set; }
  22.    }

About many to many , create Product class with this definition

  1.     public class Product
  2.     {
  3.         public int Id { get; set; }
  4.         public string ProductName { get; set; }
  5.         public decimal Amount { get; set; }
  6.     }

And Customer class :

  1.   public class Customer
  2.     {
  3.         public int Id { get; set; }
  4.         public string CustomerName { get; set; }
  5.     }

And finally ProductCustomer :

  1.  public class ProductCustomer
  2.     {
  3.         public virtual Product ProductId { get; set; }
  4.         public virtual Customer CustomerId { get; set; }
  5.     }

I thinks You know how to map Product and Customer , Don’t you !?

you could also remove the ProductCustomer Class and change the Customer and Product class to achieve the many to many mapping 

  1. public class Customer
  2.     public int Id { get; set; }
  3.     public string CustomerName { get; set; }
  4.     public virtual List<Product> Products {get; set;}
  5.  }
  6.  
  7.  public class Product
  8.  {
  9.     public int Id { get; set; }
  10.     public string ProductName { get; set; }
  11.     public decimal Amount { get; set; }
  12.     public virtual List<Customer> Customers {get;set;}
  13.  }
  14.  

 happy coding!



About Me

Ehsan Ghanbari

Hi! my name is Ehsan. I'm a developer, passionate technologist, and fan of clean code. I'm interested in enterprise and large-scale applications architecture and design patterns. I'm spending a lot of my time on software architecture. Since 2008, I've been as a developer for companies and organizations and I've been focusing on Microsoft ecosystem all the time. During the Read More

Post Tags
Pending Blog Posts
Strategic design
Factory Pattern
time out pattern in ajax
Selectors in Jquery
using Log4net in asp.net MVC4
Redis as a cache server
How to use PagedList In asp.net MVC
Multiple submit buttons in asp.net MVC
Domain driven design VS model driven architecture
What's the DDD-lite?