Entity Framework Associated Table Counts with One Query

With the Entity Framework, we are able to use navigation properties to get the associated records. This however loads all the child rows for a parent which causes performance problems. In some instances, we may just need to get a count of the associated records without having to load all the rows.  Let’s see more detail how this can be done.

In the diagram below, we show the relationship between company, department and employee entities. We want to be able to get the company record with the aggregated count for departments and employees for each company. For this case, we can use the company navigation properties to get that counts and use a company view-model class to hold the additional information.

We start by first looking at our database entities:

We can see, the Company entity has the associations to the Department and Employee entities. We want to be able to return the Company information with associated counts using this model:

To load the company information with count records, we can use the following snippet:

    using (var context = new Entities())

           {

               companies = context.Companies.Select(c => new model.Company()

               {

                   Name = c.Name,

                   Id = c.Id,

                   EmployeeCount = c.Employees.Count(),

                   DepartmentCount = c.Departments.Count()

               }).ToList();

           }

With this lambda expression, we are using the navigation properties to get the count of the associated records. The query that is created and sent to the database is just one SQL statement that gets the company data and the associated rows count of the other tables.  The result is returned as a list of CompanyModel objects.

With this approach, we no longer need to load all the rows from the other tables using the navigation properties. This should  improve the overall performance of the application.

I hope you find this tip useful.

Originally posted at ozkary.com

CodeProject

With the Entity Framework, we are able to use navigation properties to get the associated records. This however loads all the child rows fo...

Read Article →

Control Angularjs Modal Dialog Width

Note: This solution requires Bootstrap 3.1.0 or later.

With AngularJs, we can open a modal dialog by using the $modal service. The script to open a custom modal dialog can be as simple as follows:

var modalInstance = $modal.open({

                templateUrl: ‘my-dialog’,                               

                replace: true,

                                          

            });

This AngularJS service is based on the Boootstrap’s markup and CSS definitions. The templateUrl is just a HTML template emedded on the page, and it is listed below:

<script type="text/ng-template" id="my-dialog">

    <div>

        <div class="modal-header">

            <h3>Hi Dialog by ozkary</h3>

        </div>

        <div class="modal-body">

            <table width="780px;"><tr><td>Hello by ozkary.com</td></tr></table>

        </div>

        <div class="modal-footer">

            <button class="btn btn-primary" ng-click="$dismiss()">OK</button>

            <button class="btn btn-warning" ng-click="$close()">Cancel</button>

        </div>

    </div>

</script>

This script listed above uses the default value which renders a modal dialog with a mid size frame which is mostly use for messaging. When we need control of the width, we can use the parameter size. This parameter is a bit miss-leading since it may make us think that we can enter an integer value and control the size.  Instead, we need to use string constants that is appended to the CSS class modal-?? where the ?? is replaced by the string parameter. 

When we want a bigger dialog, we can use the lg constant, so that the style is set to modal-lg as follows:

var modalInstance = $modal.open({

                templateUrl: 'my-dialog',                               

                replace: true,

                size:'lg'                          

            });

We can see this in action with this plunker. The dialog is small or large based on the size parameter that we provide.

Hope it helps.

Note: This solution requires Bootstrap 3.1.0 or later. With AngularJs, we can open a modal dialog by using the $modal service. The scri...

Read Article →