Is it possible to use buillt-in sql functions like user_name() in a LINQ query? If not, can I use something else?
2 Answers
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.)
4 Comments
RBT
I'm getting error. Here is my code snippet -
DataContext dataContext = new DataContext("conn string"); Table<Customer> customers = dataContext.GetTable<Customer>(); var b = from c in customers where SqlFunctions.CharIndex("r", c.Name) == 0 select c.Name; var v = b.ToString(); This results in following error in last line - Method 'System.Nullable``1[System.Int32] CharIndex(System.String, System.String)' has no supported translation to SQL.
Jon Skeet
@RBT: This looks unrelated to this question, and should be asked in a separate post.
RBT
Thank you for the kind suggestion Jon. I have posted it as a new question here : stackoverflow.com/questions/37814449/…
Zac Faragher
@RBT SqlFunctions methods won't work for Linq to Sql queries. Linq to Sql doesn't know how those methods map to the SQL functions, and that's why it throws that exception.
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)
};
1 Comment
RBT
I'm doing a simple linq to sql using
DataContext class. This approach doesn't involve any EDMX file. How would I use your approach to call my user defined sql server functions?