Using built-in sql functions in a LINQ query?

Question

Is it possible to use buillt-in sql functions like user_name() in a LINQ query? If not, can I use something else?

Answer 1

It depends on the provider. For example, in LINQ to Entities against SQL Server you can use SqlFunctions - which has a UserName method which corresponds to USER_NAME() in Transact-SQL. (There are many other methods and properties. For the current user, you can just use the CurrentUser property, for example.)

Answer 2

EDIT

Extension to @jon Skeet answer

SqlFunctions Class - Provides common language runtime (CLR) methods that call functions in the database in LINQ to Entities queries.

How to use

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    // SqlFunctions.CharIndex is executed in the database.
    var contacts = from c in AWEntities.Contacts
                   where SqlFunctions.CharIndex("Si", c.LastName) == 1
                   select c;

    foreach (var contact in contacts)
    {
        Console.WriteLine(contact.LastName);
    }
}

For : SqlFunctions.UserName Method use SqlFunctions.UserName ()

---

Here is MSDN : How to: Call Custom Database Functions

• Create a custom function in your database.
• Declare a function in the store schema definition language (SSDL) of your .edmx file. The name of the function must be the same as the name of the function declared in the database.
• Add a corresponding method to a class in your application code and apply a EdmFunctionAttribute to the method Note that the NamespaceName and FunctionName parameters of the attribute are the namespace name of the conceptual model and the function name in the conceptual model respectively. Function name resolution for LINQ is case sensitive.
• Call the method in a LINQ to Entities query.

Added Custom function

[EdmFunction("SchoolModel.Store", "AvgStudentGrade")]
public static decimal? AvgStudentGrade(int studentId)
{
    throw new NotSupportedException("Direct calls are not supported.");
}

In Linq query

var students = from s in context.People
                   where s.EnrollmentDate != null
                   select new
                   {
                       name = s.LastName,
                       avgGrade = AvgStudentGrade(s.PersonID)
                   };