Call Sql Server inbuilt functions using Linq

The post is about the the new class introduce in .net framwork for support of built in SQL-Server function. The SqlFunctions class allows to call SQL-Server function from linq queries while using EntityFramwork.

Following describes how it works
Create Edmx file i.e EntityFramwork file

 Create connection with database

Select Table(s),view(s) and Stored procedure(s)

Created EDMX file

Use SqlFunction in query
Now after deisigning the enityframwork edmx file following is way to utilize the inbuilt sql server functions in Linq queries.

public List<person> SqlTest()
        {
            using (SchoolEntities se = new SchoolEntities())
            {
                var person = from p in se.People
                             where SqlFunctions.CharIndex("a", p.FirstName) == 1
                             select p;
                return person.ToList<person>();
            }
        }

As you see in above linq query its find out all persons whose name starts with letter "a". This is easily achievable by CharIndex function of sql server as you can see in query. This function is part of SqlFunction class avilable in .net framework.

SqlFunction class inside

#region Assembly System.Data.Entity.dll, v4.0.30319
// C:\Program Files\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\Profile\Client\System.Data.Entity.dll
#endregion

using System;
using System.Collections.Generic;
using System.Data.Objects.DataClasses;

namespace System.Data.Objects.SqlClient
{
 // Summary:
    //     Provides common language runtime (CLR) methods that call functions in the
    //     database in LINQ to Entities queries.
    public static class SqlFunctions
    {
        ........list of other function that is supported by this class
        //
        // Summary:
        //     Returns the starting position of one expression found within another expression.
        //
        // Parameters:
        //   toSearch:
        //     The string expression to be searched.
        //
        //   target:
        //     The string expression to be found.
        //
        //   startLocation:
        //     The character position in toSearch where searching begins.
        //
        // Returns:
        //     The starting position of target if it is found in toSearch.
        [EdmFunction("SqlServer", "CHARINDEX")]
        public static int? CharIndex(string toSearch, string target, int? startLocation)
    }
}

As you can see SqlFuction is static class and contains static methods which calls sql server inbuilt function.
Get more information about SqlFunction class and its method on msdn at : SqlFunction