Some Important interview Question Answer
hi friend i want to share some important Question and answer which is very useful for interview
Explain what is LINQ? Why is it required?
Language Integrated Query or LINQ is the collection of standard query operators which provides query facilities into.NET framework language like C#, VB.NET.
LINQ is required as it bridges the gap between the world of data and world of objects.
Restriction Operator
Restriction operator can be applied by using Where clause. Example of Where clause:
var filterEnumerable = from emp in employees
where emp.age > 50
select emp;
Partitioning using Take /Skip operators
Take can be used when we take first N elements in a list, skip will take the elements after N.
var filterEnumerable = (from emp in employees
select emp).Take<Employee>(2);
Takewhile and skipwhile operator will select from a list based on a delegate passed in.
var filterEnumerable = (from emp in employees
select emp).SkipWhile<Employee>(
r => r.name.Length > 4);
Join Operators:
Join operators have 2 parts. The outer part gets results from inner part and vice versa so returns the result based on both
var filterEnumerable = from emp in employees
join ord in orders
on emp.name
equals ord.empName
select emp;
Concatenation Operator:
The Concatenation operator concats two sequence.
var filterEnumerable = (from emp in employees
select emp).Concat<Employee>(
from emp in employees1
select emp);
OrderBy / ThenBy
Orderby/ThenBy can be used to order data results.
var orderItems = from emp in employees
orderby emp.name, emp.age descending;
var orderItems =employees.OrderBy(i => i.name).ThenByDescending(i => i.age);
GroupBy Operator:
This is used to group elements.
var itemNamesByCategory = from i in _itemList
group i by i.Category into g
select new { Category = g.Key, Items = g };
var filterEnumerable2 = from emp in employees
where emp.age >65 //Normal Where clause works on all items
group emp by emp.age into gr where gr.Key > 40
select new {
aaa = gr.Key,
ccc=gr.Count<employee>(),
ddd=gr.Sum<employee>(r=>r.age),
bbb = gr
};
Union operator produces an union of two sequences
var un = (from i in _itemList
select i.ItemName).Distinct()
.Union((from o in _orderList
select o.OrderName).Distinct());
Intersect operator produces an intersection of two sequences.
var inter = (from i in _itemList
select i.ItemID).Distinct()
.Intersect((from o in _orderList
select o.OrderID).Distinct());
Except operator produces a set of difference elements from two sequences.
var inter = (from i in _itemList
select i.ItemID).Distinct()
.Except((from o in _orderList
select o.OrderID).Distinct())
Lambda Expression
A lambda expression is an anonymous function and it is mostly used to create delegates in LINQ. Simply put, it's a method without a declaration, i.e., access modifier, return value declaration, and name.
Convenience. It's shorthand that allows you to write a method in the same place you are going to use it. Especially useful in places where a method is being used only once and the method definition are short. It saves you the effort of declaring and writing a separate method to the containing class.
Benefits
Lambda expressions should be short. A complex definition makes the calling code difficult to read.
Lambda basic definition: Parameters => Executed code
a. Reduced typing. No need to specify the name of the function, its return type, and its access modifier.
b. When reading the code, you don't need to look elsewhere for the method's definition.
2. How do we define a lambda expression?
Simple Example
n => n % 2 == 1
• n is the input parameter
• n % 2 == 1 is the expression
You can read n => n % 2 == 1 like: "input parameter named n goes to anonymous function which returns true if the input is odd".
What is the difference between FirstOrDefault() and SingleOrDefault() extension method in LINQ ?
a. FirstOrDefault() = gets the first item that matches a given criteria.
b. SingleOrDefault() = if you specify this extension method that means you are specifically saying that there can be only one value that matches the criteria. If there are more than 1 value that matches the criteria, throw an exception.
What is the difference between First() and Single() extension methods in LINQ ?
First() - There is at least one result, an exception is thrown if no result is returned.
Single() - There is exactly 1 result, no more, no less, an exception is thrown if no result is returned.
IEnumerable Vrs IQueryable
In Linq, to query data from database and collections we use IEnumerable and IQueryable. IEnumerable is inherited by IQueryable, so it has all features of it and of its capabilities. Both has their own importance to query data and data manipulation.
IEnumerable :-
1. It exists in System.Collection namespace.
2. It can move forward only over collection.
3. It is best to query data from in-memory collections like Array, List, etc.
4. It is suitable for Linq to Objects and Linq to Xml queries.
5. It doesn't support lazy loading, hence not suitable for paging like scenario.
DataContextClasses db= new DataContextClasses();
IEnumerable<Employee>List =dc.Employees.Where(m=>m.Name.StartsWith("a"));
list=list.Take<Employee>(10);
IQueryable :-
1. It exists in System.Linq namespace.
2. It can move forward only over collection.
3. It is best to query data from out-memory like remote database.
4. It is suitable for Linq to Sql queries.
5. It supports lazy loading, hence suitable for paging like scenario.
DataContextClasses db= new DataContextClasses();
IQueryable<Employee>List =dc.Employees.Where(m=>m.Name.StartsWith("a"));
list=list.Take<Employee>;(10);
Mention what is the role of DataContext classes in LINQ?
DataContext class acts as a bridge between SQL Server database and the LINQ to SQL. For accessing the database and also for changing the data in the database, it contains connections string and the functions.
Extension Methods
Another new concept that comes with .NET 3.5 is the Extension methods. Now we can include our own custom methods in already defined objects. We can create static classes and include custom methods to objects. Extension method behavior is similar to that of static methods. You can declare them only in static classes. To declare an extension method, you specify the keyword this as the first parameter of the method. Let us look at the following example:
public static class ExtensionMethods
{
public static int ToInt32Extension(this string s)
{
return Int32.Parse(s);
}
}
If we include the namespace to our application, any string variable will have ToInt32Extension method. This function takes 0 arguments and passes the string to s. We can also pass parameters just like below:
public static class ExtensionMethods
{
public static int ToInt32ExtensionAddInteger(this string s,int value)
{
return Int32.Parse(s) + value;
}
}
Here integer value is also passed.
How do you assign a Lambda expression to a delegate?
delegate int del(int i);
del myDelegate = x => x * x;
int j = myDelegate(5); //j = 25
Difference between Replace and STUFF
REPLACE is used to replace all the occurrences of the given pattern in a string.
Example:
SELECT REPLACE('Hello','H','m')
-- mello
SELECT Replace('You are Rocks!', 'Rocks', 'cool')
Output: You are cool!
Note: - If the string isn't found, no changes will be made
STUFF used to replace the part of string with some other string.
Example:
SELECT STUFF('Hello Rock',1,5,'Hi')
-- Hi Rock
CHARINDEX
CHARINDEX is used to get starting position of the specified expression in a string.
SELECT ContactName, CHARINDEX('an',ContactName) AS 'Index' FROM Customers
Output
ContactName Index
Maria Anders 7
Ana Trujillo 1
Thomas Hardy 0
SELECT CHARINDEX('ax','Syntax-Example-Syntax',0) OR
SELECT CHARINDEX('ax','Syntax-Example-Syntax')
Output: 5
Above example returns position of starting index for characters 'ax' in specified string. It starts searching from beginning of the string. As it starts searching from beginning of the string, it returns position of 1st occurrence of 'ax' from expression2.
SELECT CHARINDEX('ax','Syntax-Example-Syntax',6)
Output: 20
Above example returns position of starting index for characters 'ax' in specified string, this time it starts searching after starting 6 characters. As it starts searching after 6 characters, it returns position of 2nd occurrence of 'ax' from expression2.
What are Different Types of Locks?
• Shared Locks: Used for operations that do not change or update data (read-only operations), such as a SELECT statement.
• Update Locks: Used on resources that can be updated. It prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
• Exclusive Locks: Used for data-modification operations, such as INSERT, UPDATE, or DELETE. It ensures that multiple updates cannot be made to the same resource at the same time.
• Intent Locks: Used to establish a lock hierarchy. The types of intent locks are as follows: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
• Schema Locks: Used when an operation dependent on the schema of a table is executing. The types of schema locks are schema modification (Sch-M) and schema stability (Sch-S).
• Bulk Update Locks: Used when bulk-copying data into a table and the TABLOCK hint is specified.
Optimistic VS Pessimistic
1) Optimistic means....the table is open for read/write over entire network for all users/sessions. We can move the cursor, backward or forward dynamically.
2) Pessimistic means... the table is open for read/write only for that current session. The other session users cannot edit the same.
Magic/Logical tables
Magic tables are nothing but inserted and deleted which are temporary object created by server internally to hold the recently inserted values in the case of insert and to hold recently deleted values in the case of delete, to hold before updating values or after updating values in the case of update.
Display Duplicate Record count with name
SELECT FirstName, COUNT(*) TotalCount FROM Employee GROUP BY FirstName HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
Different Types of Temporary Tables
SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:
• Local Temp Table
• Global Temp Table
Local Temp Table
Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.
CREATE TABLE #LocalTempTable(
UserID int,
UserName varchar(50),
UserAddress varchar(150))
The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a general table like:
insert into #LocalTempTable values ( 1, 'Abhijit','India');
Now select records from that table:
select * from #LocalTempTable
After execution of all these statements, if you close the query window and again execute "Insert" or "Select" Command, it will throw the following error:
Msg 208, Level 16, State 0, Line 1
Invalid object name '#LocalTempTable'.
This is because the scope of Local Temporary table is only bounded with the current connection of current user.
Global Temp Table
Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.
CREATE TABLE ##NewGlobalTempTable(
UserID int,
UserName varchar(50),
UserAddress varchar(150))
Points to Remember Before Using Temporary Tables
• Temporary table created on tempdb of SQL Server. This is a separate database. So, this is an additional overhead and can causes performance issues.
• Number of rows and columns need to be as minimum as needed.
• Tables need to be deleted when they are done with their work.
Alternative Approach: Table Variable
Alternative of Temporary table is the Table variable which can do all kinds of operations that we can perform in Temp table. Below is the syntax for using Table variable.
Declare @TempTableVariable TABLE(UserID int,UserName varchar(50), UserAddress varchar(150))
The below scripts are used to insert and read the records for Tablevariables:
insert into @TempTableVariable values ( 1, 'Abhijit','India');
select * from @TempTableVariable
When to Use Table Variable Over Temp Table
Table variable is always useful for less data. If the result set returns a large number of records, we need to go for temp table.
What are all the different normalization?
The normal forms can be divided into 5 forms, and they are explained below -.
First Normal Form (1NF): This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns.
Second Normal Form (2NF): Meeting all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.
Third Normal Form (3NF): This should meet all requirements of 2NF. Removing the columns which are not dependent on primary key constraints.
Fourth Normal Form (3NF): Meeting all the requirements of third normal form and it should not have multi- valued dependencies.
What are all types of user defined functions?
Three types of user defined functions are.
• Scalar Functions.
• Inline Table valued functions.
• Multi statement valued functions.
Intersect
It is an extension method from the System.Linq namespace. In set theory, an intersection is the subset of each collection that is found in both collections.
using System;
using System.Linq;
class Program
{
static void Main()
{
int[] array1 = { 1, 2, 3 };
int[] array2 = { 2, 3, 4 };
var intersect = array1.Intersect(array2);
foreach (int value in intersect)
{
Console.WriteLine(value);
}
}
}
Output
2
3
Filter Even Odd Data by LINQ
using System;
using System.Linq;
class Program
{
static void Main()
{
int[] array = { 1, 2, 3, 4, 5, 6, 7, 8, 9 };
var result = array.GroupBy(a => IsEven(a));
foreach (var group in result)
{
Console.WriteLine("IsEven = {0}:", group.Key);
foreach (var value in group)
{
Console.Write("{0} ", value);
}
Console.WriteLine();
}
}
static bool IsEven(int value)
{
return value % 2 == 0;
}
}
Output
IsEven = False:
1 3 5 7 9
IsEven = True:
2 4 6 8
C# program that calls Except method
using System;
using System.Linq;
class Program
{
static void Main()
{
int[] values1 = { 1, 2, 3, 4 };
int[] values2 = { 1, 2, 5 };
// Remove all values2 from values1.
var result = values1.Except(values2);
foreach (var element in result)
{
Console.WriteLine(element);
}
}
}
Output
3
4
C# program that uses ElementAt method
using System;
using System.Linq;
class Program
{
static void Main()
{
string[] array = { "Dot", "Net", "Perls" };
string a = array.ElementAt(0);
Console.WriteLine(a);
string b = array.ElementAt(1);
Console.WriteLine(b);
string c = array.ElementAt(2);
Console.WriteLine(c);
// This is out of range.
string d = array.ElementAt(3);
}
}
Output
Dot
Net
Perls
Unhandled Exception: System.ArgumentOutOfRangeException:
Index was out of range.
C# program that uses ElementAtOrDefault
ElementAtOrDefault returns the default value for the type, which for int is 0.
using System;
using System.Linq;
class Program
{
static void Main()
{
int[] array = { 4, 5, 6 };
int a = array.ElementAtOrDefault(0);
int b = array.ElementAtOrDefault(1);
int c = array.ElementAtOrDefault(-1);
int d = array.ElementAtOrDefault(1000);
Console.WriteLine(a);
Console.WriteLine(b);
Console.WriteLine(c);
Console.WriteLine(d);
}
}
Output
4
5
0
0
C# program that removes duplicate elements
using System;
using System.Linq;
class Program
{
static void Main()
{
int[] array1 = { 1, 2, 2, 3, 4, 4 };
var result = array1.Distinct();
foreach (int value in result)
{
Console.WriteLine(value);
}
}
}
Output
1
2
3
4
C# program that uses Zip extension method
using System;
using System.Linq;
class Program
{
static void Main()
{
var array1 = new int[] { 1, 2, 3, 4, 5 };
var array2 = new int[] { 6, 7, 8, 9, 10 };
var zip = array1.Zip(array2, (a, b) => (a + b));
foreach (var value in zip)
{
Console.WriteLine(value);
}
}
}
Output
7
9
11
13
15
