SQL Server: Select the first / least / max row per group or category

In case of table data, if users want to select the records based on the specific criteria i.e. get one row per category / group from the table, then this can be achieved using the following query:

E.g You have a table "Items" having columns Id,ItemName,Type and Price


Select * FROM
(
Select *,
Row_Number() over (partition by [type] order by Price ASC) as RowN
FROM Items
) T Where RowN <=1

The above query returns the records with least price from each type in the table. Although the default sorting set in the SQL is ascending, but this is added for better understanding.

Thanks.

Get the String from last occurrence of specific character / text in SQL

Select right(ColumnName, charindex('/', reverse(ColumnName) + '/') - 1) From TableName

Check the existence of Entities and their structure in SQL Server

1. Check If Column Exists in Table:
IF EXISTS(SELECT 1 FROM sys.columns
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END


2. Check If Table Exists
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = N'Customers')
BEGIN
  PRINT 'Table Exists'
END


3. Check If Stored Procedure Exists in SQL
IF EXISTS ( SELECT *
            FROM   sysobjects
            WHERE  id = object_id(N'[dbo].[MyProc]')
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    --DROP PROCEDURE [dbo].[MyProc]
END

4. Check If User-Defined Function Exists in SQL
IF EXISTS (SELECT *
           FROM   sys.objects
           WHERE  object_id = OBJECT_ID(N'[dbo].[foo]')
                  AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
Begin
  --DROP FUNCTION [dbo].[foo]
End

GO 

Register AutoMapper using UnityConfig .Net Web API 2

AutoMapper:  

Assembly AutoMapper, Version=6.2.1.0, Culture=neutral, PublicKeyToken=be96cd2c38ef1005

UnitConfig.cs

var config = new MapperConfiguration(cfg =>
            {
                cfg.AddProfile(new CommonMappingProfile());
            }); 

 var container = new UnityContainer();

container.RegisterInstance(config.CreateMapper());

Use gitignore.io to add default and not-required file to gitignore of the version control repository

www.gitignore.io

SQL: Get Distance from 2 Lat-Long pairs.

Declare @T1 GEOGRAPHY,@T2 GEOGRAPHY;
Select @T1= GEOGRAPHY::Point('42.372', '-71.0298', 4326) --Boston
,@T2=GEOGRAPHY::Point('41.953', '-87.643', 4326) --Chicago
Select @T1.STDistance(@T2) As 'Distane in Meters' ,@T1.STDistance(@T2) / 1000 As 'Distane in KM'

Serialize Specific Properties of List using Newtonsoft.Json in .Net C#

/* Custom ContractResolver: The below contract resolver will help to serialize only those properties that will be defined in the resolver settings*/

public class DynamicContractResolver : DefaultContractResolver
    {
        private readonly List<string> _propertiesToSerialize;
        public DynamicContractResolver(string propertiesToSerialize = "")
        {
            var list = !string.IsNullOrEmpty(propertiesToSerialize) ? propertiesToSerialize.Split(',').ToList() : new List<string>();
            _propertiesToSerialize = list;
        }

        protected override IList<JsonProperty> CreateProperties(Type type, MemberSerialization memberSerialization)
        {
            var properties = base.CreateProperties(type, memberSerialization);
            properties = properties.Where(p => _propertiesToSerialize.Contains(p.PropertyName.ToLower())).ToList();
            return properties;
        }
    }



/* Entity */
public class TestClass
{
public int Id {get;set;}
public string Name {get;set;}
public string Age {get;set;}
}


/*
var list = new List<TestClass>();
list.Add(new TestClass { Id=1, Name ="Amit Jain",Age="31"});
list.Add(new TestClass { Id=2, Name ="Michael",Age="25"});
list.Add(new TestClass { Id=3, Name ="Robin",Age="26"});
*/


Now, Lets try to serialize only Name and Age:

Note: TO use below, one must include the Newtonsoft.Json reference into the project.

var data = JsonConvert.SerializeObject(list, new JsonSerializerSettings { ContractResolver = new DynamicContractResolver("name,age") });


/* Here's the output of the Data String as Json*/

[
  {
    "Name": "Amit Jain",
    "Age": 31
  },
  {
    "Name": "Michael",
    "Age": 25
  },
  {
    "Name": "Robin",
    "Age": 26
  }
]


Try it..