LINQ Distincts with multiple properties from Generic List

This post describes how to get distinct rows from generic list by multiple fields or properties.

.Net Framework 4.5 provides IEqualityComparer Interface.

This interface allows the implementation of customized equality comparison for generic collections.

will go through step by step to how to use IEqualityComparer interface for distinct records.

Step 1 : First create one class

    public class Product
    {
        public int ProductID { get; set; }

        public string ProductName { get; set; }

        public string Code { get; set; }

        public double Price { get; set; }

        public int Quantity { get; set; }
    }

Step 2 : Create Comparer class for retrieve Distinct product by Quantity & Price.

    public class ProductComparer : IEqualityComparer
    {
        public bool Equals(Product x, Product y)
        {
            return (x.Quantity == y.Quantity && x.Price == y.Price);
        }
        public int GetHashCode(Product x)
        {
            return (x.Quantity.GetHashCode());
        }
    }

Equals method is used to check whether specified object are equals or not. i have used quantity and price in this equals method.

you can add more fields if you required for distinct result.
(for example : return (x.Code == y.Code && x.Price == y.Price); ) this will return distinct records by Code and Price properties.

Step 3 : Create List<product> object and insert some records.

List<Product> productList = new List<Product>();

productList.Add(new Product()
{
    ProductID = 1, Code = "ABC", Price = 10, ProductName = "Dairy Milk", Quantity = 5
});
productList.Add(new Product()
{
    ProductID = 2, Code = "DEF", Price = 20, ProductName = "Lux", Quantity = 40
});
productList.Add(new Product()
{
    ProductID = 3, Code = "GGG", Price = 10,  ProductName = "5Star", Quantity = 5
});
productList.Add(new Product()
{
    ProductID = 4, Code = "ABC", Price = 50, ProductName = "Himalaya Face Wash", 

    Quantity = 10
}); 

Step 4 :Create Linq query with above comparer class to generate distinct result.

List<Product> result = productList.Distinct(new ProductComparer()).ToList();

Above linq statement give list of product which has Quantity and Price are common in all rows.

Drop all SqlQueryNotificationService from Database

DECLARE @PROCNAME VARCHAR(500)
DECLARE CUR CURSOR 

FOR SELECT [NAME] FROM SYS.OBJECTS 

    WHERE TYPE IN (N'SQ') AND NAME LIKE 'SQLQUERYNOTIFICATIONSERVICE%'
OPEN CUR
FETCH NEXT FROM CUR INTO @PROCNAME
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        EXEC ('DROP QUEUE [' + @PROCNAME+']')
        FETCH NEXT FROM CUR INTO @PROCNAME
    END TRY
    BEGIN CATCH
        FETCH NEXT FROM CUR INTO @PROCNAME
    END CATCH
END
CLOSE CUR
DEALLOCATE CUR

Drop all SqlQueryNotificationStoredProcedure from Database

below query will drop all SqlQueryNotificationStoredProcedure available in specific database.

ALTER AUTHORIZATION ON DATABASE :: <Your Database Name> TO <[login user]>

for example your DB name : TestDB and sql server login user: sa, then you can write
ALTER AUTHORIZATION ON DATABASE :: TestDB TO [sa]

Example :

ALTER AUTHORIZATION ON DATABASE :: TestDB  TO [sa]
DECLARE @PROCNAME VARCHAR(500)
DECLARE CUR CURSOR 
FOR SELECT [NAME] FROM SYS.OBJECTS WHERE TYPE IN (N'P', N'PC') AND NAME LIKE 'SQLQUERYNOTIFICATIONSTOREDPROCEDURE%'

OPEN CUR
FETCH NEXT FROM CUR INTO @PROCNAME

WHILE @@FETCH_STATUS = 0
BEGIN

  EXEC ('DROP PROCEDURE [' + @PROCNAME+']')
  FETCH NEXT FROM CUR INTO @PROCNAME

END
CLOSE CUR
DEALLOCATE CUR

Sql Query for generate script of all stored procedure in Database

In this post will show you, how to generate script that lists all stored procedure in single file.

below is the query for that :

Method 1 :

DECLARE @MYSP AS TABLE(ID INT IDENTITY(1,1), SPNAME VARCHAR(255),SPDESC VARCHAR(MAX))
INSERT INTO @MYSP 
SELECT ROUTINE_NAME,ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_TYPE='PROCEDURE' order by ROUTINE_NAME

DECLARE @MIN INT
DECLARE @MAX INT
SELECT @MIN=MIN(ID),@MAX=MAX(ID) FROM @MYSP

WHILE @MIN <= @MAX
BEGIN
    DECLARE @sp_desc varchar(max)
    SELECT @sp_desc=SPDESC FROM @MYSP WHERE ID=@MIN
    
    print @sp_desc
    
    SET @MIN=@MIN+1
END

you can get list of all stored procedures from Information_Schema.Routines tables. after that you can loop each record and print SP.


Method 2 :

 DECLARE @MYSP AS TABLE(ID INT IDENTITY(1,1), SPNAME VARCHAR(255))

 INSERT INTO @MYSP SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
 WHERE  ROUTINE_TYPE='PROCEDURE' order by ROUTINE_NAME

 DECLARE @MIN INT
 DECLARE @MAX INT

 SELECT @MIN=MIN(ID),@MAX=MAX(ID) FROM @MYSP

 declare @text nvarchar(max)

 WHILE @MIN <= @MAX

 BEGIN

    DECLARE @sp_name varchar(255)
    SELECT @sp_name=SPNAME FROM @MYSP WHERE ID=@MIN
    EXEC SP_HELPTEXT @sp_name    

    SET @MIN=@MIN+1

 END