User Id :    Password :      New Member   Forgot Password  
 
Call Sql Server inbuilt functions using Linq
Description 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.   No. of Views     3650
  Rating     0
Author Pranay Rana   Posted On     21 Aug 2012
Tags LINQ,LINQ to Database,SQL SERVER,LINQ : How To,SQL SERVER : How To    

Sample Code   Download Code

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

 

About Author

About Author Hey, I am Pranay Rana, working as a Senior Software engineer. Web development in Asp.Net with C# and MS sql server are the experience tools that I have had for the past 5 years now.
For me def. of programming is : Programming is something that you do once and that get used by multiple for many years
You can visit me on my blog - http://pranayamr.blogspot.com/
Pranay Rana
No Photo
 
Country India
Company GMind Solutions
Home Page http://pranayamr.blogspot.com/

Rate this article

Rating options from poor, fair, good, very good to excelent.  
 

Comments

   
Write your comment here.
Comment
Verification Code