I recently read an article from SimpleTalk. It pretty much covered the common mistake and i am listing the one which i commonly do.

Bring only Columns you require,instead of All :

Lets assume we need only first name and last name from the table User  which holds information of user details (First Name, Last Name, Telephone No,Email Id,Location).

we need to write a query to bring all users whose location is  NewYork.

Most Common Query we write:

string Location= "NewYork"

var result= dbcontext.User.where(x=>x.Location==Location).ToList();

foreach(var usersinnewyork in result)

{

Console.WriteLine(usersinnewyork.FirstName);

Console.WriteLine(usersinnewyork.LastName);

}

In the above query the result brings all the columns while we need only first and last name.We might not be aware of impact of query we write,but identifying such queries in application would help us to avoid certain performance issues.

Modified Query with Anonymous Object :

string Location= "NewYork"

var result= dbcontext.User.where(x=>x.Location ==Location).Select( x=>new 

{
FirstName =x.FirstName,
LastName =x.LastName
});

In the above query we only bring what we need (first and last name).

 

What is Cascading Delete ?

There may be situation where if a record in parent table is deleted than corresponding records in child tables(Foreign Key ) are also deleted.

Parent and Child Mapping with Fluent Api in Entity Framework:

I am not going to describe how to map parent and child table with fluent api. you can read it more about here.

In the below table diagram, the AssessmentChatRoom is the base parent whose foreign key is referenced by ChatRoomData and ChatRoomParticipants table.

Mapping the relation with FluentAPI and Setting the Cascade Delete:

AssessmentChatRoom is the parent and ChatRoomData and ChatRoomParticipants are the child tables.In the ChatRoomData WillCascadeDelete() Property is set to true.

 

Parent Table:

public class AssesmentChatRoomConfig
 {
  public AssesmentChatRoomConfig()
{
ToTable("AssessmentChatRooms").HasKey(e => e.Id);
Property(e => e.Id).HasColumnName("Id");
Property(e => e.Name).HasColumnName("Name");
Property(e => e.LastUpdatedOn).HasColumnName("LastUpdatedOn");
Property(e => e.CreatedOn).HasColumnName("CreatedOn");
Property(e => e.AssessmentChatRoomParentId).HasColumnName("AssessmentChatRoomParentId");
}
 }

ChildTable

public class ChatRoomDataConfig 
 {
public ChatRoomDataConfig()
{
ToTable("ChatRoomData").HasKey(e => e.Id);
Property(e => e.Id).HasColumnName("Id");
Property(e => e.AssessmentChatRoomId).HasColumnName("AssessmentChatRoomId");
Property(e => e.ChatText).HasColumnName("ChatText");
Property(e => e.PostedBy).HasColumnName("PostedBy");
Property(e => e.TimeStamp).HasColumnName("TimeStamp");
Property(e => e.MessageType).HasColumnName("MessageType");
HasRequired(s => s.AssessmentChatRoom)
.WithMany(s => s.ChatRoomDatas).HasForeignKey(s => s.AssessmentChatRoomId).
WillCascadeOnDelete(true); // set Cascade delete
}
}

Deleting the Parent Table with Cascade Delete :

1.Get the parent record id and its related child record.

var result =dbcontext.Assessmentchatroom.where(x=>x.id == id);

 

 

The above query will bring only the parent record and you might see the child records to be null,this is because the entity framework by default has lazy loading enabled. It is not a good idea to disable lazy loading,instead use include keyword and retrieve the child elements.

Retrieving Child Record using Include Keyword of entity framework:

Its possible to let know the parent to load the child elements using include keyword as below.

var entity = Context.AssessmentChatRooms.
Include(c=>c.ChatRoomDatas.Select(b=>b.AssessmentChatRoom)).
where(x=>x.id =id)

Code to Remove :

This is a pretty standard way to remove.you just got to pass the parent entity and got to call the Remove method of entity framework and this will in turn delete all the child elements,since cascade delete is set to true.