none: 11/01/2013 - 12/01/2013

This post presents my ef notes while studying http://www.entityframeworktutorial.net/.

The following table is version history of EF.

Actually, EF 6.0 has been published recently..

EF is the ORM solution of Microsoft. Alternative modelling techniques with EF: code first, model first, database first.

Overall architecture of EF:

Conceptual Model = model classes + their relationships (independent from database tables)
Storage Model = database tables
Mapping maps conceptual and storage models.

Linq To Entities => Query language for object model. Return type belongs to conceptual model.

To install EF on Visual Studio, Tools → Library Package Manager → Package Manager Console can be used.

(EDM Designer ~~~ conceptual model)
Changes to the conceptual model should be mapped to the storage model.
The XML view of the .edmx file clearly separates between conceptual, storage models and mapping.
EntityContainer wraps EntitySet wraps EntityType in the xml.
AssociationSets define relations.

.edmx.cs regions: Context (Object Context) and Entities.

"An instance of the ObjectContext class encapsulates the connection to the database, metadata that describes the model, and an ObjectStateManager object that tracks objects during create, update, and delete operations."

Create the context in using block, thus it automatically disposes.

//Querying with LINQ to Entities 
    using (var objCtx = new SchoolDBEntities())
    {
        var schoolCourse = from cs in objCtx.Courses
                           where cs.CourseName == "Course1"
                           select cs;
        Course mathCourse = schoolCourse.FirstOrDefault<Course>();
        IList<Course> courseList = schoolCourse.ToList<Course>();

        string courseName = mathCourse.CourseName;
    }

//Querying with LINQ to Entities 
    using (var objCtx = new SchoolDBEntities())
    {
        var schoolCourse = from cs in objCtx.Courses
                           where cs.CourseName == "Course1"
                           select cs;
        Course mathCourse = schoolCourse.FirstOrDefault<Course>();
        IList<Course> courseList = schoolCourse.ToList<Course>();

        string courseName = mathCourse.CourseName;
    }

//Querying with native sql            
    using (var objCtx = new SchoolDBEntities())
        {
            //Inserting Student using ExecuteStoreCommand
            int InsertedRows = objCtx.ExecuteStoreCommand("Insert into Student(StudentName,StandardId) values('StudentName1',1)");

            //Fetching student using ExecuteStoreQuery
            var student = objCtx.ExecuteStoreQuery<Student>("Select * from Student where StudentName = 'StudentName1'", null).ToList();

        }

Entity types: Entity Object, POCO, POCO Proxy, Self tracking entities.
Entity properties: scalar, navigational.

"When you work with EntityObject derived types, the object context manages the relationships between your objects, tracks changes as they occur, and supports lazy loading in the most efficient manner. However, the EntityObject derived types have strong dependency on the Entity Framework."

"However, in n-tier application, you have to transfer entities to a tier where the object context is not available e.g. Business tier or presentation tier. So how to track changes and report those changes back to the object context? Answer is “self-tracking entities”. Starting with the .NET Framework version 4, self-tracking entities as its name suggests, can record changes to scalar, complex, and navigation properties on its own. Self-tracking entities do not depend on the Entity Framework so it can be transferred to other tier.

Self-Tracking entities have additional change tracking functions and it implements IObjectWithChangeTracker and INotifyPropertyChanged interface. It also mark it as DataContract to be used in WCF services."
Despite the above comment, STE are not recommenden anymore! (http://msdn.microsoft.com/en-us/data/jj613668)

introduced for code first: "DbContext is a simplified alternative to ObjectContext and is the primary object for interacting with a database using a specific model. "

"Context is able to track modifications to the objects it references. This is true, but only to a certain extent. The truth is that the context delegates change-tracking management to another inner component called “ObjectStateManager”. ObjectStateManager is responsible for everything related to object tracking in the context.

When we add, attach to, or delete an entity from the context, we actually do that against the state manager.

When we say that the context keeps an in-memory collection of all entities read from the database, it’s the state manager that holds this data.

When the context performs an identity-map check, it’s really the ObjectStateManager that performs the check.

When we say that the context keeps track of relationships between entities, it’s the ObjectStateManager that keeps track of everything.

ObjectStateManager is exposed as ObjectContext’s property named ‘ObjectStateManager”.

Automatically created poco entities:

"You can see that entity classes have all properties marked as "Virtual". So this means that these entities fulfill the requirements of POCO Proxy entities along with other requirements. These entities can be treated as POCO entities or POCO Proxy entities. By default it will be treated as POCO Proxy entities but you can disable the proxy creation by setting "ObjectContext.ContextOptions.ProxyCreationEnabled = false". So that context will not create Proxy for the POCO entities.

Replace ObjectSet<> to IObjectSet<> if you want to write unit test for context.

" Many-to-Many relationship is being managed in C-S mapping in EDM. So when you add student in course or Course in Student entity and when you save it then it will insert PK of added student and course in StudentCourse table. So this mapping not only enables a convenient association directly between the two entities, but also manages querying, inserts, and updates across this join.

But remember EDM does this only when joining table has PK columns for both tables. If you have some other columns in joining table then EDM will treat as normal entity and you have to use ‘Join’ in your query to fetch the data."

Query - Projection:

var student = (from s in ctx.Students
                where s.StudentName == "Student1"
                select s).FirstOrDefault<Student>();

var studentList = (from s in ctx.Students
                where s.StudentName == "Student1"
                select s).ToList<Student>();

var students = from s in ctx.Students 
                    groupby s.StandardId into studentsByStandard
                    select studentsByStandard;

var student1 = from s in ctx.Students
                    orderby s.StudentName ascending
                    select s;

var projectionResult = from s in ctx.Students
                       where s.StudentName == "Student1"
                       select new { 
                       s.StudentName, s.Standard.StandardName, s.Courses 
                       };

Eager Loading with DBContext:

using (var ctx = new SchoolDBEntities())
        {
            stud = ctx.Students.Include(s => s.Standard)
                               .Where(s => s.StudentName == "Student1").FirstOrDefault<Student>();
           
            //you can also pass entity name as a string "Standard"
            // stud = ctx.Students.Include("Standard")
                                .Where(s => s.StudentName == "Student1").FirstOrDefault<Student>();
        }

using (var ctx = new SchoolDBEntities())
        {
            stud = ctx.Students.Where(s => s.StudentName == "Student1")
                        .Include(s => s.Standard.Teachers).FirstOrDefault();
        }

However, using too many include statements kills the performance.

Lazy Loading:

using (var ctx = new SchoolDBEntities())
        {
            ctx.Configuration.LazyLoadingEnabled = true;

            //Loading students only
            IList<Student> studList = ctx.Students.ToList<Student>();

            Student std = studList[0];

           //Loads Student address for particular Student only (seperate SQL query)
           StudentAddress add = std.StudentAddress;
        }

"To turn off lazy loading for particular property, do not make it virtual. To turn off lazy loading for all entities in the context, set its configuration property to false:"

public SchoolDBEntities(): base("name=SchoolDBEntities")
    {
        this.Configuration.LazyLoadingEnabled = false;
    }

Explicit Loading with DBContext:

"Even with lazy loading disabled it is still possible to lazily load related entities, but it must be done with an explicit call."

using (var ctx = new SchoolDBEntities())
            {
                //Loading all students only
                IList<Student> studList = ctx.Students.ToList<Student>();

                Student std = studList[0];

                //Separate DB call to Load Standard for particular Student only (seperate SQL query)
                ctx.Entry(std).Reference(s => s.Standard).Load();

                // Separate DB call to Load Courses for particular Student only (seperate SQL query)
                ctx.Entry(std).Collection(s => s.Courses).Load();
            }

using (var ctx = new SchoolDBEntities())
            {
                //Loading students only
                IList<Student> studList = ctx.Students.ToList<Student>();

                Student std = studList[0];

                //count courses without loading
                var CourseCount = ctx.Entry(std).Collection(s => s.Courses).Query().Count<Course>();

                //Loads Courses for particular Student only (seperate SQL query)
                ctx.Entry(std).Collection(s => s.Courses).Query()
                        .Where<Course>(c => c.CourseName == "New Course1").Load();
            }

"When you call ObjectContext.SaveChanges(), it performs insert, update or delete operation on the database based on EntityState of the entities."

//Update entity using SaveChanges method
      using (SchoolEntities ctx = new SchoolDBEntities())
            {
                var stud = (from s in ctx.Students
                            where s.StudentName == "Student1"
                            select s).FirstOrDefault();

                stud.StudentName = "Student2";

                int num = ctx.SaveChanges();
            }

"SaveChanges also accepts SaveOptions parameter. SaveOption is an Enum which has three values:

"AcceptAllChangesAfterSave: After saving entities values to the database, context change entity states. Added and Modified entities become Unchanged and deleted entities are removed from the context.
DetectChangesBeforeSave: It tells context to detect changes before saving.
None: Neither AcceptAllChangesAfterSave or DetectChangesBeforeSave occurs"

"Connected Scenario:Connected scenario is when an entity is retrieved from the database and modified in the same context.

Disconnected Scenario:Disconnected scenario is when an entity is retrieved from the database and modified in the different context. Disconnected scenario is complex because context doesn’t know anything about modified entity so you have to tell to ObjectContext that what has changed in entity."

        Student student = new Student();
        student.StudentName = "Student1";
        
        using (var ctx = new SchoolDBContext())
        {
            ctx.Students.AddObject(student);
            ctx.SaveChanges();
        }

"Above code will work in both connected and disconnected scenario to persist new entity as a new row in the database."

            using (var ctx = new SchoolDBContext())
            {
            var stud = (from s in ctx.Students
                        where s.StudentName == "Student1"
                        select s).FirstOrDefault();

            stud.StudentName = "Updated Student1";

            int num = ctx.SaveChanges();
            }

            Student stud = null;
            using (SchoolDBContext ctx = new SchoolDBContext())
            {
                ctx.ContextOptions.LazyLoadingEnabled = false;
                
                stud = (from s in ctx.Students
                        where s.StudentName == " student1"
                        select s).FirstOrDefault();

            }
            //Out of using scope so ctx has disposed here
            stud.StudentName = "Updated student1";

            using (SchoolDBContext newCtx = new SchoolDBContext())
            {
                newCtx.Students.Attach(stud);
                newCtx.ObjectStateManager.ChangeObjectState(stud, System.Data.EntityState.Modified);
                newCtx.SaveChanges();
            }

            using (var ctx = new SchoolDBContext())
            {
                var stud = (from s in ctx.Students
                            where s.StudentName == "Student1"
                            select s).FirstOrDefault();

                ctx.Students.DeleteObject(stud);

                int num = ctx.SaveChanges();
            }

            Student stud = null;

            using (SchoolDBContext ctx = new SchoolDBContext())
            {
                stud = (from s in ctx.Students
                        where s.StudentName == "Student1"
                            select s).FirstOrDefault();
            }

            using (SchoolDBContext newCtx = new SchoolDBContext())
            {
                newCtx.Students.Attach(stud);
                newCtx.Students.DeleteObject(stud);
                //you can use ObjectStateManager also
                //newCtx.ObjectStateManager.ChangeObjectState(stud, 
                                        System.Data.EntityState.Deleted);
                int num = newCtx.SaveChanges();
            }

DBContext vs ObjectContext

"DbContext is conceptually similar to ObjectContext. It is a wrapper around ObjectContext which is useful in all development models: Code First, Model First and Database First.

DBContext API is easier to use than ObjectContext API for all common tasks. However, you can get reference of ObjectContext from DBContext to use some of the features of ObjectContext by implementing IObjectContextAdapter. "

public class SchoolDBContext: DbContext, IObjectContextAdapter
    {
        ObjectContext IObjectContextAdapter.ObjectContext
            {
                get { return (this as IObjectContextAdapter).ObjectContext; }
            }

    }

DBContext

using (var ctx = new SchoolDBEntities())
        {
            var stud = ctx.Students.Include(sadd => sadd.StudentAddress)
                .Where(s => s.StudentName == "Student1").FirstOrDefault<Student>();
            stud.StudentName = "Changed Student Name";

            
            string propertyName = ctx.Entry(stud).Property("StudentName").Name;
            string currentValue = ctx.Entry(stud).Property("StudentName").CurrentValue.ToString();
            string originalValue = ctx.Entry(stud).Property("StudentName").OriginalValue.ToString();
            bool isChanged = ctx.Entry(stud).Property("StudentName").IsModified;
            var dbEntity = ctx.Entry(stud).Property("StudentName").EntityEntry;
        }

SQL Samples

using (var ctx = new SchoolDBEntities())
            {
              var studentList = ctx.Students.SqlQuery("Select * from Student").ToList<Student>();
  
            }

using (var ctx = new  SchoolDBEntities())
            {                
                var studentName = ctx.Students.SqlQuery("Select studentid, studentname 
                    from Student where studentname='New Student1'").ToList();

            }

using (var ctx = new SchoolDBEntities())
            {                
                //this will throw an exception
                var studentName = ctx.Students.SqlQuery("Select studentid as id, studentname as name 
                        from Student where studentname='New Student1'").ToList();
            }

 using (var ctx = new SchoolDBEntities())
            {
                //Get student name of string type
                string studentName = ctx.Database.SqlQuery<string>("Select studentname 
                    from Student where studentid=1").FirstOrDefault<string>();
             }

using (var ctx = new SchoolDBEntities())
            {

                //Update command
                int noOfRowUpdated = ctx.Database.ExecuteSqlCommand("Update student 
                        set studentname ='changed student by command' where studentid=1");
                //Insert command
                int noOfRowInserted = ctx.Database.ExecuteSqlCommand("insert into student(studentname) 
                        values('New Student')");
                //Delete command
                int noOfRowDeleted = ctx.Database.ExecuteSqlCommand("delete from student 
                        where studentid=1");

            }

Entity Validation

protected override System.Data.Entity.Validation.DbEntityValidationResult ValidateEntity(DbEntityEntry entityEntry, System.Collections.Generic.IDictionary<object, object> items)
        {
            if (entityEntry.Entity is Student)
            {
                if (entityEntry.CurrentValues.GetValue<string>("StudentName") == "")
                {
                    var list = new ListDbValidationError

>(); list.Add(new System.Data.Entity.Validation.DbValidationError("StudentName", "StudentName is required")); return new System.Data.Entity.Validation.DbEntityValidationResult(entityEntry, list); } } return base.ValidateEntity(entityEntry, items); }

        try
        {
            using (var ctx = new SchoolDBEntities())
            {
                ctx.Students.Add(new Student() { StudentName = "" });
                ctx.Standards.Add(new Standard() { StandardName = "" });

                ctx.SaveChanges();


            }
        }
        catch (DbEntityValidationException dbEx)
        {
            foreach (DbEntityValidationResult entityErr in dbEx.EntityValidationErrors)
            {
                foreach (DbValidationError error in entityErr.ValidationErrors)
                {
                    Console.WriteLine("Error Property Name {0} : Error Message: {1}",
                                      error.PropertyName, error.ErrorMessage);
                }
            }
        }

Add Entity

// create new Standard entity object
    var newStandard = new Standard();

    // Assign standard name
    newStandard.StandardName = "Standard 1";

    //create DBContext object
    using (var dbCtx = new SchoolDBEntities())
    {
        //Add standard object into Standard DBset
        dbCtx.Standards.Add(newStandard);
        // call SaveChanges method to save standard into database
        dbCtx.SaveChanges();
    }

Add Entity - One-to-One

            // create new student entity object
            var student = new Student();

            // Assign student name
            student.StudentName = "New Student1";

            // Create new StudentAddress entity and assign it to student entity
            student.StudentAddress = new StudentAddress() { Address1 = "Student1's Address1", 
                    Address2 = "Student1's Address2", City = "Student1's City", 
                    State = "Student1's State" };

            //create DBContext object
            using (var dbCtx = new SchoolDBEntities())
            {
                //Add student object into Student's EntitySet
                dbCtx.Students.Add(student);
                // call SaveChanges method to save student & StudentAddress into database
                dbCtx.SaveChanges();
            }

Add Entity - One-to-Many

    //Create new standard
    var standard = new Standard();
    standard.StandardName = "Standard1";

    //create three new teachers
    var teacher1 = new Teacher();
    teacher1.TeacherName = "New Teacher1";

    var teacher2 = new Teacher();
    teacher2.TeacherName = "New Teacher2";

    var teacher3 = new Teacher();
    teacher3.TeacherName = "New Teacher3";

    //add teachers for new standard
    standard.Teachers.Add(teacher1);
    standard.Teachers.Add(teacher2);
    standard.Teachers.Add(teacher3);

    using (var dbCtx = new SchoolDBEntities())
    {
        //add standard entity into standards entitySet
        dbCtx.Standards.Add(standard);
        //Save whole entity graph to the database
        dbCtx.SaveChanges();
    }

Add Entity - Many-to-Many

//Create student entity
    var student1 = new Student();
    student1.StudentName = "New Student2";

    //Create course entities
    var course1 = new Course();
    course1.CourseName = "New Course1";
    course1.Location = "City1";

    var course2 = new Course();
    course2.CourseName = "New Course2";
    course2.Location = "City2";

    var course3 = new Course();
    course3.CourseName = "New Course3";
    course3.Location = "City1";

    // add multiple courses for student entity
    student1.Courses.Add(course1);
    student1.Courses.Add(course2);
    student1.Courses.Add(course3);

    using (var dbCtx = new SchoolDBEntities())
    {
        //add student into DBContext
        dbCtx.Students.Add(student1);
        //call SaveChanges
        dbCtx.SaveChanges();
    }

Update Entity

    Student stud ;
    // Get student from DB
    using (var ctx = new SchoolDBEntities())
    {
        stud = ctx.Students.Where(s => s.StudentName == "New Student1").FirstOrDefault<Student>();
    }

    // change student name in disconnected mode (out of DBContext scope)
    if (stud != null)
    {
        stud.StudentName = "Updated Student1";
    }

    //save modified entity using new DBContext
    using (var dbCtx = new SchoolDBEntities())
    {
        //Mark entity as modified
        dbCtx.Entry(stud).State = System.Data.EntityState.Modified;    
        dbCtx.SaveChanges();
    }

Update Entity - One-to-One

"In disconnected scenario, DBContext doesn't know whether child entity is added, modified or deleted. So DBContext has to find whether StudentAddress entity is newly added or modified or deleted at the time on SaveChanges."

    using (var dbCtx = new SchoolDBEntities())
    {
        //Get existing StudentAddress for database for the student
        StudentAddress existingStudentAddress = dbCtx.StudentAddresses.AsNoTracking()
        .Where(addr => addr.StudentID == stud.StudentID).FirstOrDefault<StudentAddress>();
        
        //Mark Student entity as modified
        dbCtx.Entry(stud).State = System.Data.EntityState.Modified;

        //Find whether StudentAddress is modified
        //if existing StudentAddress is not null then delete existing address
        if (existingStudentAddress != null)
            dbCtx.Entry<StudentAddress>(existingStudentAddress).State = 
                                            System.Data.EntityState.Deleted;

        //if new StudentAddress is not null then add new StudentAddress. 
        //This takes care modified address also.
        if (stud.StudentAddress != null)
            dbCtx.StudentAddresses.Add(stud.StudentAddress);

        dbCtx.SaveChanges();
    }

Update Entity - One-to-Many

using (var dbCtx = new SchoolDBEntities())
        {
            //1- Get fresh data from database
            var existingStudent = dbCtx.Students.AsNoTracking().Include(s => s.Standard).Include(s => s.Standard.Teachers).Where(s => s.StudentName == "updated student").FirstOrDefault<Student>();

            var existingTeachers = existingStudent.Standard.Teachers.ToList<Teacher>();

            var updatedTeachers = teachers.ToList<Teacher>();
            
            //2- Find newly added teachers by updatedTeachers (teacher came from client sided) - existingTeacher = newly added teacher
            var addedTeachers = updatedTeachers.Except(existingTeachers, tchr => tchr.TeacherId);

            //3- Find deleted teachers by existing teachers - updatedTeachers = deleted teachers
            var deletedTeachers = existingTeachers.Except(updatedTeachers, tchr => tchr.TeacherId);

            //4- Find modified teachers by updatedTeachers - addedTeachers = modified teachers
            var modifiedTeacher = updatedTeachers.Except(addedTeachers, tchr => tchr.TeacherId);

            //5- Mark all added teachers entity state to Added
            addedTeachers.ToList<Teacher>().ForEach(tchr => dbCtx.Entry(tchr).State = System.Data.EntityState.Added);

            //6- Mark all deleted teacher entity state to Deleted
            deletedTeachers.ToList<Teacher>().ForEach(tchr => dbCtx.Entry(tchr).State = System.Data.EntityState.Deleted);


            //7- Apply modified teachers current property values to existing property values
            foreach(Teacher teacher in modifiedTeacher)
            {
                //8- Find existing teacher by id from fresh database teachers
               var existingTeacher = dbCtx.Teachers.Find(teacher.TeacherId);
                
                if (existingTeacher != null)
                {
                    //9- Get DBEntityEntry object for each existing teacher entity
                    var teacherEntry = dbCtx.Entry(existingTeacher);
                    //10- overwrite all property current values from modified teachers' entity values, 
                    //so that it will have all modified values and mark entity as modified
                    teacherEntry.CurrentValues.SetValues(teacher);
                }

            }
            //11- Save all above changed entities to the database
            dbCtx.SaveChanges();
        }

"following extension method to compare values from two lists and return entities from one list which is not present into second list. "

public static IEnumerable Except(this IEnumerable items, IEnumerable other, 
                                                                                Func getKey)
    {
        return from item in items
               join otherItem in other on getKey(item)
               equals getKey(otherItem) into tempItems
               from temp in tempItems.DefaultIfEmpty()
               where ReferenceEquals(null, temp) || temp.Equals(default(T))
               select item;

    }

Update Entity - Many-to-Many

using (var dbCtx = new SchoolDBEntities())
            {
                /* 1- Get existing data from database */
                var existingStudent = dbCtx.Students.Include("Courses")
                        .Where(s => s.StudentName == stud.StudentName).FirstOrDefault<Student>();

                /* 2- Find deleted courses from student's course collection by 
                students' existing courses (existing data from database) minus students' 
                current course list (came from client in disconnected scenario) */
                var deletedCourses = existingStudent.Courses.Except(stud.Courses, 
                        cours => cours.CourseId).ToList<Course>();

                /* 3- Find Added courses in student's course collection by students' 
                current course list (came from client in disconnected scenario) minus 
                students' existing courses (existing data from database)  */
                var addedCourses = stud.Courses.Except(existingStudent.Courses, 
                        cours => cours.CourseId).ToList<Course>();

                /* 4- Remove deleted courses from students' existing course collection 
                (existing data from database)*/
                deletedCourses.ForEach(c => existingStudent.Courses.Remove(c));
                
                //5- Add new courses
                foreach(Course c in addedCourses)
                {
                    /*6- Attach courses because it came from client 
                    as detached state in disconnected scenario*/
                    if (dbCtx.Entry(c).State == System.Data.EntityState.Detached)
                        dbCtx.Courses.Attach(c);

                    //7- Add course in existing student's course collection
                    existingStudent.Courses.Add(c);
                }

                //8- Save changes which will reflect in StudentCourse table only
                dbCtx.SaveChanges();
            }

Delete Entity

using (var dbCtx = new SchoolDBEntities())
        {

            //if already loaded in existing DBContext then use Set().Remove(entity) to delete it.
            var newtchr = dbCtx.Teachers.Where(t => t.TeacherName == "New teacher4")
                                .FirstOrDefault<Teacher>();
            dbCtx.Set(Teacher).Remove(newtchr);
            
            //Also, you can mark an entity as deleted
            //dbCtx.Entry(tchr).State = System.Data.EntityState.Deleted;    
            
            //if not loaded in existing DBContext then use following.
            //dbCtx.Teachers.Remove(newtchr);

            dbCtx.SaveChanges();
        }

ENTITY FRAMEWORK 5.0

"Entity Framework 5.0 includes a number of new features and bug fixes to the EF4.3 release. Most of the new features are only available in applications targeting .NET 4.5"

- enum support

- spatial data types

- query performance enhancements

"It will create POCO entities and DBContext by default. So you don’t have to add code generator item for DBContext manually."

none

15 Kasım 2013 Cuma

mvc examples

12 Kasım 2013 Salı

lazy loading vs eager loading in ef5

11 Kasım 2013 Pazartesi

json editors

6 Kasım 2013 Çarşamba

my entity framework notes

4 Kasım 2013 Pazartesi

asp.net mvc vs webforms

ASP.net Web Form

The Suggestion:

References

Popüler Yayınlar