Object Oriented Programming

Object Oriented Programming is a methodology to design a program using classes and objects. It simplifies the software development and maintenance with the help of:

• Object: Any Entity that has state and behavior is known as an Object which can be physical and logical
• Class: Collection of Objects is called class. It is a logical entity
• Inheritance: This allows one to define a class in terms of another class, which makes it easier to create and maintain applications. This provides code reusability and is also be used to achieve runtime polymorphism.
  
  Terminology used for Inheritance:
  • Base Class: The class whose features are inherited is knows as base class / super class / parent class
  • Sub Class: The class that inherits the base/super/parent class is known as subclass. This can add it's own fields and methods on top of base class
  • Reusability: When we want to create a new class and if there is a class already existing with some methods, we can derive that as a new class which is called reusability
  Syntax:

  The symbol used for Inheritance is :
  
  public class derived-class : base-class {
  

  // code goes here . . .

  }

  Types of Inheritance:
  • Single Inheritance: In this sub classes will inherit features, methods and fields from one super class
  • Multilevel Inheritance: In this, a derived class will inherit from a base class as well as the derived class will also be a base class for other classes
  • Hierarchical Inheritance: In this, once class will be served as super class for more than one sub class
  • Multiple Inheritance: The derived class can be derived from more than one base class. Remember, C# does not support multiple inheritance at class level, but will support multiple inheritance at interface level
  • Hybrid Inheritance: This is a mix of two or more inheritance types. This also, can be achieved in C# using interfaces and not with classes
• Polymorphism: Means having many forms. In OOP paradigm, this is often referred as, "One interface, multiple functions"
  
  Types of Polymorphism:
  • Static Polymorphism: The mechanism of linking a function with an object during compile time is called static polymorphism which is also called as early binding. Below are the techniques used to implement static polymorphism in C#:
    • Function Overloading: We can have multiple definitions for the same function in the same scope and must differ from each other by the types or the number of arguments. And, cannot be differentiated by the return type
    • Operator Overloading: Most of the built-in C# operators can be overloaded. These are the functions with special names with Operator keyword followed by the symbol for the operator being defined. These also have a return type and a parameter list
  • Dynamic Polymorphism: This is implemented in C# by Abstract classes and Virtual Functions
    • Abstract classes provide partial class implementation of an interface. Implementation is completed when a derived class inherits from it. These classes contain abstract methods which are implemented by the derived class. You cannot create an instance of an abstract class. You cannot declare an abstract method outside an abstract class. And, when a class is declared as Sealted, then, it cannot be inherited and abstract classes cannot be sealed
    • You use virtual functions defined when you want the function to be implemented differently as part of inherited classes. This will help deciding the function behavior at runtime instead of compile time.
• Abstraction: Hiding internal details and showing functionality is known as Abstraction. Using Abstract class and Interfaces one can achieve Abstraction in C#. An abstract class is never intended to be instantiated directly. And, these classes are typically used to deifne a base class in the class hierarchy. This class must contain atleast one abstract method which is marked by the keyword abstract in the class definition. The keyword abstract modifier indicates the incomplete implementation.
  
  Points to remember:
  • Abstraction is used at the time of Inheritance
  • One must use the override keyword before the method which is declared as abstract in child class, the abstract class is used to inherit in the child class
  • An abstract class cannot be inherited by structures
  • Contains constructors and destructors
  • Can implement methods with non-abstract mehtods
  • Cannot support multiple inheritance
  • Cannot be static
• Encapsulation: Wrapping Data and Code together into a single unit is called as Encapsulation. Technically, the variables or data of a class are hidden from any other class and this can be shared or accessed only through access modifiers. As the data in a class is hidden for other classes, this is also called as data hiding
  
  Advantages:
  • Data Hiding: User will have no idea about the inner implementation of a class. He only knows the we are passing values to accessors and variables are getting initialized to that value
  • Increased Flexibility: The variables of the class are either read-only or write-only depending on requirement. By using Get and Set accessor this can be achieved
  • Reusability: This also improves reusability to change the behavior
  • Unit Testing: This is easy to unit test code

Advantages

• Simple: The programmes written with OOP are really easy to understand. Since everything is treated as Objects, one can model a real-world concepts using OOP
• Modular: Since the parallel development of classes is possible in OOP concept, it results in the quick development of the complete programmes. Each object forms a separate entity whose internal workings are decoupled from other parts of the system
• Secured: It is a secured development technique since data is hidden and can't be accessed by external functions
• Extensible: Adding new features or responding to changes in operating environments can be solved by introducing few new object and modifying existing ones
• Maintainable: Programmes written using OOP technique are marginally easier to test, manage as well as maintain
• Reusable: This approach offers the reusability of classes. We can reuse the classes that are already created without writing them again and again

DisAdvantages

• Sometimes the relation between classes become artificial
• Designing a program using OOP concepts is little bit tricky
• One should have a proper planning before designing a program using OOP approach
• As everything is treated as objects in OOP, the programmers need proper design, programming and thinking skills in terms of objects
• The size of programmes developed with OOP is larger than the procedural approach
• Since large in size, more instructions need to be executed, which results in slower execution of programmes

Feature Differences

Trying to explain the Differences between some of the important features of C# with examples.

Differences between Var, Dynamic and Object

var is used to store an anonymous type or a collection of anonymous types, and scope limited to within the declared method. Dynamic type variables type checking is done at run-time and can be initialized by any type of data. Object is basically a block of memory that has been configured according to the blueprint.

Var	Dynamic	Object
Can store any type but mandatory to initialize the var type at the time of declaration	Can store any type of the variable	Can store any kind of value as this is the base for all types in .NET
It is type safe as compiler has all information of the stored value. No issues are run time	It is not type safe as compiler won't have any information until run time about variable type	Compiler has little information about the type
The scope of this type is limited to where it is defined. Neither be passed as argument nor used as return type	This can be passed as argument as well as can be used as method return type	This can be passed as argument as well as can be used as method return type
Casting is not required at anytime as compiler has all the information	Casting is not required but should be aware of properties and methods related to the stored type	Casting is required to original type to use it and perform operations
No problems as compiler has all the information	Causes problems, If wrong properties or methods are accessed as all the information will be resolved at run time	Causes problem at run time if the value is not getting converted to base data type
Introduced in C# 3.0 and useful cases of anonymous type scenarios	Introduced in C# 4.0 and useful in cases of working with Reflection, COM or dynamic languages	Introduced in C# 1.0 and sseful in cases where we don't know any information about the type

Collections and Collection Interfaces

Collections: Collection refers to a set of objects. These can grow and shrink dynamically as the objects added or deleted. Collection is basically a class, so needs to be declared before one can add elements to that. System.Collections namespace contains below collections:

• ArrayList: Represents an array of objects whose size is dynamically increased or decreased as required. It supports add or remove methods for adding and removing objects from the collection.
• Hashtable: Also represents a collection of objects which are stored in key/value pair, where key is a hash code and value is an object. Using key one can access the objects in the collection. Also supports add and remove methods for adding and removing objects from the collection.
• SortedList: This collection is a combination of ArrayList and Hashtable and along with can be sorted by the keys. It can be accessible by key or by index. Also supports add and remove methods for adding and removing objects from the collection.
• Stack: Represents a LIFO (Last In First Out) collection of objects. It supports Push and Pop methods for adding and removing objects from the collection.
• Queue: Represents a FIFO (First In First Out) collection of objects. Supports Enqueue and Dequeue methods for adding and removing objects from the collection.

Collection Interfaces: All the collection types use common interfaces which define the basic functionality for each collection class. Below are the key collection classes. IEnumerable acts as a base interface for all the collection types which is extended by ICollection which is further extended by IDictionary and IList interfaces. All collection interfaces are not implemented by all the collections. It depends on the nature of collection.

• IEnumerable: Provides an enumerator which supports a simple iteration over a non generic collection
• ICollection: Defines size, enumerators and synchronization methods for all nongeneric collections
• IDictionary: Represents nongeneric collection of key/value pairs
• IList: Represents nongeneric collection of objects that can be individually accessed b index

Virtual, Override, and New keywords

All these three keywords will help support working with Polymorphism which is nothing but method overriding and method overloading. Virtual and Override keywords support method overriding whereas New keyword supports method hiding.

Virtual: this keyword lets a method, property, indexer or event declared in the base class to be overridden or modifying the functionality in the derived class.

Override: this keyword lets to extend or modify a base classes method, property, indexer or event in derived class.

New: this keyword lets to hide a method, property, indexer or event declared in the base class in to derived class.

• Virtual and Override causes late binding and is called as runtime poluymorphism. This takes same name as base class with same parameters
• New causes early binding and also called as compile time polymorphism. This takes same name as base class with different parameters

Type Casting or Type Conversion

This is a mechanism to convert one data type value to another data type, and is possible only if both the data types are compatible to each other. Otherwise InvalidCastException will be thrown. Below are the different type of conversions:

• Implicit Conversion: This is being handled automatically by the compiler with no data lose. This is safe type conversion and includes converting smaller to larger data types as well as derived classes to base classes. This is also called as Upcasting.
• Explicit Conversion: This is being done by using a cast operator. In this type of conversion data might be lost or conversion might not be succeed for some reasons and not an type safe conversion. Also supports converting smaller to larger data types as well as derived classes to base classes. This is also called as Downcasting.
• User-defined Conversion: This conversion is performed by using special methods that you can define to enable explicit and implicit conversions. In this type, all conversions methods must be declared as static. It includes conversion of class to struct or basic data type and struct to class or basic data type.

Is vs As

IS and AS operators are helpful to handle the exceptions occurred during casting / converting one data type to another data type. These are helpful to do a safe type casting. Usually exceptions occur when the new data type is not compatible with the given object data type.

IS Operator: This operator checks whether both given object and the new object type is compatible or not. It returns boolean (True / False) value as a result. In case of a given object is null, this will return false as there is no object to check for type. The drawback of using this operator is, the Performance. As every time CLR checks for each base type with the specified type following the inheritance hierarchy. Below is how to use IS operator:

Object obj = new Object(); // Creates a new Object
Boolean isCompatible = (obj is Organization); // No exception in this case but sets isCompatible as False
If (isCompatible) { Organization org = (Organization) obj; }

( OR )

if (obj is Organization) { Organization org = (Organization) obj; }

AS Operator: Like IS operator, AS operator also helps to check the type of a given object is compatible with new object type. Instead of boolean, this returns NON-NULL if given object is compatible else NULL as result. This operator performs only reference conversions, nullable conversions and boxing conversions, and cannot perform user-defined conversions. Using this, CLR checks object type for only one time. So, AS operator provide good performance over IS operator.

Object obj = new Object(); // Creates a new Object obj
Organization org = obj as Organization; // This cast fails, but no exception is thrown and org will set to NULL
If (org != null) { // Execute your code here }

Boxing and Unboxing

These always refers to the allocation of a value on the Heap rather than on the Stack.

Boxing: Refers to an Implicit conversion of a value type to a reference type. Basically in this process, a value type is being allocated on Heap rather from Stack

Unboxing: Refers to an Explicit conversion of a reference type to a value type. Basically in this process, a reference type is being allocated back to Stack from Heap

int curValue = 12; // value type which will be created on Stack
Object retBoxed = curValue;
int retUnboxed = (int) retBoxed; // Unboxed from heap to Stack

• Try to eliminate using Boxing as it slows down the performance and increases memory usage
• NullReferenceException will be thrown by compiler in case of unboxing a null
• InvalidCastException will be thrown by compiler in case of unboxing a reference type to an incompatible value type

Constant, ReadOnly and Static

Constants: These must be assigned a value at the time of declaration and after that they cannot be modified.

• These are Static by default, so no need to declare them using static keyword
• They must have a value at compilation time
• Can be declared within functions and be used as Attributes
• These can be declared as pubic, private, protected, internal or protected internal access modifiers

ReadOnly: These can be initialized either at the time of declaration or with in the constructor of the defined class.

• Must have set the value by the time constructor exists
• As these are not static by default, one need to define them using static keyword if they want this type of variable to be static
• Will be evaluated when instance is created
• Use this modifier when one want to make a field constant at run time
• ReadOnly can be applied to Value and Reference types but not to delegates and events

Static: This is used to specify a static member, meaning these are common to all the objects and they do not tied up to a specific object.

• Evaluated when code execution hits class reference ie. when new instance is created or a static method is executed
• Must have a value by the time the static constructor is done
• These can be used with classes, fields, methods, properties, operators, events and constructors, but no on indexers, destructors or other than classes

When to use what:

• If we know the value will never ever change use const
• If we re unsure that the value will change, and you don't want other classes or code to be able to change it, use readonly
• If we need a field to be a property of a type, and not a property of an instance of that type, use static

ref and out parameters

Both ref and out parameters are used to pass an argument within a method.

ref: the ref keyword is used to pass an argument as a reference, which means that when a value of the parameter is changed then it gets reflected in the calling method. An argument which is passed using a ref keyword must be initialized in the calling method before it is passed to the called method.

public static string GetNextName(ref int id)
{

string returnText = "Next-" + id.ToString();

id += 1;

return returnText;

}
static void Main(string[] args)
{

int i = 1;

Console.WriteLine("Previous value of integer i:" + i.ToString());

string test = GetNextName(ref i);

Console.WriteLine("Current value of integer i:" + i.ToString());

}

out: the out keyword is also used to pass an argument like ref but can be passed without assigning any value to it. An argument that is passed using an out keyword must be initialized in the called method before it returns back to calling method.

public static string GetNextNameByOut(out int id)
{

id = 1;

string returnText = "Next-" + id.ToString();

return returnText;

}
static void Main(string[] args)
{

int i = 0;

Console.WriteLine("Previous value of integer i:" + i.ToString());

string test = GetNextNameByOut(out i);

Console.WriteLine("Current value of integer i:" + i.ToString());

}

Working with XML Data Type

The XML data type, introduced in SQL Server 2005. XML data type can store either a complete XML or a fragment of XML document. Before xml data type the xml data was stored in varchar or text data type, that was proven to be poor in terms of querying and manipulating the XML data.

When to use XML data type:

• If one wants to store the XML document in the database and retrieve and update the document as a whole - that is, if you never need to query or modify the individual XML components - you should consider using one of the large object data types like VARCHAR(MAX), NVARCHAR(MAX) or VARINARY(MAX).
• If you want to preserve the original form such as legal documents or want to retain an exact textual copy, use large object storage.
• The rest of the time, one should consider using XML data type which ensures that the data is well formed according to ISO standards, and supports fine-grained queries and modifications to specific elements and attributes within the XML. One can also index an XML column and associate its data with an XML schema collection in order to preserve its content and structure.

Limitations of using XML data type:

• This cannot be used as a key in an index
• Cannot exceed 2 GB size
• XML data type cannot be compared, sorted, or used in GROUP BY
• It does not support casting or converting to either text or ntext
• It cannot be used as a parameter to any scalar, or built-in functions other than ISNULL, COALESCE and DATALENGTH

Data Manipulations on XML column: Below illustrations explains how to SELECT, INSERT, UPDATE and DELETE data in a XML column.

Below is the sample XML used to walk through the manipulations:

<Employees>

<Employee>

<ID>1</ID>

<Name>John Test</Name>

<Department>IT</Department>

<Address>

<Street1>4404, Main Road</Street1>

<Street2/>

<City>Reston</City>

<State>VA</State>

<Zip>20171</Zip>

</Address>

</Employee>

<Employee>

<ID>2</ID>

<Name>King George</Name>

<Department>Admin</Department>

<Address>

<Street1>3004, St. Patric Road</Street1>

<Street2>Apt D</Street2>

<City>Vienna</City>

<State>VA</State>

<Zip>20171</Zip>

</Address>

</Employee>

</Employees>

SELECTing Employee Name, Department and Address for the given Employee ID

SELECT

t.c.value('Name[1]', 'varchar(max)') as 'Employee Name',

t.c.value('Department[1]', 'varchar(max)') as 'Department',

t1.c1.value('Street1[1]', 'varchar(max)') + ', ' +

t1.c1.value('Street2[1]', 'varchar(max)') +

-- Conditionalyy append comma based on Street2 value, Append when Street2 available else don't

case when ((t1.c1.value('Street2[1]', 'varchar(max)') != '')) then (', ') else ('') end +

t1.c1.value('City[1]', 'varchar(max)') + t1.c1.value('State[1]', 'varchar(max)') + ' - ' +

t1.c1.value('Zip[1]', 'varchar(max)') as 'Address'

FROM

@XML.nodes('//Employees/Employee') as t(c)

CROSS APPLY t.c.nodes('Address') as t1(c1)

WHERE

t.c.value('ID[1]', 'int') = 1

UPDATing Street2 of Address for the given Employee ID

SET @employeeID = 2 -- Provide employee ID
-- Check for provided employee ID and if Street2 value is NULL
IF @Xml.exist('/Employees/Employee/ID[text()=sql:variable("@employeeID")]/../Address/Street2/text()') = @employeeID

BEGIN -- will execute for employeeID = 2

PRINT 'First Part'

SET
@Xml.modify('replace value of (/Employees/Employee/ID[text()=sql:variable("@employeeID")]/../Address/Street2/text())[1] with "Apt F"')

END

ELSE -- IF Street2 tag value is present execute below

BEGIN -- will execute for employeeID = 1

PRINT 'Second Part'

SET @Xml.modify('delete (/Employees/Employee/ID[text()=sql:variable("@employeeID")]/../Address/Street2)')

SET @Xml.modify('insert (<Street2>Apt A</Street2>) after (/Employees/Employee/ID[text()=sql:variable("@employeeID")]/../Address/Street1)[1]')

END

SELECT @Xml

DELETing Street2 of Address for the given Employee ID

SET @employeeID = 1 -- Provide employee ID

BEGIN

SET @Xml.modify('delete (/Employees/Employee/ID[text()=sql:variable("@employeeID")]/../Address/Street2)')

END

SELECT @Xml

INSERTing Street3 after Street2 of Address for the given Employee ID

SET @employeeID = 1 -- Provide employee ID

BEGIN

SET @Xml.modify('insert (<Street3>Near</Street3>) after (/Employees/Employee/ID[text()=sql:variable("@employeeID")]/../Address/Street2)[1]')

END

SELECT @Xml

Performance Tuning

LINQ to Entity is a ORM for querying and managing database. Below are some tips and tricks that we should keep an eye on while designing and querying database using entity framework ORM.

Cold Vs Warm Query Execution:
The very first time any query is made against a given model, the Entity Framework does lot of work behind the scenes to load and validate the model. This is called Cold query. These are slow in nature. Further queries against an already loaded model are known as Warm queries and are much faster.

Below section explains different ways of reducing performance cost of both cold and warm queries.

Caching
Entity Framework comes with below built-in cache mechanisms:

Object Caching: This is also called as First Level Caching and uses ObjectContext instance's ObjectStateManager to keep track of the objects in memory that have been retrieved using that instance.

The behavior of this caching is - when EF materializes an entity returned as resultant of a query, the ObjectContext will check if an entity with the same key has already been loaded into its ObjectStateManager, if found EF will include it in the results. Although EF will still issue the query against the database. This behavior will decrease the performance and can be bypassed to eliminate materializing the same entity multiple times as below:

• Getting entities from the object cache using DbContext Find: Unlike a regular query, the Find method in DbSet will perform a search in memory before even issuing the query against the database. Find uses the primary key value to attempt to find an entity tracked by the context. NULL will be returned if the entity is not found in the context or database.
  
  Example of Find with auto-detect changes disabled:
  context.Configuration.AutoDetectChangesEnabled = false;
  var product = context.Products.Find(productId);
  context.configuration.AutoDetectChangesEnabled = true;
  
  Per MSDN, Find method can be used n below scenarios:
  • If the object is not cache the benefits of find are negated, but the syntax is still simpler than a query by key
  • If auto detect changes is enabled the cost of the Find method may increase by one order or magnitude, or even more depending on the complexity of your model and the amount in your object cache.
• Issues when the object cache has many entities: If the Object Cache has a very large amount of entities loaded, it may effect operations like Add, Remove, Find,Entry and more. Especially DefectChanges will negatively affected by large object caches. This synchronizes the object graph with the object state manager and its performance will determine directly by the size of the object graph.
  
  Range methods like AddRange and RemoveRange should be used instead of iterating through the collection as the advantage of using range methods is that the cost of DetectChange is called only once for the entire set of entities as opposed to once per each added entity.

Query Plan Caching: Reusing the generated store command when a query is executed more than once. When the query is executed first time, it goes through the internal plan compiler to translate the conceptual query into the store command. When the query plan caching is enables, whenever the query is executed next time, it retrieves the store command from query plan cache for execution bypassing the plan compiler. The query plan caching is shared across ObjectContext instances within the same AppDomain.

• Query plan cache is shared for all Entity SQL, LINQ to Entities and Compiled Query types
• Query plan caching is enabled by default for Entity SQL queries and LINQ to Entity queries, whether they are executed through an EntityCommand or through an ObjectQuery.
• Query plan cache can be disabled by setting the EnablePlanCaching property to false as below:
  var query = from customer in context.Customer where customer.CustomerId == id
  select new { customer.CustomerId, customer.Name };
  
  ObjectQuery oQuery = query as ObjectQuery;
  oQuery.EnablePlanCaching = false;
  
• For parameterized queries, changing the parameter value will still use the cached query

Metadata Caching : This is essentially caching of type information and type-to-database mapping information across different connections to the same model and unique per AppDomain.

Results caching: This is also called as Second Level Caching which keeps the results of queries in a local cache. One can check this before running a query against the database/store. This caching is not directly supported by EF but can be implemented using wrapping provider.

MSDN wrapper samples can be found at: Wrappers

Avoid using One Single Entity Model:
Entity Model specifies a single unit of work and not the whole database. So to eliminate the wastage of space and performance degradation, create separate entity models for related DB objects. For ex: all logs, batch process etc.

Disable Change Tracking for Entity
Object Tracking is not required whenever we are handling the read only data. So, disable object tracking by using MergeOption as below:

TestContext context = new TestContext();

context.testTable.MergeOption = MergeOption.NoTracking;

Using Views
Whenever the ObjectContext is created first time in an application, entity framework creates a set of classes which are required to access database. These set of classes are called views and if the model is large then creating this view may delay the web application's response time for the first request. Try to create this view at compile time using T4 templates or EdmGen.exe command line tool to reduce the response time.

Fetch only Required Fields
Avoid fetching all the fields of a database table. For example, if a table contains 25 fields and the current scenario requires only 5 fields, then just fetch only the required 5 fields instead of all the 25 fields.

Good Practice

var customer = (from cust in dataContext.Customers select new {customer. CustomerID, customer.Name, customer.Address }). ToList ();

Bad Practice

var customer = (from cust in dataContext.Customers select cust).ToList();

Use Appropriate Collection
As LINQ is highly used to query ObjectContext in EF and LINQ has Var, IEnumerable, IQueryable, IList type collections for data manipulation, and each collection has its own purpose in regards to performance, beware of using these collections for data manipulation.

Use Compiled Query
Compiled queries are helpful in scenarios where we know that it is used very frequently to fetch records. Comparatively this query is slow while executing first time but boost the performance significantly in following calls. Query is compiled once and can be used any number of time. Compiled Query does not need to be recompiled even if the parameter of the query is being changed.

Steps to create Compiled Query:

• Create a static class
• Add System.Data.Linq namespace
• Use LINQ's CompiledQuery class to create compiled LINQ query

Below example illustrates creating CompiledQuery with no parameters:

static class MyCompliedQueries

{

public static Func<DataClasses1DataContext ,IQueryable<Person>>
CompliedQueryForPesron = CompiledQuery.Compile((DataClasses1DataContext context)=>from c in context.Persons select c );

}

Above CompiledQuery can be called as below:

DataClasses1DataContext context = new DataClasses1DataContext();
var result = MyCompliedQueries.CompliedQueryForPesron(context);

Below example illustrates creating CompiledQuery with parameters:

static class MyCompliedQueries

{

public static Func<DataClasses1DataContext, int, IQueryable<Person>>
CompliedQueryForPesron = CompiledQuery.Compile(
(DataClasses1DataContext context, int personId)=>from c in context.Persons where c.PersonID == personId select c );

}

Above CompiledQuery can be called as below:

DataClasses1DataContext context = new DataClasses1DataContext();
var result = MyCompliedQueries.CompliedQueryForPesron(context, 9);

Retrieve only Required Records
To improve performance while retrieving records, retrieve only required no of records by using Take, While and Skip methods.

DataClasses1DataContext context = new DataClasses1DataContext();
int pageSize=10,startingPageIndex=2;

List lstCus = context.Persons.Take(pageSize).Skip(startingPageIndex * pageSize).ToList();

Avoid Contains
Try avoid using Contains of LINQ. This will be converted as "WHERE IN" in SQL which cause performance degrades.

Avoid Views
SQL Views degrade LIQ query performance. These are slow and impact performance badly. So avoid using Views in LINQ to Entities.

Debug and Optimize LINQ Query
With the help of LINQ Pad one can debug and optimize query. This tool can be used to optimize LINQ query.

LINQ: Language Integrated Query

LINQ is an acronym for Language Integrated Query. The Language Integrated part means that it is part of programming language syntax, and Query means, as it explains - is a means to retrieve data from a data source.

To retrieve data from different/multiple data sources in .NET, the underlying query languages use are:

• SQL and ADO.Net for relational databases
• XQuery and XSLT for XML

LINQ simplifies this working model by providing a common platform to execute the query and get the results. Basically LINQ query always works with objects, so the basic coding to query and transform data in XML, SQL database, ADO.NET Datasets, Collections and any other format for which LINQ provider available is common.

LINQ Archiecture

Below are different Types of LINQ:

• LINQ to Objects
• LINQ to XML (XLINQ)
• LINQ to Dataset
• LINQ to SQL (DLINQ)
• LINQ to Entites

LINQ Syntax: Following are the two ways of LINQ syntaxes:

• Lamda (Method) Syntax:
var testWords = words.where(w => w.length >10);
• Query (Comprehension) Syntax:
var testWords = (from word in words where w.length > 10);

Per MSDN, Query expression consists below clauses:

• FROM refers to Data Source
• WHERE takes care of Filtering the requirement
• SELECT takes responsibility of elements to return

Below are the Advantages and Disadvantages of using LINQ:

Advantages:

• LINQ can be used against different data sources and it is not limited to RDBMS
• Viewing table relationships is easy due to its hierarchical feature, which also enables composing queries by joining multiple table in less time
• LINQ allows a single syntax while querying different data sources with support of its unitive foundation
• LINQ is extensible, mean it is possible to use LINQ to query new data sources
• A single LINQ query can join several data sources
• Easy transformation for conversion of one data type to another, ex: SQL data to XML data
• Debugging is easy due to its integration in C#
• Writing more accurate queries is easy using LINQ intellisense
• LINQ queries are typesafe as the errors will be type checked at compile time

Disadvantages:

• For every query change, assembly needs to be recompiled and deployed
• LINQ queries are not precompiled so need to take extra cautions to handle performance
• In given scenarios, it is hard to understand advance LINQ queries
• There will always be some things you can do in SQL but not in LINQ

With this I am concluding the illustration. Feel free to share your feedback.

Happy Programming !!!

URL Rewrite

URL Rewrite is the process of altering the parameters in a URL (Uniform Resource Locator). The URL rewrite module is an extension to IIS which is available as a download for your stand-alone IIS Server. URL Rewrites can be managed at the server level or for individual sites as required.

Patterns are used through the URL Rewrite module. These are in one of three modes:

• Exact Match
• Wildcards - where an asterisk is used to mean "anything here" and is captured when matched
• ECMAScript regular expressions, which are Perl compatible regular expressions

Rules are two types as below:

• Inbound rules - looks at the request URLs and change them
• Outbound rules - inspects the traffic sent out, look for URLs within it, and rewrite them as needed. This is very handy when the content may use an absolute URL that is not what the user should be receiving (especially handy in reverse proxy scenarios)

Built in Rules URL rewrite supports various built in rules as below:

• Rule with rewrite map: allows you to define a set of paths and their replacements as a simple list
• Request blocking: disallow access to a path
• User-friendly URL: quickly creates rules to map path segments (separated by slashes) to query strings
• Reverse Proxy: allows the current server to reverse proxy another
• Enforce lowercase URLs: makes the client always use lowercase URLs via an HTTP status 301 ("Permanent") redirect
• Canonical domain name: uses an HTTP status 301 ("Permanent") redirect to ensure that clients always use the specified domain name
• Append or remove the trailing slash symbol: will either always add or always remove the trailing slash in an URL path using an HTTP status 301 ("Permanent") redirect

Database Schema

Here are some helpful queries to compare two databases at schema level to identify the newly added, modified or deleted database objects. Below illustration uses ExistingDB and LatestDB as two databases. LatestDB refers to latest and greatest whereas ExistingDB refers to existing PROD database.

ADDED Tables: Query to identify newly added tables in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying ADDED TABLES'
SELECT schema_name(schema_id), name FROM [@latestDB].sys.tables
EXCEPT
SELECT schema_name(schema_id), name FROM [@existingDB].sys.tables
ORDER BY schema_name(schema_id)
PRINT N'END OF Identifying ADDED TABLES'

DELETED Tables: Query to identify deleted tables in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying DELETED TABLES'
SELECT schema_name(schema_id), name FROM [@existingDB].sys.tables
EXCEPT
SELECT schema_name(schema_id), name FROM [@latestDB].sys.tables
ORDER BY schema_name(schema_id)
PRINT N'END OF Identifying DELETED TABLES'

ADDED Columns: Query to identify newly added columns in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying ADDED COLUMNS'
SELECT tab.SchemaName, tab.tablename, col.name as columnname FROM [@latestDB].sys.columns col
INNER JOIN (SELECT object_id, schema_name(schema_id) SchemaName, name as tablename FROM [@latestDB].sys.tables
          INTERSECT
          SELECT object_id, schema_name(schema_id) SchemaName, name as tablename FROM [@existingDB].sys.tables) tab
ON col.object_id = tab.object_id
EXCEPT
SELECT tab.SchemaName, tab.tablename, col.name as columnname FROM [@existingDB].sys.columns col
INNER JOIN (SELECT object_id, schema_name(schema_id) SchemaName, name as tablename FROM [@existingDB].sys.tables
          INTERSECT
          SELECT object_id, schema_name(schema_id) SchemaName, name as tablename FROM [@latestDB].sys.tables) tab
ON col.object_id = tab.object_id
PRINT N'END OF Identifying ADDED COLUMNS'

DELETED Columns: Query to identify deleted columns in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying DELETED COLUMNS'
SELECT tab.SchemaName, tab.tablename, col.name as columnname FROM [@existingDB].sys.columns col
INNER JOIN (SELECT object_id, schema_name(schema_id) SchemaName, name as tablename FROM [@existingDB].sys.tables
          INTERSECT
          SELECT object_id, schema_name(schema_id) SchemaName, name as tablename FROM [@latestDB].sys.tables) tab
ON col.object_id = tab.object_id
EXCEPT
SELECT tab.SchemaName, tab.tablename, col.name as columnname FROM [@latestDB].sys.columns col
INNER JOIN (SELECT object_id, schema_name(schema_id) SchemaName, name as tablename FROM [@latestDB].sys.tables
          INTERSECT
          SELECT object_id, schema_name(schema_id) SchemaName, name as tablename FROM [@existingDB].sys.tables) tab
ON col.object_id = tab.object_id
PRINT N'END OF Identifying DELETED COLUMNS'

ADDED Procedures: Query to identify newly added procedures in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying ADDED PROCEDURES'
SELECT schema_name(schema_id) as SchemaName, prc.name as ProcedureName FROM [@latestDB].sys.procedures prc
EXCEPT
SELECT schema_name(schema_id) as SchemaName, prc.name as ProcedureName FROM [@existingDB].sys.procedures prc
ORDER BY SchemaName, ProcedureName
PRINT N'END OF Identifying ADDED PROCEDURES'

DELETED Procedures: Query to identify deleted procedures in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying DELETED PROCEDURES'
SELECT schema_name(schema_id) as SchemaName, prc.name as ProcedureName FROM [@existingDB].sys.procedures prc
EXCEPT
SELECT schema_name(schema_id) as SchemaName, prc.name as ProcedureName FROM [@latestDB].sys.procedures prc
ORDER BY SchemaName, ProcedureName
PRINT N'END OF Identifying DELETED PROCEDURES'

MODIFIED Procedures: Query to identify modified procedures in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying MODIFIED PROCEDURES'
SELECT schema_name(prc.schema_id) as SchemaName, prc.name as ProcedureName, prc.modify_date FROM [@latestDB].sys.procedures prc
JOIN [@existingDB].sys.procedures prc2 on prc.name = prc2.name AND object_definition(prc.object_id) <> object_definition(prc2.object_id)

SELECT schema_name(prc.schema_id) as SchemaName, prc.name as ProcedureName, prc.modify_date FROM [@existingDB].sys.procedures prc
JOIN [@latestDB].sys.procedures prc2 on prc.name = prc2.name AND object_definition(prc.object_id) <> object_definition(prc2.object_id)
PRINT N'END OF Identifying MODIFIED PROCEDURES'

ADDED Views: Query to identify newly added views in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying ADDED VIEWS'
SELECT schema_name(schema_id) as SchemaName, vie.name as ViewName FROM [@latestDB].sys.views vie
EXCEPT
SELECT schema_name(schema_id) as SchemaName, vie.name as ViewName FROM [@existingDB].sys.views vie
ORDER BY SchemaName, ViewName
PRINT N'END OF Identifying ADDED VIEWS'

DELETED Views: Query to identify deleted views in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying DELETED VIEWS'
SELECT schema_name(schema_id) as SchemaName, vie.name as ViewName FROM [@existingDB].sys.views vie
EXCEPT
SELECT schema_name(schema_id) as SchemaName, vie.name as ViewName FROM [@latestDB].sys.views vie
ORDER BY SchemaName, ViewName
PRINT N'END OF Identifying DELETED VIEWS'

ADDED Indexes: Query to identify newly added indexes in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying ADDED INDEXES'
SELECT i.Name as IndexName, OBJECT_NAME(i.object_ID) as TableName, c.Name as ColumnName, i.type_desc FROM [@latestDB].sys.indexes i
INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
INNER JOIN [@latestDB].sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
EXCEPT
SELECT i.Name as IndexName, OBJECT_NAME(i.object_ID) as TableName, c.Name as ColumnName, i.type_desc FROM [@existingDB].sys.indexes i
INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
INNER JOIN [@existingDB].sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
ORDER BY TableName, ColumnName
PRINT N'END OF Identifying ADDED INDEXES'

DELETED Indexes: Query to identify deleted indexes in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying DELETED INDEXES'
SELECT i.Name as IndexName, OBJECT_NAME(i.object_ID) as TableName, c.Name as ColumnName, i.type_desc FROM [@existingDB].sys.indexes i
INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
INNER JOIN [@existingDB].sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
EXCEPT
SELECT i.Name as IndexName, OBJECT_NAME(i.object_ID) as TableName, c.Name as ColumnName, i.type_desc FROM [@latestDB].sys.indexes i
INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
INNER JOIN [@latestDB].sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
PRINT N'END OF Identifying DELETED INDEXES'

ADDED Triggers: Query to identify newly added triggers in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying ADDED TRIGGERS'
SELECT object_name(parent_id) as TableName, trg.name as TriggerName FROM [@latestDB].sys.triggers trg
EXCEPT
SELECT object_name(parent_id) as TableName, trg.name as TriggerName FROM [@existingDB].sys.triggers trg
ORDER BY TableName, TriggerName
PRINT N'END OF Identifying ADDED TRIGGERS'

DELETED Triggers: Query to identify deleted triggers in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying DELETED TRIGGERS'
SELECT object_name(parent_id) as TableName, trg.name as TriggerName FROM [@existingDB].sys.triggers trg
EXCEPT
SELECT object_name(parent_id) as TableName, trg.name as TriggerName FROM [@latestDB].sys.triggers trg
ORDER BY TableName, TriggerName
PRINT N'END OF Identifying DELETED TRIGGERS'

ADDED Functions: Query to identify newly added functions in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying ADDED FUNCTIONS'
SELECT schema_name(schema_id) as SchemaName, fun.name as FunctionName, fun.type as FunctionType FROM [@latestDB].sys.objects fun
WHERE (type = 'TF' or type = 'FN' or type = 'IF')
EXCEPT
SELECT schema_name(schema_id) as SchemaName, fun.name as FunctionName, fun.type as FunctionType FROM [@existingDB].sys.objects fun
WHERE (type = 'TF' or type = 'FN' or type = 'IF')
ORDER BY SchemaName, FunctionName, FunctionType
PRINT N'END OF Identifying ADDED FUNCTIONS'

DELETED Functions: Query to identify deleted functions in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying DELETED FUNCTIONS'
SELECT schema_name(schema_id) as SchemaName, fun.name as FunctionName, fun.type as FunctionType FROM [@existingDB].sys.objects fun
WHERE (type = 'TF' or type = 'FN' or type = 'IF')
EXCEPT
SELECT schema_name(schema_id) as SchemaName, fun.name as FunctionName, fun.type as FunctionType FROM [@latestDB].sys.objects fun
WHERE (type = 'TF' or type = 'FN' or type = 'IF')
ORDER BY SchemaName, FunctionName, FunctionType
PRINT N'END OF Identifying DELETED FUNCTIONS'

ADDED Schemas: Query to identify newly added schemas in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying ADDED SCHEMAS'
SELECT schema_name(schema_id) as SchemaName FROM [@latestDB].sys.schemas
EXCEPT
SELECT schema_name(schema_id) as SchemaName FROM [@existingDB].sys.schemas
ORDER BY SchemaName
PRINT N'END OF Identifying ADDED SCHEMAS'

DELETED Schemas: Query to identify deleted schemas in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying DELETED SCHEMAS'
SELECT schema_name(schema_id) as SchemaName FROM [@existingDB].sys.schemas
EXCEPT
SELECT schema_name(schema_id) as SchemaName FROM [@latestDB].sys.schemas
ORDER BY SchemaName
PRINT N'END OF Identifying DELETED SCHEMAS'

With this I am concluding the illustration. Feel free to share your feedback.

Happy Programming !!!