Entity Framework: How to update stored procedure in EDMX

An .edmx file is an XML file that defines an Entity Data Model (EDM), describes the target database schema, and defines the mapping between the EDM and the database.

Update imported stored procedure /view in EDMX:

1.       Open the .edmx file and right click on EDMX and select Model Browser

2.       In Model Browser, the Functions Imports under OfficeModel tab and select stored procedure ‘GetOfficeHistoryByID’ and Click on Edit

3.       “Edit Function Import” window will be opened and Click on “Get Column Information” button to get stored procedure the output schema.

4.       Click on Update Button under “Returns a Collection of” Section to update complex type “GetOfficeHistoryByID_Result”, which will be used to map the stored procedure output data.

5.       Save .edmx file to reflect field changes related to stored procedure /view.

Deferred Execution vs Immediate Execution of LINQ Query

In this blog, we will discuss about how deferred query execution and Immediate Query Execution works in LINQ, and what the difference between two are.

Deferred Execution: 

Deferred execution of LINQ query means it constructs the query/expression tree and it defers query execution until its value is requested. When value is required, it evaluate the execution tree in locally and then it send generated SQL to server. Deferred execution approach improves query execution performance by avoiding unnecessary database call.

Here is an example of Deferred LINQ query:

IQueryable<Order> orders = _dbContext.Orders.Where(x => x.OrderNumber == orderNumber);

foreach (Order item in orders)

      {

       new OrderData

       {

         OrderID = item.OrderID,

         OrderNumber = item.OrderNumber,

         OrderStatusCode = item.OrderStatu.StatusCode

       };

}

For above LINQ query, the SQL is not generated until the foreach statement executes.

Expression tree – expression tree is a data structure, which holds LINQ to SQL query, which will be sent to SQL server /database.

Immediate Execution: 

Immediate execution of LINQ means it enforces the LINQ query to execute and get the result immediately and there are many methods like ToList(),ToArray(), ToDictionary() executes the LINQ query immediately.

Here is an example of Immediate LINQ query: 

IList<Order> orders = _dbContext.Orders.Where(x => x.OrderNumber == orderNumber).ToList();

foreach (Order item in orders)

      {

       new OrderData

       {

         OrderID = item.OrderID,

         OrderNumber = item.OrderNumber,

         OrderStatusCode = item.OrderStatu.StatusCode

       };

}

Entity Framework - When an object is returned with a NoTracking merge option, Load can only be called when the EntityCollection or EntityReference does not contain objects.

I recently encountered the below Entity Framework error message while mapping to entity data to domain object.

System.InvalidOperationException: When an object is returned with a NoTracking merge option, Load can only be called when the EntityCollection or EntityReference does not contain objects

Here is Entity Framework Model (edmx) diagram and repository method GetOrder() which is  used to get order information on based on order number.

Entity Framework Model (edmx) diagram:

Domain Class:

    public class OrderData

    {

        public int OrderID { get; set; }

        public int OrderNumber { get; set; }

        public int? StatusID { get; set; }

        public string  OrderStatusCode { get; set; }

    }

Repository Class:

           

    public OrderData GetOrder(int orderNumber)

        {

           Order orderEntity =  _dbContext.Orders.AsNoTracking().FirstOrDefault(x => x.OrderNumber == orderNumber);

            return orderEntity != null ? new OrderData

            {

                OrderID = orderEntity.OrderID,

                OrderNumber = orderEntity.OrderNumber,

                OrderStatusCode = orderEntity.OrderStatu.StatusCode

            }: null;

        }

In above EF query, AsNoTracking() option is used for Orders entity, it means EF will not track the instantiated Order value and Order entity ‘s navigation properties also will be OFF.

In above Edmx diagram, we can see the Order has a navigation property ‘OrderStatu’ but due to asNoTracking option, all navigation properties of Order entity will be OFF and when we will try to access any navigation property value and It will be throw entity framework  exception

Order orderEntity = _dbContext.Orders.AsNoTracking().FirstOrDefault(x => x.OrderNumber == orderNumber);

So in this scenario, we need to explicitly include navigation property OrderStatu’ by using Include () function

Order orderEntity = _dbContext.Orders.Include(x => x.OrderStatu).AsNoTracking().FirstOrDefault(x => x.OrderNumber == orderNumber);

In other way

Order orderEntity =  _dbContext.Orders.Include("OrderStatu").AsNoTracking().FirstOrDefault(x => x.OrderNumber == orderNumber);

The entity or complex type 'Project' cannot be constructed in a LINQ to Entities query

The entity or complex type 'Project' cannot be constructed in a LINQ to Entities query.

Recently I encountered the above exception when I tried to create object of entity type eg, Project and tried to map only those columns which will be used in mapping of entity to business or DTO object. For performance point of view, instead of pulling of all columns of entity, I wanted to included only those columns which will be used in mapping of entity to business object.

Here is EF Query:

List projects = _db.Projects.Select(x = > new Project { Name = x.Name, Location = x.Location }).ToList();

To resolve this problem, you have two option, either you can first map the entity column to an anonymous type or direct maps to Business object.

Maps to anonymous type:

In below EF query, First Project entity is being mapped to anonymous type then call to ToList() to populate data into location memory object and again maps to Project entity object.

C# EF Query:

List projects = _db.Projects.Select(x = > new { Name = x.Name, Location = x.Location }).ToList();

Maps to Business object:

In below code, Project entity directly maps to Business object (ProjectData).

C# EF Query:

List projects = _db.Projects.Select(x = > new ProjectData {  Name = x.Name, Location = x.Location }).ToList();

C# entity framework BULK INSERT

This post demonstrates How to make bulk insert in entity framework by using SQLBulkCopy and in my previous post (Entity Framework - Bulk Update) I explained that How entity framework has a performace issue to perform bulk insert or update records.

SQLBulkCopy class is used to bulk load a SQL Server table with data from another source and SqlBulkCopy  has a method WriteToServer() , which copies all rows from a data source to a destination table specified by the DestinationTableName property of the SqlBulkCopy object

public static void BulkInsertRecord<TEntity>(IEnumerable items,string schemaName) where TEntity : class

        {           

            DbContext.Database.Connection.Open();

            var conn = DbContext.Database.Connection;        

            SqlBulkCopy sqlBulk = new SqlBulkCopy(conn as SqlConnection)

            {           

            DestinationTableName = schemaName + $".{typeof(TEntity).Name}",

            };                

   

                
                   var dbReader = items.AsDataReader();

sqlBulk.WriteToServer(dbReader);

                

        }

Here, ProjectData is Domain or DTO class, which is being mapped with Project (Data Entity Class) and this Project Entity maps to Project Table and schema is dbo

   public bool BulkInsertProject (List records)

        {

            List entityProject = new List>();

            foreach (ProjectData record in records)

            {

Project project = new Project

                {

                    ProjectID = record.ProjectID

                    ProejctDesc = record.ProejctDesc

                };

                entityProject.Add(project);

            }

                     BulkInsert(entityProject,        "dbo");

           
           return true;

        }

Other Entity framework related Links: 

Entity Framework : Bulk Insert

Entity Framework 4.0: How to log SQL statements

Entity Framework : Create, Update and Delete

Entity Framework - Asynchronous Programming (async/await)

Entity Framework : Inner /Left Outer Joins

Entity Framework : Eager Loading of Multiple Levels of Entities by Using Include

Entity Framework : Bulk Update

EF 4.0 : Managing Concurrency

Entity Framework : Basic Steps to improve EF query performance

Thanks for visiting !!

C# Entity Framework : Asynchronous Programming (async/await)

Asynchronous programming is a means of parallel programming in which a unit of work runs separately from the main application thread and notifies the calling thread of its completion, failure or progress. The main benefits one can gain from using asynchronous programming are improved application performance and responsiveness.

Entity Framework 6.0 supports asynchronous Programming, it means asynchronously you can query data and save data. By using async/await you can easily write the asynchronous programming for entity framework.

Example:

public async Task<Project> GetProjectAsync(string name)

{

 DBContext _localdb = new DBContext();

 return await _localdb.Projects.FirstOrDefaultAsync(x => x.Name == name);

}

In above example, asynchronously return the first element of records which satisfied condition or default value.

public async Task<Project> SaveProjectAsync(Project project)

{

 DBContext _localdb = new DBContext();

_localdb.Projects.Add(project);

 await _localdb.SaveChangesAsync();

 return project;

}

In above example, asynchronously saves all changes made in this context to the underlying database.

if you are doing asynchronous programming in entity framework, then the each async method should have own DBContext object otherwise if you are sharing dbcontext among multiple threads it could thrown exception

other links related to entity framework : 

Entity Framework : Bulk Update

Basic Steps to improve EF query performance

Entity Framework 4.0: How to log SQL statements

Entity Framework : Create, Update and Delete

Entity Framework - Asynchronous Programming (async/await)

Entity Framework : Inner /Left Outer Joins

Entity Framework : Eager Loading of Multiple Levels of Entities by Using Include 

EF 4.0 : Managing Concurrency

Thanks for visiting!!

Entity Framework : Eager Loading of Multiple Levels of Entities by Using Include

This blog will demonstrates how to do multiple levels of entities loading by using include() method in entity framework and there are two ways to load the related entities data

1.       Eagerly Loading
2.       Lazy Loading

Eagerly Loading : 
In eager loading process, the query related to other related entities is part of the main query and it will be achieved by using INCLUDE method.

Example :

List<Customer> customers = _db.Customers.Include("Addresses").ToList();

OR

List<CustomerData> customers = _db.Customers.Include(c => c.Addresses).ToList();

as you can see in above ef query, Customer entities will be loaded along with included thier assciated Addresses entity data

in below exmaple you can see the multiple level of entities is being loaded. Customer enetity is being loaded with included entities Addresses, Order and it's releated Review entities.

Example :

List<Customer> customers = _db.Customers. Include("Addresses").Include("Orders.Reviews").ToList();

Lazy Loading:
In lazy loading process, the related entities is automatically loaded from the database the first time that property is being referenced or used

Thanks for visiting!!

C# LINQ : Joins (Inner Join, Left Outer Join, CROSS JOIN)

this blog will demonstrate that how Join (Left outer join, inner join) work in LINQ . LINQ has a join query operator that work like SQL JOIN. LINQ use join query operator to fetch data from more than one tables on common columns/properties.

1. INNER JOIN
2. LEFT OUTER JOIN
3. CROSS JOIN

There are few tables Student, StudentCourse and StudentAddress :

Student Table:

StudentID	StudentNumber	FirstName	LastName	DateofBirth
1	911	John	Lewis	1/23/1985
2	912	King	George	4/3/1989
3	913	Jolly	Singh	6/7/1986

StudentCourse Table:

StudentID	CourseID	TeacherID
1	M1		12
1	C1		13
1	E1		15
2	M1		12
2	E1		15

StudentAddress Table:

Student ID	City	State	Zip Code
1	Dublin	OH	43017
2	Rockville	TN	32012

Inner Join:

LINQ uses Inner Join query operator to get the data from two or more tables based on a common columns, and It will ignore those record, which are not present into both table similar Like SQL Inner Join.

 INNER JOIN

Here is an example of  Inner Join in LINQ :

 var query = from s in _dbContext.Students

              join c in _ dbContext.StudentCourses on s.StudentID equals c.StudentID

              select new { s, c };

Lambda Expression :

var query = _dbContext.Students.Join(_dbContext.StudentCourses , s=> s.StudentID , c =>  c.StudentID,(s,c) => new { s, c})
.Select(m => new { m.s, m.c });

Output:

Student ID	Student Number	First Name	Last Name	Date of Birth	Course ID	Teacher ID
1	911	John	Lewis	1/23/1985	M1	12
1	911	John	Lewis	1/23/1985	C1	13
1	911	John	Lewis	1/23/1985	E1	15
2	912	King	George	4/3/1989	M1	12
2	912	King	George	4/3/1989	E1	15

In above joining query, we are fetching student and its associated course details on given student Number (911). In this query, it will returns only matching and common records on both tables and you can see, there is not any record for Jolly (StudentID : 3) . StudentCourse table does not have course details for student Jolly.

If you want to get the specific given student number records (added where clause for Student Number)

var query = from s in _dbContext.Students

              join c in _dbContext.StudentCourses on s.StudentID equals c.StudentID

              where s.StudentNumber = 911

              select new { s, c };

Lambda Expression :

var query = _dbContext.Students.Join(_dbContext.StudentCourses , s=> s.StudentID , c => c.StudentID,(s,c) =>; new { s, c})
.Where(w => w.s.StudentNumber = 911)
.Select(m => new { m.s, m.c });

Output:

Student ID	Student Number	First Name	Last Name	Date of Birth	Course ID	Teacher ID
1	911	John	Lewis	1/23/1985	M1	12
1	911	John	Lewis	1/23/1985	C1	13
1	911	John	Lewis	1/23/1985	E1	15

Left Join or Left Outer Join:
Left Outer join is used to get the completely data from one table and only matching data from second table

LEFT OUTER JOIN

Here is an example of Left Outer Join in LINQ :

var query = from s in _dbContext.Students

join a in _db.StudentAddress on s.StudentID equals a.StudentID into StudentAddressDetails

            from address in StudentAddressDetails.DefaultIfEmpty()                     

            select new { s, address.City };

Lambda Expression :

 var query = dbContext.Students.GroupJoin(_db.StudentAddress, s => s.StudentID , a => a.StudentID , (s, a) => new { s, a}).SelectMany(m => m.a.DefaultIfEmpty(),(m,s) => new { m.s, m.a.City});

Output:

Student ID	Student Number	First Name	Last Name	Date of Birth	City
1	911	John	Lewis	1/23/1985	Dublin
2	912	King	George	4/3/1989	Rockville
3	913	Jolly	Singh	6/7/1986	NULL

The above query returns the complete list of student record from students table and its city address, if there is not address record for any student, it will return student record along with City Default value (NULL).

Cross Join :

Cross join is a Cartesian join and it means Cartesian product of both the tables. This join does not use any condition to join two table and it returns the multiplication of record number of both tables

CROSS JOIN

Here is an example of Cross Join in LINQ

var query = from s in _dbContext.Students

a in _db.StudentAddress 

            select new { s.StudentID ,s.StudentNumber, s.FirstName, a.City, a.Zipcode };

Output:

Student ID	Student Number	First Name	City	ZipCode
1	911	John	Dublin	43017
2	912	King	Dublin	43017
3	913	Jolly	Dublin	43017
1	911	John	Rockville	32012
2	912	King	Rockville	32012
3	913	Jolly	Rockville	32012

The above query returns all records from both tables.

Thanks for visiting!!