EF

Create first Simple basic EF - Code first Application

1. Create console application with EF nuget
2. create simple class as following

 public class Person

    {

        public int PersonId { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

    }

 public class Context: DbContext

    {

         // EF is not created database

        public Context(): base(@"Data Source=.;Initial Catalog=EF")

        {

        }

        public DbSet<Person> People { get; set; }

    }

  

 static void Main(string[] args)

    {

      // if not created EF database create it

      using (var context = new Context())

       {

                context.Database.CreateIfNotExists();

       }

  }

now created new database call EF with in sql server . (it is fun and vary easy)

 

primary key was id, and it will be identity colom

Save Tuple in Database

       using (var context = new Context())

            {

                context.People.Add(new Person

                {

                    FirstName = "chamith",

                    LastName = "Sarang",

                    PersonId = 1

                });

                context.SaveChanges();

            }

Select To db

using (var context = new Context())

            {

                var x =  context.People;

                foreach (var item in x.ToList())

                {

                  Console.WriteLine("first name {0} \t last name {1} \t Person id {2} \t",

                    item.FirstName, item.LastName, item.PersonId

                  );

                }

                Console.WriteLine(x.ToString());

            }

console result

first name chamith       last name Sarang        Person id 1

first name chamith       last name Sarang        Person id 2

SELECT

    [Extent1].[PersonId] AS [PersonId],

    [Extent1].[FirstName] AS [FirstName],

    [Extent1].[LastName] AS [LastName]

    FROM [dbo].[People] AS [Extent1]

created

Updating recode

           // update recode

            using (var context = new Context())

            {

                var x = context.People.SingleOrDefault(p => p.PersonId == 2);

                x.FirstName = "sajeeka";

                x.LastName = "nanayakkara";

                context.SaveChanges();

            }

.NET && sql server data type mapping

Domain Headers

check constraint entity framework

EF does not currently support Check constraints.
use following code on code first migration

public override void Up()
{
    // Auto generated code here
    // ...

    // YOUR CUSTOM SQL GOES HERE
    Sql("ALTER TABLE foo ADD CONSTRAINT CK_Some_Check CHECK (SomeDate > '20121110 00:00:00.000'");**
}

Uniq key constrant

[Index("IX_FirstNameLastName", 1, IsUnique = true)]

public string Emial{get;set;}

or update uniqkey by migration script .

Call sp from Entity framework 

it is just like dapper

this.Database.SqlQuery<YourEntityType>("storedProcedureName",params);

Entity Framework code first with Data Annotations

 [NotMapped]

Data tyle
 [Column("Name", TypeName="ntext")]
ComplexType
Validation 
[MaxLength(50),MinLength(2)]
[MaxLength(20)] ==  [StringLength(50)]
[Required]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[Range(typeof(DateTime), "1/1/1966", "1/1/2020")]
[Range(1, 100),DataType(DataType.Currency)]

Time Span (Ask from peter)

use for row versioning.
can use over concurrencyCheck annotation.
none-nullable. 
can be have one timeSpan propoerty in db

 [Timestamp]
 public Byte[] TimeStamp { get; set; }

Alias
[Table("StudentMaster")]

[Table("StudentMaster", Schema="Admin")]

[Column("Name")]
public string StudentName { get; set; }
[Column("Name", TypeName="varchar")]

Index

default indexing 
[Index]
public int Rating { get; set; }

Naming Indexing 
 [Index("PostRatingIndex")]
    public int Rating { get; set; }
Multiple Colomn Indexing
  [Index("IX_BlogIdAndRating", 2)]
   public int Rating { get; set; }
[Index("IX_BlogIdAndRating", 1)]
 public int BlogId { get; set; }


Primary Key
[Key]
public int StudentId
Composite Key 
[Key] //composite key
[Colomn(Order = 1)]
public int StudentId{get;set;}
[Key]
[Colomn(Order = 2)]
public int SubjectId{get;set;}
ConcurrencyCheck
 [ConcurrencyCheck, MaxLength(10, ErrorMessage="BloggerName must be 10 characters or less"),MinLength(5)]
 public string BloggerName { get; set; }
if some one read 
BloggerName . mean while some one update to BloggerName.In That case him 

get an exception call 
ConcurrencyCheck. (THAT AVOID CONCURRENCY READ AND UPDATE)

Handle transaction on Entity framework 

we dont want to handle transaction to ef explisitly. cntx.SaveChange(); will handle transactions also

example:
public void DeleteUser(int userId){

 using(var cnt = Context){
   
   try{
    DeleteUserRoles(cnt,userId);
    DeleteUserIdentity(cnt,userId);
   cnt.saveChange();
  }catch{ throw;}

}
}
public void DeleteUserRoles(DbContext cnt,int userId){

  try{
   var user = cnd.UserRoles.Where(p=>p.Id == userId).FirstOrDefault();
   cnt.UserRoles.Remove(user);
  }catch{ throw;}

}
public void DeleteUserIdentity(DbContext cnt,int userId){

try{
  var user = cnd.UserIdentity.Where(p=>p.Id == userId).FirstOrDefault();
 cnt.UserIdentity.Remove(user);
}catch{throw;}
}

Or someting like following 

using (System.Data.Entity.DbContextTransaction dbTran = context.Database.BeginTransaction( ))
    {
        try
        {
            Student std1 = new Student() { StudentName = "newstudent" };
            context.Students.Add(std1);
            context.Database.ExecuteSqlCommand(
                @"UPDATE Student SET StudentName = 'Edited Student Name'" +
                    " WHERE StudentID =1"
                );
            context.Students.Remove(std1);

            //saves all above operations within one transaction
            context.SaveChanges();

            //commit transaction
            dbTran.Commit();
        }
        catch (Exception ex)
        {
            //Rollback transaction if exception occurs
            dbTran.Rollback();
        }

    }

Entity framework with async 

http://www.entityframeworktutorial.net/entityframework6/async-query-and-save.aspx

Migration commands

-> enable-migrations
-> add migration nameOfMigration
-> update-database
-> update-database -verbose
   update database with showing executed quary

Create Stored Procedure DbMigration

Sql(@"Alter PROCEDURE SomeSP
               AS
                BEGIN
                   select Getdate() as DateTime;
             END
           GO");