Tips and Tricks in a world of Mix

Posts tagged ‘Entity Framework’

Asp.Net MVC5 build a website DB mdf(local) first

I’ve created some time ago a local MDF file with a bunch of tables for use. So now I want to get back to it and implement it through the edmx Ado.Net template


After I’ve added the mdf local db file to the App_DATA folder I can now connect it to the automatic created scheme like this –

untitled-picture2I will go through the wizard and choose the objects of tables to recreate at the edmx model

At the end I will have the objects created with C# from DB for me by this automatic process.


Rebuild your solution to make everything checked and ready to go!

I am adding a controller


By description I want the scaffold to build everything for me – including the view representation of my objects.


Choosing the entity to render


After a lot of thinking on his part I am getting new files in the solution folders structure


That’s it.

Modell .edmx 
Modell .Context.cs 
Modell Designer.cs 
Modell .edmx.diagram 
Modell .tt 
A dress.cs 
Events EventDetaiIs.cs 
Users UserDetaiIs.cs 

This slideshow requires JavaScript.

SPA 3–JOHN PAPA– Data Models, Entity Framework, and Data Patterns of the Code Camper SPA

You can check out the series of the posts on the subject

<—SPA 2 Technologies&Patterns 

 SPA 4 Surfacing JSON Data with ASP.NET Web API—>

Data Layer Technologies


  • SQL Server CE
  • ORM                   Using Entity Framework Code First (version 5)

                  Data stored and save to its own context (DBContext)




Models are simple classes without any additional references.


Creating a Model

Data containers – define your data and vehicle for you data

Domain objects

POCO– plain old class(CLR) object

Don’t have additional references – Independent and stand alone objects

makes them ease to  pass around across domain –

through the EF(fills the POCO up)  –> Repositories –> UoW –> WebAPI


So we can define a SessionBrief object to contain a short minimum data to revive the program when fails and some more robust and full object to init the program fully after the primamry reviving. All that through inj=heritance




Model Diagram




You can set [Key] attribute for EF to understand the key property



Defines relations between Models And Database

Stores objects and changes in its context (in memory)


Defines Sets of Data – DBSet<T>

Configuring the DBContext –




Defining Conventions with the DBContext


  • One to Many connection

Connecting unique Speaker to multiple sessions

Doing it through list property on a persons POCO model.

(in my opinion can be added to speaker class instead)


   1: public class SessionConfiguration : EntityTypeConfiguration<Session>

   2:   {

   3:       public SessionConfiguration()

   4:       {

   5:           // Session has 1 Speaker, Speaker has many Session records

   6:           HasRequired(s => s.Speaker)

   7:              .WithMany(p => p.SpeakerSessions)

   8:              .HasForeignKey(s => s.SpeakerId);

   9:       }

  10:   }

  11: }


  • A Many-to-Many example – Sessions to  Persons through Attendance

There can be few persons attending few sessions – the key


   1: public class AttendanceConfiguration : EntityTypeConfiguration<Attendance>

   2:    {

   3:        public AttendanceConfiguration()

   4:        {

   5:            // Attendance has a composite key: SessionId and PersonId

   6:            HasKey(a => new { a.SessionId, a.PersonId });


   8:            // Attendance has 1 Session, Sessions have many Attendance records

   9:            HasRequired(a => a.Session)

  10:                .WithMany(s => s.AttendanceList)

  11:                .HasForeignKey(a => a.SessionId)

  12:                .WillCascadeOnDelete(false);


  14:            // Attendance has 1 Person, Persons have many Attendance records

  15:            HasRequired(a => a.Person)

  16:                .WithMany(p => p.AttendanceList)

  17:                .HasForeignKey(a => a.PersonId)

  18:                .WillCascadeOnDelete(false);

  19:        }

  20:    }



the key is to have a connecting model between them two

   1: public class Attendance

   2:    {

   3:        public int PersonId { get; set; }

   4:        public Person Person { get; set; }


   6:        public int SessionId { get; set; }

   7:        public Session Session { get; set; }


   9:        /// <summary>Get and set the person's rating of the session from 1-5 (0=not rated).</summary>

  10:        [Range(0,5)]

  11:        public int Rating { get; set; }


  13:        /// <summary>Get and set the person's session evaluation text.</summary>

  14:        public string Text { get; set; }

  15:    }

Here we can see that there are two properties of ID type – one for session and the second for person.

So through the connection of attendance activity we are connecting multiple persons to multiple sessions.


All that is connected in DBContext


  • Establish Seed Data – creating basic data if the db doesn’t exist (good for development and debugging on unattached environments) through SetInitializer of Database
  • Define conventions – plural/singular table names
  • Adding the configurations for Model Relationships as described before


   1: public class CodeCamperDbContext : DbContext

   2:    {

   3:        // ToDo: Move Initializer to Global.asax; don't want dependence on SampleData

   4:        static CodeCamperDbContext()

   5:        {

   6:            Database.SetInitializer(new CodeCamperDatabaseInitializer());

   7:        }


   9:        public CodeCamperDbContext()

  10:            : base(nameOrConnectionString: "CodeCamper") { }


  12:        protected override void OnModelCreating(DbModelBuilder modelBuilder)

  13:        {

  14:            // Use singular table names

  15:            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();


  17:            modelBuilder.Configurations.Add(new SessionConfiguration());

  18:            modelBuilder.Configurations.Add(new AttendanceConfiguration());

  19:        }


  21:        public DbSet<Person> Persons { get; set; }

  22:        public DbSet<Session> Sessions { get; set; }

  23:        public DbSet<Attendance> Attendance { get; set; }


  25:        // Lookup Lists

  26:        public DbSet<Room> Rooms { get; set; }

  27:        public DbSet<TimeSlot> TimeSlots { get; set; }

  28:        public DbSet<Track> Tracks { get; set; }

  29:    }



Repository Pattern – Why?

Maintenance  – Data access code is easy to find , debug an d change

Code Reuse – brake down the functionality , concentrate the code

Focused on getting and saving data

Consistent API

Single Responsibility Principle (SRP) pattern – have each of your classes to concentrate on one thing only


Make a simple consistent API matched for any type


   1: public interface IRepository<T> where T : class

   2:    {

   3:        IQueryable<T> GetAll();

   4:        T GetById(int id);

   5:        void Add(T entity);

   6:        void Update(T entity);

   7:        void Delete(T entity);

   8:        void Delete(int id);

   9:    }


EFRepository –

  • Generalizing a set of ordinary functionality for each model , so we shouldn’t write it specific for each of them.
  • It implements IRepository – It allows you to abstract the types in additional layer, even though it’s not a necessary. Allows to use LINQ queries.
  • And we’ll have additional Interfaces for each Model when it’ll be a specific functionality per Model, or a DTO arrangement that will rearrange and minify the amount of data that will be passed.
  • We also can inherit from EFRepository but override the functions to match the specific scenario for our Model.
  • The EFRepository encapsulates the DBContext   and exposes functionality to deal with it.
  • Interactions with DBContext  through the EF  abilities.


Uow – Unit of Work –




Through the Factory Pattern it builds the wright repositories concluded by the given type.

   1: public class CodeCamperUow : ICodeCamperUow, IDisposable

   2: {

   3:     // Code Camper repositories

   4:     // You can add here your types easily

   5:     public IRepository<Room> Rooms { get { return GetStandardRepo<Room>(); } }


   7:     /// Save pending changes to the database

   8:     public void Commit()

   9:     {

  10:         //System.Diagnostics.Debug.WriteLine("Committed");

  11:         DbContext.SaveChanges();

  12:     }


  14: }



  17: //and you'll call the UoW from Controllers like this :



  20: public class LookupsController : ApiControllerBase

  21:  {

  22:      public LookupsController(ICodeCamperUow uow)

  23:      {

  24:          Uow = uow;

  25:      }


  27:      // GET: api/lookups/rooms

  28:      [ActionName("rooms")]

  29:      public IEnumerable<Room> GetRooms()

  30:      {

  31:          return Uow.Rooms.GetAll().OrderBy(r => r.Name);

  32:      }

  33: ...


  35: }


So you can organize and rearrange the repositories inside the UoW and that’s the methods that will be called from the WebAPI layer .


You can check out the series of the posts on the subject

<—SPA 2 Technologies&Patterns 

 SPA 4 Surfacing JSON Data with ASP.NET Web API—>

Synonym of Oracle is missing– Is the Oracle False messiah ?!

After installing the ODT and updating it after to work with VS2012 to ODAC with Oracle Developer Tools for Visual Studio on the same Oracle Client Home

I was able to create the EDMX model , but the synonyms stayed hidden to me on the wizard of the VS (by the way at VS 2010 also) .

The connection works perfect and gets me the regular tables , views and stored procedures .

The trouble is with the rest .




From my approach to Oracle I’ve got a couple of links and a statement that it’s Microsoft’s product that doesn’t support the import at the initialization wizard.

The links provided –

1) Make Entity Framework Designer support Synonyms  – we are not Code First – the schema exists and running

2)A problem to view synonym of Oracle in the Entity Data Model Designer – can’t see the synonyms , not with dba not with any user ..


Any ideas will be appreciated!

Tag Cloud

%d bloggers like this: