Linq to Entities (or Entity Framework) - Find index of row matching condition in entity set (DbSet/ ObjectSet)

This article aims to explain how to find the index of a row matching a particular condition in DbSet/ ObjectSet using Linq to Entities.

While working with Entity Framework (at least till Entity Framework 5), and Linq-to-entities, I realized it doesn't have support to find index of row within the DbSet for obvious reasons. And so I thought to write a method manually. There are many approaches for the same, and different developers might have implemented different approaches. I found the one I implemented below as more performance optimized.

Say, for example, you have an entity set "Cities". There exists, thousands of rows in this table. You need to sort the cities by their name, and want to find index of a particular city within that ordered collection.

One instance of such need is to have paging implemented in your site, and you want your user to jump to the page containing record of a particular city by keeping paging intact.

The approach is to - First check if that particular city exists in the "Cities" collection or not.
If it exists, call the below FindIndex method:

Also, take a note of loopCapacity variable in below code. In below example, it means, the final "for loop" to find and calculate the index will be started only after a bunch of 100 records are found. Here, these 100 records contain that particular record (i.e. city) which we are looking for.  

public int FindIndex(String CityName)
        {
            int index = 0;
            int totalRecords = db.Cities.Count();
            int halfCount = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(totalRecords / 2))) + 1;
            int loopCapacity = 100;
            int recordsToSkip = 0;
            if (totalRecords > 0)
            {
                bool nextIteration = true;
                while (nextIteration)
                {
                    for (int counter = 0; counter < 2; counter++)
                    {
                        recordsToSkip = recordsToSkip + (counter * halfCount);
                        if (db.Cities.OrderBy(c => c.CityName).Skip(recordsToSkip).Take(halfCount).Where(c => c.CityName == CityName).Count() > 0)
                        {
                            if (halfCount > loopCapacity)
                            {
                                totalRecords = totalRecords - (halfCount * 1);
                                halfCount = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(totalRecords / 2))) + 1;
                                nextIteration = true;
                                break;
                            }
                            else
                            {
                                foreach (Cities city in db.Cities.OrderBy(c => c.CityName).Skip(recordsToSkip).Take(halfCount))
                                {
                                    if (city.CityName == CityName)
                                    {
                                        index = index + 1;
                                        index = recordsToSkip + index;
                                        break;
                                    }
                                    else
                                    {
                                        index = index + 1;
                                    }
                                }
                                nextIteration = false;
                                break;
                            }
                        }
                        else
                        {
                            nextIteration = true;
                        }
                    }
                }
            }
            return index;
        }

Hope this helps!

Do let me know if anything is unclear, or if you need explanation for any part of code

Thanks

CodeProject

C# - Generate random number

Sometimes we need to generate a random number in our application for various purpose (such as, to use it as a part of unique key, etc).

There is a quick way in C#, that can generate a random number. Here, you can also specify the range within which the random number should be generated.

int generatedRandomNumber = new Random(System.DateTime.Now.Millisecond).Next(int.MinValue, int.MaxValue);

This will generate a random number within the range of -2147483648 and 2,147,483,647. But you can narrow down your range to only positive values or some explicit range by changing the parameters in Next function above.

Hope it does help!
 

Javascript: Function to check if the string is numeric (i.e. valid number)

Following is the javascript function which accurately validates if the supplied string a valid number or not.

function isNumber(n) {
    return !isNaN(parseFloat(n)) && isFinite(n);
}

Trim, Ltrim, Rtrim, FullTrim - trimming string functions in Javascript

JQuery 1.9+ have inbuilt functions for string trimming, but they aren't supported in many browsers, such as, IE 8, IE 9, etc., and so I refrain myself from using those function, and hence there is a need to have our custom function to perform this job!

Following are javascript functions for trimming strings: 

function StringTrim(inputText){return inputText.replace(/^\s+|\s+$/g, '');};

function StringLtrim(inputText){return inputText.replace(/^\s+/,'');};

function StringRtrim(inputText){return inputText.replace(/\s+$/,'');};

function StringFulltrim(inputText){return inputText.replace(/(?:(?:^|\n)\s+|\s+(?:$|\n))/g,'').replace(/\s+/g,' ');};

Add them in javascript library of your application, and you can use them to trim strings in various ways, such as, left trim, right trim, trim, and full trim.

Hope this helps!

SQL Query to return a table containing all dates falling within a date range along with their week day names

Following is the T-SQL code to return all dates falling within a given date range. This uses CTE for populating all dates, and store them in a temporary table.

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '01-Apr-2013'
SET @EndDate = '30-Apr-2013'

;WITH DatesList
AS
(
SELECT @StartDate [fldDate]
UNION ALL
SELECT fldDate + 1 FROM DatesList WHERE fldDate < @EndDate
)

SELECT fldDate, DATENAME(dw, fldDate) as fldDayFullName INTO #DatesList FROM DatesList option (maxrecursion 0)

SELECT * FROM #DatesList

CodeProject

How to convert an array of one type to another type in using Linq in .NET

Following is the sample code that converts a string array into GUID array with faster performance:

String strGUIDs[];
//Consider the above array is containing all GUIDs in string format.
Guid[] guids = Array.ConvertAll(strGUIDs, x => Guid.Parse(x));

You can convert an array from one type to another using the above approach.

Vertical Scrollbar in JQuery AutoComplete UI

In most cases, we need to display a vertical scrollbar in JQuery AutoComplete UI.

JQuery and UI libraries come along with their own styling and theming, and it has its own CSS "jquery-ui-n.n.nn.css".

And we certainly need to play with the styles inside that in order to change any behaviour or styling of same.

Instead of doing any changes inside JQuery libraries, add following styling attributes in your page/ css file, and you will find vertical scrollbar.

/* setting up height for autocomplete UI */
.ui-autocomplete {
    max-height: 150px;
    overflow-y: auto;
    /* prevent horizontal scrollbar */
    overflow-x: hidden;
    /* add padding to account for vertical scrollbar */
    padding-left: 5px;
    padding-right: 5px;
}
/* IE 6 doesn't support max-height
 * we use height instead, but this forces the menu to always be this tall
 */
* html .ui-autocomplete {
    height: 150px;
}

Remember, changing JQuery libraries are never a good idea, because you will also need to take care of the same each time when you upgrade your libraries:

CodeProject