EC-Counsil

 1. How did you implemented OAuth?

2. How will you stop a application from opening in second tab?

Database Anomalies

 

What is Database Anomaly?

Database anomaly is normally the flaw in databases which occurs because of poor planning and storing everything in a flat database. Generally this is removed by the process of normalization which is performed by splitting/joining of tables.

Prof_ID	Prof_Name	Dept.	Course Group
39404	Ashish	Marketing	Sec A
39445	Sonam	Product	Sec B
43576	Anu Priya	Finance	Sec C
54325	Anu Priya	Finance	Sec C
99823	Anushka	HR	Sec D
14325	Anushka	HR	Sec E

There are three types of database anomalies:

a) Insertion anomaly: An insertion anomaly occurs when we are not able to insert certain attribute in the database without the presence of other attribute. For example suppose any professor is hired but not immediately assigned any course group or any department may not get his/her place in such type of flat database mentioned above, if null entries are not allowed in the database. So in the case mentioned above removing such type of problems requires splitting of the database which is done by normalization.

b) Update anomaly: This occurs in case of data redundancy and partial update. In other words a correct update of database needs other actions such as addition, deletion or both. For example in the above table the department assigned to Anushka is an error because it needs to be updated at two different place to maintain consistency.

c) Deletion Anomaly: Deletion anomaly occurs where deletion some data is deleted because of deletion of some other data. For example if Section B is to be deleted then un-necessarily Sonam’s detail has to be deleted. So normalization is generally done before deleting any record from a flat database.

Source : https://www.mbaskool.com/business-concepts/it-and-systems/12909-database-anomaly.html

ngAfterViewInit()

 Angular ngAfterViewInit() is the method of AfterViewInit interface. ngAfterViewInit() is a lifecycle hook that is called after Angular has fully initialized a component's views. ngAfterViewInit() is used to handle any additional initialization tasks. Find the AfterViewInit interface code from Angular doc.

interface AfterViewInit {
  ngAfterViewInit(): void
} 

ngAfterViewInit() is used to access properties annotated with @ViewChild() and @ViewChildren() decorators.

ngAfterViewInit() in Angular Lifecycle Hooks

ngAfterViewInit() is executed after Angular initializes the component's views and child views. The child view is the view that a directive is in. ngAfterViewInit() is executed only once after the first call of ngAfterContentChecked() life cycle hook. After ngAfterViewInit() lifecycle hook, the ngAfterViewChecked() is called. ngAfterContentChecked() responds after Angular checks the content projected into the directive/component and ngAfterViewChecked() responds after Angular checks the component's views and child views.

ngAfterViewInit can be used with @ViewChild() and @ViewChildren() properties. ngAfterContentInit() can be used with @ContentChild and @ContentChildren properties.

Example

import { Directive, ViewContainerRef } from '@angular/core';

@Directive({
    selector: '[cpMsg]'
})
export class MessageDirective {
    constructor(public viewContainerRef: ViewContainerRef) { }
} 

message.component.ts

import { Component, ViewChild, ViewChildren, AfterViewInit, TemplateRef, QueryList } from '@angular/core';
import { MessageDirective } from './message.directive';

@Component({
    selector: 'app-message',
    template: `
    <h3>@ViewChildren() and @ViewChild()</h3>
	<div cpMsg></div>
	<div cpMsg></div>
	<div cpMsg></div>	
		
	<ng-template #msgTemp>
          Namaste!
	</ng-template>
   `
})
export class MessageComponent implements AfterViewInit {
	@ViewChildren(MessageDirective)
	private msgList: QueryList<MessageDirective>;

	@ViewChild('msgTemp')
	private msgTempRef: TemplateRef<any>;

	ngAfterViewInit() {
                console.log("this.msgList.length: " + this.msgList.length);
        
		this.msgList.forEach(messageDirective =>
			messageDirective.viewContainerRef.createEmbeddedView(this.msgTempRef));
	}
} 

As we know that ngAfterViewInit() responds when the component's view and its child view is initialized, so here inside this method we are reading the msgTemp template data and embedding it into the cpMsg directive.

Source : https://www.concretepage.com/angular/angular-ngafterviewinit

When to use Include() with Entity Framework?

When to use Include with Entity Framework and is it related to lazy loading?

Answer

Before jumping into the answer of when to use Include, let's have a look at the following simple example which contains three entities.

public class Customer
{
	public int CustomerID { get; set; }
	public string Name { get; set; }
	public virtual List<Invoice> Invoices { get; set; }
}

public class Invoice
{
	public int InvoiceID { get; set; }
	public DateTime Date { get; set; }
	public int CustomerID { get; set; }
	public virtual Customer Customer { get; set; }
	public virtual ICollection<Item> Items { get; set; }
}

public class Item
{
	public int ItemID { get; set; }
	public string Name { get; set; }
	public int InvoiceID { get; set; }
	public virtual Invoice Invoice { get; set; }
}

• Lazy loading is the process whereby an entity or collection of entities is automatically loaded from the database.
• Lazy loading is enabled by default in Entity Framework, and we can mark specific navigation properties or even whole entities as lazy by making them virtual.

Now let's retrieve all the customers from a database and also iterate over their invoices as well and then print the invoice date.

using (var context = new MyContext())
{
    var list = context.Customers.ToList();
    foreach (var customer in list)
    {
        Console.WriteLine("Customer Name: {0}", customer.Name);
        foreach (var customerInvoice in customer.Invoices)
        {
            Console.WriteLine("\tInvoice Date: {0}", customerInvoice.Date);
        }
    }
}

Try it online

If you look at the generated SQL, then you will see that one SQL query is executed for retrieving customers and then for each customer, another query is executed for retrieving the Invoices related to that customer. So, it means, if you have 1000 customers in your database then EF will execute 1000 queries for retrieving invoices for that 1000 customers.

EF generates the following query for retrieving customers.

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name]
    FROM [dbo].[Customers] AS [Extent1]

And the following query is generated for retrieving invoices of a specific customer.

1
SELECT
2
    [Extent1].[Id] AS [Id],
3
    [Extent1].[Date] AS [Date],
4
    [Extent1].[CustomerId] AS [CustomerId]
5
    FROM [dbo].[Invoices] AS [Extent1]
6
    WHERE [Extent1].[CustomerId] = @EntityKeyValue1

• Lazy loading is a great mechanism but only if you know when and how to use it.
• But look at our example again. Now if you look at this example, then you will see the select N+1 problem.
• The problem is happening because the Lazy loading is enabled by default and when we are executing a single query and then N following queries (N is the number of parent entities) to query for something.

The best way to avoid the select N+1 problem in Entity Framework is to use the Include method. It will create one query with needed data using SQL JOIN clause which is more efficient as compared to the previous one.

Let's update our query by using the Include method.

using (var context = new MyContext())
{
    var list = context.Customers
        .Include(c => c.Invoices)    
        .ToList();    
    
    foreach (var customer in list)
    {
        Console.WriteLine("Customer Name: {0}", customer.Name);
        foreach (var customerInvoice in customer.Invoices)
        {
            Console.WriteLine("\tInvoice Date: {0}", customerInvoice.Date);
        }
    }
}

Try it online

In the above example, we are telling EF explicitly that besides Customers we also need their Invoices. The following is the SQL generated query:

1
SELECT
2
    [Project1].[Id] AS [Id],
3
    [Project1].[Name] AS [Name],
4
    [Project1].[C1] AS [C1],
5
    [Project1].[Id1] AS [Id1],
6
    [Project1].[Date] AS [Date],
7
    [Project1].[CustomerId] AS [CustomerId]
8
    FROM ( SELECT
9
        [Extent1].[Id] AS [Id],
10
        [Extent1].[Name] AS [Name],
11
        [Extent2].[Id] AS [Id1],
12
        [Extent2].[Date] AS [Date],
13
        [Extent2].[CustomerId] AS [CustomerId],
14
        CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
15
        FROM  [dbo].[Customers] AS [Extent1]
16
        LEFT OUTER JOIN [dbo].[Invoices] AS [Extent2] ON [Extent1].[Id] = [Extent2].[CustomerId]
17
    )  AS [Project1]
18
    ORDER BY [Project1].[Id] ASC, [Project1].[C1] ASC

As you can see that Entity Framework has used LEFT OUTER JOIN clause to get all needed data. Another important point is that using Include method in the context which supports lazy loading can prevent the n+1 problem.

Source : https://entityframework.net/when-to-use-include

TIUConsulting

  1. What does include function of Linq do

Ans : https://interview-preparation-for-you.blogspot.com/2021/06/when-to-use-include-with-entity.html

  2.  How to write left join in linq query

      Ans using defualtIfEmpty function

var q =
    from c in categories
    join p in products on c.Category equals p.Category into ps
    from p in ps.DefaultIfEmpty()
    select new { Category = c, ProductName = p == null ? "(No products)" : p.ProductName };

  3. What we will do in ngAfterViewInit

Ans : https://interview-preparation-for-you.blogspot.com/2021/06/ngafterviewinit.html

  4. What we will do in ngDoCheck

  5. Delete duplicate row except one

Delete from Employee m1 , Employee m2 where m1.Name=m2.Name and m1.CreatedDate<m2.CreateDate

Indexed View

An indexed view has a unique clustered index. The unique clustered index is stored in SQL Server and updated like any other clustered index. An indexed view is more significant compared to standard views that involve complex processing of large numbers of rows, such as aggregating lots of data, or joining many rows. If such views are frequently referenced in queries, we can improve performance by creating a unique clustered index on the view. For standard view result set is not stored in database, instead of this the result set is computed for each query but in case of clustered index the result set is stored in the database just like a table with a clustered index is stored. Queries that don’t specifically use the indexed view can even benefit from the existence of the clustered index from the view. Index view has some cost in the form of performance, if we create an indexed view, every time we modify data in the underlying tables then not only must SQL Server maintain the index entries on those tables, but also the index entries on the view. In the developer and enterprise editions of SQL Server, the optimizer can use the indexes of views to optimize queries that do not specify the indexed view. In the other editions of SQL Server, however, the query must include the indexed view and specify the hint NOEXPAND to get the benefit of the index on the view.

 

How to create indexed Views?

 

To create an indexed view, first we need to create a view with schema binding option and after this create an index on view. For better understanding let us take an example.

 

 

We will create an indexed view for above table. First we create a view on Employee table,

1. CREATE VIEW VW_Employee WITH SCHEMABINDING AS    
2. SELECT e.Emp_Id,e.EmployeeName,e.EmpSalary,e.StateId,e.CityId    
3. FROM dbo.Employee e    
4. WHERE e.EmployeeName LIKE '[A-P]%'    

In above query create a view with schemabinding option. It is important for indexed view that view must be created with schemabinding option. Schemabinding option ensure that the underlying tables cannot be altered in any way that would materially affect the indexed view unless the view is first altered or dropped. If we try to create an indexed view without schemabinding option then SQL Server will throw an error of “Cannot create index on view 'VW_Employee' because the view is not schema bound”.

 

Example

 

 

Above image clear that for indexed view can’tbe created without Schema binding option. Once this index is created, the result set of this view is stored in the database just like any other clustered index. 

 

How an Indexed View Works

 

Whenever we add a unique clustered index to a view, materialize view is created. Materialized views are disk based and are updated periodically based upon the query definition. In other word the view persists to disk, with its own page structure, and we can treat it just like a normal table. Any aggregations defined by the indexed view are now pre-computed, and any joins pre-joined, so the engine no longer has to do this work at execution time. A well-crafted indexed view write fewer pages to disk than the underlying tables, that means fewer pages queries need to read fewer pages to return results. In form of result we will get fast and efficient result.

 

 

 

 

In above query we can see that query optimizer perform clustered index scan.

 

If we created a clustered index on a view then it is not compulsory that query optimizer always uses this clustered index, query optimizer can use another execution plan that it finds more efficient compared to clustered index. Let us take an example.

 

Query

1. --Create View  
2. CREATE VIEW VI_Demo WITH SCHEMABINDING as    
3. SELECT tcgi.Company_Id, tcgi.Company_Name, tcgi.Contact_Person, tmmc.Category_Name, tcgi.Establish_Year, tcgi.Address+', '+tcgi.PincodeAS Address_, tcgi.Mobile_Number+ISNULL(','+tcgi.Landline_Number,'')ASContact_Info    
4. ,ISNULL(tcgi.Website,'Not Available')ASWebsite,ISNULL(tcgi.Email_Id,'Not Available')AS Email,tcgi.Latitude, tcgi.Longitude, ISNULL(tcgi.Facebook_Id,'Not Available') AS Facebook_Id,    
5. ISNULL(tcgi.Linkedin_Id,'Not Available')AS Linkedin_Id,ISNULL(tcgi.Twitter_Id,'Not Available')AS Twitter_Id,ISNULL(tcgi.Google_Plus_Id,'Not Available')AS Google_Plus_Id    
6. FROM dbo.TblCompany_General_Infotcgi    
7. INNER JOIN    
8. dbo.TblMaster_Main_Categorytmmc    
9. ON    
10. tcgi.Category_Id=tmmc.Category_Id    
11. INNER JOIN    
12. dbo.TblUser_Profiletup    
13. ON    
14. tup.User_Id=tcgi.User_Id    
15. WHERE    
16. tcgi.Company_Id>1    
17. --Create Clustered Index  
18. CREATE UNIQUE CLUSTEREDINDEX idx_MyView ON VI_Demo(Company_Id)    
19. --Select Data from view  
20. SELECT * FROM VI_Demo    

Execution Plan

 

 

We can see that query optimizer doesn’t use the clustered index because this happens often if the optimizer thinks it can do better with the base tables.

 

To force the query optimizer to use the “Clustered Index” always use “NOEXPAND “ option like below:

 

 

Now query optimizer always use the clustered index even if it have better execution plan, so it is not nice to force the query optimizer to use clustered index using the NOEXPAND option.

 

Where to Use Indexed View

 

Indexed views have both a benefit and a cost. The benefit is that query optimizer provides more efficient and faster results for complex and redundant queries. The cost of an indexed view is on the maintenance of the clustered index. In the following scenario, Indexed View can be used.

• When you use the same complex query on many tables, multiple times.
• When new system need to read old table data, but doesn't watch to change their perceived schema.
• The environments that are best suited for indexed views are data warehouses, data marts, OLAP databases but transactional environment are less suitable for Indexed View.

Restrictions on Indexed Views

• View must be created with SCHEMABINDING.
• Functions using in definition of view must have been created with SCHEMABINDING.
• Base tables must have been created with the proper ANSI_NULLS setting..

Source :  https://www.c-sharpcorner.com/article/sql-server-indexed-views/