Call Sql Server inbuilt functions using LinqFollowing 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