In Clause in Linq?

IN clause With Linq To Sql

There is no direct equivalent in LINQ. Instead you can use contains () or any
other trick to implement them. Here's an example that uses Conains ().:

String [] s = new String [5];
s [0] = "34";
s [1] = "12";
s [2] = "55";
s [3] = "4";
s [4] = "61";

var RESULT1 =  from d in context.TableName
                        where s.Contains (d.fieldname)
                        select d;

Eg:
int[] productList = new int[] { 1, 2, 3, 4 };

var myProducts = from p in db.Products

                 where productList.Contains(p.ProductID)

                select p;

Start with the Order (pretend my order is ID=44):

AdventureWorks.DB db=new DB();

var itemQuery = from orders in db.SalesOrder

              where orders.SalesOrderID == 44

              select orders.ProductID;

Next we need to get the products, but only those that are in the cart.
We do this by using our first query, inside the second:

var myProducts = from p in db.Products

                where itemQuery.Contains(p.ProductID)

                select p;

Difference between Object & Instance in Oops

 Instance refers to the copy of the object at a particular time whereas object refers to the memory address of the class.

OR,

eg:Bike b1=new Bile();

Bike b2=new Bike()

—->we create two instances for storing two bike object values.

→At this stage b1&b2 are just instances because they have not contain any Bike object values.

—-> after storing values in instances then they are treated as objects

—-> we can store values in instances in 3 ways

1.Directly by assigning values to variables

2.Through constructers

3. Through setter methods

like

b1.brand=”pulsur”

then the b1 contains values so we can say it object

OR,

instance means just creating a reference(copy) .

object :means when memory location is associated with the object( is a runtime entity of the class)  by using the new operator

OR,

Instance referes to Reference of an object.

Object is actaully pointing to memory address of that instance.

Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT

Introduction

In most of our application scenario, we need to get latest inserted row information through SQL query. And for that, we have multiple options like:

• @@IDENTITY
• SCOPE_IDENTITY
• IDENT_CURRENT

All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ.

Compare

@@IDENTITY

It returns the last identity value generated for any table in the current session, across all scopes.

Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then @@IDENTITY returns that identity record which is created by trigger.

SCOPE_IDENTITY

It returns the last identity value generated for any table in the current session and the current scope.

Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then SCOPE_IDENTITY result is not affected but if a trigger or a user defined function is affected on the same table that produced the value returns that identity record then SCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function.

IDENT_CURRENT

It returns the last identity value generated for a specific table in any session and any scope.

In other words, we can say it is not affected by scope and session, it only depends on a particular table and returns that table related identity value which is generated in any session or scope.

SQL Query

I am explaining the above process with the help of some sample query, hope it helps:

Hide   Shrink    Copy Code

CREATE TABLE Parent(id int IDENTITY);

CREATE TABLE Child(id int IDENTITY(100,1));

GO

CREATE TRIGGER Parentins ON Parent FOR INSERT

AS

BEGIN

   INSERT Child DEFAULT VALUES

END;

GO

--End of trigger definition

SELECT id FROM Parent;
--id is empty.

SELECT id FROM Child;
--ID is empty. 

--Do the following in Session 1
INSERT Parent DEFAULT VALUES;
SELECT @@IDENTITY;
/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();
/* Returns the value 1. This was inserted by the
INSERT statement two statements before this query.*/ 

SELECT IDENT_CURRENT('Child');

/* Returns value inserted into Child, that is in the trigger.*/

SELECT IDENT_CURRENT('Parent');

/* Returns value inserted into Parent. 
This was the INSERT statement four statements before this query.*/ 

-- Do the following in Session 2.

SELECT @@IDENTITY;

/* Returns NULL because there has been no INSERT action
up to this point in this session.*/ 

SELECT SCOPE_IDENTITY();

/* Returns NULL because there has been no INSERT action
up to this point in this scope in this session.*/

SELECT IDENT_CURRENT('Child');

/* Returns the last value inserted into Child.*/

How to delete/update records from Parent table then corresponding records deleted/updated on child table in Sql server

CASCADE in SQL Server with example-

Use the ON DELETE CASCADE option if you want rows deleted in the child table when corresponding rows are deleted in the parent table.

Use the ON DELETE CASCADE option if you want rows deleted in the child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.

If you specify this option, when you delete a row in the parent table, the database server also deletes any rows associated with that row (foreign keys) in a child table. The advantage of the ON DELETE CASCADE option is that it allows you to reduce the quantity of SQL statements needed to perform delete actions.

Hide   Shrink    Copy Code

select * from dbo.ProductDetails
select * from dbo.Products

CREATE TABLE [dbo].[Products](
[ProductID] [int] NOT NULL,
[ProductDesc] [varchar](50) NOT NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)) ON [PRIMARY]

CREATE TABLE [dbo].[ProductDetails](
[ProductDetailID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[Total] [int] NOT NULL,
CONSTRAINT [PK_ProductDetails] PRIMARY KEY CLUSTERED
(
[ProductDetailID] ASC
)) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ProductDetails] WITH CHECK ADD CONSTRAINT 
[FK_ProductDetails_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductID])
ON UPDATE CASCADE
ON DELETE CASCADE

INSERT INTO Products (ProductID, ProductDesc)
SELECT 1, 'Bike'
UNION ALL
SELECT 2, 'Car'
UNION ALL
SELECT 3, 'Books'

INSERT INTO ProductDetails
([ProductDetailID],[ProductID],[Total])
SELECT 1, 1, 200
UNION ALL
SELECT 2, 1, 100
UNION ALL
SELECT 3, 1, 111
UNION ALL
SELECT 4, 2, 200
UNION ALL
SELECT 5, 3, 100
UNION ALL
SELECT 6, 3, 100
UNION ALL
SELECT 7, 3, 200

SELECT *
FROM Products
SELECT *
FROM ProductDetails

DELETE
FROM Products
WHERE ProductID = 1

DROP TABLE ProductDetails
DROP TABLE Products

Difference between Application & cache object

Application: As in the preceding snapshots you see that the Application and Cache values are the same all over the application. Basically we use a static value in the Application Object that we want to show somewhere in the application that will be visible for every form in the Application.

We can use the Application Object in the global.asax file. 

The Application Object maintains its data until the web application is shut down or we release the data manually by assigning null or call the Clear() method. The Application Object does not have any timeout. 

Basically we use The Application Object to hit a counter on the Application, or read a readonly file or table to display in various web pages.

Cache: A Cache is very useful in web applications for performance optimization. Since application data can only be stored on the server, Cache data is stored on both the client side as well as the server side. We can define the time period for the cache using the Cache.Insert() or Cache.Add() method and the time period can be assigned from seconds to years. We even can maintain an absolute time expiration for the Cache.

We can assign value in the Cache only from web forms, not from the global.asax (we only can pass in the case of the Application and Session) file.

We retrieve the Cache data from the Cache without repeating the full cycle, which doesn't happen in the case of the Application.

And we can use the Cache if we want to calculate the time the user was logged in. Or if we are giving an online examination, then we can maintain the questions in the cache and retrieve them from there for better performance.

OR

Application and Cache objects are used for storing static data for a certain period of time.Both use Key-value pair format for data storage.

Both Application and Cache objects are one per web application.Their data can be accessed in all the pages of a web site.

APPLICATION OBJECT:

1)Application object always stores data on the server side RAM

example: Application["hits"]=1;

(key-value pair).

2)Application object maintains its data till the web application is shut down or we release the data manually by assigning null or Clear() method is called.

3)Application object has no Timeouts or File Dependencies.

4)Its data can be assigned using Global.asax file

5)Application object is not used for performance optimization.

USED in maintaining hit counters, data from readonly files/tables which can then

be displayed on varrious web pages.

CACHE OBJECT:

1)Cache object can store the data on server side RAM as well as client side RAM

example: -- Cache["data"]="asp.net";

2)Cache object maintains the static data as specified by the Absolute Expiration/ Sliding Expiration or File Dependency. The Time Period for Cache can be defined using the Cache.Insert() overloaded method or Cache.Add() method. It can be from seconds to years.

3)Cache object can be assigned data from web page and not from Global.asax file.

4)Cache is used for performance optimization. We retreive the Cache data from

the Cache without repeating the full cycle again, which is not so in the case

of the Application object.

USES OF CACHE OBJECT:

1)Static images for a certain period of time

2)Calculating the time of the users who login in a certain period of time.

3)On line exams: store the questions in the Cache and then retreive them from Cache.

Difference between throw and throw ex in c#

Throw

In Throw, the original exception stack trace will be retained. To keep the original stack trace information, the correct syntax is 'throw' without specifying an exception.

Declaration of throw

try { // do some operation that can fail } catch (Exception ex) { // do some local cleanup throw; }

Throw

In Throw ex, the original stack trace information will get override and you will lose the original exception stack trace. I.e. 'throw ex' resets the stack trace.

Declaration of throw ex

try { // do some operation that can fail } catch (Exception ex) { // do some local cleanup throw ex; } }

Example -

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ThrowAndThrowEx
{
    class Program
    {
        static void Main(string[] args)
        {
              try{
                    ThrowException1(); // line 19
                } catch (Exception x) {
                    Console.WriteLine("Exception 1:");
                    Console.WriteLine(x.StackTrace);
                }
                try {
                    ThrowException2(); // line 25
                } catch (Exception x) {
                    Console.WriteLine("Exception 2:");
                    Console.WriteLine(x.StackTrace);
                }

                Console.ReadLine();

        }

        private static void ThrowException1()
        {
            try
            {
                DivByZero(); // line 34
            }
            catch
            {
                throw; // line 36
            }
        }
        private static void ThrowException2()
        {
            try
            {
                DivByZero(); // line 41
            }
            catch (Exception ex)
            {
                throw ex; // line 43
            }
        }

        private static void DivByZero()
        {
            int x = 0;
            int y = 1 / x; // line 49
        }
    }
}

Output -

Named and optional parameters in C# 4.0

Introduction

In this article, I will explore named and optional parameters in C# 4.0. Named and optional parameters are really two distinct features, and allow us to either omit parameters which have a defined default value, and/or to pass parameters by name rather than position. Named parameters are passed by name instead of relying on its position in the parameter list, whereas optional parameters allow us to omit arguments to members without having to define a specific overload matching.

Let’s have a look at Optional parameters:

Hide   Copy Code

//In old way we will write the code as shown below.
public static double MyOldCurrencyExchange(double amount, double rate) 
{ 
    return (amount * rate); 
}

We will call the above method as shown below:

Hide   Copy Code

MyOldCurrencyExchange(500, 1.18);

Now, by using Optional parameters:

Hide   Copy Code

//In new way we will write the code as shown below
public static double MyNewCurrencyExchange(double amount, double rate=1) 
{ 
    return (amount * rate); 
}

We will call the above method as shown below:

Hide   Copy Code

MyNewCurrencyExchange (500, 1.18);  // ordinary call 
MyNewCurrencyExchange (500);  // omitting rate

Now, by using Named parameters:

Hide   Copy Code

MyNewCurrencyExchange (rate:1.18, amount:500); // reversing the order of arguments.

Now, by using Named and Optional parameters:

Hide   Copy Code

MyNewCurrencyExchange (amount:500);

Difference between IEnumerable and Ilist

In LINQ to query data from collections, we use IEnumerable and IList for data manipulation. IEnumerable is inherited by IList, hence it has all the features of it and except this, it has its own features. IList has below advantage over IEnumerable. In previous article, I explain the difference between IEnumerable and IQueryable.

IList

1. IList exists in System.Collections Namespace.
2. IList is used to access an element in a specific position/index in a list.
3. Like IEnumerable, IList is also best to query data from in-memory collections like List, Array etc.
4. IList is useful when you want to Add or remove items from the list.
5. IList can find out the no of elements in the collection without iterating the collection.
6. IList supports deferred execution.
7. IList doesn't support further filtering.

IEnumerable

1. IEnumerable exists in System.Collections Namespace.
2. IEnumerable can move forward only over a collection, it can’t move backward and between the items.
3. IEnumerable is best to query data from in-memory collections like List, Array etc.
4. IEnumerable doesn't support add or remove items from the list.
5. Using IEnumerable we can find out the no of elements in the collection after iterating the collection.
6. IEnumerable supports deferred execution.
7. IEnumerable supports further filtering.

Difference between IEnumerable and Iqueryable

In LINQ to query data from database and collections, we use IEnumerable and IQueryable for data manipulation. IEnumerable is inherited by IQueryable, Hence IQueryable has all the features of IEnumerable and except this, it has its own features. Both have its own importance to query data and data manipulation. Let’s see both the fetures and take the advantage of both the fetures to boost your LINQ Query performance.

IEnumerable

1. IEnumerable exists in System.Collections Namespace.
2. IEnumerable can move forward only over a collection, it can’t move backward and between the items.
3. IEnumerable is best to query data from in-memory collections like List, Array etc.
4. While query data from database, IEnumerable execute select query on server side, load data in-memory on client side and then filter data.
5. IEnumerable is suitable for LINQ to Object and LINQ to XML queries.
6. IEnumerable supports deferred execution.
7. IEnumerable doesn’t supports custom query.
8. IEnumerable doesn’t support lazy loading. Hence not suitable for paging like scenarios.
9. Extension methods supports by IEnumerable takes functional objects.

IEnumerable Example

 MyDataContext dc = new MyDataContext ();
IEnumerable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith("S"));
list = list.Take<Employee>(10); 

Generated SQL statements of above query will be :

 SELECT [t0].[EmpID], [t0].[EmpName], [t0].[Salary] FROM [Employee] AS [t0]
WHERE [t0].[EmpName] LIKE @p0

Notice that in this query "top 10" is missing since IEnumerable filters records on client side

IQueryable

1. IQueryable exists in System.Linq Namespace.
2. IQueryable can move forward only over a collection, it can’t move backward and between the items.
3. IQueryable is best to query data from out-memory (like remote database, service) collections.
4. While query data from database, IQueryable execute select query on server side with all filters.
5. IQueryable is suitable for LINQ to SQL queries.
6. IQueryable supports deferred execution.
7. IQueryable supports custom query using CreateQuery and Execute methods.
8. IQueryable support lazy loading. Hence it is suitable for paging like scenarios.
9. Extension methods supports by IQueryable takes expression objects means expression tree.

IQueryable Example

 MyDataContext dc = new MyDataContext ();
IQueryable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith("S"));
list = list.Take<Employee>(10); 

Generated SQL statements of above query will be :

 SELECT TOP 10 [t0].[EmpID], [t0].[EmpName], [t0].[Salary] FROM [Employee] AS [t0]
WHERE [t0].[EmpName] LIKE @p0

Notice that in this query "top 10" is exist since IQueryable executes query in SQL server with all filters.