Create your own custom ArrayList in c#

As per Microsoft c# team:

Initial capacity of an ArrayList in .NET 1.0 is 16. In 2.0 it was 4, and now - with .NET 3.5 - the initial capacty has been lowered to 0. I don't have an explanation of why, thou. When adding a first element to the list, the capacity will be set to 4. There after, every time when arraylist.Count eq arraylist.Capacity, the capacity will double.

Below code is just for reference  that how we can create custom ArrayList. In the below example I have just increased 1 size at a time. But the default size is 10 

class CArray
    {
        private int[] arr;
        private int upper;
        private int numElements;
        public CArray(int size)
        {
            arr = new int[size];
            upper = size - 1;
            numElements = 0;
        }
        public void Insert(int item)
        {
            if(numElements == arr.Length) //reallocate size
            {
                IncreaseArraySize();
                upper = arr.Length - 1;
            }

            arr[numElements] = item;
            numElements++;
        }
        public void DisplayElements()
        {
            for (int i = 0; i <= upper; i++)
            {
                Console.Write(arr[i] + " ");
                Console.WriteLine();
            }
        }
        public void Clear()
        {
            for (int i = 0; i <= upper; i++)
            {
                arr[i] = 0;
                numElements = 0;
            }
        }

        public void IncreaseArraySize()
        {
            Array.Resize(ref arr, arr.Length + 1);
        }
}

class Program
    {
        static void Main(string[] args)
        {
            CArray nums = new CArray(10);

            Random rnd = new Random(100);

            for (int i = 0; i < 10; i++)
            {
                nums.Insert((int)(rnd.NextDouble() * 100));
            }

            nums.Insert(203); //Dynamic size allocation here
            nums.Insert(2);

            nums.DisplayElements();
         
        }
    }

Note: This is not the exact implementation of ArrayList in C# , this is just a reference 

How to replace kendo grid odata parameter using parametermap

parameterMap: function (data, operations) {
                    var paramMap = kendo.data.transports.odata.parameterMap(data);
                    if (paramMap.$inlinecount) {
                        if (paramMap.$inlinecount == "allpages") {
                            paramMap.$count = true;
                        }
                        delete paramMap.$inlinecount;
                    }
                    if (paramMap.$take) {
                        paramMap.$top = paramMap.$take;
                        delete paramMap.$take;
                    }
                    if (paramMap.$filter) {
                        paramMap.$filter = paramMap.$filter.replace(/substringof\((.+),(.*?)\)/, "contains($2,$1)");
                    }
                    return paramMap;
                }

OData using ODataQueryOptions $count $select $expand $filter without using [EnableQuery] attribute

If you are using OData controller without using [EnableQuery] attribute, Below example help you to get all your odata query

Odata Query

/odata/GetTestData?$format=json&$filter=(Name+eq+'santosh'+AND+Age+eq+30)&$select=Name,Address&$top=50&$count=true

Step 1:

declare temp variable and load data from your repository like below:

IQueryable tempQuery = repo.GetTestData();
IQueryable result = tempQuery;

Step 2:

Add below lines of code to apply your query.
for $count- get count() from temp result variable so that you will get all data count

if (opts.Filter != null){
tempQuery = opts.Filter.ApplyTo(tempQuery, new ODataQuerySettings()) as Queryable;
}
if (opts.Top != null){
tempQuery = opts.Top.ApplyTo(tempQuery, new ODataQuerySettings()) as IQueryable;
}
if (opts.Skip != null){
tempQuery = opts.Skip.ApplyTo(tempQuery, new ODataQuerySettings()) as IQueryable;
}
if (opts.OrderBy != null){
tempQuery = opts.OrderBy.ApplyTo(tempQuery, new ODataQuerySettings()) as Queryable;
}
if (opts.SelectExpand != null){
Request.ODataProperties().SelectExpandClause = opts.SelectExpand.SelectExpandClause;
}
 if (opts.Count != null)
{
Request.ODataProperties().TotalCount = result.Count();
}

Step 3:
Final full Odata Controller code

//Web API Odata Controller

 [HttpGet]
 [ODataRoute("GetTestData")]
public IQueryable GetTestData(ODataQueryOptions opts)
        {
            
            var repo = unitOfWork.TestRepository;

            IQueryable tempQuery = repo.GetTestData();
            IQueryable result = tempQuery;

            if (opts.Filter != null){
                tempQuery = opts.Filter.ApplyTo(tempQuery, new ODataQuerySettings()) as IQueryable;
}
            if (opts.Top != null){
                tempQuery = opts.Top.ApplyTo(tempQuery, new ODataQuerySettings()) as IQueryable;
}
            if (opts.Skip != null){
                tempQuery = opts.Skip.ApplyTo(tempQuery, new ODataQuerySettings()) as IQueryable;
}
            if (opts.OrderBy != null){
                tempQuery = opts.OrderBy.ApplyTo(tempQuery, new ODataQuerySettings()) as IQueryable;
}
            if (opts.SelectExpand != null){
                Request.ODataProperties().SelectExpandClause = opts.SelectExpand.SelectExpandClause;
}
            if (opts.Count != null)
            {
                Request.ODataProperties().TotalCount = result.Count();
            }

            result = tempQuery.ToList().AsQueryable();

            return result;
        }

Happy coding👍

OData $expand and $select

How to implement OData $expand and $select in Web API

You can download the full code from my GitHub repository 

https://github.com/mesan21ster/ODataExpandAndSelect

And follow my YouTube channel for detailed description

https://youtu.be/S3oIR4i9WZQ

Step 1:

Create a new Web API project in visual studio and add below nuget packages to enable odata
"Microsoft.AspNet.OData"

Step 2:

Create below model class Product, Supplier, Category and ProductList
-----------------------------------------------------------------------------
using System.ComponentModel.DataAnnotations.Schema;

namespace ODataExpandAndSelect.Models
{
    public class Product
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }

        [ForeignKey("Category")]
        public int CategoryId { get; set; }
        public virtual Category Category { get; set; }

        [ForeignKey("Supplier")]
        public string SupplierId { get; set; }
        public virtual Supplier Supplier { get; set; }
    }
}
----------------------------------------------------------------------------------------
using System.ComponentModel.DataAnnotations;

namespace ODataExpandAndSelect.Models
{
    public class Supplier
    {
        [Key]
        public string Key { get; set; }
        public string Name { get; set; }
    }

}
----------------------------------------------------------------------------------------------------
using System.Collections.Generic;
using System.Linq;

namespace ODataExpandAndSelect.Models
{
    public class Category
    {
        public Category()
        {
            Products = new HashSet();
        }
        public int ID { get; set; }
        public string Name { get; set; }
        public virtual ICollection Products { get; set; }
    }
------------------------------------------------------------------------------------------
    public class ProductList
    {
        public IQueryable getProducts()
        {
            List products = new List();

            Category c1 = new Category() { ID = 1, Name = "Category1", Products = products };
            Category c2 = new Category() { ID = 2, Name = "Category2", Products = products };
            Supplier s1 = new Supplier() { Key = "s1", Name = "Supplier1" };
            Supplier s2 = new Supplier() { Key = "s2", Name = "Supplier2" };

            Product p1 = new Product() { ID = 1, Category = c1, CategoryId = 1, Name = "product1", Price = 100.50M , Supplier = s1, SupplierId = "SupplierS1" };
            Product p2 = new Product() { ID = 2, Category = c2, CategoryId = 2, Name = "product2", Price = 200.50M , Supplier = s2, SupplierId = "SupplierS2" };

            products.Add(p1);
            products.Add(p2);
           
            return products.AsQueryable();
        }
    }
}
------------------------------------------------------------------------------------

Since I am not using any database in this example, So I have created ProductList class and returning some dummy data, You may use any database or entity framework as per your requirement.

Step 3:  
In WebApi.Config Please add below changes

using System.Linq;
using System.Web.Http;
using Microsoft.AspNet.OData.Batch;
using Microsoft.AspNet.OData.Builder;
using Microsoft.AspNet.OData.Extensions;
using Microsoft.OData.Edm;
using ODataExpandAndSelect.Models;

namespace ODataExpandAndSelect
{
    public static class WebApiConfig
    {
        private static IEdmModel GetEdmModel()
        {
            ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
            builder.Namespace = "WebAPITest";
            builder.ContainerName = "DefaultContainer";
            builder.EntitySet("Product");
            builder.EntitySet("Category");
            builder.EntitySet("Supplier");
            var edmModel = builder.GetEdmModel();
            return edmModel;
        }
        public static void Register(HttpConfiguration config)
        {
            config.Count().Filter().OrderBy().Expand().Select().MaxTop(null); 
            config.MapODataServiceRoute("odata", null, GetEdmModel(), new DefaultODataBatchHandler(GlobalConfiguration.DefaultServer));
            config.EnsureInitialized();
        }
    }

}

Step 4:

Create a ProductController in controller folder like below

using System.Linq;

using Microsoft.AspNet.OData;

using ODataExpandAndSelect.Models;

namespace ODataExpandAndSelect.Controllers

{

    public class ProductController : ODataController

    {

        [EnableQuery]

        public IQueryable Get()

        {

            ProductList list = new ProductList();

            var data = list.getProducts();

            return data;

        }

    }

}

Step 5:

In Global.asax.cs add below changes

using System.Web.Http;

namespace ODataExpandAndSelect

{

    public class WebApiApplication : System.Web.HttpApplication

    {

        protected void Application_Start()

        {

            GlobalConfiguration.Configure(WebApiConfig.Register);

        }

    }

}

Step 6:

Run your application on IIS express and use query like below

http://localhost:5197/Product?$expand=Category

You can expand to next level through comma separated like below query

http://localhost:5197/Product?$expand=Category,Supplier

For $select you can you below query

http://localhost:5197/Product?$select=Price

Thank you

Happy coding

Extract Filter part in Odata QueryOptions Web API

If you are creating Web API using Odata controller  and want to extract filters query from Odata request. Refer below sample query(bold part is odata filter)

You can download the code from (https://github.com/mesan21ster/ExtractOdataFilterOptions)
You can also follow my YouTube channel (https://www.youtube.com/playlist?list=PLbAvaAiacB_FwIYjEU8hDXnn6r56XeHrU)

Follow below simple steps to get that.

Sample Odata URL

https://localhost:44335/odata/testData/testDataType?$format=json&$filter=(FirstName+eq+'Santosh'+AND+LastName+eq+'Kumar'+AND+Age+eq+ 30+AND+BirthYear+eq+2019+AND+BirthCity+eq+'Delhi')&$count=true&$orderby=Age+desc

Namespaces used in code
using System;
using System.Collections.Generic;
using Newtonsoft.Json;
using System.Text.RegularExpressions;
using System.Linq;
using Microsoft.AspNet.OData;
using Microsoft.AspNet.OData.Query;

using Microsoft.AspNet.OData.Routing;

Sample OdataController
namespace test.Controllers

{
[ODataRoutePrefix("testData")]

public class TestDataController : ODataController
{
[ODataRoute("testDataType")]
[HttpGet]
public IQueryable testoData(ODataQueryOptions oDataQueryOptions)
        {
//here you have to follow below steps to extract odata filters from odata query options parameter
}
}
}
Step 1

Declare filter expression

 string oDataFilterExpression = @"(?" +
                            "\n" + @"     (?.+?)\s+" +
                            "\n" + @"     (?eq|ne|gt|ge|lt|le|add|sub|mul|div|mod)\s+" +
                            "\n" + @"     '?(?.+?)'?" +
                            "\n" + @")" +
                            "\n" + @"(?:" +
                            "\n" + @"    \s*$" +
                            "\n" + @"   |\s+(?:or|and|not)\s+" +
                            "\n" + @")" +
                            "\n";  

Step 2
Declare regex and target filter string ( you can get your target filter string from "oDataQueryOptions.Filter.RawValue"
Regex oDataFilterRegex = new Regex(oDataFilterExpression, RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace);


string strTargetString = @"FirstName eq 'Santosh' AND LastName eq 'Kumar' AND Age eq  30 AND BirthYear eq 2019 AND BirthCity eq 'Delhi'".TrimEnd(')').TrimStart('(');

Step 3
Create a replace string in json format and make json array
string strReplace = @"{ 'Resource' : '${Resource}'," + @"   'Operator' : '${Operator}'," + @"   'Value'    : '${Value}'},";
 var val = oDataFilterRegex.Replace(strTargetString, strReplace);

 var filterJson = "[" + val.TrimEnd(',') + "]"; //Make json array

Step 4
Create a class to Deserialize json string to object
    public class FilterValue
    {
        public string Resource { get; set; }
        public string Operator { get; set; }
        public string Value { get; set; }

    }

Step 5 get result back
Deserialize json string to object and find your expected value

var oDataFilterObject = JsonConvert.DeserializeObject<List<FilterValue>>(filterJson);

var firstName= oDataFilterObject.Find(x => x.Resource == "FirstName").Value;

Dynamic sql query

Use dynamic sql query like below

declare @TableName varchar(max)

declare @type varchar(max)

  set @TableName='test'

  set @type ='''abc'''

  declare @SQLQuery varchar(max)

   SET @SQLQuery = 'SELECT LETTER_ID FROM ' + @TableName  + ' WHERE LETTER_TYPE =  ' + @type

  print @SQLQuery

 execute(@SQLQuery)

Delete duplicate Row in sql server

Delete Duplicate Row in sql server

create table test(name varchar(20),rollNo int)

insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('alok',2)
insert into test values('alok',2)
insert into test values('alok',2)
insert into test values('alok',2)
insert into test values('alok',2)
insert into test values('alok',2)

SET ROWCOUNT 0 --execute rowcount 0 after executing the cursor
select * from test order by name


--Create cursor for duplicate row delete

Declare @Count int
DECLARE @rollNo int
DECLARE @name nvarchar(20)


DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR
SELECT name, rollNo, Count(*) - 1
FROM test
GROUP BY name, rollNo
HAVING Count(*) > 1

OPEN dublicate_cursor

FETCH NEXT FROM dublicate_cursor INTO @name,@rollNo,@Count

WHILE @@FETCH_STATUS = 0
BEGIN
SET ROWCOUNT @count
--DELETE TOP(@Count) FROM test WHERE name = @name AND rollNo = @rollNo
DELETE FROM test WHERE name = @name AND rollNo = @rollNo

FETCH NEXT FROM dublicate_cursor INTO @name, @rollNo, @Count
END

CLOSE dublicate_cursor
DEALLOCATE dublicate_cursor


Note:- SET ROWCOUNT @count is used when you are using sql server 2000
In sql server 2005 you can use DELETE TOP(@Count) FROM test WHERE name = @name AND rollNo = @rollNo


Thanks and enjoy